Needing an explaination on queries please.

[NightWalker]

I have these 2 queries, the first one didn't work. It would give me an "Enter Parameter value" msgbox when I clicked the second cbo. After entering the same item I selected into the msgbox I would get the proper results in the second cbo. I don't understand why the second qry works and the first doesn't. Could someone please explain why the WHERE sections are different?




Thank you for your time and knowledge
Walker

Private Sub cboTestItemType_AfterUpdate()
     'Sets the Item Part Number cbo to be limited by Test Item Type
Me.cboProductionItemPartNumber.RowSource = "SELECT tbl_ProductionItem.ProductionItemPartNumber, tbl_ProductionItem.ProductionItem " & _
                                                "FROM tbl_ProductionItem " & _
                                                "Where ProductionItemType = " & Nz(Me.cboTestItemType) & _
                                                " order by tbl_ProductionItem.ProductionItem"

    'Refresh cbo
    Me.cboProductionItemPartNumber.Requery

End Sub

Private Sub cboTestItemType_AfterUpdate()
    Me.cboProductionItemPartNumber.RowSource = "SELECT tbl_ProductionItem.ProductionItemPartNumber, tbl_ProductionItem.ProductionItem " & _
                                                "FROM tbl_ProductionItem " & _ 
                                                 "WHERE (((tbl_ProductionItem.ProductionItemType)=[forms]![frm_DailyTested].[cboTestItemType])) " & _
                                                "GROUP BY tbl_ProductionItem.ProductionItemPartNumber, tbl_ProductionItem.ProductionItem ORDER BY tbl_ProductionItem.ProductionItem"
    Me.cboProductionItemPartNumber.Requery
End Sub

[NightWalker]

Can anyone explain why the WHERE statement in the first qry didn't work compared to the second one. I figured out how to fix it myself but I don't understand why it makes a difference.

Thank you for any help you can give me.

[orange]

Are you saying that your second query(below) worked without giving an error????

Private Sub cboTestItemType_AfterUpdate()
    Me.cboProductionItemPartNumber.RowSource = "SELECT tbl_ProductionItem.ProductionItemPartNumber, tbl_ProductionItem.ProductionItem " & _
                                                "FROM tbl_ProductionItem " & _ 
                                                 "WHERE (((tbl_ProductionItem.ProductionItemType)=[forms]![frm_DailyTested].[cboTestItemType])) " & _
                                                "GROUP BY tbl_ProductionItem.ProductionItemPartNumber, tbl_ProductionItem.ProductionItem ORDER BY tbl_ProductionItem.ProductionItem"
    Me.cboProductionItemPartNumber.Requery
End Sub

NZ()

[NightWalker]

Yes and I don't understand why. I thought Me. was the same as [forms]![frm_DailyTested]. I kept getting an enter parameter msgbox with the first qry.

[orange]

Sort of. You'll notice that you have quotation marks surrounding the sql

"SELECT tbl_ProductionItem.ProductionItemPartNumber, tbl_ProductionItem.ProductionItem " & _
         "FROM tbl_ProductionItem " & _ 
         "WHERE (((tbl_ProductionItem.ProductionItemType)=[forms]![frm_DailyTested].[cboTestItemType])) " & _
         "GROUP BY tbl_ProductionItem.ProductionItemPartNumber, tbl_ProductionItem.ProductionItem ORDER BY tbl_ProductionItem.ProductionIt

When you enclose a string in quotes, that makes it a constant/literal string. So, because this
[forms]![frm_DailyTested].[cboTestItemType]
is inside the quotes, it is considered a literal/constant.

In overview, when vba is processed, it looks for constants/literals, and it looks for expressions to be evaluated. You wanted
[forms]![frm_DailyTested].[cboTestItemType] to be evaluated, and the result placed within the SQL string. But you enclosed it in quotes, so vba/Access did not try to evaluate it(also referred to as rendering the expression).
Bottom line is that SQL did not do what you expected because of the quotes. In fact, [forms]![frm_DailyTested].[cboTestItemType] was ignored from SQL rendering, but might have been interpreted as a missing parameter.

To get the SQL to work the way you intended --get the values of [forms]![frm_DailyTested].[cboTestItemType].

 Me.cboProductionItemPartNumber.RowSource = "SELECT tbl_ProductionItem.ProductionItemPartNumber, tbl_ProductionItem.ProductionItem " & _
  "FROM tbl_ProductionItem " & _ 
 "WHERE (((tbl_ProductionItem.ProductionItemType)='" & [forms]![frm_DailyTested].[cboTestItemType])) & "' " & _
  "GROUP BY tbl_ProductionItem.ProductionItemPartNumber, tbl_ProductionItem.ProductionItem ORDER BY tbl_ProductionItem.ProductionItem"

Here the [forms]![frm_DailyTested].[cboTestItemType] is now outside of the quotes and will be evaluated/rendered before substituting in the SQL.

In my view you could use Me.cboTestItemType interchangeably with [forms]![frm_DailyTested].[cboTestItemType]. The quotes were the issue as I see things.

[NightWalker]

Ok so I was on the right track with the first query I just didn't have the quotes around Me.cboTestItemType properly. I would like to Thank you greatly for the datapig videos I found in another one of your replies to someone else. I watched those and built the query through the query builder then cut and pasted it into my VBA code for the proper event and now works like a charm.

I try not to post questions here without exhausting every other option. Thank you again for explaining that to me and the datapig video link.

[orange]

Glad the posts and links were helpful to you.
Good luck with your project(s).