Changing criteria in an access query based on a set of records in a table

[New-user via AccessMonster.com]

Hi there,

This is my first post so be gentle.

I need to run a query a number of times ( exporting the results to excel via
transferspreadsheet ) each time the criteria of the Account Number field has
to change based on a set of Account numbers in a table. I am stuck on the
code required to achieve this, I started down the track of creating a
function and using this as a criteria trying to reference the function to a
form. this was causing problamatic. Next I thought there must be someway of
using the table records as the criteria but here I am stuck on the syntax
required in the actual query.

the table is basically a set of account ids

Can anyone help me with code or point me in the right direction - should i be
using a form or function or using the records from the table.

I am able to write the loop on the query but just struggling with changing
the criteria in the query !

Any help would be fantastic !

Thanks!

 

[Guest]

Do this on a button click event (VBA) on a form. Once your loop has
determined the criteria, assign it to a text box on your form. It could be a
hidden text box). Let's call these frm1 and critBox.

As the last line in the loop, run the query that does the update. Here is
the key. In the criteria portion of the query, enter [Forms]![frm1
]![critBox].

This is assuming that the criteria is a single element. If it, for example a
range, have two: a critBoxMin and critBoxMax. In this case, the criteria
section in your query would look like this: >=[Forms]![frm1 ]![critBoxMin]
and <=[Forms]![frm1 ]![critBoxMax]

 

[New-user via AccessMonster.com]

Great thanks for the quick response !

I have set this up using the text box as the criteria within the query. The
problem is knowing the syntax to assign the 1st table record into the textbox
? Then i can figure out how to loop through each record in the table(so the
query criteria changes), while executing a macro at the end of the loop to
send the query result to excel.

Do i need to setup a recordset and loop through the records somehow?

Sorry but very new to this, thanks for your patience.

Cheers!

Do this on a button click event (VBA) on a form. Once your loop has
determined the criteria, assign it to a text box on your form. It could be a
hidden text box). Let's call these frm1 and critBox.

As the last line in the loop, run the query that does the update. Here is
the key. In the criteria portion of the query, enter [Forms]![frm1
]![critBox].

This is assuming that the criteria is a single element. If it, for example a
range, have two: a critBoxMin and critBoxMax. In this case, the criteria
section in your query would look like this: >=[Forms]![frm1 ]![critBoxMin]
and <=[Forms]![frm1 ]![critBoxMax]

Hi there,

[quoted text clipped - 20 lines]

Thanks!

 

[Guest]

I would use a variable in the module to act as a placeholder for the current
record's primary key. Let's call it curKey. I will also assume for the moment
that you have an autonumber field in your table called KeyID so that we can
loop through them using this as a reference.

Dim curKey as Integer
Dim recCount as Integer
For recCount = 1 to DCount("[fldField]","[tblTable]")
critBox = Dlookup("[AccountNumber]","[Accounts]","[KeyID] = " & recCount)
'Query and/or TransferSpreadsheet statements go here
Next

Using the autonumber field as a reference ensures that we have a way of
looping through all the records one time. If you do not have an autonumbe
field, you can create one (it does not have to be the primary key) and even
delete it later if this is just a one-time update.

There is a way to loop through the records in a recordset (For Each
<Record>In <Recordset>...Next), but I can't remember the correct object
references to refer to the records in the recordset. Using this method, you
can loop without having an autonumber field. I just posted this up in the
Access Programming forum, because I think it is probably more flexible than
relying on an AutoNumber field.

Great thanks for the quick response !

I have set this up using the text box as the criteria within the query. The
problem is knowing the syntax to assign the 1st table record into the textbox
? Then i can figure out how to loop through each record in the table(so the
query criteria changes), while executing a macro at the end of the loop to
send the query result to excel.

Do i need to setup a recordset and loop through the records somehow?

Sorry but very new to this, thanks for your patience.

Cheers!

Do this on a button click event (VBA) on a form. Once your loop has
determined the criteria, assign it to a text box on your form. It could be a
hidden text box). Let's call these frm1 and critBox.

As the last line in the loop, run the query that does the update. Here is
the key. In the criteria portion of the query, enter [Forms]![frm1
]![critBox].

This is assuming that the criteria is a single element. If it, for example a
range, have two: a critBoxMin and critBoxMax. In this case, the criteria
section in your query would look like this: >=[Forms]![frm1 ]![critBoxMin]
and <=[Forms]![frm1 ]![critBoxMax]

Hi there,

[quoted text clipped - 20 lines]

Thanks!