I'n not exactly sure what you are doing or why you are using a listbox to
return records... personally I would have used a subform datasheet (if I do
understand what it is you trying to accomplish), but that's your choice.
Anyways, I also feel that you are making things a little more difficult than
they have to be with the addition of records to a recordset etc.... why
don't you simply requery the listbox after specifying the parameters?
Here's what I'm *assuming* that you are trying to do. (Please forgive me if
I've misunderstood completely.)
1.) You want to enter some text into a textbox, or maybe even use a
combo-box to select an existing value in your Customer table.
2.) Then you want to click a button to query that table and return a set of
records that meet the specified criteria. Am I right so far?
If so, this is how I would handle it....
1.) Create an unbound form called frmFindCustomers. This form will be used
to display a set of customers who all live in the same (selected) city. ie
"Show me all of my customers that live in Calgary (Canada's Greatest City,
by the way. <G>)" (or whatever other city that I select from a combo-box.)
2.) Create an unbound combo-box called "cboCity", which uses a set of unique
city name values that is based on tblCustomers. Here is the SQL from the
SELECT DISTINCT tblCustomers.City
ORDER BY tblCustomers.City;
3.) The next step is to add a data container for the returned records. Like
I said, my first choice would have been a subform datasheet, but you did say
listbox, so we'll go with that. Shouldn't really matter anyway... this
should work for either.
Create an unbound listbox (also based on tblCustomers). I let the wizard do
it's thing, then went in and modified the SQL to allow for parameters. This
is what I ended up with for the recordsource for the listbox:
SELECT DISTINCTROW tblCustomers.CustID, tblCustomers.CompanyName,
tblCustomers.LastName, tblCustomers.FirstName, tblCustomers.City,
ORDER BY tblCustomers.CompanyName, tblCustomers.LastName,
4.) OK, now we have the basic structure set up. Now we have to deal with the
requerying of the listbox when we select a different value from the City
I created an event procedure in the AfterUpdate property of that combo-box:
Private Sub cboCity_AfterUpdate()
5.) That's it... simple, easy and quick!
I didn't bother with the "" MsgBox "No matches found" "part, because I know
that the city exists because it's in my combo-box. Also there should be at
least one record returned because the City field was filled when the
customer record was added!
6.) You might want to stick with the command button to do the requery if you
want to specify more than one criteria. This would also probably mean that
you would have to use CreateQueryDef in order to build the SQL on-the-fly.
It would also mean using a few "If Not IsNull" 's to ignore empty controls
when building the WHERE part of your SQL statement. I do have an Access 2.0
sample .MDB that will guide you through this if you want... actually it *is*
an address-book type application and may be exactly what you are looking
for... (shoulda thought of that sooner). Let me know if you would like me to
e-mail it to you (Provided that this is not your homework assignment!)
> Im trying to put the results of a query into a list box.
> I am currently getting the following error: Too Few Parameters:
> Expected 1
> This is my code:
> Dim Customer As Database
> Dim SearchResults As Recordset
> Private Sub CmdSubmitSearchDetails_Click()
> Set Customer = CurrentDb()
> Set SearchResults = Customer.OpenRecordset("qrySearch")
> If SearchResults.RecordCount = 0 Then
> MsgBox "No matches found", vbOKOnly, ""
> Do Until .EOF
> [form data product catalogue
> [form data product catalogue dummy].Show
> 'close the recordset "SearchResults"
> Set SearchResults = Nothing
> End with
> End If
> End Sub
> if anyone knows what im doing wrong id be grateful