Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2009
    Posts
    9

    Unanswered: Listbox SQL problem

    How do I make a listbox on a form to list all recommended customers?

    The form, named "Customer Form", is based on a simple table named "Customers" with the fields ID,Name,Recommended_by. The field "Recommended_by" has the datatype Long Integer and just stores another custromers ID number.

    On the Form is a listbox in which I want to display all customers (name only) that has been recommended by the customer that is presently beeing displayed in the form.

    I set the listbox's rowsource to:
    SELECT Customer.ID, Customer.Name, Customer.Recommended_by
    FROM Customers WHERE Customers.Recommended_by=[Forms]![Custumer Form]![ID];

    But this doesent work! The listbox get empty!

    If I put a textbox in the form and just write =[Forms]![Custumer Form]![ID]; it shows the ID so theres nothing wrong with anything else it seems. Also if I change the "[Forms]![Custumer Form]![ID]" to an ID number, lets say 1141, it shows all customers with ths ID numer in the field Recommended_by.

    Whats wrong?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There are several typos in the SQL code you posted:
    Code:
    SELECT Customer.ID, Customer.Name, Customer.Recommended_by
    FROM Customers WHERE Customers.Recommended_by=[Forms]![Custumer Form]![ID];
    1. If the name of the table is "Customers" you should have "Customers.Name, etc.
    2. If the name of the form is "Customer Form", then the reference to a control on this form should be "[Forms]![Customer Form]![ID]
    Have a nice day!

  3. #3
    Join Date
    Dec 2009
    Posts
    9

    No typos

    Well it was just a typo in my example above. The object names are correct in the database. The problem must be of other kind.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The RowSource property of a Lisbox (also true for a Combobox) is static in the sense that the contents of the object is not automatically requeried (at least when the RowSourceType property is Table/Query), as it is the case for the ControlSource property of a Textbox for instance. In such controls, the data is fetched when the form is open. You can keep the RowSource property as it is and use:
    Code:
    Private Sub Form_Current()
    
        Me.List_Customers.Requery
    
    End Sub
    Or you can left the RowSource property of the Listbox empty and use:
    Code:
    Private Sub Form_Current()
    
        Me.List_Customers.RowSource = "SELECT Customers.ID, Customers.Name, Customers.Recommended_by " & _
                                      "FROM Customers WHERE Customers.Recommended_by=" & Me.ID.Value
        Me.List_Customers.Requery
    
    End Sub
    If the form allows the modification of the ID Textbox, you'll need to use the same code in its AfterUpdate event handler:
    Code:
    Private Sub ID_AfterUpdate()
    
        Me.List_Customers.Requery
    
    End Sub
    Or:
    Code:
    Private Sub ID_AfterUpdate()
    
        Me.List_Customers.RowSource = "SELECT Customers.ID, Customers.Name, Customers.Recommended_by " & _
                                      "FROM Customers WHERE Customers.Recommended_by=" & Me.ID.Value
    
        Me.List_Customers.Requery
    
    End Sub
    You can also create a procedure and call it wherever you need:
    Code:
    Private Sub Form_Current()
    
        RefreshList
        
    End Sub
    
    Private Sub ID_AfterUpdate()
    
        RefreshList
        
    End Sub
    
    Private Sub RefreshList()
    
        Me.List_Customers.RowSource = "SELECT Customers.ID, Customers.Name, Customers.Recommended_by " & _
                                      "FROM Customers WHERE Customers.Recommended_by=" & Me.ID.Value
    
        Me.List_Customers.Requery
    
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Dec 2009
    Posts
    9

    Case closed!

    Excellent answer! Just wish I understod it..

    This did it:

    Private Sub Form_Current()
    Me.List_Customers.Requery
    End Sub

    If anyone likes to explain for me the diffrence between RowSource, RowSourceType and ControlSource, Go ahead!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. RowSourceType determines how the control (Listbox or Combobox) will be populated (i.e. where it will fetch its data from). It can be:
    a) Table/Query: a table or a query will be the source of the data population the control.
    b) Value List: a list of comma (or the list separator defined in the Regional Settings in the Control Pannel of Windows).
    c) Field List: The names of the Fields (not the Values!) of a table or query.
    d) It can also be the name of a special function which must have a special structure (see help for RowSourceType ).
    See: RowSourceType Property [Access 2003 VBA Language Reference]

    2. RowSource:
    a) If RowSourceType is a. or c. (here above), this property contains the name of a table, the name of a query, or a SQL statement.
    b) If RowSourceType is b. (here above), this property is the list of comma-separated values.
    c) If RowSourceType contains the name of a special function, this property is empty.
    See: RowSource Property [Access 2003 VBA Language Reference]

    3. ControlSource is the name of a Field in the Form Recordset or a valid expression from which Access can yield a value.
    See: ControlSource Property [Access 2003 VBA Language Reference]
    Have a nice day!

Posting Permissions

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