dlookup looking up the wrong thing! 1

[mrtauntaun]

Hi. I am having trouble with dlookup, I am phrasing it as followings:

Forms!Forma!ReturnedField = dlookup ("Forms!FormA![Field Name]", "Table Name", "Row=Forms!FormsA!Number&quot

I don't get errors with this code, but it is returning the wrong value. Instead of looking it up, it is returning "Field Name" instead of using it as part of the lookup. Any thoughts?

 

[JeremyNYC]

I'm assuming you replaced the actual values with dummy values here. It's sort of hard to tell what's going on. You should not have a form's control in the first parameter--that should be the name of a field in the table.


And you have to leave the control out of the quotes in the third parameter, so that access passes the correct value to Jet.

Forms!Forma!ReturnedField = dlookup ("[Name of some Field in Your Table]", "Table Name", "NameOfSomeOTHERfieldInThatTable = " Forms!FormsA!NameOfSomeControlOnYourForm)

If the second field in the table is text, you'll have to use single quotes around that parameter. If it's a date, use #s. Here's and example with #s:

Forms!Forma!ReturnedField = dlookup ("[Name of some Field in Your Table]", "Table Name", "NameOfSomeOTHERfieldInThatTable = #" & Forms!FormsA!NameOfSomeControlOnYourForm & "#&quot

All that said, DLookup is a REALLY slow way to go about this. Far better would be to use a recordset to get that datum. It's more code to write, but it runs much more quickly.

Jeremy


==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[briandh]

I could be wrong, but I think one of the problems could be your last argument of the DLookup function.

Instead of "Row=Forms!FormsA!Number"
Try: "Forms!FormsA!Number=Row"

Forms!Forma!ReturnedField = dlookup ("Forms!FormA![Field Name]", "Table Name", "Forms!FormsA!Number=Row&quot

Maybe I'm way off, but you could try that and see if it works.

 

[mrtauntaun]

You're right, sorry, I'll use the real values here:

Table to be looked up: Skills
Column to be looked up varies depending on input:
Forms!Generator![Initial Class]
Row to look up: Skill
Skill defined as: Forms!Generator!z

I had the code thusly:

dlookup ("Forms!Generator![Initial Class]", "Skills", "Skill=Forms!Generator!z&quot

Lookup and return value in Column "[Initial Class]" Row "Z"

Any thoughts? I did try your thought, Brian, however is still returned the same incorrect value.

 

[JeremyNYC]

You're not using the dlookup correctly. Does InitialClass select from the field names in the table "Skills"? If so, get rid of the quotes.

You'll have to get the reference to the control outside of the quotes in the third parameter.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[mrtauntaun]

That did it, thanx!