469,332 Members | 7,058 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,332 developers. It's quick & easy.

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

77 64KB
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.

Expand|Select|Wrap|Line Numbers
  1.  ="[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?
Oct 24 '11 #1

✓ answered by 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 :
    Expand|Select|Wrap|Line Numbers
    1. Dim strFilter As String
    2.  
    3. strFilter = "([OrdH_Cust_No]='" & Me.txt_OrdH_Cust_No & "') AND " & _
    4.             "([OrdD_Stock_No]=" & Me.txtItemCode & ")"
    5. Call DoCmd.OpenForm(FormName:="Your2ndForm", WhereCondition:=strFilter)
  2. Otherwise it would be more like :
    Expand|Select|Wrap|Line Numbers
    1. Dim strFilter As String
    2.  
    3. strFilter = "([OrdH_Cust_No]='" & Me.txt_OrdH_Cust_No & "') AND " & _
    4.             "([OrdD_Stock_No]=[Please enter Item Code])"
    5. Call DoCmd.OpenForm(FormName:="Your2ndForm", WhereCondition:=strFilter)
    Access will prompt for a value whenever it finds a reference (within []) that it can't resolve.

7 4702
NeoPa
32,182 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.OpenForm(FormName:="Your2ndForm", _
  2.                     WhereCondition:=strFilter)
Before this is run you will need to prepare the value of strFilter correctly :
Expand|Select|Wrap|Line Numbers
  1. Dim strFilter As String
  2.  
  3. strFilter = "([OrdH_Cust_No]='" & Me.txt_OrdH_Cust_No & "') AND " & _
  4.             "(???)"
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.
Oct 24 '11 #2
Joe Y
77 64KB
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_stock_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.
Oct 25 '11 #3
NeoPa
32,182 Expert Mod 16PB
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 :
    Expand|Select|Wrap|Line Numbers
    1. Dim strFilter As String
    2.  
    3. strFilter = "([OrdH_Cust_No]='" & Me.txt_OrdH_Cust_No & "') AND " & _
    4.             "([OrdD_Stock_No]=" & Me.txtItemCode & ")"
    5. Call DoCmd.OpenForm(FormName:="Your2ndForm", WhereCondition:=strFilter)
  2. Otherwise it would be more like :
    Expand|Select|Wrap|Line Numbers
    1. Dim strFilter As String
    2.  
    3. strFilter = "([OrdH_Cust_No]='" & Me.txt_OrdH_Cust_No & "') AND " & _
    4.             "([OrdD_Stock_No]=[Please enter Item Code])"
    5. Call DoCmd.OpenForm(FormName:="Your2ndForm", WhereCondition:=strFilter)
    Access will prompt for a value whenever it finds a reference (within []) that it can't resolve.
Oct 25 '11 #4
Joe Y
77 64KB
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmb_Price_Hist_AfterUpdate()
  3.  
  4.  
  5. Dim strFilter As String
  6.  
  7. strFilter = "([ordH_cust_no]='" & Me.txt_ordh_cust_no & "') AND " & _
  8.              "([ordd_stock_no]=" & Me.cmb_Price_Hist & ")"
  9.  
  10. Call DoCmd.OpenForm(FormName:="F_Ord_History", View:=acFormDS, WhereCondition:=strFilter)
  11.  
  12. End Sub
  13.  
Oct 25 '11 #5
Joe Y
77 64KB
After a few trial and error, the following codes worked.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmb_Price_Hist_AfterUpdate()
  2.  
  3.  Dim strFilter As String
  4.  
  5.  strFilter = "([ordh_cust_no]='" & Me.txt_ordh_cust_no & "') AND " & _
  6.  "([ordd_stock_no]='" & Me.cmb_Price_Hist & "')"
  7.  
  8. Call DoCmd.OpenForm(FormName:="F_Ord_History", View:=acFormDS, WhereCondition:=strFilter)
  9.  
  10. End Sub
  11.  
I was confused with quote and brackets signs and still is.

Thanks.
Oct 25 '11 #6
NeoPa
32,182 Expert Mod 16PB
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.
Oct 25 '11 #7
Joe Y
77 64KB
Thanks for clarification!
Oct 26 '11 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Mike Turco | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by haryvincent176 | last post: by
reply views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.