filtering using multiple combo boxes

[Dave]

I have 3 tables, PipeSize, Schedule, Componets, Weight.

Using 3 drop down boxes, PipeSize, Schedule, Componets, I want to be able
look up one individual field in the weight table. I have the tables set up:
tblPipeSize
PipeSizeID
PipeSize

tblSchedule
ScheduleID
Schedule

tblComponents
ComponentID
Components

tblWeight
WeightID
PipeSizeID
ScheduleID
ComponetID
Weight

How would I get the individual weight value to display in a text box?

Thanks

 

[Allen Browne]

The ControlSource of your text box will be something like this:
= DLookup("Weight", "tblWeight",
"(PipeSizeID = " & Nz([PipeSizeID],0) &
") AND (ScheduleID = " & Nz([ScheduleID],0) &
") AND (ComponentID = " & Nz([ComponentID],0) & ")")

This assumes the 3 ID fields are all of type Number.
The Nz() avoids the error if one of these is blank.

 

[Dave]

Worked like a charm. Thanks for that.

The ControlSource of your text box will be something like this:
= DLookup("Weight", "tblWeight",
"(PipeSizeID = " & Nz([PipeSizeID],0) &
") AND (ScheduleID = " & Nz([ScheduleID],0) &
") AND (ComponentID = " & Nz([ComponentID],0) & ")")

This assumes the 3 ID fields are all of type Number.
The Nz() avoids the error if one of these is blank.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have 3 tables, PipeSize, Schedule, Componets, Weight.

Using 3 drop down boxes, PipeSize, Schedule, Componets, I want to be able
look up one individual field in the weight table. I have the tables set
up:
tblPipeSize
PipeSizeID
PipeSize

tblSchedule
ScheduleID
Schedule

tblComponents
ComponentID
Components

tblWeight
WeightID
PipeSizeID
ScheduleID
ComponetID
Weight

How would I get the individual weight value to display in a text box?

Thanks