Loop Through a Query to Find Particular Values

[AccordingToDale]

Hi,

I'm not a big VBA or Access guy, so...

Anyone know how to loop through an MS Access Query in VBA? This is for a code module in Access.

What I need to do is loop through the Query and write certain values to an Excel Worksheet.

The writing to Excel is no problem, but I have no idea how to loop through the Query. (I need to loop through it b/c depending on the result I will write to different cells in Excel.)

Thanx.

d

 

[AccordingToDale]

ahhh. got it.

 

[afryer]

Hi,

Best way is to use a recordset:


dim l_rec_set as RecordSet
dim l_str_sql as String
dim l_counter as integer

l_str_sql = "SELECT...." 'your SQL here

set l_rec_set = currentDb.OpenRecordSet l_str_sql

' get recordcount

l_rec_set.MoveLast
l_rec_set.MoveFirst

for l_counter = 1 to l_rec_set.RecordCount

' print the data for the first field

msgbox l_rec_set.fields (0)
l_rec_set.MoveNext
next l_counter

l_rec_set.Close

 

[MichaelRed]

Seems like an (un) reasonable approach? Sort of Picking through the hay stack by hand instead of using the magnet hanging on the side of the wagon?

[tab]"Looking through a query ... " implies ther existance of a RECORDSET? So why not just use a QUERY to search the recordset for the item(s) of interest and export the query results?




MichaelRed

 

[AccordingToDale]

i am dealing with 3 recordsets. each containing anywhere from 2 to 15 records. depending on the number of records and the values returned makes all the difference as to what i do with the data.

the query is also dynamic, based upon user input.

it's actually very straight forward to loop through and do what needs to be done. easier than creating the exponential number of queries to cover all possibilities.

thanx anyway.

it's all doing everything that needs to be done.