Results 1 to 6 of 6

Thread: List Box Query?

  1. #1
    Join Date
    Feb 2004
    Posts
    67

    Question Unanswered: List Box Query?

    I have a "frmPatients" that has a combobox linked to tblPatients. After-Update the form displays info on the patients. I want to create a listbox on "frmPatients" to display the tblVisit information. tblVisit has VisitID, PatientID...etc. I can get this to work if I use a subform, but I would like to know if a listbox can work as well.

    I am trying to query a listbox as follows;
    SELECT tblVisit.VisitID, tblVisit.PatientID, tblVisit.DOI, tblVisit.DiagID, tblVisit.ClaimNum, tblVisit.ContactID, tblVisit.OpenClose
    FROM tblVisit
    WHERE (((tblVisit.PatientID)=[Forms]![frmPatients]![patientID]));

    All I get is a BLANK listbox. if I delete the WHERE statement the listbox list all visits.
    If I use this code in a SubForm is works just fine.

    Can someone help??? Thanks...Chuck

  2. #2
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    Try putting an equal sign in front, the SQL text in quotes and the form reference without quotes like this:

    ="SELECT tblVisit.VisitID, tblVisit.PatientID, tblVisit.DOI, tblVisit.DiagID, tblVisit.ClaimNum, tblVisit.ContactID, tblVisit.OpenClose
    FROM tblVisit
    WHERE (((tblVisit.PatientID)=" & [Forms]![frmPatients]![patientID] & "));"

    You can also put

    listbox_name.Requery

    in the AfterUpdate event to make sure it refreshes whenever you change the combo box.

  3. #3
    Join Date
    Feb 2004
    Posts
    67
    Bummer:

    I tried your suggestion, but Now I get an error: "Invalid SQL Statement: expected DELETE, INSERT, PRCEDURE, SELECT, or UPDATE".

    what did I do wrong?

  4. #4
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    Actually, now that I think about it, that's my mistake - you probably should clear out the listbox Row Source put it in the code for the Change event for your combo box. Try this:

    Code:
    Private Sub patientID_Change()
       listbox_name.RowSource = "SELECT tblVisit.VisitID, " & _
          "tblVisit.PatientID, tblVisit.DOI, tblVisit.DiagID, tblVisit.ClaimNum, " & _
          "tblVisit.ContactID, tblVisit.OpenClose FROM tblVisit " & _
          "WHERE (((tblVisit.PatientID)=" & Forms!frmPatients!patientID & "));"
       listbox_name.Requery
    End Sub
    Last edited by BummerJeff; 10-20-04 at 17:52.

  5. #5
    Join Date
    Feb 2004
    Posts
    67
    Bummer;

    I placed the code iin the Change() event, now when the combobox changes, the list box displays the code: "SELECT tblVisit.VisitID, " & .............WHERE (((tblVisit.PatientID)=" & Forms!frmPatients!patientID & "));"

  6. #6
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    Make sure the listbox's Row Source Type property is set to "Table/Query" and not "Value List".

Posting Permissions

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