Solved DLookUp in lieu of cascading combo boxes? (1 Viewer)

[Puff]

Good morning all, I have a test DB where I am trying to replace 3 cascading combo boxes with just one combo and 2 text boxes.

The DB has one table: tbl_Part with 2 fields; Part_Number (short txt) and Mod_Number (number). One Form: frm_Mod which has 3 comboxes: cboPart, cboMod and cboID

I have 3 queries to return the values needed, (originally 2 but I then needed the record ID to pass to another form for further processing) qryPart, qryMod & qryID.

Once the user has selected the Part_Number combo box (cboPart), there is only one outcome for the other 2, as the second query will return the max value for the Mod_Number and the third returns the ID for that Part / Mod combination.

I could leave it like this, it is functional, but not elegant and runs the risk of the user error. I would like to streamline the process and replace the second and third combo boxes with text boxes if possible?

I have the first of these 2 boxes working correctly with the following set into the Record Source: =DMax("[Mod_Number]","tbl_part","[Part_Number] = '" & [cboPart] & "'")

I have tried to use DLookUp for the third txt box but have run into problems with the syntax for the multiple criteria (if it is possible).

The only success I have had is to use: =DLookUp("[ID]","qryID") but this relies on the second combo box being selected. I imagine I need to reference the output from the first combo box cboPart and the second cboMod, (or the output from the DMax function, but I can't work out how to do this

SQL code for the ID qry:

SELECT tbl_Part.ID, tbl_Part.Mod_Number, tbl_Part.Part_Number
FROM tbl_Part
WHERE (((tbl_Part.Mod_Number)=Forms!frm_Mod!cboMod) And ((tbl_Part.Part_Number)=Forms!frm_Mod!cboPart));

Please be gentle with me, I have been a long time DB user, but have not ventured into coding before

Thank you

Puffs

 

[Gasman]

Appears to be working for me?

 

[bob fitz]

Is this what you require:

 

[Puff]

Hi Bob,

This is exactly what I am looking for, thank you!

 

[bob fitz]

Hi Bob,

This is exactly what I am looking for, thank you!

Your welcome.