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 & " )"

 

[Richard Hollenbeck]

Doug,

I don't know; this quote thing has me baffled. I kind-of understand the
concept, where quotes inside quotes need to alternate between single and
double quotes. For some reason, I still get confused even when I think I
understand. So I fiddle around with different combinations of single and
double quotes until I get it right. With enough practice, it will
eventually gel in my thick little bone-head.

Thanks for the corrected quotes:

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

I'll try it.

In chapter 7 of SYBEX's <U>Access 2002 Desktop Developer's Handbook</U>
(ch07.mdb), they provide two modules called TaggedValue and TaggedValues. I
am experimenting with the idea of using a pop-up form instead of two input
boxes. I'll still need get the INSERT INTO query to work, but it might be
more elegant to use a custom input box--a pop-up form called frmNewGroup
instead of two input boxes.

I successfully created the tagged value string in the pop-up form but I
haven't yet figured out how to get the form to return this string nor how to
retrieve the pop-up form's string value in the original form (frmGroups.)
So I'm scouring the web looking for a quick tutorial on gathering data from
a pop-up form. I've been reading pages 300-305 and pages 440-444. I think
I am missing something. Maybe I need to study ch08.mdb to see how they use
the pop-up tools.

Rich Hollenbeck

 

[Richard Hollenbeck]

I took your advice on the quotes and it worked--to a point. Also, instead
of the input boxes, I created a pop-up form called "frmNewGroup" to gather
the information from two text boxes called txtGD and txtGW (for group
description and group weight respectively.) So here's the revised string:

StrSQL = "INSERT INTO groups (courseCode, groupDescription, groupWeight)
VALUES ( '" &
Me!courseCode & "', '" & Forms!frmNewGroup!txtGD & "', " &
Forms!frmNewGroup!txtGW & " )"

It worked to the point that it said it was about to append 1 record. But
then I got the following error:

"Grades Program can't append all the records in the append query.
Grades Program set 0 field(s) to Null due to a type conversion failure, and
it didn't add
1 record(s) to the table due to key violations, 0 record(s) due to lock
violations, and 0
record(s) due to validation rule violations.
Do you want to run th eaction query anyway?
To ignore the error(s) and run the query, click Yes.
For an explanation of the causes of the violations, click Help.
|Yes| |No| |Help|"

None of these fields are key. The key field, groupID, is an autonumber
(long integer) and is not included in the query. Shouldn't an INSERT INTO
query create a key value for the new record based on the autonumber?

Then when I close frmNewGroup, frmGroups.form_load says something like
"Grades Program can't find the form 'frmNewGroup' indicated in a macro or
VBA code." I know it is spelled correctly. If I comment out the SQL stuff
and just put in a message box I do get the values. Just to test it I tried
something like:

MsgBox Forms!frmNewGroup!txtGD & ", " & Forms!frmNewGroup!txtGW

and that worked perfectly!

Hmmmmmm...... (pulling out my hair)

Rich Hollenbeck

 

[Douglas J. Steele]

You could try using a conversion function around your txtGW value to see
whether that helps:

StrSQL = "INSERT INTO groups (courseCode, groupDescription, groupWeight)
VALUES ( '" &
Me!courseCode & "', '" & Forms!frmNewGroup!txtGD & "', " &
CLng(Forms!frmNewGroup!txtGW) & " )"

If that doesn't work, check what the actual value of strSQL is. Does it look
like valid SQL? If so, try running that SQL through the Query interface:
does it work?

Is groupID the only other field in the table? If there are others, are any
of them mandatory? Do you have any unique indexes on the table (other than
the PrimaryKey on groupID)?

 

[Richard Hollenbeck]

Doug,

I don't know what I did but it's working correctly now. One thing I did
was, instead of assuming a correct filter on "courseCode", I'm now pulling
the data directly from the combobox, "cboSelectCourse" in the previous form.
Perhaps that did the trick. Also, I suppose it's more elegant to put the
code in the main part of the subroutine than to rely on an error handler,
since it is a predictable condition rather than an actual error.

Thanks, Doug, for patiently assisting me through this struggle.

Here's the new StrSQL:

StrSQL = "INSERT INTO groups (courseCode, groupDescription, groupWeight)
VALUES ( '" & Forms!frmSelectCourse!cboSelectCourse & "', '" &
Forms!frmNewGroup!txtGD & "', " & Forms!frmNewGroup!txtGW & " )"