SQL Criteria from Multiple Controls

Zydeceltico · Apr 6, 2019

Hi All -

I have a form that has cboJob, cboWorkstation, and cboPieceToInspect.

I want to populate cboPieceToInspect by running a query that uses the values selected in cboJob and cboWorkstation as criteria.

I am not sure how to set this up.

I do know that this SQL works when I manually enter ID numbers at the prompts when I run it from the QBE. I just don't know how to tell Access to get the values for the WHEREs from cboJob and cboWorkstation.

Thanks for any insight!

Tim

Code:

SELECT tbljobs.job_id, 
       tblworkstations.workstation_id, 
       tblparts.parttype 
FROM   (tblfinalproducts 
        INNER JOIN tbljobs 
                ON tblfinalproducts.finalproduct_id = tbljobs.finalproduct_fk) 
       INNER JOIN ((tblworkstations 
                    INNER JOIN tblparts 
                            ON tblworkstations.workstation_id = 
              tblparts.workstation_fk) 
                   INNER JOIN tblassemblycomponents 
                           ON tblparts.part_id = tblassemblycomponents.part_fk) 
               ON tblfinalproducts.finalproduct_id = 
                  tblassemblycomponents.finalproduct_fk 
WHERE  ( ( ( tbljobs.job_id ) = [enter job id:] ) 
         AND ( ( tblworkstations.workstation_id ) = [enter ws id:] ) );

Zydeceltico · Apr 6, 2019

And this SQL works even cleaner for getting the correct values for cboPieceToInspect but I still cannot figure out how to get the values from cboFinalProduct and cboWorkstation in to the SQL or really how to set this up.

Code:

SELECT tblfinalproducts.finalproduct_id, 
       tblworkstations.workstation_id, 
       tblparts.parttype 
FROM   tblworkstations 
       INNER JOIN (tblparts 
                   INNER JOIN (tblfinalproducts 
                               INNER JOIN tblassemblycomponents 
                                       ON tblfinalproducts.finalproduct_id = 
                                          tblassemblycomponents.finalproduct_fk) 
                           ON tblparts.part_id = tblassemblycomponents.part_fk) 
               ON tblworkstations.workstation_id = tblparts.workstation_fk 
WHERE  ( ( ( tblfinalproducts.finalproduct_id ) = [enter final product id:] ) 
         AND ( ( tblworkstations.workstation_id ) = [enter workstation id:] ) );

Zydeceltico · Apr 6, 2019

This is beginning to make me nuts.

I've attached the db and it opens to the correct form.

If I make a Job selection and a Workstation selection, the combo box associated with the label "To Be Inspected:" should be populated - but it isn't.

However, if I double click qryPartByFinalProductAndWorkstation in the Navigation pane I get exactly the results I want.

And to make matters weirder - - it actually worked ONE TIME as expected and I don't know why and I didn't change a single thing before or after.

All parts have an associated workstation in tblParts.

I've stripped down the Jobs to just two to make it easier to test.

I can't strip the workstations down because they're too integrated with so much else - so just pick either Mill 8 or Mill 9. One job uses both. The other Job uses only Mill 8 and should return two values.

Like I said - the query works when double clicked after selections have been made.

Code:

SELECT tblfinalproducts.finalproduct_id, 
       tblworkstations.workstation_id, 
       tblparts.parttype 
FROM   tblworkstations 
       INNER JOIN (tblparts 
                   INNER JOIN (tblfinalproducts 
                               INNER JOIN tblassemblycomponents 
                                       ON tblfinalproducts.finalproduct_id = 
                                          tblassemblycomponents.finalproduct_fk) 
                           ON tblparts.part_id = tblassemblycomponents.part_fk) 
               ON tblworkstations.workstation_id = tblparts.workstation_fk 
WHERE  ( ( ( tblfinalproducts.finalproduct_id ) = [forms] ! 
                    [frminspectionevent].[txtFinalProduct_FK] ) 
         AND ( ( tblworkstations.workstation_id ) = [forms] ! 
               [frminspectionevent].[txtWorkstation_FK] ) );

essaytee · Apr 6, 2019

Which form to look at?

Zydeceltico · Apr 6, 2019

essaytee said:
Which form to look at?

frmInspectionEvent

The db should have opened to it.

SQL Criteria from Multiple Controls

Zydeceltico

Registered User.

Zydeceltico

Registered User.

Zydeceltico

Registered User.

Attachments

essaytee

Need a good one-liner.

Zydeceltico

Registered User.

Similar threads

Users who are viewing this thread