Link to home
Create AccountLog in
Avatar of StampIT
StampITFlag for United States of America

asked on

Cannot step through Access VB code

I inherited an Access 2007 database that I must maintain. In a module there is a Boolean function that I tried to step through(F8). However it apparently is not allowed. If I try to execute this code a Macro window appears. Without going into a lot of detail does anyone know why this would be? I have attached the code I attempted to execute. Thanks.
Function Test3(datStartDate As Date, datEndDate As Date)

  Dim db As Database
  Dim qdf As QueryDef
  Dim strSQL As String
  Dim Fst As String
  
  Test3 = True
  
  On Error GoTo Test3_Err
  
  Set db = CurrentDb
  Set qdf = db.QueryDefs("qraAttDistTotalsShift")
  
  strSQL = "INSERT INTO tblDistTotalsShift ( GLNumber, PayDate, Amt, Shift ) " & _
           "SELECT tblAttendance.GLNumber, tblAttendance.Shift, tblPayDay.PayDate, " & _
           "Sum(RoundNum((CalcHrs([ModClockIn],[ModClockOut])-[ModLunchMin])*" & _
           "HourlyRate(tblAttendance.EM_KEY, ModRate, 0, leadman, tblAttendance.EarnCode),2)) AS Amt " & _
           "FROM (tblPayDay INNER JOIN tblAttendance ON (tblPayDay.Em_Key = " & _
           "tblAttendance.EM_KEY) AND (tblPayDay.PayDate = tblAttendance.PayDate))" & _
           " INNER JOIN tblEM ON tblAttendance.EM_KEY = tblEM.EM_KEY " & _
           "WHERE (((tblPayDay.Incentive)=False) AND ((tblAttendance.PayDate) " & _
           "Between #" & datStartDate & "# And #" & datEndDate & "#)) " & _
           "GROUP BY tblAttendance.GLNumber, tblPayDay.PayDate, tblAttendance.Shift;"


  qdf.SQL = strSQL
  
  db.Execute "qraAttDistTotalsShift"
  
  MsgBox "qraAttDistTotalsShift Finished"
  
Test3_Exit:
  On Error Resume Next
  
  Set qdf = Nothing
  Set db = Nothing
  
  Exit Function
  
Test3_Err:
  Test3 = False
  
  MsgBox "The following error occurred in Function in Test3: " & Chr(13) & _
         "Error # " & str(Err.Number) & " " & Err.Description & "." & Chr(13) & _
         "Generated by " & Err.Source
         
  Resume Test3_Exit
  
End Function

Open in new window

Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

 A macro dialog will appear if the code has already stopped executing.

  Put a STOP right at the top of the procedure, then use F8 once you hit it.

JimD.
It's because it is a function that takes parameters. You can't call them using F8 since it won't know what to do with datStartDate and datEndDate.
What you need to do it put a breakpoint at the top of the function. Then when you use the function, you can step through the code.
If you want to test it do something like.
Public Sub funcTester()
  Test3(DateSerial(2010, 12, 29), DateSerial(2010, 12, 32)
End Sub

Then step through that using F8.
<<I inherited an Access 2007 database that I must maintain. In a module there is a Boolean function that I tried to step through(F8).>>

 and BTW, that's not returning a boolean data type but a variant as you didn't declare the return type.

JimD.
ASKER CERTIFIED SOLUTION
Avatar of TommySzalapski
TommySzalapski
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Actually, part of your problem is that your Insert fields read: GLNumber, PayDate, Amt, Shift
and your Select fields read: GLNumber, Shift, PayDate, Amt

These two sequences must coincide!  So change the INSERT  sequence to:

INSERT INTO tblDistTotalsShift ( GLNumber, Shift , PayDate, Amt)

You could also replace the line:

db.Execute "qraAttDistTotalsShift"

with

qdf.Execute
Avatar of StampIT

ASKER

Thanks.