Hot Fox: [Access VBA Central] Loop a Query / SQL Statement

terça-feira, 8 de setembro de 2009

[Access VBA Central] Loop a Query / SQL Statement

I have a request to do a loop to automate a Query that appends the -1 if the infraction didn't happen. I tried to create a module to do this and then embed this into the Home screen when the app loads. But as you can see I am lacking my VBA Skills. Can someone help me with what I am doing wrong.

----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----

Option Compare Database

Function MinusOneLoop(MinusOneLoop) As Long

'Dimension Variables
Dim rs As Recordset
Dim db As Database

'Loop Through Query Results
Set rs = db.OpenRecordset("SELECT Count(*) AS MinusOneCheck FROM FACT AS F GROUP BY F.EMP_ID HAVING (((DateDiff('d',Max([STARTDATE]),Now()))>=30));")

Do While rs >= 1

DoCmd.OpenQuery ("qry_30Days_APPEND")

Loop Until rs = 0

End Function