"no records" message for append query

  • Thread starter Thread starter PHisaw
  • Start date Start date
P

PHisaw

Hi,

I'm trying to add an error message to an append query when there are no
records to append to table for subform. The query is based on the main form
text boxes of model and spmodule. It will update a template list of parts to
the subform when there is a template of parts for these two fields. When
there is a match, it works properly. When there is not a match for both
fields, nothing happens and I would like a message "not available". I
searched thru posts and added it to the error handler per Klatuu's post, but
still nothing happens. Can someone please let me know what I'm doing wrong?

Private Sub Command24_DblClick(Cancel As Integer)
Dim db As DAO.Database
Dim strSql As String
Set db = CurrentDb()

strSql = "INSERT INTO tsparepartssubform3 ( QuoteNumber, Model, SPModule,
PartIDNumber, Qty, Class1, Class2, Class3, DelWks )" & _
"SELECT tSparePartsMainForm.QuoteNumber, tSparePartsTemplate.Model,
tSparePartsTemplate.SPModule, tSparePartsTemplate.PartIDNumber,
tSparePartsTemplate.Qty, tSparePartsTemplate.Class1,
tSparePartsTemplate.Class2, tSparePartsTemplate.Class3,
tSparePartsTemplate.DelWks " & _
"FROM tSparePartsMainForm INNER JOIN tSparePartsTemplate ON
(tSparePartsMainForm.SPModule = tSparePartsTemplate.SPModule) AND
(tSparePartsMainForm.Model = tSparePartsTemplate.Model)" & _
"WHERE tSparePartsMainForm.QuoteNumber= " &
Forms!fsparepartsmainform!QuoteNumber & _
" AND tSparePartsTemplate.Model= '" & Forms!fsparepartsmainform!Model & "' "
& _
" AND tSparePartsTemplate.SPModule= '" & Forms!fsparepartsmainform!SPModule
& "' "

db.Execute strSql, dbFailOnError
Me.Refresh
MsgBox "Test Msg - It worked"

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Update Failed" & vbNewLine & "With Error " & Err.Number & " - "
& Err.Description, , "command24_DblClick"
Resume Exit_Handler

End Sub
 
PHisaw said:
Hi,

I'm trying to add an error message to an append query when there are no
records to append to table for subform. The query is based on the main
form
text boxes of model and spmodule. It will update a template list of parts
to
the subform when there is a template of parts for these two fields. When
there is a match, it works properly. When there is not a match for both
fields, nothing happens and I would like a message "not available". I
searched thru posts and added it to the error handler per Klatuu's post,
but
still nothing happens. Can someone please let me know what I'm doing
wrong?

Private Sub Command24_DblClick(Cancel As Integer)
Dim db As DAO.Database
Dim strSql As String
Set db = CurrentDb()

strSql = "INSERT INTO tsparepartssubform3 ( QuoteNumber, Model, SPModule,
PartIDNumber, Qty, Class1, Class2, Class3, DelWks )" & _
"SELECT tSparePartsMainForm.QuoteNumber, tSparePartsTemplate.Model,
tSparePartsTemplate.SPModule, tSparePartsTemplate.PartIDNumber,
tSparePartsTemplate.Qty, tSparePartsTemplate.Class1,
tSparePartsTemplate.Class2, tSparePartsTemplate.Class3,
tSparePartsTemplate.DelWks " & _
"FROM tSparePartsMainForm INNER JOIN tSparePartsTemplate ON
(tSparePartsMainForm.SPModule = tSparePartsTemplate.SPModule) AND
(tSparePartsMainForm.Model = tSparePartsTemplate.Model)" & _
"WHERE tSparePartsMainForm.QuoteNumber= " &
Forms!fsparepartsmainform!QuoteNumber & _
" AND tSparePartsTemplate.Model= '" & Forms!fsparepartsmainform!Model & "'
"
& _
" AND tSparePartsTemplate.SPModule= '" &
Forms!fsparepartsmainform!SPModule
& "' "

db.Execute strSql, dbFailOnError
Me.Refresh
MsgBox "Test Msg - It worked"

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Update Failed" & vbNewLine & "With Error " & Err.Number & " - "
& Err.Description, , "command24_DblClick"
Resume Exit_Handler

End Sub
Click to expand...


When you run an action query, the result of "no records
updated/appended/deleted" is not an error, so your error handling won't
catch it. Instead, so long as you are using DAO methods to run the query
(as you are in your posted code), you can interrogate the database object's
RecordsAffected property afterward, like this:

With db
.Execute strSql, dbFailOnError
If .RecordsAffected = 0 Then
MsgBox "Not available"
Else
Me.Refresh
MsgBox "Test Msg - It worked"
End If
End With
 
Thank you sooooo much! I spend hours yesterday with all sorts combinations
trying to make this work. I should have asked the experts to begin with!!

I must admit I don't know anything about DAO methods - just code pieced
together trying to make it work.

Thanks again,
Phisaw
 
Back
Top