autopopulate field in continuous form

[pbuethe]

I have a continuous form which is based on my main table, tblRequests. IDNbr is the primary key of tblRequests and is an Autonumber. Another field is RequestBy which is a text field which is a user id. tblUsers stores the UserID and UserName. UserID is an Autonumber.

How can I autopopulate a text box with the UserName corresponding to RequestBy for each record? I have tried dLookUp (in Record Source and code) and the best result was that it displayed the UserName for record 1 on every record. I want it to display for each record, the UserName associated with the value of the RequestBy field.

My code is as follows:

Private Sub Form_Open(Cancel As Integer)

Dim varX As Variant
' Text84 is the text box I am trying to
' auto populate
varX = DLookup("UserName", "tblUsers", "UserID =[RequestBy] ")
If (Not IsNull(varX)) Then Me![Text84] = varX

End Sub

I also tried this code in the BeforeUpdate event of the text box.

Thanks in advance for your help.

 

[Rich]

Why not just use a combo box to display the name?

 

[pbuethe]

Thanks Rich, that works, but is there a way to suppress the combo box arrow? This is because the user cannot enter in the form. I have set AllowEdits, etc. to No so that the user can only view the records. Can I take off the arrow so it looks better and saves room on the form? Or is there another method (easier than DLookUp

) to display the names?

 

[Rich]

Paste a rectangle over the arrow, set it's backround colour to white, if that's the backround colour of your form. You can disable/lock the field to prevent edits.

 

[pbuethe]

Thanks Rich, you have helped me again!