-3

I got this code for measuring the time of a query in Access database. Every time I try to run it, I get syntax error and MyTest() line is highlighted.

Option Compare Database

Option Explicit

Private Declare Function timeGetTime _
Lib "winmm.dll" () As Long
Private mlngStartTime As Long

Private Function ElapsedTime() As Long
ElapsedTime = timeGetTime() - mlngStartTime
End Function

Private Sub StartTime()
mlngStartTime = timeGetTime()
End Sub

Public Function MyTest()

Call StartTime
DoCmd.OpenQuery "Query1"
DoCmd.GoToRecord acDataQuery, "Query1", acLast

Debug.Print ElapsedTime() & _

Call StartTime
DoCmd.OpenQuery "Query2"
DoCmd.GoToRecord acDataQuery, "Query2", acLast

Debug.Print ElapsedTime() & _
End Function
halfer
  • 19,824
  • 17
  • 99
  • 186

2 Answers2

2

Here's another alternative (old VB6/VBA - not VB.Net syntax).

KEY SUGGESTION: the "_" characters are "continuation lines". I honestly don't think you want them in most of the places you're using them.

IMHO...

Option Explicit

Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Private startTime, endTime As Long

Private Function elapsedTime(t1, t2 As Long) As Long
  elapsedTime = t2 - t1
End Function

Public Function MyTest()

  startTime = Now
  ' << do stuff >>
  endTime = Now
  MsgBox "Elapsed time=" & elapsedTime(startTime, endTime)

End Function

Private Sub Command1_Click()
  Call MyTest
End Sub

edited.

Zaider
  • 1,950
  • 1
  • 21
  • 31
paulsm4
  • 114,292
  • 17
  • 138
  • 190
1

In the two lines

Debug.Print ElapsedTime() & _

you are using the string concatenation character & and the line continuation character _ at the end of the line, even though the statement does not continue on the next line. So, either

  1. continue the statement on the next line, e.g.

    Debug.Print ElapsedTime() & _
        " milliseconds"  ' or whatever unit is returned by your ElapsedTime call
    

    or

  2. Remove the unneeded code:

    Debug.Print ElapsedTime()
    

PS: Welcome to StackOverflow. Please get a good book on VBA development and read it. It's ok to ask basic questions here, but "please fix this code I found, because I don't know the basics of the language and don't have the time to learn it" type of questions are frowned upon and likely to be closed.

Heinzi
  • 167,459
  • 57
  • 363
  • 519
  • pls try it on ur system cos I am new to VBA – Emmanuel Asogwa Dec 25 '11 at 23:00
  • 4
    @EmmanuelAsogwa: Nope, sorry. People on SO will gladly help you with specific questions and share knowledge, but we won't do your work and debug your code for you. If you need that, consider paid IT consulting services. – Heinzi Dec 25 '11 at 23:06