Put value from table/query into a variable

  • Thread starter Thread starter jasoneplatt
  • Start date Start date
J

jasoneplatt

I am trying to use a variable to repeat a macro. Depending on the
number of records in the affilliate table the macro will repeat once
for each. I can do the following:

Get the number of records in the affilliate table with SQL-
"SELECT Max(affilliate.aff_num) AS MaxOfaff_num FROM affilliate"

Repeat the macro for each record in the affilliate table-
Dim intRepeat as Integer
DoCmd.RunMacro "Copy Report", intRepeat, ""

What I don't know how to do is get the number of records from the SQL
query (or the temporary table it creates) to the intRepeat variable.
Am I going about this all wrong or am I just missing something. I have
searched far and wide for an answer and plenty of people are
interested in passing a variable to a query but I need to GET a
varible FROM a query.

Any help is much appreciated.
 
I am trying to use a variable to repeat a macro. Depending on the
number of records in the affilliate table the macro will repeat once
for each. I can do the following:

Get the number of records in the affilliate table with SQL-
"SELECT Max(affilliate.aff_num) AS MaxOfaff_num FROM affilliate"
Click to expand...

This does NOT get the number of records. It gets the largest value of aff_num.
If aff_num is an Autonumber you might *think* you're getting a count - but you
can't said:
Repeat the macro for each record in the affilliate table-
Dim intRepeat as Integer
DoCmd.RunMacro "Copy Report", intRepeat, ""

What I don't know how to do is get the number of records from the SQL
query (or the temporary table it creates) to the intRepeat variable.
Am I going about this all wrong or am I just missing something. I have
searched far and wide for an answer and plenty of people are
interested in passing a variable to a query but I need to GET a
varible FROM a query.
Click to expand...

intRepeat = DCount("*", "[affiliate]")

will count the number of records in the Affiliate table. You don't need the
query at all.

John W. Vinson [MVP]
 
Thanks. After some more searching I found that the best way to do it
in my situation was DLookup. Because there is more than one entry for
some of the affiliates Lookup skips the total number and looks at the
last ID number of the affiliate. Thanks again.
 
Thanks. After some more searching I found that the best way to do it
in my situation was DLookup. Because there is more than one entry for
some of the affiliates Lookup skips the total number and looks at the
last ID number of the affiliate. Thanks again.
Click to expand...
 
Back
Top