Dlookup Default Value (1 Viewer)

Locopete99 · Sep 17, 2018

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?

Code:

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

Minty · Sep 17, 2018

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.

Code:

= 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 · Sep 17, 2018

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:

Code:

Me.initials = Combo35.Column(3)

No luck.

Minty · Sep 17, 2018

Try adding Me.

Code:

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

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

arnelgp · Sep 17, 2018

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 · Sep 17, 2018

Thannks thats worked!

Pat Hartman · Sep 18, 2018

There is no reason to store the initials since you can always obtain them from the user table.

I need the initials to add to the ID to make the enquiry number - ####XX

This doesn't make sense for several reasons. The first being that once you get more than a couple of users, you are very likely to have duplicates if you use only two initials. Even if you use 3, you still might have dupes so you would probably need to use 3 initials + a sequence number to handle two people whose initials are JFK

If you need a compound PK, then you can easily do that by selecting both the columns using either Cntl-click or shift-click and THEN pressing the key icon.

Locopete99 · Sep 18, 2018

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

Dlookup Default Value (1 Viewer)

Locopete99

Registered User.

Minty

AWF VIP

Locopete99

Registered User.

Minty

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

Locopete99

Registered User.

Pat Hartman

Super Moderator

Locopete99

Registered User.

Similar threads

Users who are viewing this thread