I can get my Dlookup fields to work fine when I view the subform by itself, but not within the Main form. When I open the main form, the fields in question all show #Name?

The Record Source for each field is in the format: =DLookUp("[subtableField]","Automobiles","[DeviceID] = " & [Forms]![Subform for Devices]![DeviceID])

Where DeviceID (numerical) is the linking field (the two tables have a one-one relationship due to only a fraction of the devices having this extra data). I spent three days last week wrestling with Dlookup and finally thought I had it figured out. Where am I going offbase?

TIA
David R

[This message has been edited by David R (edited 12-10-2001).]

=DLookUp("[FieldNameInAutomobilesTable]","Automobiles","[DeviceID] = " & Me![DeviceID])

I am assuming that DeviceID is also a field on your Main form.

No, although I did have to add it as a hidden field on the subform. I suppose I have to do that on the main form as well? How would I do so? I seem to be going in circles with Dlookups. DeviceID is not a field on the Main Form's source table, so I would have to add it with Dlookup, right? How would it know which device I want to look at? (Each participant can have multiple devices).

Main Form links to Devices subform via ParticipantID (PK). Devices subform pulls details from Automobiles table via Dlookups based on DeviceID (PK of Devices table, long integer of Automobiles table). Is there a better way?

[This message has been edited by David R (edited 12-10-2001).]

I am really not clear on how you have your form/subform set up but if the DeviceID is a field on your subform then try this:

=DLookUp("[FieldNameInAutomobilesTable]","Automobiles","[DeviceID] = " & Me![SubformName].[Form]![DeviceID])

Be sure you have the correct subform name...

[This message has been edited by Jack Cowley (edited 12-10-2001).]

Sorry Jack that didn't work. is [Form] supposed to be the name of the main form?
I've never used Dlookup before and it looks like now is not the time to start. The entire point of this endeavour was to be able to _edit_ the information in the Automobiles table, not just view it. Dlookup only gives locked information however, which isn't useful.

Unfortunately the database is a normalization-work-in-progress, Jack, but I can try to explain how it is linked together.

Tables:
Participants, PK: ParticipantID. Holds the demographic information for the person in question.
Devices, PK: DeviceID. Linked Field: ParticipantID, one-to-many relationship. Holds the information on what devices the person picked up from us.
Automobiles, PK: AutomobileID. Linked Field: DeviceID, one-to-one relationship. Holds vehicle information for a subset of devices "Car Clubs". A person may (rarely) have more than one Car Club, which is why I moved this off of the Participants table.

Forms:
Participant Information, the main data entry screen. It has a subform (added with the wizard)...
Subform for Devices, which has always worked beautifully. This subform is in Continuous Form view because generally the list of devices will fit in the space allotted (usually less than 1 or 2, sometimes 5 or more). I am trying to add fields tied to when the DeviceName field = "Car Club". I cannot 'nest' another subform (the obvious solution) because Continuous Forms cannot have subforms.

If it helps, I can send a blank copy of the test database with just structure and a few dummy records.

I am much better in a 'hands-on' situation so send me your sample db and let me see if I can work out a solution....

Jack

For future reference, the problem was solved with changing the recordsource to an updatable query containing fields from both the Devices and Automobiles tables. Dlookup wasn't needed in this case at all.