Hi
I am trying to use a macro to run an Append Query. The number of times the query needs to run is determined by the number of months from a certain date to now. The problem that I am experiencing is in referring to a field in a query or table in the "repeat expression" part of the macro.

[LastDate]![LastOfBalDate]< Now()

It seems the macro will only look for controls in forms or reports. How do I get it to look at a query?

thanks for your help
liz

Give more details and I bet this can be done by running the query once...

not really ...and it is quite complicated to go through now. A brief outline is that I need to calculate compounded interest monthly per client and then adjust it for any withdrawals or deposits made. I need the balance per month to be a seperate record, which is fine because I can append them to a balances table monthly. The only problem is that the user often needs to amend transactions from a couple of months. When this happens, I need to update the monthly balances from when the change occured to date. Hence the loop macro.

[LastDate]![LastOfBalDate]

You can't refer to a field in this way as the field may have more than one record - with forms and reports this method references a specific value.

Can't you use code rather than macros to create what you want? The flexibility is definitely much greater.

Would love to - but I really don't know where to start.

I tried converting the macro to a module and have pasted the code below.

Option Compare Database

'------------------------------------------------------------
' UpdateLoop_UpdateLoop2
'
'------------------------------------------------------------
Function UpdateLoop_UpdateLoop2()
On Error GoTo UpdateLoop_UpdateLoop2_Err

DoCmd.SetWarnings False
DoCmd.RunMacro "UpdateLoop.Loop1", , "[LastDate]![LastOfBalDate]<Now()"


UpdateLoop_UpdateLoop2_Exit:
Exit Function

UpdateLoop_UpdateLoop2_Err:
MsgBox Error$
Resume UpdateLoop_UpdateLoop2_Exit

End Function


'------------------------------------------------------------
' UpdateLoop_Loop12
'
'------------------------------------------------------------
Function UpdateLoop_Loop12()
On Error GoTo UpdateLoop_Loop12_Err

DoCmd.RunSQL "SELECT CuLineBal.ClientID, DateAdd(""d"",1,[MonthEnd]) AS [Date], CuLineBal.SumOfLineBal INTO OpeningBalances FROM CuLineBal, LastDate WHERE (((DateAdd(""d"",1,[MonthEnd]))=DateAdd(""m"",1,[LastDate]![LastOfBalDate])));", -1


UpdateLoop_Loop12_Exit:
Exit Function

UpdateLoop_Loop12_Err:
MsgBox Error$
Resume UpdateLoop_Loop12_Exit

End Function