need help trapping errors!

[serviceman via AccessMonster.com]

Me again...
I'm having trouble trapping errors in the code below when a duplicate key is
trying to inserted with the DoCmd.RunSQL command (error 2627):
Private Sub Command16_Click()

Dim SQLStr As String
SQLStr = "INSERT TESTRESULTS SELECT '" & Forms!formtestentry.Combo14.Column(0)
& "' as ATT_HDR_ID, STUDENT_ID AS STUDENT_ID,'" & Forms!formtestentry.Combo14.
Column(1) & "' as EVENT_ID, '" & Forms!formtestentry.LOCATION_ID & "' as
LOCATION_ID,'" & Forms!formtestentry.EVENT_DAT & "' as EVENT_DAT, 0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'','','','','' FROM STUDENTS
WHERE STUDENT_ID IN (" & MySelected2 & ")"
DoCmd.SetWarnings False
DoCmd.RunSQL (SQLStr)
DoCmd.SetWarnings True
Set colCheckBox = Nothing
Me.requery
Forms!formtestentry!subformadminteststudents.requery

End Sub

The code works fine, I'm just trying to beautify the 'violation of primary
key' message. Can someone show me the way?
Thanks a bunch,
Andy

 

[Rick Brandt]

Me again...
I'm having trouble trapping errors in the code below when a duplicate key is
trying to inserted with the DoCmd.RunSQL command (error 2627):
Private Sub Command16_Click()

Dim SQLStr As String
SQLStr = "INSERT TESTRESULTS SELECT '" & Forms!formtestentry.Combo14.Column(0)
& "' as ATT_HDR_ID, STUDENT_ID AS STUDENT_ID,'" & Forms!formtestentry.Combo14.
Column(1) & "' as EVENT_ID, '" & Forms!formtestentry.LOCATION_ID & "' as
LOCATION_ID,'" & Forms!formtestentry.EVENT_DAT & "' as EVENT_DAT, 0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'','','','','' FROM STUDENTS
WHERE STUDENT_ID IN (" & MySelected2 & ")"
DoCmd.SetWarnings False
DoCmd.RunSQL (SQLStr)
DoCmd.SetWarnings True
Set colCheckBox = Nothing
Me.requery
Forms!formtestentry!subformadminteststudents.requery

End Sub

The code works fine, I'm just trying to beautify the 'violation of primary
key' message. Can someone show me the way?
Thanks a bunch,
Andy

Don't use DoCmd.RunSQL Instead use...

CurrentDB.Execute SQLStr, dbFailOnError

Then you will get an error that you can handle in your error handler. Another
advantage is that there are no confirmation prompts so no need for your
SetWarnings lines.

The Error Handler structure would be...

On Error GoTo ErrHandler

(your code)

Egress:
Exit Sub

ErrHandler:
Select Case Err.Number
Case 3022
MsgBox "Duplicate Record"
Case Else
MsgBox Err.Description
End Case
End Sub

 

[serviceman via AccessMonster.com]

Hey Rick,
Thanks for the tips. I'll put that to use today. I knew I was missing
something!
Andy

Me again...
I'm having trouble trapping errors in the code below when a duplicate key is

[quoted text clipped - 21 lines]

Thanks a bunch,
Andy

Don't use DoCmd.RunSQL Instead use...

CurrentDB.Execute SQLStr, dbFailOnError

Then you will get an error that you can handle in your error handler. Another
advantage is that there are no confirmation prompts so no need for your
SetWarnings lines.

The Error Handler structure would be...

On Error GoTo ErrHandler

(your code)

Egress:
Exit Sub

ErrHandler:
Select Case Err.Number
Case 3022
MsgBox "Duplicate Record"
Case Else
MsgBox Err.Description
End Case
End Sub

 

[serviceman via AccessMonster.com]

Hey Rick,
I'm stuck a little bit. For some reason no matter what I do I keep getting
the 'object variable or with block variable not set' error when trying to use
CurrentDb.execute; is there soemthing I need to change in my SQL string, or
am I doing something wrong?
Here is the line of code:

CurrentDb.Execute SQLStr, dbFailOnError
What am I missing?
Andy

Hey Rick,
Thanks for the tips. I'll put that to use today. I knew I was missing
something!
Andy
[quoted text clipped - 27 lines]

End Case
End Sub

 

[Rick Brandt]

Hey Rick,
I'm stuck a little bit. For some reason no matter what I do I keep
getting the 'object variable or with block variable not set' error
when trying to use CurrentDb.execute; is there soemthing I need to
change in my SQL string, or am I doing something wrong?
Here is the line of code:

CurrentDb.Execute SQLStr, dbFailOnError
What am I missing?

What is in SQLStr? I would guess that there's a problem with that.

 

[serviceman via AccessMonster.com]

SQLStr = "INSERT TESTRESULTS SELECT '" & Forms!formtestentry.Combo14.Column(0)

& "' as ATT_HDR_ID, STUDENT_ID AS STUDENT_ID,'" & Forms!formtestentry.Combo14.

Column(1) & "' as EVENT_ID, '" & Forms!formtestentry.LOCATION_ID & "' as
LOCATION_ID,'" & Forms!formtestentry.EVENT_DAT & "' as EVENT_DAT, 0,0,0,0,0,0,

0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'','','','','' FROM STUDENTS
WHERE STUDENT_ID IN (" & MySelected2 & ")"

This works fine in the DoCmd. setup, but not in the .Execute...
I seem to remember reading somewheres that the variables can't be in there? I
dunno, I need some help on this one...
Andy

Hey Rick,
I'm stuck a little bit. For some reason no matter what I do I keep

[quoted text clipped - 5 lines]

CurrentDb.Execute SQLStr, dbFailOnError
What am I missing?

What is in SQLStr? I would guess that there's a problem with that.

 

[Rick Brandt]

SQLStr = "INSERT TESTRESULTS SELECT '" &
Forms!formtestentry.Combo14.Column(0)

& "' as ATT_HDR_ID, STUDENT_ID AS STUDENT_ID,'" &
Forms!formtestentry.Combo14.

Column(1) & "' as EVENT_ID, '" & Forms!formtestentry.LOCATION_ID & "'
as LOCATION_ID,'" & Forms!formtestentry.EVENT_DAT & "' as EVENT_DAT,
0,0,0,0,0,0,

0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'','','','','' FROM
STUDENTS WHERE STUDENT_ID IN (" & MySelected2 & ")"

This works fine in the DoCmd. setup, but not in the .Execute...
I seem to remember reading somewheres that the variables can't be in
there? I dunno, I need some help on this one...
Andy

Normally if form references are included in the SQL you cannot use Execute
unless you specify these as parameters because Execute does not utilize the
Expression Service to resolve form references as RunSQL does.

However; you have delimited the SQL string so that the form references are
outside the quotes. This means that your evaluated SQL string should
contain the values found at the form references instead of the references
themselves and that SHOULD work with Execute.

Add a line...

Debug.Print SQLStr

....immediately following the line that assigns the SQL string to it and then
look in the debug window at the SQL that should show up there. See if it
looks correct. You can even paste it into an empty query design to see if
it will run.

 

[serviceman via AccessMonster.com]

Hey Rick,
I took a look at the SQL (in the immediate window) and it looks fine. It
seems like maybe the VB isn't recognizing CurrentDb; if I let it error out
without the custom msgBox and go to debug, the SQLStr looks fine. Could I
maybe not have a reference turned on or something? This seems kind of weird
to me....
Andy

SQLStr = "INSERT TESTRESULTS SELECT '" &
Forms!formtestentry.Combo14.Column(0)

[quoted text clipped - 13 lines]

there? I dunno, I need some help on this one...
Andy

Normally if form references are included in the SQL you cannot use Execute
unless you specify these as parameters because Execute does not utilize the
Expression Service to resolve form references as RunSQL does.

However; you have delimited the SQL string so that the form references are
outside the quotes. This means that your evaluated SQL string should
contain the values found at the form references instead of the references
themselves and that SHOULD work with Execute.

Add a line...

Debug.Print SQLStr

...immediately following the line that assigns the SQL string to it and then
look in the debug window at the SQL that should show up there. See if it
looks correct. You can even paste it into an empty query design to see if
it will run.

 

[serviceman via AccessMonster.com]

Hey Rick,
if it helps at all, when I debug the code it tells me the dbFailOnError is
'128'....
Andy

Hey Rick,
I took a look at the SQL (in the immediate window) and it looks fine. It
seems like maybe the VB isn't recognizing CurrentDb; if I let it error out
without the custom msgBox and go to debug, the SQLStr looks fine. Could I
maybe not have a reference turned on or something? This seems kind of weird
to me....
Andy
[quoted text clipped - 19 lines]

looks correct. You can even paste it into an empty query design to see if
it will run.

 

[serviceman via AccessMonster.com]

Hey!!!!!
Check this out:
Private Sub Command16_Click()
On Error GoTo ErrHandler
Dim SQLStr As String
SQLStr = "INSERT TESTRESULTS SELECT '" & Forms!formadmintest.ATT_HDR_ID & "'
as ATT_HDR_ID, STUDENT_ID AS STUDENT_ID,'" & Forms!formadmintest.EVENT_ID &
"' as EVENT_ID,'" & Forms!formadmintest.LOCATION_ID & "' as LOCATION_ID,'" &
Forms!formadmintest.EVENT_DAT & "' as EVENT_DAT, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,'','','','','' FROM STUDENTS WHERE STUDENT_ID IN
(" & MySelected2 & ")"


DoCmd.RunSQL (SQLStr)

Egress:
Set colCheckBox = Nothing
Me.requery
Forms!formadmintest!subformadminteststudents.requery
Exit Sub

ErrHandler:
Select Case Err.Number
Case 2627
MsgBox "You are trying to add students that have already registered for this
test." & _
"Please re-select the students to add after reviewing the registered student
list."
Case Else
MsgBox Err.Description
End Select

Set colCheckBox = Nothing
Me.requery
Forms!formadmintest!subformadminteststudents.requery

End Sub

It works just fine! traps the error with no problem!
Now why do you suppose that is?
Andy

Hey Rick,
if it helps at all, when I debug the code it tells me the dbFailOnError is
'128'....
Andy

Hey Rick,
I took a look at the SQL (in the immediate window) and it looks fine. It

[quoted text clipped - 9 lines]

 

[tina]

i've found that in using an Append query, when you use the CurrentDb.Execute
method, if *any* of the to-be-appended records violate a unique index in the
receiving table, the action fails and no records at all are added. however,
if you run the same Append query, with no changes at all, using
DoCmd.OpenQuery (bracketed by DoCmd.SetWarnings false-then-true), the query
runs without failing, and the records that *do not violate the unique index*
are added and the violating records are discarded.

hth

Hey!!!!!
Check this out:
Private Sub Command16_Click()
On Error GoTo ErrHandler
Dim SQLStr As String
SQLStr = "INSERT TESTRESULTS SELECT '" & Forms!formadmintest.ATT_HDR_ID & "'
as ATT_HDR_ID, STUDENT_ID AS STUDENT_ID,'" & Forms!formadmintest.EVENT_ID &
"' as EVENT_ID,'" & Forms!formadmintest.LOCATION_ID & "' as LOCATION_ID,'" &
Forms!formadmintest.EVENT_DAT & "' as EVENT_DAT, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,'','','','','' FROM STUDENTS WHERE STUDENT_ID IN
(" & MySelected2 & ")"


DoCmd.RunSQL (SQLStr)

Egress:
Set colCheckBox = Nothing
Me.requery
Forms!formadmintest!subformadminteststudents.requery
Exit Sub

ErrHandler:
Select Case Err.Number
Case 2627
MsgBox "You are trying to add students that have already registered for this
test." & _
"Please re-select the students to add after reviewing the registered student
list."
Case Else
MsgBox Err.Description
End Select

Set colCheckBox = Nothing
Me.requery
Forms!formadmintest!subformadminteststudents.requery

End Sub

It works just fine! traps the error with no problem!
Now why do you suppose that is?
Andy

Hey Rick,
if it helps at all, when I debug the code it tells me the dbFailOnError is
'128'....
Andy

Hey Rick,
I took a look at the SQL (in the immediate window) and it looks fine. It

[quoted text clipped - 9 lines]

looks correct. You can even paste it into an empty query design to see if
it will run.

 

[RoyVidar]

Hey Rick,
I'm stuck a little bit. For some reason no matter what I do I keep
getting the 'object variable or with block variable not set' error
when trying to use CurrentDb.execute; is there soemthing I need to
change in my SQL string, or am I doing something wrong?
Here is the line of code:

CurrentDb.Execute SQLStr, dbFailOnError
What am I missing?
Andy

Getting that error on usage of the CurrentDB function indicates that
CurrentDB isn't available for some reason.

One reason for that, could be for instance if you've declared

dim CurrentDB as DAO.Database

within the current scope. If so, rename it, for instance

dim db as dao.database
set db = currentdb
db.execute SQLStr, dbFailONError

Another reason, could be if you're working with an ADP and not an mdb.
If so, you could try to execute on the connection in stead

currentproject.connection.execute SQLStr,,adcmdtext+adexecutenorecords

 

[TC]

Folks, check out dbFailOnError.

HTH,
TC (MVP Access)
http://tc2.atspace.com