Text Box Default Value (Form in Edit Mode)

[RiskIt]

Hi,

I am currently making a database which has a access front end and SQL back end, Here is some background info and the setup I have.

I have one form that enters data into the database but on that form it has a users box (Who put the item in the database), This box pulls the username from the active directory domain using this VBA Module:

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If



Then in the form I have a text box which has the default value of: =DLookUp("USER_ID","USER_ID_FROM_SYSTEM_QRY")

This is the SQL code behind the "USER_ID_FROM_SYSTEM_QRY":

SELECT dbo_USER.USER_ID, dbo_USER.USER_NAME, dbo_USER.USER_LOGIN
FROM dbo_USER
WHERE dbo_USER.USER_ID = (SELECT dbo_USER.USER_ID FROM dbo_USER WHERE dbo_USER.USER_LOGIN =fOSUserName() );

Now this works fine I face the issue of, A second user will then edit this item further and I have a second user box for this part which is a separate column in the table, So We know who edited the item in part 2 of the items data in the database.

If I open the form in normal data entry mode this box above works for the second user BUT if I open the same form in edit mode (as the second user is editing the the record the first user has created) then this box does not work and remain blank.

Hopefully the above makes sense and any help will be great.

[ranman256]

the 2nd user box should fill in in the FORM AFTERUPDATE event.

[RiskIt]

Hi,

Thank you for taking the time to help, I have tried with the DLookUp in the FORM AFTERUPDATE event but this box still remains empty.

As a side note I have checked the QRY and this does run and show the current user.

**Edit: If I paste the DLookUp into a text box this does pull up the user that is editing the record (This doesnt save to the table) I have also made a new text box set it up and still displays nothing. Is this because the form is now in edit mode? (Its the only difference I can see)

Thanks

[Micron]

You've got code, so why isn't the lookup there instead of the form? Ought to work for any case then.
What happens when/if you want to edit a second time and track that? Add another edityBy field to table/forms/reports? Can you see a problem with normalization here?

[RiskIt]

Hi,

I am not sure I follow your comment, So the first user has their box for who created the entry and then later when the item is then edited and more data is added the second user has their own field again. The first persons field is working fine auto filling with the correct details that is returned from the DLookUp but the second users just doesn't show any results and the thing I can see is the second user is revising the entry and the form displays in edit mode.

[Micron]

You know it's an edit somehow because you're opening the form in edit mode (I think). So your code would recognize that fact and simply perform the lookup in code and set the field value. Like

Me.txtEdit2 = DLookUp("USER_ID","USER_ID_FROM_SYSTEM_QRY")

Then you either use update sql to update the Edit2 table field (whatever it's named), or run a stored query that references that form field. In other words, the lookup is done in code, not on the form. Then it shouldn't matter what mode the form is in.

Maybe a bit too advanced for now, but if you're going to be doing this for a lot of user parameters (e.g. email address, first & last names, permission levels, pc id, etc.) consider creating a user object (dbUser) along with those parameters as properties of dbUser object. When you open the db, code gets those properties and assigns them to the user object. Any time you want to know who is doing something, it's dbUser.EmplNo (employee number) or whatever property you want.

Lastly, this isn't entirely clear.

If I paste the DLookUp into a text box this does pull up the user that is editing the record (This doesnt save to the table) I have also made a new text box set it up and still displays nothing.

One works, the other doesn't - but are they on the same form? If yes, you could have a corrupted textbox. Does happen.

[RiskIt]

Hi,

Thank you for your reply.
Okay, I understand. So In this case I am only storing the user so that we know who put the item in the database.

So if I place "=DLookUp("USER_ID","USER_ID_FROM_SYSTEM_QRY") " in an unbound text box on the form it picks up my Domain login (USER_LOGIN) and displays the initials (USER_ID) The issue here is as soon as I bind the text box to the field I need the data stored in it no longer displays anything in the text box.

My apologies, Ill clear that up. I have 2 forms, 1 is for the first user who initially creates the entry in the database and then the second user has their own form.

Hope this clears it up.

Thanks

[thebigthing313]

I think the DefaultValue property only triggers for new records. For the 2nd form (the edit form), DefaultValue will no longer work.
Try progammatically setting the value of the textbox the Form_Load event. Maybe even check if value is null first before setting the value. Would look something like this:

Private Sub Form_Load()
   If IsNull(Me.txtUser2) Then
        Me.txtUser2 = DLookUp("USER_ID","USER_ID_FROM_SYSTEM_QRY")
   End If
End Sub

The IsNull would prevent overwriting the value for that field should another user open the record with the edit form. If you need it to overwrite, just remove the If statement.

[Micron]

Try progammatically setting the value of the textbox

What I've been saying since my first post - move the lookup into code.
I believe you're right about this having to be a new record for default value to take effect. That means form edit mode likely isn't a factor.

While I'm at it, as long as the query will only ever return one record, you can use a lookup on it with no criteria. If the query ever returns more than one record, the lookup value can be anything without criteria. When the domain is a query it's usually the first record. From a table, it could be something other than what you think is the 1st record.

[RiskIt]

Hi,

Thank you all for your help, I think I was misunderstanding but going back over the messages, I now have this working with the DLookUp code in the Form load event.

Thank you very appreciative!