Populate listbox from paramaterized query in code

[mcalex]

Hi all
I'm trying to populate a listbox with the result of a parameterized query. This means I can't use the properties box to set the list box's query, coz I keep getting the Parameter Value dialog box, but I want to set the parameter in vba.

How do I tell the listbox what its RowSource is when I'm using a parameterized query? The RowSourceTypes don't include QueryDef (only Table/Query) so

lstNames.RowSource = qdfNames

doesn't work.

Can someone show me how I populate the listbox from a parameterized query.

tia,
mcalex

 

[Galaxiom]

lstNames.RowSource = qdfNames.SQL
It is a query and the sql is written into the Row Source.

 

[mcalex]

Hi Galaxiom

I tried your suggestion, but it didn't work - the listbox remained empty.
I also tried:
set lstNames.Recordset = qdfNames.OpenRecordset, but again, the listbox remained empty.

Have you any other suggestions?

cheers

 

[Galaxiom]

How do you generate the querydef?

I suspect you may not be referring to it properly.
How do you assign a value to the variable qdfNames.

 

[mcalex]

Yeah, starting to think I'm doing something stupid meself

OK

Dim candidate as String
Dim qdfNames As QueryDef
Dim rstNames As Recordset
 
candidate = Form_AnotherForm.getCandidate 'this works, i can test with MsgBox
 
Set qdfNames = CurrentDb.QueryDefs("qFindNames")
qdfNames.Parameters(0) = candidate
 
'here i've tried different ways to populate the list box
'lstNames.Recordset = qdfNames.OpenRecordset
 
'lstNames.RowSource = qdfNames.SQL
 
rstNames = qdfNames.OpenRecordset
lstNames.RowSource = rstNames.getRows

what am I doing wrong

 

[namliam]

Put in your query "qFindNames" something like <InsertName> as the parameter...

Then instead of using the Parameters thing, use the replace function to replace <InserName> by the candidate and send the replaced SQL into the Rowsource

Something like:
lstNames.Rowsource = replace(CurrentDb.QueryDefs("qFindNames").sql, x,y)

Alternatively if your query qFindNames is always and only used this way, instead of using a parameter, have it search on your Form_AnotherForm.getCandidate directly...
Using a parameter like:
Forms("Form_AnotherForm")!getCandidate

Good luck!

 

[DCrake]

This is going back to the old problem of trying to use one query for many purposes. If queries a form or parameter dependant then they tend to become isolated to the original form or calling agent.

To overcome this issue I put together a sample database on how to work with public variables when using form, queries and reports. Here is the link to it. There is full documentation on how to accomplish this.

David

 

[Galaxiom]

Running out of ideas.
If the listbox was on a subform I would expect a message can't find control.

When Access can't find a control it will try to apply it to a field in the record source. (This should complain that Parameter is not a property of a field).

Try fully qualifying the lstNames control
Me.Form!lstNames.RowSource = Candidate

Debug.Print the sql string from for the Querydef. Either it doesn't have the sql or it doesn't copy to the RowSource and this will tell you which step isn't happening.

 

[mcalex]

Hi Galaxiom

you're not the only one running out of ideas. At the moment, I'm taking DCrake's advice and trying to separate all the code bits from the form and database bits. I'm doing this more because I 'get' the correctness of it, rather than because I think it will help as, in this particular instance, the query IS tied to the form, is only used by the form, and if it wasn't for the form and query already existing, I would have written the sql string in vba and used a temporary qdf.

However, having moved all required variables into a code only module, with appropriate getX and setX methods, I'm still getting a form with a blank listbox. I kinda suspect some syntax error somewhere. Will look a bit more whilst trying to refactor further (ie, debug.print on every other line), and if I find the answer I'll pop a reply on this thread. If i remain stuck I'll probably give up and try some other approach.

Thanks for your time & effort, they're most appreciated

 

[mcalex]

a-ha

I didn't set the RowSourceType property.

When you set the RowSourceType property AND supply some sql to the RowSource property, you get values in your list box.

woo-hoo!

PS: I've set the params according to DCrake's 'use a get() method' idea, and I'm using a temp querydef so there's no more coupling of a named query to the form. That didn't do anything to fix the problems, but my code's cleaner.

cheers

 

[Galaxiom]

I guess I didn't make it clear when I said "Its a query" in my first post.

It was meant to be a response to:
RowSourceTypes don't include QueryDef (only Table/Query)