Update Pass through Query?

[ceej]

Hi All,

I'm trying to populate a list box. Due to performance I'm having to use a Pass through Query. Query will be different every time user runs it due to criteria selection.

I was successful at creating a recordset using ADO, but I couldn't figure out how to pass the recordset to the listbox.

So after reading several posts, I thought I would make the Pass through query a saved query and then update the query when the user makes their selections.

Is this the best way to handle this scenario?

Thanks,

Ceej

 

[ProgramError]

Hi Ceej

try using the query as the 'row source' for the listbox. Any queries you have created should appear in the dropdown box when you click on the arrow on the right od the row source text box. Just select the approrpiate one from the list.

Alternativelty you could use the wizard to create the listbox and have the source based on a query when building it.


Ian M (UK)


Program Error
Programmers do it one finger at a time!

 

[ceej]

Hi Ian,

That is what I'm thinking. The problem is that the query is dependent on user selected customers and is a pass through query (Access will not look at the SQL code).

It looks like I will have to update the pass through query before it runs.

I wished that I new how to populate a listbox with a recordset. The only way I could think of is creating a temporary table and then requery the listbox based on that table.

Ceej

 

[RoyVidar]

Setting the recordset of a combo/list, I think is a bit dependent on version, and I think it requires 2002 (xp)+ versions

[tt]with rs
set .activeconnection = <your connection *)>
.cursortype = adopenkeyset
.locktype = adlockreadonly
set me!lstMyList.recordset = rs
end with[/tt]

*) this might sometimes bomb on a combo, see ACC2002: Combo Box Value Is Blank After You Set Recordset Property

Use rowsourcetype "table/query".

You can alter the sql of queries through ADOX, see for instance How To Use ADO and ADOX to Modify the Base Query of an Access QueryDef Object in Visual Basic, but it's probably both easier and faster using DAO querydef (should be available through search).

Roy-Vidar