Passing a Date Variable from Code to Update Query in a Loop

[MJ]

My challenge is to pass a changing date variable from the code to an Update
Query.

I already have a variable declared (DateComplete) and in use within the code
that contains the value I want to pass, but just not sure how to make it
happen using the DoCmd.OpenQuery structure.

Below is some of the code to help scope this out:

======================
Private Sub CmdImport_Click() 'Import Files -- using the dates input on the
form

On Error GoTo Err_CmdImport_Click


Dim dbMyDB As Database
Dim EndDate As Date
Dim PrevDate As Date
Dim StartDate As Date
Dim d As Integer
Dim DaystoImport As Integer
Dim DateComplete As String
Dim strEnd As String
Dim strStart As String
Dim strStart2 As String

Set dbMyDB = CurrentDb
DoCmd.SetWarnings False
PrevDate = DLookup("When", "LastDateDone")
StartDate = PrevDate + 1
EndDate = Me.EndDate

If (EndDate < StartDate) Then
...

Else
DaystoImport = (EndDate - StartDate) + 1
For d = 1 To DaystoImport
DateComplete = Format(DateAdd("d", (d - 1), StartDate),
"mm/dd/yyyy")
...
DoCmd.OpenQuery "qry: Step3_Completed"
...
Next d
End If
...

End Sub
=================

Is there a simple/easy way to pass the value of variable "DateComplete" to
"qry: Step3_Completed" so that my users do not have to enter a date everytime
the loop is processed?

Thanks in advance for your assistance with this.

MJ

 

[Biz Enhancer]

Hi MJ,

You can try the following (excuse the roughness of code)
The code will delete and then recreate the query on each loop using the
variable to filter/modify the SQL string.

Private Sub CmdImport_Click() 'Import Files -- using the dates input on the
form

On Error GoTo Err_CmdImport_Click

Dim dbMyDB As Database
Dim EndDate As Date
Dim PrevDate As Date
Dim StartDate As Date
Dim d As Integer
Dim DaystoImport As Integer
Dim DateComplete As String
Dim strEnd As String
Dim strStart As String
Dim strStart2 As String
Dim qdf As QueryDef**
Dim vq1 As String, strSQL As String**
Set dbMyDB = CurrentDb()
DoCmd.SetWarnings False
PrevDate = DLookup("When", "LastDateDone")
StartDate = PrevDate + 1
EndDate = Me.EndDate

If (EndDate < StartDate) Then
....

Else
DaystoImport = (EndDate - StartDate) + 1
For d = 1 To DaystoImport
DateComplete = Format(DateAdd("d", (d - 1), StartDate),
"mm/dd/yyyy")
vq1 = "qry: Step3_Completed"**
strSQL = "SELECT table.field, table.field1, etc FROM table WHERE
(((table.date)= " & Datecomplete & "));"**
MyDB.QueryDefs.Delete vq1**
Set qdf = MyDB.CreateQueryDef(vq1, strSQL)**
DoCmd.OpenQuery "qry: Step3_Completed"
Next d
End If
End sub

If the code runs without the query existing, it will throw an error when it
tries to delete it. It will pay to trap for that error number and do a Resume
Next so that it does not get hung up on finding something that does not exist.

HTH,

Nick.

 

[Biz Enhancer]

Meant to say that the "**" were the additions I put in and should be removed.

 

[MJ]

Nick,

Looks possible, but I have a couple questions:
(1) In one of the lines you added, "MyDB.QueryDefs.Delete vq1**", the MyDB
is not defined. Were we suppose to :
(a) define it in another DIM statement (in addition to the dbMyDB DIM
statement); OR
(b) did you mean to make your line as "dbMyDB.QueryDefs.Delete vq1**"?
(2) These actions to delete and rewrite the query "qry: Step3..." with
each iteration of the loop, will still require user intervention to OK the
deletion each time, correct?

Thanks for your quick replies and helping me to understand this process
better. While my coding is rusty, I am the "expert" in my shop and need to
be able to help others understand what is happening when we make these
changes.

MJ

 

[Pat Hartman]

Rather than using the OpenQuery method, use the DAO or ADO (your preference)
..execute method. Here's a DAO example:

Dim db As DAO.Database
Dim qdDAO As DAO.QueryDef

Set db = CurrentDb()
Set qdDAO = db.QueryDefs!qDeleteRequiredEndorsementsStateOnly
qdDAO.Parameters![Enter VariableDataHeaderID] =
Forms!frmVariableDataHeader!txtVariableDataHeaderID
qdDAO.Parameters![Enter cboStateID] = StateID
qdDAO.Execute dbSeeChanges 'SQL 2005 seems to require the see changes
argument although 2000 did not and neither does Jet if that is what your
back end is

 

[Biz Enhancer]

It should read;
dbMyDB.QueryDefs.Delete vq1

My bad typing. The ** should all be removed they were only there to show
what I had added in.
The Delete line doesn't require any intervention from the users, they won't
even know it is happening. To them it will appear they click the button and a
query opens.
Personally, I never open queries for the users. If they need an output I
will always place it in a form, a report or pass data to an external
programme.

Regards,
Nick

 

[MJ]

Nick,

I inserted the code as you presented it with that minor change and it worked
great, thank you for the assist.

MJ

 

[Biz Enhancer]

Glad to be of use. Considering the amount I have gained from these forums
it is nice to be able to give back occasionally.