Dlookup question 1

[acct98]

I read some of the questions concerning the dlookup function but apparently I'm not getting it.

Problem:
{Using a Form}I'm trying to look up and return the [firstname] of the individual in another table identified by [attendeeid] but I'm not having much success.

Here is the syntax for the "Control Source":

=DLookUp([FirstName],[tbl_B_FamilyMembers],[spouseid]=[AttendeeID])


Any help would be greatly appreciated!

 

[madhouse]

First of all, where are you getting the value for AttendeeID? Is this value from a field on the form?

 

[vb6novice]

acct98,

The Dlookup has to be able to evaluate to a statement, like a SELECT statement, so you usually have to place each of the arguments in a set of quotes, and take the brackets off of the table name. If this was in code it would be:

=DLookUp("[FirstName]","tbl_B_FamilyMembers","[spouseid]='" & [AttendeeID] & "'&quot

But in a text box the syntax is different. Try a few variations of

=DLookUp("[FirstName]","tbl_B_FamilyMembers","[spouseid]=" & [AttendeeID])

 

[acct98]

madhouse: the AttendeeID is on tbl_B_FamilyMembers.

vbdnovice: I tried you suggestion but got the #Error message

 

[madhouse]

Sorry I should have asked where you are also getting the value for spouseid as well?

 

[AppStaff]

Most likely the problem is in the syntax of your criteria. Try it without criteria to help identify the issue. If criteria is the issue then try it with simple criteria. Build your criteria in steps with tests to ensure syntax.

 

[allepa]

Hi,
=DLookUp([FirstName],[tbl_B_FamilyMembers],[spouseid]=[AttendeeID])
= giveme, from, where

What's important here is where your spouseid and attendeeid are located, and how they're linked. You're referencing both, so we need to understand where both are kept. is one a member of your familymembers and one a member of your form (that's displayed or refernced on the form) ?

 

[acct98]

madhouse

Form Table (pulling Data) Field
frm_Main_Family tbl_A_FamilyMembers SpouseID


I would like to do the vlookup / retieve information i.e FIRSTNAME from tbl_B_FamilyMembers
The SpouseID is the same as the AttendeeID which is a field on tbl_B_FamilyMembers

 

[madhouse]

This isn't going to work as far as I can see. Either the SpouseID or AttendeeID needs to be referenced on the form, otherwise the DLookUp method will just try and find the first occurence of where the SpouseID = AttendeeID. As allepa explained previously, the DLookUp method will try and find a value like this:

DLookUp(Field, From Table, Where Criteria)

For the Where Criteria, the DLookUp method needs to be able to use a value to search for the Field. In your case, you are asking the DLookUp statement to try and find a Field but are not giving it any values to use for the Where Criteria.

Hope that makes sense.

 

[acct98]

madhouse and allepa,

Spouce Id is referenced on the form.

 

[madhouse]

If SpouseID is referenced on the form then you shoul be able to use the following DLookUp statement:

= DLookup("FirstName", "tbl_B_FamilyMembers", "AtendeeID = " & Me.SpouseID)

 

[acct98]

I tried that and I'm Getting the "#Name?" Error. I understand what you and othere are saying but I not sure why its not working.

 

[acct98]

Does anyone have any idea why i'm receiving this error?

 

[madhouse]

If you like, send me the database and I'll take a look - markaustin@totalise.co.uk.

 

[acct98]

Problem:
{Using a Form}I'm trying to look up and return the [firstname] of the individual in another table identified by [attendeeid] but I'm not having much success.

Here is the syntax for the "Control Source":

= DLookup("FirstName", "tbl_B_FamilyMembers", "AtendeeID = " & Me.SpouseID)

 

[AppStaff]

Try This. if doesnt work then do this.

verify field name in function
verify table name in function
verify control name on YourFormName for SpouseID.
verify that your table mis-spells attendeeID as your function syntax does.

Here is the syntax for the "Control Source" assuming that SpouseID is on a main form and not a subform:

= DLookup("FirstName", "tbl_B_FamilyMembers", "AtendeeID = Forms![YourFormName]!SpouseID&quot

 

[DseeDm]

Hi,

I re-created your problem, even got the "#Name?" error

This worked for me:

= DLookup ("[FirstName]", "tbl_B_FamilyMembers",
"[AtendeeID] = " & [SpouseID])

where FirstName and AtendeeID are
fields in tbl_B_FamilyMembers

and SpouseID is on the form...

Good luck!
- DCD