Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2003
    Posts
    48

    Smile Unanswered: How to save a Global Variable as a table field value? Pretty Please!!

    I have created a database with a password login. I would like to stamp each record created by the current user logged in. To do this, I have created a global variable linked to the current username and this value is sent automatically to a textbox field on the record entry form.

    This is the code I use:

    Code:
    Global gUserName As String 'The variable you will hold the user name in
    
    Function Get_UserName() As String
    Get_UserName = gUserName
    End Function
    
    Sub Set_UserName(strUserName As String)
    gUserName = strUserName
    End Sub
    I use this code to identify the variable in the form:

    Code:
    Private Sub Form_Current()
    If Me.NewRecord Then
        txtUser.Value = Get_UserName()
        txtUser = txtUser.Value
    HOWEVER...

    When I save the record (using a SAVE command button) all the other fields values are saved to the table except this USER field value.

    Can anyone advise please? My head is ready to explode any time now!!!

    Cheers,

    Luke

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Does the txtUser textbox display the correct data (user name) when you
    move to a new record?

    Is the txtUser textbox bound to the txtUser field in your table? If so,
    the txtUser = txtUser.Value line in your code isn't necessary.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Aug 2003
    Posts
    48
    Hi there RNG!!

    Yes it does seem to appear on the next record without that line (so I've snipped it off now!)

    Do you by chance know how I can define the value of the username so that it will save to a table record field?

    It just seems to 'float' there in the form textbox for cosmetic value whilst you enter the form data, but without actually being saved with the rest of the record.

    I have a feeling it is something to do with the fact that the variable is originally based somewhere else, and although I am calling the variable into the textbox, it is only displayed there 'passively', and not 'actively' to be saved as a 'solid' value.

    Is there some code I can write to copy or save the value of the variable so that it can be saved to the record in the table?

    This one has really got me puzzled, I'm not very experienced with VBA, but it seems like it should be relatively simple to fix!!

    Cheers for the pointer!!

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Luke,

    Since the value is showing up on the form as you expect it to, then that
    says that you don't have a problem with your global variable. For some reason, the value is not being sent to the table along with the rest of your data. Is there any way to post your form and some data so that I
    can take a look at it?

    Mark
    Inspiration Through Fermentation

  5. #5
    Join Date
    Aug 2003
    Posts
    48
    Hi again!

    Since I last posted, I've had a chance to recheck my code and I think it was a "Red Herring". My value was coming from some other code I have put in!!!

    I can't post the actual file due to the (harsh IMHO) restrictions on the PCs here...

    ...However, I can provide the significant part of the code for your perusal:


    Code:
    Function VALIDATE_SIGNIN()
    On Error GoTo VALIDATE_SIGNIN_Err
    
    If IsNull([Forms]![SIGN IN]![USERLU]) Or IsNull([Forms]![SIGN IN]![PSWRDLU]) Then
    MsgBox "You must enter both a user name and password.", vbCritical, "Error!"
    GoTo VALIDATE_SIGNIN_EXIT
    Else
    
    Dim ACTIVEUSER As String, ACTIVEPASSWRD As String
    ACTIVEUSER = [Forms]![SIGN IN]![USERLU]
    ACTIVEPASSWRD = [Forms]![SIGN IN]![PSWRDLU]
    
    
    If IsNull(DLookup("[USER]", "USERS", "[USER]=" & "'" & ACTIVEUSER & "'")) Then
    MsgBox ACTIVEUSER & " is not a valid user name.  Please contact the system administrator.", vbCritical, "Error!"
    GoTo VALIDATE_SIGNIN_EXIT
    Else
    
    If DLookup("[PSWRD]", "USERS", "[USER]=" & "'" & ACTIVEUSER & "'") <> ACTIVEPASSWRD Then
    MsgBox "The password / user name combination that you have entered is not valid.  Please contact the system administrator.", vbCritical, "Error!"
    GoTo VALIDATE_SIGNIN_EXIT
    Else
    
    DoCmd.Close acForm, "SIGN IN", acSavePrompt
    DoCmd.OpenForm "USERS", acNormal, , , acFormEdit
    [Forms]![USERS]![CURRENTUSER] = ACTIVEUSER
    [Forms]![USERS]![Text34] = ACTIVEUSER
    Application.Run "SECURITY_VIS"
    MsgBox "Welcome " & DLookup("[FIRST]", "USERS", "[USER]=" & "'" & ACTIVEUSER & "'") & " " & DLookup("[LAST]", "USERS", "[USER]=" & "'" & ACTIVEUSER & "'") & "!", vbInformation, "Welcome!"
    
    
    
    End If
    End If
    End If
    
    VALIDATE_SIGNIN_EXIT:
        Exit Function
    
    VALIDATE_SIGNIN_Err:
        MsgBox Error$
        Resume VALIDATE_SIGNIN_EXIT
    
    End Function
    The ACTIVEUSER variable seems to be the key as it is essentially the user's Login ID.

    Is there a way of setting up ACTIVEUSER as a Global Variable in it's own module that can then be automatically designated to various form fields as a value to then "Stamp" in a 'USERID' field on records created in a table?

    Once ACTIVEUSER is set up as a Global Variable, can you explain how and when I would go about actually calling up this information to get it into the Form object fields in question.

    If you can answer these questions, I would be ETERNALLY grateful as this problem has been holding me back for a couple of days now and it's driving me crazy!

    Cheers,

    Luke
    Last edited by Lukasx; 09-02-03 at 12:17.

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    To create ACTIVEUSER as a Global variable, so that it maintains it's value as long as the app is running....

    Go into any standard module, (or start a new one), and in the top section, before any Subs or Functions, put...
    Global activeuser As String

    Then remove any Dim statements for activeuser in any functions, as this will take precedence over the global declaration.

    In the code below... Does the Users form need to be open for some reason, or are you just trying to use that to add information to a table?
    Inspiration Through Fermentation

  7. #7
    Join Date
    Aug 2003
    Posts
    48
    Thanks for that!

    The "Users" form doesn't need to be open, I was indeed trying to transfer the ACTIVEUSER value that was being sent to one of the fields on the "Users" form to a locked field in a "CreateRecord" form. I would then set the "CreateRecord" form to save that value along with all the manually User-entered field values to a record created on a "Records" table.

    The whole point is...

    The security of the database (though not particularly tight) is based on a record-level (each user having access to log-in and edit their own records only). Therefore I must include a field on all the records created by a particular user with a UserID stamp, the value of which is taken from the Login name when the user logs in with their Login name and password.

    That's what I'm TRYING to do anyway!!

    The trick now I think is to somehow get the value of that ACTIVEUSER variable and set my new Global variable to the same value so it can start working it's magic on the other forms!!

    Can you advise me on what code I can use to accomplish this?

    I am really looking forward to making this work!!!

    Cheers,

    Luke

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You were on the right track with your first post. In each form's OnCurrent event:

    ...
    If Me.NewRecord then
    txtUser = ACTIVEUSER
    ...

    That SHOULD populate the User Name on the form, and the field that
    it's bound to on the table.

    Mark
    Inspiration Through Fermentation

  9. #9
    Join Date
    Aug 2003
    Posts
    48
    Hi Mark,

    I already had this bit of code in place.

    When I set the Table field required property to true and try to create a record I get the following error message:


    "The field 'REPORTS.User' cannot contain a null value because the required property for this field is set to True. Enter a value in this field."

    This confirms that nothing is getting to the table.

    I have no idea why this would be!

    Any thoughts?

  10. #10
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I've tried to recreate this problem, but everything works as expected.
    You said earlier that you cannot post your file here due to security restrictions. Can I assume that you wouldn't be able to pull a sample file that I would post here? What if I emailed it to you?
    Inspiration Through Fermentation

  11. #11
    Join Date
    Aug 2003
    Posts
    48
    Hi there Mark!!

    I could see that what you had suggested 'should' be working, so I decided to 'purge' the records form and replace it with a fresh one generated with the "Form Wizard".

    AND NOW IT WORKS!!!!

    I must have left something on the original form that was preventing it from functioning correctly!!

    I am soooo grateful for your help on this, it really has been holding me back! I'm not sure I'd have been confident enough in my own coding to redo the form, but I could see that your solution should be working and so I didn't hesitate.

    Thanks so much for your help on this, you really made my day!

    Cheers,

    Luke

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •