INSERT query inside an error handler?

[Richard Hollenbeck]

In a gradebook program, when a user clicks to view groups (of activities)
within a course, my groups form assumes that particular course has a group
already in it. If it doesn't have a group the groups form will be
blank--completely blank. The course needs to have groups (at least one
group) for the form to work correctly.

So, I'm trying to give the user an opportunity to create a group on-the-fly
by inserting code in the error
handler. The query is based on the current courseCode and two functions
that prompt the user for a group name and group weight. I'm getting a
syntax error. Can I even do this? THANKS!

The error handler and both functions are pasted below:

'the sub PopulateListBox reads the groups related to the
'current courseCode and displays them in a list box
'It is one of the first things referenced in the Form_Load sub.

Private Sub PopulateListBox
On Error Goto Err_PopulateListBox

reload_PopulateListBox:
'tries the code again, this time with a group in the course

dim StrSQL as String

' ( a bunch of other code here )

Exit_PopulateListBox:
Exit Sub

Err_PopulateListBox:
Select Case Err.Number
Case 3021 'Error number 3021 is "No Current Record"
MsgBox "This course contains no groups of activities. You must
have at least one group in this course to continue", vbInformation, ""
'StrSQL is already declared earlier in the Sub.
StrSQL = "INSERT INTO groups (courseCode, groupDescription,
groupWeight) VALUES ( '" & Me!courseCode & "'" & ", " & getNGN & ", " &
getNGW & " );"
DoCmd.RunSQL StrSQL 'normally I use currentDB.Execute..., but I
want to see the feedback.
Resume reload_PopulateListBox:

Case Else
MsgBox "Error " & Err.Number & "." & vbCrLf & Err.Description &
vbCrLf & "on line " & Erl, vbCritical, "Big Fat Error"
Resume Exit_PopulateListBox
End Select
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private Function getNTW() As Double
On Error GoTo Err_getNTW

getNTW = CDbl(InputBox("Enter target weight for new activity", "New
Activity"))

Exit_getNTW:
Exit Function

Err_getNTW:
MsgBox "Error " & Err.Number & "." & vbCrLf & Err.Description & vbCrLf &
"on line " & Erl, vbCritical, "Big Fat Error"
Resume Exit_getNTW

End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Function getNGN() As String
On Error GoTo Err_getNGN

getNGN = Nz(InputBox("Enter name for group", ""), "")

Exit_getNGN:
Exit Function

Err_getNGN:
MsgBox "Error " & Err.Number & "." & vbCrLf & Err.Description & vbCrLf &
"on line " & Erl, vbCritical, "Big Fat Error"
Resume Exit_getNGN

End Function

 

[Douglas J. Steele]

StrSQL = "INSERT INTO groups (courseCode, groupDescription,
groupWeight) VALUES ( '" & Me!courseCode & "'" & ", " & getNGN & ", " &
getNGW & " );"

From the name, I would assume groupDescription is a text field, yet you
haven't got quotes around getNGN, as you do around Me!courseCode:

StrSQL = "INSERT INTO groups (courseCode, groupDescription,
groupWeight) VALUES ( '" & Me!courseCode & "', '" & getNGN & "', " &
getNGW & " )"

(Is courseCode text? If not, then the quotes you have there are wrong)

Also, re:

DoCmd.RunSQL StrSQL 'normally I use currentDB.Execute..., but I want
to see the feedback.

You can use

CurrentDb.Execute strSQL, dbFailOnError

(of course, that may cause issues since you're already in the error
handler... Try Err.Clear first)

 

[Richard Hollenbeck]

Thanks Doug,

I thought you might be right about the quotes so I took another look. I
changed StrSQL to this:

StrSQL = "INSERT INTO groups (courseCode, groupDescription, groupWeight)
VALUES ( " & Me!courseCode & ", '" & getNGN & "', " & getNGW & ");"

courseCode and groupDescription are both strings groupWeight is a Double.

I'm still getting a syntax error. I think I need to do something with the
quotes around courseCode.

Yes, I usually use "CurrentDb.Execute strSQL, dbFailOnError" but for
troubleshooting purposes, I want to see whether it is about to append one 1
record. Now it's back to the drawing board.

*** Is it possible that I can't run an insert query from an error handler at
all? *** I'll try Err.Clear like you said to see if that does it. But then
I can't use Resume reload_PopulateListBox:? Just GoTo Resume
reload_PopulateListBox?

Thanks.
Richard Hollenbeck

 

[Douglas J. Steele]

You had quotes around the courseCode before (and I left them in the response
I posted): why did you remove them?

StrSQL = "INSERT INTO groups (courseCode, groupDescription, groupWeight)
VALUES ( '" & Me!courseCode & "', '" & getNGN & "', " & getNGW & " )"

Exagerated for clarity, the VALUES line is

VALUES ( ' " & Me!courseCode & " ', ' " & getNGN & " ', " & getNGW & " )"