VBA Code

[scoe]

I have an error in code set up in one of our databases and I can't see why it doesn't like it. Any help would be gratefully appreciated.

Private Sub Course_GotFocus()
   Dim Msg, Style, Title, Help, Ctxt, Response, MyString
   If ClaimCount > 0 Then
      Msg = "Would you like to print the claim form before you select another course? If you don't, the claims for this course will be lost"
      Style = vbQuestion + vbYesNo
      Title = "Change course?"
      Response = MsgBox(Msg, Style, Title, Help, Ctxt)
      If Response = vbYes Then         If TotalEnglish > 0 And TotalNotEnglish = 0 Then
            stDocName = "Functional Skills Entry Level ENGLISH"
            DoCmd.OpenReport stDocName, acPreview
         Else
            If TotalEnglish > 0 And TotalNotEnglish > 0 Then
               stDocName = "Functional Skills Entry Level plus ENGLISH"
               DoCmd.OpenReport stDocName, acPreview
            Else
               stDocName = "Functional Skills Entry Level"
               DoCmd.OpenReport stDocName, acPreview
            End If
         End If
         DoCmd.SetWarnings False
         stDocName = "Update FS Entry Level Claims"
         DoCmd.OpenQuery stDocName, acNormal, acEdit
         Refresh
         stDocName = "Update FS Entry Level Claims ENGLISH"
         DoCmd.OpenQuery stDocName, acNormal, acEdit
         Refresh
      End If
      Forms![Functional Skills Entry Level].Requery
      If Response = vbNo Then
         stDocName = "Clear Functional Skills Entry Level Claims"
         DoCmd.OpenQuery stDocName, acNormal, acEdit (This is the bit the debug selected)
         Course.SetFocus
      End If
   End If
Exit_Course_GotFocus_Click:
   Exit Sub
End Sub

[Dal Jeanis]

FYI - if you put the word CODE in square brackets [] before your code, and /CODE in square brackets afterward, then the forum will preserve your indenting, making it easier to read.


I don't see anything much wrong with it, syntax-wise.
First, verify that this is actually the precise name of a query: "Clear Functional Skills Entry Level Claims"

Code Cleanup -

1) It may help identify some problems if, at the top of the module, you put :
Option Compare Database
Option Explicit

2) Next, declare all your variables

Dim stDocName As String
Dim stQueryName As String

3) For the DoCmd OpenQuery, use stQueryName instead of stDocName (this is for clarity, not for syntax)

4) For the DoCmd OpenQuery, use acViewNormal rather than acNormal. Both those constants equate to zero, so that's not your problem. Once again, this is for clarity, not syntax.

[scoe]

Thank you, I will have another look at it.

[scoe]

Thank you , just to update, I went back to the original data and broke it down to two select queries and then combined them to get the correct answer in the end, thank you again for all your help.

[scoe]

Thank you, my error (mispelled the check box) works brilliantly