Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2012
    Posts
    9

    Unanswered: Combo Box problem

    Hi everyone!

    I do not know what I am doing wrong with the VB Code. When I select a value in my Combo Box, I must see related information in the sub form below the Combo Box. Can anyone look at it and solve it for me.

    You can find attached MS Access 2007 file which is compressed with 7zip. Just change the extension of the file to .7z in order to be able to open the file.

    The VB Code I used is as following:

    Option Compare Database

    Sub SetFilter()

    Dim LSQL As String

    LSQL = "select * from Customer"
    LSQL = LSQL & " where Name = '" & Combo2 & "'"

    Form_Customer_Sub.RecordSource = LSQL

    End Sub


    Private Sub Combo2_AfterUpdate()
    SetFilter
    End Sub

    Private Sub Form_Open(Cancel As Integer)
    SetFilter
    End Sub

    Thanks in advance for your help.

    Manan
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Windows reports that there is nothing inside your attachment to unzip!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Aug 2012
    Posts
    9
    Here is the ZIP file once again
    Attached Files Attached Files

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    In Form Design View
    1. Right-Click on Combo2
    2. Click on Properties
    3. Go to the Data Tab
    4. Change the Bound Column Property to 2
    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Aug 2012
    Posts
    9
    Hi Missinglinq

    Thanks for the previous help.

    Can you look at the following example database. The form Home is not functioning as it should be.
    Attached Files Attached Files

  6. #6
    Join Date
    Aug 2012
    Posts
    9
    Can anyone look at the database above and solve the issue with form Home.

    When selecting a value in combo box other relevant data in below form should appear.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The form Home is not functioning as it should be.
    means doodly squat

    what isn't work
    what should the form do
    what steps have you taken to work out what is (or isn't happening)
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so I'm guessing the problem is the code in set filter
    Code:
        Dim LSQL  As String
        LSQL = "select * from Order Details"
        LSQL = LSQL & " where Order ID = '" & Combo2 & "'"
    Form_OrderDetails_sub.RecordSource = LSQL
    first off when supplying numeric values you don't delimit the value. you do for a string/text value (with either a pair of ' or ", you do with a date.

    if you a foolish enough to use spaces in column or table names then you must delimit the table and or column name with square brackets. ferinstance:-

    Code:
    Select [my column] from [my table]
    where [my table].[a numeric column] > 1234
    and [my table].[a string column] like "abcdef*"
    and [my table].[a date column] >= #2014/06/01#
    further more as you run the setfilter code on the forms initial load you need to cater for the possibility that the combo box doesnt' have a value. either explicitly set a value on start up OR modify your code to spot there is no value and take appropriate action (or inaction)

    Code:
    Sub SetFilter()
    
        Dim LSQL  As String
        
        LSQL = "select * from [Order Details]"
        If Not IsNull(Combo2) Then ' the user has selected an order number
          LSQL = LSQL & " where [Order ID] = " & Combo2
        'else nothing selected so dont apply the filter
        End If
        
    Form_OrderDetails_sub.RecordSource = LSQL
    Form_OrderDetails_sub.Requery
    End Sub
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Aug 2012
    Posts
    9
    Quote Originally Posted by healdem View Post
    means doodly squat

    what isn't work
    what should the form do
    what steps have you taken to work out what is (or isn't happening)
    The problem with the form is that it does not open. It gives the error 3131 " Syntax error in FROM clause" I do not understand what I am doing wrong in the VB Code or somewhere else in the file...

    In the Form HOME when I select an order id from Combo box I should get related data in the below form called orderdetails_sub.

    Can anyone look at it and solve it plz?

  10. #10
    Join Date
    Aug 2012
    Posts
    9
    Problem is solved.

    I have used the set filter vb code as proposed by healdem and then changed the property settings of the combo box by setting the value 2 in bound columns.

    Thanks for your help healdem.

Posting Permissions

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