Help with my database - dlookup

[richardk213]

Hello all, I am working on a metering fault database (for electricity, gas and water meters), to help us track and record all faults. I have created my first access database (after much trying) and have hit an issue. The database currently consists of two tables (Meter Details) and (Faults), and an input form (Meter Fault Form). Meter Details contains all the useful meter information (Meter_ID, Building, Alias, etc) and Faults will contain the Meter Id, the type of fault, comments and who raised the issue.

The form is linked to the Fault table, and there is a linked text box for Meter ID, Building, Date and Actioned By. The idea is that the user will fill these in and it is saved to the Fault Table. This bit works ok. I have also included several text boxes with Dlookups to bring up meter information (building, alias, etc) when the user enters the meter ID in the Meter ID box.
For the dlookup for the building name I have used
DLookUp("[Building]","Meter Details","[Meter_ID] = '" & [Forms]![Meter Fault Form]![Meter_ID] & "'")
This results in a blank text box. However if I create a new text box, and enter the meter id, it works (using the code below)


=DLookUp("[Building]","Meter Details","[Meter_ID] = '" & [Forms]![Meter Fault Form]![text101] & "'")
It looks like the issue lies with how I am referencing Meter_ID in the Dlookup. Any clues?

Thanks

(i have added in my database)

[June7]

The first is referencing field name, the second is referencing textbox name.

Why don't you use a multi-column combobox for user to enter/select Meter_ID?

Then instead of DLookup() expressions in textbox, reference combobox columns to display the related info. This should perform faster than DLookup()s.

Another method is to include MeterDetails table in the form RecordSource (join type 'include all records from Faults ...') then bind textboxes to meter info fields and set textboxes as Locked Yes, TabStop No to prevent edits.

[richardk213]

The first is referencing field name, the second is referencing textbox name.

Why don't you use a multi-column combobox for user to enter/select Meter_ID?

Then instead of DLookup() expressions in textbox, reference combobox columns to display the related info. This should perform faster than DLookup()s.

Another method is to include MeterDetails table in the form RecordSource (join type 'include all records from Faults ...') then bind textboxes to meter info fields and set textboxes as Locked Yes, TabStop No to prevent edits.

Hi thanks for the reply.

The first is referencing field name, the second is referencing textbox name. Is there a way of getting this to work?

I have just looked at combobox, but it doesnt seem to like it, as it only gives ID as the available data, nothing else (but i do like this idea)

Another method is to include MeterDetails table in the form RecordSource (join type 'include all records from Faults ...') then bind textboxes to meter info fields and set textboxes as Locked Yes, TabStop No to prevent edits.
I followed you right up to "form"....

I should point out that this is the first time i have used access!

[June7]

A combobox can have multiple columns so it certainly can show multiple data in the dropdown list. However, only the Meter_ID would be saved.

Have you completed an introductory tutorial book? Everything I suggested is really quite basic Access functionality.