Command button to open a form meeting two WHERE conditions by Expression Builder

[Joe Y]

I want to use a command button in 1st form to open a 2nd form that meets two “WHERE” conditions.

One of the conditions is the customer number (ordh_cust_no) that resides in both forms. Then I want user to enter second condition, Item Code, to meet the condition in a text box (txt_ordd_stock _no) from the 2nd form.

When I use the command button wizard, the wizard created the following where statement in the Expression Builder.

 ="[ordh_cust_no]=" & "'" & [txt_ordh_cust_no] & "'"

How do I make Access to ask for the manually entered 2nd condition so it display only records that meet both conditions?

[NeoPa]

Within the code of the Command Button you will need a line to open the new form (Call DoCmd.OpenForm( )). One of the parameters will be your filter string. For now we'll call that strFilter :

Call DoCmd.OpenForm(FormName:="Your2ndForm", _
                    WhereCondition:=strFilter)

Before this is run you will need to prepare the value of strFilter correctly :

Dim strFilter As String

strFilter = "([OrdH_Cust_No]='" & Me.txt_OrdH_Cust_No & "') AND " & _
            "(???)"

Unfortunately that's where your question let's us down as it doesn't make proper sense. You can't compare it against a TextBox control you haven't opened yet. We don't know what to compare with what here. You mention Item Code, but fail to explain if it's a field (If it is then it's in a different format from the other field mentioned) or what it is. Is it [Item Code], [ItemCode], [Item_Code] or none of the above? This sort of information should be included clearly in the question if you don't want to waste any of our, or your, time.

I've answered what I can, and that may be enough to get you going on this occasion, but I expect there'll be more questions coming (and you're welcome to post more) and it would be far better if you get those right first time, as it'll save lot's of time.

[Joe Y]

Sorry I did not make it clear in my question.

So far, I can make the command button to open the 2nd form to list all records matching current customer number on the 1st form. However, I need to filter further by specifying an item code.

The item code exists only in the table “DBA_ordd” (record source of the 2nd form). The data field for Item Code is “ordd_stoc k_no” that is displayed in the text box “txt_ordd_stock _no” in the 2nd form. The 1st form has no object that is tied to item code, so I cannot add the rest of Where condition after AND (or perhaps, I just don’t know how).

What I am imaging of the design is to have Access pop up a window asking user to enter Item Code as filter criteria then execute the VBA code in the open form Command as you described. How to do this in the VBA or in the Expression Builder?

Thanks for the helps.

[NeoPa]

Thank you Joe. That makes the situation clear :-)

It seems to me you have two main options :

1. (Recommended) Add a new control on your first form to allow the operator to specify the value they want for [OrdD_Stock_No].
2. Allow Access to prompt the operator every time.

A is recommended because the value persists after it's been used in the filter. Also, it looks so much more professional. Data prompts are generally considered to reflect a poorly implemented database.

1. Assuming we have a control on your first form called [txtItemCode] and the Item Code is numeric, we would use code like :
   

   Dim strFilter As String
    
   strFilter = "([OrdH_Cust_No]='" & Me.txt_OrdH_Cust_No & "') AND " & _
               "([OrdD_Stock_No]=" & Me.txtItemCode & ")"
   Call DoCmd.OpenForm(FormName:="Your2ndForm", WhereCondition:=strFilter)

2. Otherwise it would be more like :
   

   Dim strFilter As String
    
   strFilter = "([OrdH_Cust_No]='" & Me.txt_OrdH_Cust_No & "') AND " & _
               "([OrdD_Stock_No]=[Please enter Item Code])"
   Call DoCmd.OpenForm(FormName:="Your2ndForm", WhereCondition:=strFilter)

   Access will prompt for a value whenever it finds a reference (within []) that it can't resolve.

[Joe Y]

Thanks NeoPa. The #2 option works. I added View:=acFormDS in order to open 2nd form as datasheet.

Now I am testing the #1 option. Instead of a command button and a text box, I am planning to place the option #1 codes in the After Update event of a Combo Box for convenient reason. User would click and choose desired item code. After update, the 2nd form opened with two filter criteria.

However, I am stuck with an error message 3464, data type mismatch in criteria expression.

The item code is a text field in the table. Does that require a different VBA code? Your message says assuming item code is a numeric, which make me think the code may need to be different.

Also, is there a instruction somewhere to guide how and the purposes to place quotation mark (“ or ‘) and bracket (() or [])? I am very confused with this. VBA is brand new to me.

Below is the code I have and the debug stopped at the line starting with Call.

Thanks.

 
Private Sub cmb_Price_Hist_AfterUpdate()


Dim strFilter As String

strFilter = "([ordH_cust_no]='" & Me.txt_ordh_cust_no & "') AND " & _
             "([ordd_stock_no]=" & Me.cmb_Price_Hist & ")"

Call DoCmd.OpenForm(FormName:="F_Ord_History", View:=acFormDS, WhereCondition:=strFilter)

End Sub

[Joe Y]

After a few trial and error, the following codes worked.

Private Sub cmb_Price_Hist_AfterUpdate()

 Dim strFilter As String

 strFilter = "([ordh_cust_no]='" & Me.txt_ordh_cust_no & "') AND " & _
 "([ordd_stock_no]='" & Me.cmb_Price_Hist & "')"
            
Call DoCmd.OpenForm(FormName:="F_Ord_History", View:=acFormDS, WhereCondition:=strFilter)

End Sub

I was confused with quote and brackets signs and still is.

Thanks.

[NeoPa]

Joe Y:
Also, is there a instruction somewhere to guide how and the purposes to place quotation mark (" or ') and bracket (() or [])?

Try Quotes (') and Double-Quotes (") - Where and When to use them for the first. Brackets ([]) identify an element of a reference. Parentheses (()) are used to ensure a section of code is interpreted before those othe sections around it.

Brackets are only required when without them the reference is ambiguous or wrong (Such as a control name with an embedded space in it, or one that uses a reserved word - [Name] is often used for fields and controls). SQL references often require brackets around the elements in more situations than those in VBA code. A qualified reference should only have brackets around the elements. Never around the full reference. EG. [Forms.frmMenu] is totally wrong and will always cause a failure. It could be [Forms].[frmMenu] though.

PS. Your working code matches the article I linked to above. That's exactly how I would have suggested to do it. Nice work.
PPS. ComboBoxes usually have the prefix cbo rather than cmb. It's up to you of course but I changed all my work a while back to reflect this (I started out using cmb too). The idea of using the ComboBox AfterUpdate event in place of a Command Button is another good one.

[Joe Y]

Thanks for clarification!