Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2002
    Posts
    88

    Unanswered: text box vs combo box values from a form in a query

    I'd like to run a query based on values taken from a combo box in a form. The values in the combo box would come from a table.

    When I do this the query doens't return any values. If I do the same thing using a text box it works. I'm using the syntax [forms]![form name]![box name]. The only difference between it working and not is the fact that if the box is a combo box it doesn't work.

    Thanks in advance

  2. #2
    Join Date
    Dec 2003
    Posts
    172
    tmort

    Try this demo. After you open it, try frmTest. It is an unbound form with a combo box linked to a table called customers by the customer id.

    Choose a customer in the drop-down. Then, click on the build-SQL button. It will generate a SQL statement, which I demo in the text box to the left of the button.

    On the bottom of the form is a subform datasheet which displays the results of the SQL statement generated above it.

    For example, clicking on Nancy Drew creates this SQL Statement:
    SELECT * FROM tblCustomers WHERE tblCustomers.CustomerId=3
    where 3 is the id for Nancy Drew

    Press Alt-F11 to go into the VBA editor. You will find the code for generating the SQL in the on-click event of the form:

    Option Compare Database
    Option Explicit

    Private Sub Form_Open(Cancel As Integer)
    Me.child0.Form.RecordSource = "SELECT * FROM tblCustomers"
    End Sub

    Private Sub cmdBuildSQL_Click()

    Dim lCustomerId As Long
    lCustomerId = Nz(Me.cmbCustomerId.Column(0, Me.cmbCustomerId.ListIndex), 0)
    If Nz(lCustomerId) > 0 Then
    Me.txtSQLStatement = "SELECT * FROM tblCustomers WHERE tblCustomers.CustomerId=" + CStr(lCustomerId)
    Me.child0.Form.RecordSource = Me.txtSQLStatement
    Else
    Me.txtSQLStatement = ""
    Me.child0.Form.RecordSource = "SELECT * FROM tblCustomers"
    MsgBox "No customer selected!", vbOKOnly + vbInformation, "Demo"
    End If


    End Sub

    Note how I get the combo box selected item:
    lCustomerId = Nz(Me.cmbCustomerId.Column(0, Me.cmbCustomerId.ListIndex), 0)

    Joe G
    Attached Files Attached Files

  3. #3
    Join Date
    Dec 2003
    Posts
    6

    Re: text box vs combo box values from a form in a query

    I'm not entirely sure I follow what you're trying to do. You cannot bind a textbox to a query. If you're seeing a list of values in a textbox, then I expect you're seeing a field in the query which is bound to the form.

    If you're having trouble viewing a query with a combo box, then check the following in the combo box's properities:

    Be sure your "Column Count" is not set to 0.

    If the query returns more than one field, make sure that your "Column Count" corresponds to the column of the field you want to view (i.e. If your query returns 3 columns, and you want to view the 2nd column, set the column count to 2).

    Make sure that the "Column Widths" property is set properly. You should indicate the width of each column you want to view, one for each column in the column count, deliminted by a semicolon. Check the Access help for a more explicit description.

    Finally, make sure your bound column is set to the column you want your combo box to return.

  4. #4
    Join Date
    Oct 2002
    Posts
    88
    Originally posted by JoeG
    tmort

    Try this demo. After you open it, try frmTest. It is an unbound form with a combo box linked to a table called customers by the customer id.

    Choose a customer in the drop-down. Then, click on the build-SQL button. It will generate a SQL statement, which I demo in the text box to the left of the button.

    On the bottom of the form is a subform datasheet which displays the results of the SQL statement generated above it.

    For example, clicking on Nancy Drew creates this SQL Statement:
    SELECT * FROM tblCustomers WHERE tblCustomers.CustomerId=3
    where 3 is the id for Nancy Drew

    Press Alt-F11 to go into the VBA editor. You will find the code for generating the SQL in the on-click event of the form:

    Option Compare Database
    Option Explicit

    Private Sub Form_Open(Cancel As Integer)
    Me.child0.Form.RecordSource = "SELECT * FROM tblCustomers"
    End Sub

    Private Sub cmdBuildSQL_Click()

    Dim lCustomerId As Long
    lCustomerId = Nz(Me.cmbCustomerId.Column(0, Me.cmbCustomerId.ListIndex), 0)
    If Nz(lCustomerId) > 0 Then
    Me.txtSQLStatement = "SELECT * FROM tblCustomers WHERE tblCustomers.CustomerId=" + CStr(lCustomerId)
    Me.child0.Form.RecordSource = Me.txtSQLStatement
    Else
    Me.txtSQLStatement = ""
    Me.child0.Form.RecordSource = "SELECT * FROM tblCustomers"
    MsgBox "No customer selected!", vbOKOnly + vbInformation, "Demo"
    End If


    End Sub

    Note how I get the combo box selected item:
    lCustomerId = Nz(Me.cmbCustomerId.Column(0, Me.cmbCustomerId.ListIndex), 0)

    Joe G
    I pretty much see what is going on but I'm not sure about the syntax etc for getting the selected item in the combo box and I really don't know VB or SQL. Ultimately I'm going to want to have multiple combo boxes so I will want to have multiple filters. I'm thinking that if I want to filter on another field named number from the same table after defining the variable I would do something like:

    Option Compare Database
    Option Explicit
    Private Sub Form_Open(Cancel As Integer)
    Me.child0.Form.RecordSource = "SELECT * FROM tblCustomers"
    End Sub
    Private Sub cmdBuildSQL_Click()

    Dim lCustomerId As Long
    Dim lnumber As Long
    lCustomerId = Nz(Me.cmbCustomerId.Column(0, Me.cmbCustomerId.ListIndex), 0)
    lnumber = Nz(?????)
    If Nz(lCustomerId) > 0 Then
    Or If Nz(lnumber) > 0 Then
    Me.txtSQLStatement = "SELECT * FROM tblCustomers WHERE tblCustomers.CustomerId=" + CStr(lCustomerId) AND tblCustomers.number= “+CStr(lnumber)
    Me.child0.Form.RecordSource = Me.txtSQLStatement
    Else
    Me.txtSQLStatement = ""
    Me.child0.Form.RecordSource = "SELECT * FROM tblCustomers"
    MsgBox "No customer selected!", vbOKOnly + vbInformation, "Demo"
    End If


    End Sub

    Am I close?

    Thanks

  5. #5
    Join Date
    Dec 2003
    Posts
    172
    You're on the right track. You may want to do something like this:

    strSQLSelect = "SELECT * FROM tblCustomers WHERE "

    strSQLCriteria1 = "tblCustomers.OrderDate=#" + CStr(dOrderDate)+"#"

    strSQLCriteria2 = "tblCustomers.CustomerId=" + CStr(lCustomerId)

    strSQL=strSQLSelect+" WHERE "+strSQLCriteria1+" AND "+strSQLCriteria2+"

    ORDER BY tblCustomers.OrderDate"

    Comment 1:

    I usually use a variation of the example above in which I check each entry in the drop-down box - if the value is null, missing or invalid (for example if the dates don't make sense, I do not add that clause to the final SQL statement.

    Now the only trick there is that you have to remember that the first clause needs WHERE and the 2nd, 3rd, 4th etc use AND. You can see then that if you plan to skip one of these clauses, you can't end up with this:

    WHERE AND tblCustomers.OrderDate = #5/1/03#
    or
    AND tblCustomers.CustomerId = 5 AND tblCustomers.OrderDate = #5/1/03#

    One approach to prevent this is to assign the word WHERE and AND to a variable and when the first valid entry for the SQL statements is used,

    you pop in:
    +" WHERE "+
    When the 2nd, 3rd, 4th and beyond statements get used you pop in:
    + " AND "+

    I usually say something like:
    If NZ(MyDropDown,0)>0 then
    strSQLClause1= "tblCustomers.MyFieldName="+MyDropDownValue
    fSkipThisClause=False
    SQLClauseCount=SQLClauseCount+1
    Else
    strSQLClause1=""
    fSkipThisClause=True
    End If

    That way, I can increment the number of clauses (and know when to use WHERE and AND) and I can pass a blank string if the clause is not used so:

    If strSQLCriteria1 was equal to "" then:
    strOperator1=""
    strOperator2="WHERE"

    strSQL=strSQLSelect+" "+strOperator1+" "+strSQLCriteria1+"

    "+strOperator2+" "+strSQLCriteria2+" "+"ORDER BY

    tblCustomers.OrderDate"

    strSQL="SELECT * FROM tblCustomers"+" "+""+ " "+"" +" "+"WHERE"+"

    "+"tblCustomers.CustomerId=" + CStr(lCustomerId)+ " "+"ORDER BY

    tblCustomers.OrderDate"

    strSQL="SELECT * FROM tblCustomers WHERE tblCustomers.CustomerId=5

    ORDER BY tblCustomers.OrderDate"

    (NOTE: I didn't test these clauses - I hand typed them here for the example so I may have missed a few spaces or quotes but you should get the basic idea.)

    Comment #2:

    Make sure you add spaces between the words or the SQL won't compile right and you'll get cryptic errors from VBA. In other words you don't want to end up with:

    WHEREtblCustomers.OrderDate=#5/1/03#ORDER BY tblCustomers.OrderDate

    Joe G

  6. #6
    Join Date
    Oct 2002
    Posts
    88
    I'm still only part way there. I'm getting lost on the Where and AND veriables and how to handle them. I don't know how to increment them. It looks like you are suggesting to sort of do a loop to get the values for the different citeria. This syntax is also new to me.

    Thanks

    I have:

    Option Compare Database
    Option Explicit
    Private Sub Form_Open(Cancel As Integer)
    Me.child0.Form.RecordSource = "SELECT * FROM Analyzed Results"
    End Sub
    Private Sub cmdBuildSQL_Click()



    strSQLSelect = "SELECT * FROM Analyzed Results WHERE "

    strSQLCriteria1 = "Analyzed Results.outfall number=" + CStr(OutfallNumber)

    strSQLCriteria2 = " Analyzed Results.Collection Date=#" + CStr(CollectionDate)+"#"

    strSQLCriteria3 = " Analyzed Results.Sampler=" + CStr(sampler)

    strSQLCriteria4 = " Analyzed Results.Sample Type=" + CStr(sampletype)

    strSQLCriteria4 = " Analyzed Results.compliance Sample=" + CStr(compsample)

    strSQLCriteria5 = " Analyzed Results.analyte=" + CStr(Parameter)

    strSQLCriteria6 = " Analyzed Results.Result Number=" + CStr(result)




    If NZ(MyDropDown,0)>0 then
    strSQLClause1= "analyzed results.outfall number="+ CStr(OutfallNumber)
    strOperator1=”WHERE” [I'm assuming I set the values here]
    fSkipThisClause=False
    SQLClauseCount=SQLClauseCount+1
    Else
    strSQLClause1=""
    fSkipThisClause=True
    End If



    strSQL=strSQLSelect+" "+strOperator1+" "+strSQLCriteria1+"

    "+strOperator2+" "+strSQLCriteria2+" "+"ORDER BY

    tblCustomers.OrderDate"

    strSQL="SELECT * FROM tblCustomers"+" "+""+ " "+"" +" "+"WHERE"+" [I don't understand this line]

    "+"tblCustomers.CustomerId=" + CStr(lCustomerId)+ " "+"ORDER BY

    tblCustomers.OrderDate"

    strSQL="SELECT * FROM tblCustomers WHERE tblCustomers.CustomerId=5

    ORDER BY tblCustomers.OrderDate"


    End Sub

  7. #7
    Join Date
    Dec 2003
    Posts
    172
    okay, back to you soon with additional information

    joeg

  8. #8
    Join Date
    Dec 2003
    Posts
    172
    Maybe this demo will help.

    I created a search form with 2 combo boxes and one text box.

    Combo box 1 lets you choose a customer.
    Combo box 2 lets you choose a product.
    The text box lets you choose a city name.

    When you press the search button, the VB code behind the form assembles a SQL statement on the fly from the criteria you choose from any of the 2 combo boxes or the text box.

    For example, if you wanted to see Harry Potter's order for a computer modem, you would choose Harry Potter as the customer, and modem as the product.

    Here's the attachment, comboboxsqldemo2.zip. You should download this attachment to see how the search form adds the SQL criteria together.

    Here's the code in the search form (with comments attached):

    Option Compare Database
    Option Explicit

    'module level variable
    'this means you can check its value anywhere in this module
    Private fFirstSQLClause As Boolean

    'to make it easy, I created a basic query (using the Access query designer)
    'then I cut and pasted the SQL code and put it in a constant labeled
    'strDefaultSQLString
    'now I can just add my WHERE, AND and ORDER BY clauses to this basic
    'query string anytime I wish and it will always return the basic
    'set of records, minus any criteria restrictions
    Private Const strDefaultSQLString As String = "SELECT tblCustomers.CustomerId, tblCustomers.FirstName, tblCustomers.LastName, tblCustomers.City, tblCustomers.State, tblProductsOrdered.ProductId, tblProducts.ProductName FROM (tblCustomers INNER JOIN tblProductsOrdered ON tblCustomers.CustomerId = tblProductsOrdered.CustomerId) INNER JOIN tblProducts ON tblProductsOrdered.ProductId = tblProducts.ProductId"

    'when the form opens, just set the child0 search results subform
    'to this recordsource (basically all records)
    Private Sub Form_Open(Cancel As Integer)
    Me.child0.Form.RecordSource = strDefaultSQLString
    End Sub

    Private Sub cmdSearch_Click()

    'Define the variables used to build the SQL

    Dim lCustomerId As Long 'to hold the value for the customer id
    Dim lProductId As Long 'to hold the value for the product id

    Dim strSQLSelectStatement As String
    Dim strSQLClause1 As String 'for combo box 1
    Dim strSQLClause2 As String 'for combo box 2
    Dim strSQLClause3 As String 'for the text box
    Dim strCity As String 'to hold the city string entered by the user
    Dim strOrderByClause As String 'the sort order for the query
    Dim strFinalSQLStatement As String 'the final SQL string to return results with

    fFirstSQLClause = True 'tells the VB function to use WHERE or AND before each SQL search criteria

    'Combo box 1 criteria for customer id
    lCustomerId = Nz(Me.cmbCustomerId.Column(0, Me.cmbCustomerId.ListIndex), 0)
    If lCustomerId > 0 Then 'then we have a valid customer criteria
    'create the search criteria for the customer id entry
    strSQLClause1 = "tblProductsOrdered.CustomerId = " + CStr(lCustomerId)
    'append WHERE or AND as necessary to the SQL clause
    strSQLClause1 = strFixedSQLClause(strSQLClause1)
    Else
    'no restriction here-just make it equal to ""
    strSQLClause1 = ""
    End If

    'Combo box 2 criteria for product id
    lProductId = Nz(Me.cmbProductId.Column(0, Me.cmbProductId.ListIndex), 0)
    If lProductId > 0 Then 'then we have a valid product criteria
    'create the search criteria for the product id entry
    strSQLClause2 = "tblProductsOrdered.ProductId = " + CStr(lProductId)
    'append WHERE or AND as necessary to the SQL clause
    strSQLClause2 = strFixedSQLClause(strSQLClause2)
    Else
    'no restriction here-just make it equal to ""
    strSQLClause2 = ""
    End If

    'Text box criteria for city
    strCity = Nz(Me.txtCity, "")
    If strCity <> "" Then 'then we have a valid city criteria
    'create the search criteria for the city entry
    strSQLClause3 = "tblCustomers.City LIKE '" + strCity + "*'"
    'append WHERE or AND as necessary to the SQL clause
    strSQLClause3 = strFixedSQLClause(strSQLClause3)
    Else
    'no restriction here-just make it equal to ""
    strSQLClause3 = ""
    End If

    'set up the sort order clause
    strOrderByClause = "ORDER BY tblCustomers.LastName, tblProducts.ProductName"

    'add it all together!
    strFinalSQLStatement = strDefaultSQLString + " " + strSQLClause1 + " " + strSQLClause2 + " " + strSQLClause3 + " " + strOrderByClause

    'set the recordsource of the child0 search results subform
    'to our new SQL statement!
    Me.child0.Form.RecordSource = strFinalSQLStatement

    End Sub
    Private Function strFixedSQLClause(strSQLClause As String) As String

    If fFirstSQLClause = True Then
    fFirstSQLClause = False
    strFixedSQLClause = "WHERE " + strSQLClause
    Else
    strFixedSQLClause = "AND " + strSQLClause
    End If

    End Function

    We can review this here or chat online if needed.

    Joe G
    Attached Files Attached Files

  9. #9
    Join Date
    Oct 2002
    Posts
    88
    That helps a lot. I think I pretty much have it set up for my application now but it looks like I have some sort of syntax problem with the Select Statement.

    I thought it might have to do with my table names having a space in them, but, that doen't appear to be it. [I do see the value in labeling tables with tbl now though]


    I have attached a stripped down db to show the problem

    Thanks.

    Also my next step is to be able to export the subfom data as an excel file. Any hints on this?

    Thanks so much
    Attached Files Attached Files

  10. #10
    Join Date
    Dec 2003
    Posts
    172
    tmort,

    will take a look

    joeg

  11. #11
    Join Date
    Oct 2002
    Posts
    88
    Never mind that last test db. I found lots of errors.

    I think I've pretty much got it now. It just doesn't like the SQL statement
    Attached Files Attached Files

  12. #12
    Join Date
    Jan 2004
    Posts
    64

    Calculate Totals In Form

    I'm trying to calculate a couple of fields in a form. It seems simple enough but there might be a little trick you experts can help me out with.
    Here it goes:
    I'm trying to calculate two fields: OrderAmount and DiscountAmount.

    1) I have a field called OrderType in the form. When I fill it out, it populates another field called OrderAmount.
    Eg: If OrderType is Yearly, OrderAmount returns $895

    They're both on the same form (Orders). And they're both combo fields, even though the second combo field; OrderAmount is protected since it's populated when OrderType is filled out.

    2) I also have another field called MarketingSourceCode in the same form as above. When I fill it out, it populates a field called MarketingSourceDesc, when this field is populated, it, in return populates another field called; DiscountAmount.
    Eg: If MarketingSourceDesc is AP25 , DiscountAmount returns $25.

    Both OrderAmount and DiscountAmount fields are Currency fields and combo boxes.

    All I want to do is: OrderAmount - DiscountAmount to get the OrderTotal.
    But when I do the calculation, it uses their ID fields to do the calculation. In other words; in the orders table; Yearly is the first record and its OrderTypeID is 4 and AP25 is the second record and its DiscountAmountID is 2.
    SO WHEN I CALCULATE THE DIFFERENCE BETWEEN THESE TWO FIELDS I GET 2, INSTEAD OF GETTING THE REAL )SALE AMOUNT - DISCOUNT AMOUNT)


    Thanks for your help.
    Bora


    Originally posted by JoeG
    tmort

    Try this demo. After you open it, try frmTest. It is an unbound form with a combo box linked to a table called customers by the customer id.

    Choose a customer in the drop-down. Then, click on the build-SQL button. It will generate a SQL statement, which I demo in the text box to the left of the button.

    On the bottom of the form is a subform datasheet which displays the results of the SQL statement generated above it.

    For example, clicking on Nancy Drew creates this SQL Statement:
    SELECT * FROM tblCustomers WHERE tblCustomers.CustomerId=3
    where 3 is the id for Nancy Drew

    Press Alt-F11 to go into the VBA editor. You will find the code for generating the SQL in the on-click event of the form:

    Option Compare Database
    Option Explicit

    Private Sub Form_Open(Cancel As Integer)
    Me.child0.Form.RecordSource = "SELECT * FROM tblCustomers"
    End Sub

    Private Sub cmdBuildSQL_Click()

    Dim lCustomerId As Long
    lCustomerId = Nz(Me.cmbCustomerId.Column(0, Me.cmbCustomerId.ListIndex), 0)
    If Nz(lCustomerId) > 0 Then
    Me.txtSQLStatement = "SELECT * FROM tblCustomers WHERE tblCustomers.CustomerId=" + CStr(lCustomerId)
    Me.child0.Form.RecordSource = Me.txtSQLStatement
    Else
    Me.txtSQLStatement = ""
    Me.child0.Form.RecordSource = "SELECT * FROM tblCustomers"
    MsgBox "No customer selected!", vbOKOnly + vbInformation, "Demo"
    End If


    End Sub

    Note how I get the combo box selected item:
    lCustomerId = Nz(Me.cmbCustomerId.Column(0, Me.cmbCustomerId.ListIndex), 0)

    Joe G

  13. #13
    Join Date
    Jan 2004
    Posts
    64
    Thanks very much your the help. This is the solution I ended up finding and it seems to be working.

    Private Sub MarketingSourceCodeF_AfterUpdate()

    OrderTotals.Value = Nz(Me.OrderAmountF.Column(1, Me.OrderAmountF.ListIndex), 0) + AdditonalSaleF.Value - Nz(Me.DiscountAmountF.Column(1, Me.DiscountAmountF.ListIndex), 0) - AdditionalDiscountF.Value
    End Sub
    ----------------------------------------------------------------------------------
    Private Sub OrderTypeID_AfterUpdate()

    OrderTotals.Value = Nz(Me.OrderAmountF.Column(1, Me.OrderAmountF.ListIndex), 0) + AdditonalSaleF.Value - Nz(Me.DiscountAmountF.Column(1, Me.DiscountAmountF.ListIndex), 0) - AdditionalDiscountF.Value
    End Sub

    Thanks again for sharing the knowledge.

  14. #14
    Join Date
    Jan 2004
    Posts
    64
    Thanks for sharing the knowledge. This is the solution I managed to find and it seems to be working fine.
    Private Sub MarketingSourceCodeF_AfterUpdate()

    OrderTotals.Value = Nz(Me.OrderAmountF.Column(1, Me.OrderAmountF.ListIndex), 0) + AdditonalSaleF.Value - Nz(Me.DiscountAmountF.Column(1, Me.DiscountAmountF.ListIndex), 0) - AdditionalDiscountF.Value
    End Sub

    Private Sub OrderTypeID_AfterUpdate()

    OrderTotals.Value = Nz(Me.OrderAmountF.Column(1, Me.OrderAmountF.ListIndex), 0) + AdditonalSaleF.Value - Nz(Me.DiscountAmountF.Column(1, Me.DiscountAmountF.ListIndex), 0) - AdditionalDiscountF.Value
    End Sub

    Thanks again, really appreciate your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •