Temporary Pass-through Query as Rowsource for Listbox?

[Michael Koenig]

Howdy! I'm stumped!

Here's the scenario...

I've got a form where the user inputs an account number into a text box. I
then use that number as a parameter in a dynamic pass-through query (query
changes every time account number is changed), and write the results to a
temporary, un-named DAO recordset. That works just fine.

What I'm wanting to do is display the recordset data to the user in a
listbox - first off, is this even possible? I keep getting "Type Mismatch"
errors when I try to assign the recordset (rst), or the querydef (qdf) as the
list box's rowsource.

Here's my code -

Set qdf = CurrentDb.CreateQueryDef("", strSQL)
qdf.Connect = strConnect

Set rst = qdf.OpenRecordset()

This is where I get stuck...

Suggestions?

-- Michael

 

[Dirk Goldgar]

Howdy! I'm stumped!

Here's the scenario...

I've got a form where the user inputs an account number into a text box. I
then use that number as a parameter in a dynamic pass-through query (query
changes every time account number is changed), and write the results to a
temporary, un-named DAO recordset. That works just fine.

What I'm wanting to do is display the recordset data to the user in a
listbox - first off, is this even possible? I keep getting "Type Mismatch"
errors when I try to assign the recordset (rst), or the querydef (qdf) as
the
list box's rowsource.

Here's my code -

Set qdf = CurrentDb.CreateQueryDef("", strSQL)
qdf.Connect = strConnect

Set rst = qdf.OpenRecordset()

This is where I get stuck...

Suggestions?

What version of Access are you using? In Access 2003, at least, you can set
the list box's Recordset property to the recordset you have opened:

Set lstMyListbox.Recordset = rst

 

[Michael Koenig]

Dirk,

That's the scary part... I'm stuck using Access 97.

 

[Dirk Goldgar]

That's the scary part... I'm stuck using Access 97.

Not so scary -- Access 97 is solid, stable product. But I think then your
best bet is to use a custom list-filling function. I don't have a copy of
A97 handy to look this up in, but as I recall, you can set the list box's
RowSourceType property to the name of a function you create, which has
certain specific format. I've done this to fill a list box with a list of
files in a given folder.

The function gets called at various stages in the combo box's querying
process, including once to initialize it, once to get the number of rows,
and once for each row to be returned. So in the initialize section, you'd
open a static recordset, and MoveLast and MoveFirst to make sure the
RecordCount is known so that you can return it when called to do so. Then
with each call to get row data, you return the appropriate data from the
recordset, and in the "finish up" section you close the recordset.

I think if you look up RowSourceType in the Access 97 help file -- the last
really good help file for Access -- you'll be able to find a help topic
explaining how to do it.

 

[Michael Koenig]

Dirk,

I found what you're referring to - I'll give it a shot!

Thank you for all of your help. You guys are life-savers!