Using data stored in VB variables in an update query

[Guest]

I have a database function I'm trying to create which will upload a batch of
files with a naming convention of "BDMMDDYY.TXT". The upload itself is not a
problem, but I also need to add the transaction date of the file and the date
of the upload to the table as each file is uploaded. The file is uploaded to
a temp table, which the transaction date and current date are then added to
through an update query. However, those queries were originally getting their
update dates from a form. Now I want to run them through my auto-upload code,
and they error out because the form is not open. Is there a way to use the
variables used to get the filenames to be uploaded to generate the date for
the update query as well? The code that gets the filenames looks like this:

strDestinationName = "C:\Import\" & strFileType & strFileDateMonth &
strFileDateDay & "05.txt"

I want to have the update use strFileDateMonth and strFileDateDay to update
the date of the transaction, since these variables control what file is
uploaded, and each file's filename is date-coded. The dates are not contained
in the files. Any suggestions?

 

[TC]

If you declared those variables at the top of a standard module (not a
form module) you could refer to them in the query, if I recall
correctly. When I say at the top of the module, I mean /before/ the
subs & functions (if any) in that module.

You do not need to put your actual updating code, in that module. You
just need to declare the relevant variables, there. By "declare" I
mean, their Dim statements.

HTH,
TC

 

[Guest]

I got this to work by using the code to open and update a field on a form and
then running the query based off the field value, but thanks for the tip
anyway since I'll probably need to know that for future reference.