Dlookup error

[mborg]

Hi,
I have the following dlookup formula in a field in a form:
=DLookUp("[ID]","[HRMS]","[E-mail]=" & [E-Mail Address])

I am trying to populate the ID from the HRMS Table, where E-Mail(from HRMS table) = E-maill address on the form...

Thanks.

 

[Joe4]

Try adding text qualifiers around your email address, i.e.

[COLOR=#333333]=DLookUp("[ID]","[HRMS]","[E-mail]='" & [E-Mail Address] & "'")[/COLOR]

 

[mborg]

That worked! Thanks for the tip.

 

[Joe4]

You are welcome!

 

[mborg]

You are welcome!

Is there any way to make the field editable after the lookup. Let's say the user enters an email address, but for some reason they want to change the ID field.

Essentially, I have a bunch of fields that will hinge on the email address. I added the lookup formulas to all of those, but would also like the user to be able to override..

 

[Joe4]

Calculated fields are not editable. The underlying fields in the table would be.

If you want the values to be editable, perhaps take a look at a Form/Subform relationship between your two tables.
There, you can supply the underlying fields from both tables (one table in the Form, the other in the Subform), and they should be editable.
There are lots of tutorials on how to create Subforms in Access, if you are unfamiliar with that. Just do a Google Search. I like YouTube tutorials myself, as I am a very visual person.

 

[mborg]

So, I am running into another issue. I rarely use Access, so your help is hugely appreciated.

In the form, i enter an email address which populates several fields, one being the employee ID which is a primary field. When I try to move forward in the form, I get an error message that no primary key is found or null. something to that effect. When I take out the Dlookup function and change control source to Employee ID, the table updates correctly and I can move forward. Any ideas?? I am hoping this is just a quick fix.. It was working fine before with dlookup, but i must have done something. Many thanks!

 

[Joe4]

It is a bit difficult to understand the full nature of the problem without having a complete understanding of your database structure.

However, primary key field cannot be calculated fields (i.e. they cannot de DLOOKUP calculations).
They CAN be calculated by a DLOOKUP, but then the value would have to be hard-coded into the field as a value (something like that could be done via a DLOOKUP in VBA).