Putting Unique Query Results into Variables; VBA?

[JonAccess]

Hello!

I have a query that returns unique values of one field. There can be from 1 to 10 unique values that are returned. Ex. 099 and 010 could return one day. And 002, 110, 956, 010, 099 could return another day.

I want to be able to put each returned value (however many) into an individual variable so i can use them in other queries I already have.

I tried DLookup() but it only returns the first value. And ELookup() wouldn't work for me.

Any ideas?

 

[JonAccess]

P.S. My query name is "qry_BRC" and the field with unique values in the query is named "BR"

 

[gemma-the-husky]

why save them at all

just use this query in your next query, surely

 

[mdlueck]

So you have visual QueryDef objects you desire to run from VBA code, or???

Yes you can open / run visual QueryDef type queries. Those are DAO.QueryDef objects.

Sample code to build / execute a DAO.QueryDef object

  Dim daoDB As DAO.Database
  Dim daoQDFfe As DAO.QueryDef
  Dim strQryNameFE As String

  'Attach to the FE DB
  Set daoDB = CurrentDb()

  'Build the FE Query
  Set daoQDFfe = daoDB.CreateQueryDef(strQryNameFE)
  With daoQDFfe
    .SQL = strSQLfe
    .Execute
    .Close
  End With

You may also open existing QueryDef object and manipulate the query it is to run. I just happen to define my queries dynamically at run-time, so there is no need in my case to read in what the query is going to do and edit it.

 

[JonAccess]

I have saved queries because I'm using Access as a "middleman" and manipulating imported data and then exporting it. I have a process i go through everytime to get the raw data in the desired results and I'm trying to automate it. I have the queries run in order by the db.execute method.

What I'm trying to do is...
I import data into a table. This table will have a field named "BR". "BR" will have numerous records in it, BUT will only have a few unique numbers in it. For Ex. 099,099,022,099,022,022,022,010. I only want 099,022,010.
Currently i type those numbers in an unbound from and I have VBA reference those values. Those values are stuck into a variable and used later in code. But i want VBA to grab those numbers, stick them into a variable without me manually typing them in. I want Access to look in the table, find the unique numbers, stick them in a variable. Any simple way to do that?

 

[mdlueck]

Currently i type those numbers in an unbound from and I have VBA reference those values. Those values are stuck into a variable and used later in code. But i want VBA to grab those numbers, stick them into a variable without me manually typing them in. I want Access to look in the table, find the unique numbers, stick them in a variable. Any simple way to do that?

Create a table that contains the field name / variable name mapping?

fld1 : var1
fld2 : var2
etc...

two columns, + an autoincrement ID field

 

[JonAccess]

Is there not a way to say for example...1. Open Query2. Loop through and put 1st value in variable13. Loop through 2nd value and stick it into variable24. If EOF then stop looping?