Run Query from Form with Combo Boxes (1 Viewer)

[MarcieFess]

I have a form with combo boxes that works beautifully, but I've been asked to add another feature to it. It requires adding a button that runs a query and displays the query results on the screen.

The query code is:

SELECT DISTINCT Product.MSDS
FROM Product INNER JOIN tblStoreProducts ON Product.[ProductKey] = tblStoreProducts.[ProductKey]
WHERE (((tblStoreProducts.MaxUnits)<>0) AND (([Product.HazardKey])<>79))
GROUP BY Product.MSDS, tblStoreProducts.StoreKey
HAVING (((Product.MSDS) Is Not Null))
ORDER BY Product.MSDS;

One of the existing buttons on the form has this code behind it:

'------------------------------------------------------------
' btnHMIS_Click
'
'------------------------------------------------------------
Private Sub btnHMIS_Click()
On Error GoTo Err
If IsNull(Me.cboCompany) Then
    MsgBox ("Choose a Company!")
    Me.cboCompany.SetFocus
    Exit Sub
End If

If IsNull(Me.cboStore) Then
    MsgBox ("Choose a Store!")
    Me.cboStore.SetFocus
    Exit Sub
End If
    
DoCmd.OpenReport "rptHMISReport", acViewPreview, , "[StoreKey] = " & Me.cboStore

ExitS:
    DoCmd.Echo True
    Exit Sub
    
Err:
'this error gets thrown if there is no data in the report AND the report must have the On No Data property set to CANCEL = True in the code window
'since you are going to filter the report now - if the user chooses a company or store that doesn't have records the report needs to be able to return this
If Err.Number = 2501 Then
    MsgBox ("No records exist for this store/company!")
Else
MsgBox Error$
End If

End Sub

As you can see, the button is able to pass the parameters (which Company, and which Store within the company) to the report.

I need to be able to limit the results of the query to the same store.

Here's what I have (and it's not working):

'------------------------------------------------------------
' btnMSDSSheetsPrint_Click
'
'------------------------------------------------------------
Private Sub btnMSDSSheetsPrint_Click()
On Error GoTo btnMSDSSheetsPrint_Click_Err

If IsNull(Me.cboCompany) Then
    MsgBox ("Choose a Company!")
    Me.cboCompany.SetFocus
    Exit Sub
End If

If IsNull(Me.cboStore) Then
    MsgBox ("Choose a Store!")
    Me.cboStore.SetFocus
    Exit Sub
End If

    DoCmd.OpenQuery "qryMSDSPrint", acViewNormal, "[StoreKey] = " & Me.cboStore

ExitS:
    DoCmd.Echo True
    Exit Sub
    
btnMSDSSheetsPrint_Click_Exit:
    Exit Sub

btnMSDSSheetsPrint_Click_Err:
    If Err.Number = 2501 Then
  MsgBox ("No records exist for this store/company!")
Else
MsgBox Error$
End If
    Resume btnMSDSSheetsPrint_Click_Exit

End Sub

I'm getting a Type Mismatch error. It's been almost a year since I've even looked at this code, and some of it I didn't understand even then (I just tried suggestions until it worked).

I'm sure the issue is in the line

DoCmd.OpenQuery "qryMSDSPrint", acViewNormal, "[StoreKey] = " & Me.cboStore

How do I pass the StoreKey information into the query? Is it my query that's wrong?

Marcie

 

[pbaldy]

OpenQuery doesn't have the wherecondition argument like OpenForm and OpenReport. I would use one of those, as I never expose queries or tables directly to users.

 

[MarcieFess]

I solved this! Just in case this will help anyone else, here's what I did.

In the query, I set the Criteria for the field in question (in my case, the field was StoreKey) to [Forms]![frmChooseStore].cboStore

[frmChooseStore] is the name of the form that contains the combo box with the query-limiting information.

cboStore is the name of the combo box on the form in question.

My query code is:

SELECT DISTINCT Product.MSDS, tblStoreProducts.StoreKey

FROM tblStoreInformation 
      INNER JOIN (Product INNER JOIN tblStoreProducts ON Product.[ProductKey] = tblStoreProducts.[ProductKey]) 

      ON tblStoreInformation.StoreKey = tblStoreProducts.StoreKey

WHERE (((tblStoreProducts.MaxUnits)<>0) AND (([Product.HazardKey])<>79))

GROUP BY Product.MSDS, tblStoreProducts.StoreKey

HAVING (((Product.MSDS) Is Not Null) And ((tblStoreProducts.StoreKey)=Forms!frmChooseStore.cboStore))

ORDER BY Product.MSDS;

It works perfectly. I got the tip from a video tutorial I found by Datapig Technologies.

http://datapigtechnologies.com/flashfiles/searchform.html

(hope it's OK to list the URL here. It was very helpful for me, just that one statement out of the 10-minute video).

Marcie