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:
Please be gentle with me, I have been a long time DB user, but have not ventured into coding before
Thank you
Puffs
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:
Code:
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