Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: Searching database by selecting items from a listbox & displaying in form

    frmMain is the main form that all of my records are on. frmSearch is the new search form I'm creating. The search form has a list box (named lbName) on it that gets its data from the query titled qrySearch. This query has two fields

    SID and LName. SID is an autonumber/primary key and LName is the last name on the record.

    Upon someone selecting a name from the list box in frmSearch they then click a command button that will open frmMain with the individual selected. The problem I'm having is a parameter box will pop up and will be titled whoever they selected. If I type the name in the parameter box and click OK then frmMain will open and it will have filtered that record out, if I just click OK and don't type anything then of course nothing will be shown in frmMain.

    I don't know why the parameter box is appearing and I can't seem to work through it. Initially I was using CName as the field, which combined last name and first name, but I figured I would ditch that idea until I just got LName working. Below is code that I used from a book that i modified for my scenario. I believe the problem lies in the line that's in bold... Also, if I get rid of the line that gets rid of the extra comma I still receive the same results.

    Private Sub Command9_Click()
    Dim strWhere As String, varItem As Variant
    ' Request to edit items selected in the list box
    ' If no items selected, then nothing to do
    If Me!lbName.ItemsSelected.Count = 0 Then Exit Sub
    ' Loop through the items selected collection
    For Each varItem In Me!lbName.ItemsSelected
    ' Grab the ID column for each selected item
    strWhere = strWhere & Me!lbName.Column(0, varItem) & ","
    Next varItem

    ' Throw away the extra comma on the "IN" string
    strWhere = Left$(strWhere, Len(strWhere) - 1)
    ' Open the form filtered on the selection
    gstrWhereClient = "[LName] IN (" & strWhere & ")"
    DoCmd.OpenForm FormName:="frmMain", WhereCondition:=gstrWhereClient
    ' Hide the New button, but show the Show All button
    Forms!frmMain!cmdAddNewClient.Visible = False
    DoCmd.Close acForm, Me.Name
    End Sub


    -Nate

  2. #2
    Join Date
    Feb 2004
    Posts
    4
    Ok I figured out what was wrong with the above, I added the primary key to the query box AFTER I initially created it and changed the [LName] to [SID]. ANyways, recreating the query and the list box for it fixed the problem. So that is all fixed (great!), however, I have another problem.

    In the meantime I was playing around with a sample database that queried a listbox by what was typed in a text box. That works great, however, when you double click on a record it's supposed to open the form with that record. In the database example it works flawlessly, but I can't seem to get it to work in mine... The code is only one line! So I'm not sure what's going on... Here's the one line for the double click event.

    DoCmd.OpenForm "frmMain", acNormal, , "SID = '" & Me.lbName & "'"

    When I debug I see that lbName is getting the right SID (the little popup that shows), so I'm not sure why it's not feeding correctly into the form for filtering.

  3. #3
    Join Date
    Feb 2004
    Posts
    4
    I lied, it's not equaling Null when I put my cursor over Me.lbName in the line of code below.

  4. #4
    Join Date
    Feb 2004
    Posts
    4
    Ok I just copied the code that was working for the command button into the double click event on the listbox and everything works now... Everything works now... sorry for the hassle (to anyone who reads).

Posting Permissions

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