Solved Why DLOOKUP is not working??

[jorge_bh]

A very simple example! A CUSTOMERS table, where data to CITY and STATE fields comes from the related tables CITIES and STATES.

I want to define a default value to STATE field, and tried this expression:
=DLookUp("[StateName]","[States]","[StateID]=1")
But I got an error message:

What I'm doing wrong?!

Bellow images of STATES and CITIES tables:

 

[isladogs]

Try without the square brackets. They are superfluous.
=DLookUp("StateName","States","StateID=1")

 

[jorge_bh]

Try without the square brackets. They are superfluous.
=DLookUp("StateName","States","StateID=1")

Thanks, but the same error message. I don't understand why, since everything seems to be right.

 

[jorge_bh]

Hear the database file, to tests.

 

[isladogs]

It worked for me

 

[jorge_bh]

It worked for me
View attachment 96151

On VBA works for me too, but not when I use the expression in the Default Value for the State field. I want to define a default value for it. Maybe I have to use something else there.

 

[isladogs]

It would have helped save a lot of time if you had stated that in the first post!
I don't believe you can use a DLookup in that context.

I got a different error when I tried it!

Entering "CA" would work if it was a text field but yours is a number field so it wouldn't wok anyway.
Try entering 1 instead - it will show CA because you have used a lookup field at table level.

Using lookup fields at table level is a BIG mistake - see Table Lookup Fields - Mendip Data Systems and The Access Web - The Evils of Lookup Fields in Tables (mvps.org)

 

[jorge_bh]

Thanks a lot isladogs, the number works! I'll read the articles.
Do you recommend some books to improve on Access?

 

[jorge_bh]

It would have helped save a lot of time if you had stated that in the first post!
I don't believe you can use a DLookup in that context.

I got a different error when I tried it!
View attachment 96154

Entering "CA" would work if it was a text field but yours is a number field so it wouldn't wok anyway.
Try entering 1 instead - it will show CA because you have used a lookup field at table level.

Using lookup fields at table level is a BIG mistake - see Table Lookup Fields - Mendip Data Systems and The Access Web - The Evils of Lookup Fields in Tables (mvps.org)

Thanks very much!

 

[Pat Hartman]

Do you have a table level lookup? Is that why you thought you needed to have the state name rather than the number? The domain function was returning the state name and you were trying to make that the default for the StateID.

Get rid of the table level lookups. Use combos on your forms. The table level lookups are crutches that will come back and beat you when you least expect it and the more development you do, the harder things are to change.

 

[jorge_bh]

Do you have a table level lookup? Is that why you thought you needed to have the state name rather than the number? The domain function was returning the state name and you were trying to make that the default for the StateID.

Get rid of the table level lookups. Use combos on your forms. The table level lookups are crutches that will come back and beat you when you least expect it and the more development you do, the harder things are to change.

Thanks Pat! Isladogs recommend that I read the article http://www.mendipdatasystems.co.uk/table-lookup-fields/4594445135 and I'll do it! The problem is solved. Just the ID number in the Default Value field did the job.

 

[isladogs]

Thanks Pat! Isladogs recommend that I read the article http://www.mendipdatasystems.co.uk/table-lookup-fields/4594445135 and I'll do it! The problem is solved. Just the ID number in the Default Value field did the job.

In fact I was urging you to get rid of the table level lookups.
Whilst using 1 as a lookup for CA will have solved your immediate issue, doing that is bad practice and will give you endless grief down the line

 

[jorge_bh]

In fact I was urging you to get rid of the table level lookups.
Whilst using 1 as a lookup for CA will have solved your immediate issue, doing that is bad practice and will give you endless grief down the line

Thanks! For this specific database I have more than 4000 registers in a lot of tables. All database was constructed using table levels lookups. To correct everything without messing everything would be a master chess game job! But I'll follow your good advice for the next databases. I'll be studying the article you recommend, and the material in the forum. Access is a wonderful software, and worthy of the best time investment.

 

[isladogs]

Not sure what 'more than 4000 registers in a lot of tables' means.
4000+ records? 4000+ table level lookups?
Whatever you are referring to, it will probably take less time to change it than the additional development time needed if you continue as it is now.

Anyway, good luck with your project

 

[Pat Hartman]

No data needs to change. No forms need to change if they are using combos. You would only have to change queries and not all of them. It depends on whether the query needs to include the text value as well as the numeric value. The most likely queries that would need to change would be those for exports and possibly those used for reports. If you have code that references the lookup field, you may already have encountered issues with it. The "fixes" you made to code would probably need to be undone since a recordset that does not include a lookup field does not need fixing in the first place.

This is actually a trivial change in most cases and has nothing whatsoever to do with the amount of data you have entered.