Dlookup Default Value

[Locopete99]

Hi Guys.

I have a form and 2 fields are related

1st is a Responsible field which the user selects the user that is responsible for the task. This is a look up value from Tbl_User.

Once the user selects the responsible user the form stores the ID.

I then have an initials field. This should look up the responsible fields user in from the Tbl_Users field. I need the initials to add to the ID to make the enquiry number - ####XX

I'm using the following for the default field but its not working. Can someone advise?

=DLookUp("[Initials","Frm_User",[ID]=[responsible])

 

[Minty]

Much easier to bring in the initials as a second hidden column in the combo for the user, and then set the value from the combo box, in it's after update event.

= YourCombo.Column(2)

Column Numbering starts at 0 so I have assumed the first column os the ID and the second column (displayed) is the User Name, meaning you add the initials as the third hidden column.

 

[Locopete99]

Hi Minty,

Doesn't seem to like it. I'm not on top form today though as I'm dealing with an illness.

So my initials are in Combo35.Column(3)

I've then tried in the after update ebent on the combo box:

Me.initials = Combo35.Column(3)

No luck.

 

[Minty]

Try adding Me.

Me.initials = Me.Combo35.Column(3)

As I thought you were doing this in the control not VBA . Soz.

 

[arnelgp]

remove the default value on theOtherField.
on the AfterUpdate event of [responsible] field:

Private Sub responsible_AfterUpdate()
Me.theOtherField=DLookUp("[Initials","tbl_User","[ID]=" & Me![responsible])
End Sub

 

[Locopete99]

Thannks thats worked!

 

[Locopete99]

Thanks Pat, but I have already thought about this and have it under control