Setting parameters to run a Query based on combo boxes in a form ??

[MarcieFess]

I'm not sure if this is the correct section to post this question. If I should have posted it elsewhere, please forgive me.



I have a form that has 2 combo boxes to define which data to use when the user clicks on various buttons. Here's the VBA code for one of the buttons:

'------------------------------------------------------------
' 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

I need to add a button to the form that will run a query to select data using the same parameters from the same combo boxes; this report returns a list of links associated with the store/company.

Here's what I have so far:

'------------------------------------------------------------
' 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

The problem is, I'm getting a Type Mismatch error when I click on this button.

I'm a basic beginner with VBA; most of the code in this project has been obtained from help people have given me, or from snippets I've found in various places online. I think I'm beginning to understand quite a bit of the code, but I'm baffled by this one. I appreciate all the help anyone is willing to give me!

Marcie

[BleedsOrange]

I'm assuming the issue is happening in this line:

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


Seems like there is a data type mismatch between [StoreKey] and cboStore. Worth a shot...... what is the value in cboStore when you click on button? Is it some kind of text? Does that specific value truly exist within the field StoreKey? Is it also text or is it a number.. possibly a primary key (just guessing because it's called StoreKey).

Hope that helps.

[MarcieFess]

I'm assuming the issue is happening in this line:

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


Seems like there is a data type mismatch between [StoreKey] and cboStore. Worth a shot...... what is the value in cboStore when you click on button? Is it some kind of text? Does that specific value truly exist within the field StoreKey? Is it also text or is it a number.. possibly a primary key (just guessing because it's called StoreKey).

Hope that helps.

The user must choose a Company, then in the second combo box they must choose a Store. The value is a number from a Lookup table. That specific value does exist in the field StoreKey. However, I haven't called out StoreKey in the query that's called by the button. Is that where the problem is? When I included StoreKey in the query, it stopped giving me distinct records (running it from the Run command, not from the button on the form).

[Missinglinq]

Your exact problem area is somewhat hazy, to me; probably my fault...been up too long!

But if you're having trouble with

DoCmd.OpenReport "rptHMISReport", acViewPreview, , "[StoreKey] = " & Me.cboStore

it probably means that [StoreKey] and Me.cboStore are, in fact, different Datatypes. You say "the value is a number," but Fields that only contain digits can be defined as Text as well as as a Number; you want to double check [StoreKey] and Me.cboStore.

It could also mean that [StoreKey] and Me.cboStore are both defined as Text, in which case the correct syntax, for the Where Clause, would be

"[StoreKey] = '" & Me.cboStore & "'"

In addition, you simply have to be having trouble with

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

because DoCmd.OpenQuery only takes three parameters

• Query Name
• Data View
• DataMode

It does not have a Where Clause, which is what you're attempting with "[StoreKey] = " & Me.cboStore, and since "[StoreKey] = " & Me.cboStore is not an acceptable DataMode, you're getting an error message.

Linq ;0)>

[MarcieFess]

I've decided to try a different direction with this.

I do not have issues with the combo boxes on the form returning the appropriate information. I have 3 reports that run from buttons on this form, and they all pass the parameters to the reports correctly and the reports are run properly.

I am, however, having an issue with this fourth button/report. It's not giving me the results I expect.

Here is the code for one of the buttons that DOES work properly:

Private Sub btnHMIRFRpt_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.SetWarnings False
 'OpenQuery executes the Make Table query that creates the ztblHMIRFRpt table required for the HMIRF Report
DoCmd.RunSQL "Delete * From ztblHMIRFRpt;"
DoCmd.OpenQuery "qryStoreAisleQuery_CrosstabMkTbl"


DoCmd.SetWarnings True
 
DoCmd.OpenReport "rptHMIRF", acViewPreview, , "[StoreName] = (Select StoreName from tblStoreInformation Where StoreKey = " & Me.cboStore & ")", acWindowNormal, Me.cboStore.Value


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

I just used the information/code for this button, and copied it to my new button, making the changes as needed:

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"
        
    DoCmd.OpenReport "rptMSDSReport", acViewPreview, , "[StoreName] = (Select StoreName from tblStoreInformation Where StoreKey = " & Me.cboStore & ")", acWindowNormal, Me.cboStore.Value
   
    
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 really confused as to why this isn't working. StoreKey is one of the fields in the query. Do I need to include StoreName in the query, too? The code here tells it to get the StoreName from the tblStoreInformation.

The results I am getting when I click the button are:

1. The results of the query (unfiltered) are shown.

2. I get a message bog "Enter Parameter Value StoreName". When I enter the name of the store (that was already chosen by the combo box on the form), I get the report showing on the screen, with the StoreName at the top of the report, but the results in the report are not filtered on StoreKey as they should be.

In case it matters, here's the code for the Query:

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))
ORDER BY Product.MSDS;

I feel like I'm stuck here. I really appreciate any help someone can give me. I've checked out the various tutorials, etc. that have been posted as answers on similar threads, but haven't found anything that I believe will solve my issues.

Marcie