SQL Criteria from Multiple Controls (1 Viewer)

[Zydeceltico]

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

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]

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.

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:] ) );

 

[Uncle Gizmo]

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.

There's a technique I use occasionally at the bottom of this webpage:- http://www.niftyaccess.com/combo-filter-all-or-some/

However it may not be applicable to your particular case.

There are three main problems when you are building SQL Statements that draw criteria from controls.

The first is if there is nothing selected in the controls then you want to return all of the records. You handle this requirement by providing a select SQL Statement that returns all of the records in the form of:-

SELECT FROM

Now if a single control is selected then you need to add a WHERE clause and the CRITERIA from that control.

SELECT FROM WHERE CRITERIA

If more than one control is selected then you need to add both the WHERE clause and an AND clause.

SELECT FROM WHERE CRITERIA AND CRITERIA

That's the 3 basic scenarios. Selecting more criteria is just adding an extra AND condition on the end of the SQL statement.

On the face of it it sounds a daunting task to build VBA code to put the SQL together like that, but actually once you get started it just sort of falls into place.

I demonstrate the building of such an SQL Statement on my Nifty Access website on a page called Building Search Criteria ......

http://www.niftyaccess.com/building-search-criteria/

Sent from my SM-G925F using Tapatalk

 

[Zydeceltico]

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.

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]

Which form to look at?

 

[Zydeceltico]

Which form to look at?

frmInspectionEvent

The db should have opened to it.