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) & ","
' 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
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.
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).