putting the results of a query into a listbox

putting the results of a query into a listbox

Post by Steve Jorgense » Wed, 06 Oct 1999 04:00:00



You'll get another error when you fix the first one.

The error you received indicates that the query "qrySearch" requires a
parameter that you did not specify.  If you open a recordset from a query in
code, parameters that refer to form controls are not automatically filled
in.  To run such a query from code, you must first create a reference to the
querydef, set the parameter in its Parameters collection, then open a
recordset based on the querydef reference.

Now the next problem:  The .AddItem method applies only to a command bar
combo box, not to a combo box control on a form.  To do what you want,
you'll have to set the combo box's RowSourceType to Value List, and set the
RowSource property to a string containing all the items separated by
semicolons.  Incidentally, why can you not just set the Combo Box's
RowSource to "qrySearch? to do what you want with no code at all?


>hello
>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, ""
>Else:
>   Do Until .EOF
>    [form data product catalogue
>dummy].lstSearchResults.AddItem
>rsSearchResults!DPID.MoveNext
>    Loop
>    [form data product catalogue dummy].Show

>    'close the recordset "SearchResults"
>    SearchResults.Close
>    Set SearchResults = Nothing

>End with
>End If
>End Sub

>if anyone knows what im doing wrong id be grateful
>thanks

 
 
 

putting the results of a query into a listbox

Post by Jim in Clevelan » Wed, 06 Oct 1999 04:00:00



> hello
> 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:
> <snip>

What the heck happened to my beautiful code?
--
Jim in Cleveland
If you're writing to me, in my address
change "REAL_Address.see.below" to "worldnet.att.net"

"What's so funny 'bout peace, love & understanding?"
     - Nick Lowe

 
 
 

putting the results of a query into a listbox

Post by Peter Stron » Thu, 07 Oct 1999 04:00:00


hello
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, ""
Else:
   Do Until .EOF
    [form data product catalogue
dummy].lstSearchResults.AddItem
rsSearchResults!DPID.MoveNext
    Loop
    [form data product catalogue dummy].Show

    'close the recordset "SearchResults"
    SearchResults.Close
    Set SearchResults = Nothing

End with
End If
End Sub

if anyone knows what im doing wrong id be grateful
thanks

 
 
 

putting the results of a query into a listbox

Post by Doug Hutcheso » Thu, 07 Oct 1999 04:00:00


Peter,
Access list/combo boxes do not support the VB 'AddItem' method.
Access boxes accept a value list or a recordset (query or table).
Thus, your code would be:

[form data product cataloguedummy].lstSearchResults.RowSourceType =
"Table/Query"
[form data product cataloguedummy].lstSearchResults.RowSource =
Customer.OpenRecordset("qrySearch")

You do not need to open the recordset in code, so all your DAO for this can
be discarded.

HTH
Cheers,
Doug
--
Dev Ashish's FAQ & Help site: http://www.mvps.org/access/
Michael (michka) Kaplan's site http://www.trigeminal.com
Microsoft support: http://support.microsoft.com/support
News archive: www.deja.com.
Newgroup info:  http://www.mvps.org/access/netiquette.htm
------------------------------------------
I heed advice, but ignore attacks, so don't bother to flame.
------------------------------------------


>hello
>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, ""
>Else:
>   Do Until .EOF
>    [form data product catalogue
>dummy].lstSearchResults.AddItem
>rsSearchResults!DPID.MoveNext
>    Loop
>    [form data product catalogue dummy].Show

>    'close the recordset "SearchResults"
>    SearchResults.Close
>    Set SearchResults = Nothing

>End with
>End If
>End Sub

>if anyone knows what im doing wrong id be grateful
>thanks

 
 
 

putting the results of a query into a listbox

Post by Doug Hutcheso » Thu, 07 Oct 1999 04:00:00


Dagnabbit!

I meant to say:
[form data product cataloguedummy].lstSearchResults.RowSource = "qrySearch"

Cheers,
Doug
--
Dev Ashish's FAQ & Help site: http://www.mvps.org/access/
Michael (michka) Kaplan's site http://www.trigeminal.com
Microsoft support: http://support.microsoft.com/support
News archive: www.deja.com.
Newgroup info:  http://www.mvps.org/access/netiquette.htm
------------------------------------------
I heed advice, but ignore attacks, so don't bother to flame.
------------------------------------------


>Peter,
>Access list/combo boxes do not support the VB 'AddItem' method.
>Access boxes accept a value list or a recordset (query or table).
>Thus, your code would be:

>[form data product cataloguedummy].lstSearchResults.RowSourceType =
>"Table/Query"
>[form data product cataloguedummy].lstSearchResults.RowSource =
>Customer.OpenRecordset("qrySearch")

>You do not need to open the recordset in code, so all your DAO for this can
>be discarded.

>HTH
>Cheers,
>Doug
>--
>Dev Ashish's FAQ & Help site: http://www.mvps.org/access/
>Michael (michka) Kaplan's site http://www.trigeminal.com
>Microsoft support: http://support.microsoft.com/support
>News archive: www.deja.com.
>Newgroup info:  http://www.mvps.org/access/netiquette.htm
>------------------------------------------
>I heed advice, but ignore attacks, so don't bother to flame.
>------------------------------------------

>>hello
>>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, ""
>>Else:
>>   Do Until .EOF
>>    [form data product catalogue
>>dummy].lstSearchResults.AddItem
>>rsSearchResults!DPID.MoveNext
>>    Loop
>>    [form data product catalogue dummy].Show

>>    'close the recordset "SearchResults"
>>    SearchResults.Close
>>    Set SearchResults = Nothing

>>End with
>>End If
>>End Sub

>>if anyone knows what im doing wrong id be grateful
>>thanks

 
 
 

putting the results of a query into a listbox

Post by Don Leverto » Thu, 07 Oct 1999 04:00:00


Hi Peter,

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
combo-box's recordsource:

SELECT DISTINCT tblCustomers.City
FROM tblCustomers
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,
tblCustomers.Prov
FROM tblCustomers
WHERE (((tblCustomers.City)=[Forms]![frmFindCustomers]![cboCity]))
ORDER BY tblCustomers.CompanyName, tblCustomers.LastName,
tblCustomers.FirstName;

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
combo-box...
I created an event procedure in the AfterUpdate property of that combo-box:

Private Sub cboCity_AfterUpdate()
Me![lstCustomers].Requery
End Sub

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!)

HTH,
Don
****************************************************************************
***********


Quote:> hello
> 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, ""
> Else:
>    Do Until .EOF
>     [form data product catalogue
> dummy].lstSearchResults.AddItem
> rsSearchResults!DPID.MoveNext
>     Loop
>     [form data product catalogue dummy].Show

>     'close the recordset "SearchResults"
>     SearchResults.Close
>     Set SearchResults = Nothing

> End with
> End If
> End Sub

> if anyone knows what im doing wrong id be grateful
> thanks

 
 
 

putting the results of a query into a listbox

Post by Peter Stron » Thu, 07 Oct 1999 04:00:00


I seem to have a solution.
thanks to everyone for their help
it's a little different to what I had previously:

Dim CountResults As Integer
Dim Customer As Database
Dim SearchResults As Recordset

Private Sub CmdSubmitSearchDetails_Click()

Set Customer = CurrentDb()

'opens query so the number of results can be detremined
DoCmd.OpenQuery ("qrySearch")

CountResults = DCount("*", "qrySearch")

If CountResults = 0 Then

    DoCmd.OpenForm ("Form Data Product Catalogue")

    DoCmd.Close acForm, "Search_CC"

    MsgBox "No matches found", vbOKOnly, ""

    DoCmd.Close acQuery, "qrySearch"

Else: DoCmd.OpenForm ("Form Data Product Catalogue")

    'the next two lines is are what sets the control source for the listbox
    [Forms]![Form Data Product Catalogue]!ListSearchResults.RowSourceType =
"Table/Query"
    [Forms]![Form Data Product Catalogue]!ListSearchResults.RowSource =
"qrySearch"

    DoCmd.Close acQuery, "qrySearch"

    'ListSearchResults.Requery

    DoCmd.Close acForm, "Search_CC"

End If

End Sub


Quote:> hello
> 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, ""
> Else:
>    Do Until .EOF
>     [form data product catalogue
> dummy].lstSearchResults.AddItem
> rsSearchResults!DPID.MoveNext
>     Loop
>     [form data product catalogue dummy].Show

>     'close the recordset "SearchResults"
>     SearchResults.Close
>     Set SearchResults = Nothing

> End with
> End If
> End Sub

> if anyone knows what im doing wrong id be grateful
> thanks

 
 
 

1. Using several listboxes with 1 vertical scrollbar (result of query)

Hi.
  I have a problem using listboxes for displaying the result of a query.  I
have 7 listboxes wich contains the result of a SQL-query.
The problem:
    - when running the query, the listboxes gets a vertical scrollbar when
the result is bigger than the design of the listbox (this is normal).  When
I scroll the first listbox, the rest of the listboxes is unaffected by the
scrolling.

Is there a way to make 1 vertical scrollbar to controll ALL the listboxes??
In my code I fill the listboxes this way:
    listbox1.additem rstTemp!1st_result1
    listbox2.additem rstTemp!1st_result2
    listbox3.additem rstTemp!1st_result3
    ..
rstTemp is a temp. recordset.

Please......I'm gettin desperate....

Best regards
Ole Peder Teigen

2. 7.2 and current timestamp bug?

3. can't remember how to put results of select query in a text file

4. Drop A view????

5. Please help me with a query statement and putting results in a flexgrid

6. Monolith Megacom and MS NT/MvBase

7. Put result query in procedure

8. Creating MSXML Object Hangs Job

9. please help:need secondary index put on query result

10. Putting multiple query results in a form

11. Prolly easy question: putting values in listbox

12. Putting CSV items into a listbox

13. Returning Stored Procedure Results to Listbox