Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: Textbox value based on listbox selection?

    Hello,

    I have a listbox on my form which lets me select a library (via the libraryID from tblLibrairies) to view its details.

    I also have a query in my database which lists the total of visits to each library.

    I want to be able to single-click to select a library in my listbox and have the number of visits for that library displayed in a textbox on my form. How do I achieve it?

    Thanks,

    Tom
    Last edited by moss2076; 04-06-10 at 13:09.

  2. #2
    Join Date
    Jul 2009
    Posts
    14
    textbox = name_of_listbox.Column(0)

    Keep in mind the listbox is 0 based on the first column... e.g. if you have a listbox with three columns, ID, fname, and lname, and you wanted to store the fname in the textbox, then, on event "after update" for listbox (which is when you select a record from the listbox):

    textbox = name_of_listbox.Column(1)

  3. #3
    Join Date
    Aug 2004
    Posts
    364
    Thanks but all that does is make the textbox display what is in a particular column of the listbox.

    I want the textbox to display the number of visits for the selected libraryID - The number od visits is stored a seperate query from the table being used by the listbox.

  4. #4
    Join Date
    Jul 2009
    Posts
    14
    oh, mis read... basically you need to tailor a query based on your list box selection, and return it's value to the value of the textbox:


    Code:
    Dim strSQL As String
    Dim txtBoxToFill As String
    
    strSQL = "SELECT numberOfVisits AS totalVisits FROM tblLibrary WHERE libraryID=" & name_of_listbox.Column(0) & "; "
    Set rs = New ADODB.Recordset
    rs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    
             txtBoxToFill = rs("totalVisits").Value
    
    rs.Close
    Set rs = Nothing

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Or more simply:
    Code:
    Me.TextBox.Value = DLookup("<Total of visits>", "<Query computing totals>", "libraryID=" & Me.ListBox.Column(0))
    Depending on how the database is organized, you could also use the DCount() function.
    Have a nice day!

  6. #6
    Join Date
    Aug 2004
    Posts
    364
    Quote Originally Posted by Sinndho View Post
    Or more simply:
    Code:
    Me.TextBox.Value = DLookup("<Total of visits>", "<Query computing totals>", "libraryID=" & Me.ListBox.Column(0))
    Depending on how the database is organized, you could also use the DCount() function.
    I got it with this -
    Code:
    Me.Text47.Value = DLookup("expr1", "qry_arrivals_test", "libraryID='" & Me.lst_library.Value & "'")
    Many thanks to you all for your help!!

  7. #7
    Join Date
    Aug 2004
    Posts
    364
    Just out of interest, if a library has no arrivals, can a zero be displayed? At the moment the textbox is empty of a library has no arrivals.
    Code:
    Me.Text47.Value = DLookup("expr1", "qry_arrivals_test", "libraryID='" & Me.lst_library.Value & "'")

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Like this:
    Code:
    Me.Text47.Value = Nz(DLookup("expr1", "qry_arrivals_test", "libraryID='" & Me.lst_library.Value & "'"), 0)
    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
  •