How do I get a Dlookup result to display within another textbox on an Access database?

Question

I have created on a database a Dlookup function to change the email address of a manager whenever the managers area is selected. It works fine but now that the control source is the Dlookup it doesn't save the results any more in the personal table.

I read up on http://p2p.wrox.com/access-vba/77907-how-save-results-dlookup-function.html a method to have a separate hidden box which displays the results from the table, which works but my trouble is now connecting the Dlookup result to the other text box.

I obviously cant control source the Dlookup result so I've instead tried to make it an before update event using the following code;

Option Compare Database

Private Sub ASMail_AfterUpdate()
ASMEmail.Value = ASMail.Value
End Sub

However this has not taken effect at all. The textbox does not change whenever I adjust the Dlookup results, and I've tried the same code in the other Change event which didn't work either.

Kostas K. Kostas K. · Accepted Answer · 2017-06-05T11:20:43

You don't need a separate hidden textbox.

Leave the textbox control source of the manager's email linked to the personal table field and simply update the textbox value to the DLookup value when the manager area is selected.

I don't know how the manager area is selected, but as a combobox example, it would be like this:

Private Sub Combo_AfterUpdate()
    Me.ASMEmail.Value = Nz(DLookup("Value", "Domain", "Criteria"), vbNullString)
End Sub

How do I get a Dlookup result to display within another textbox on an Access database?

1 Answers