records aren't appending...don't see an error!

[Angi]

This is too weird. This is pretty straight forward...assign an invoice
# and then append to the InvoiceMain table. It assigns the # and runs
through the code with no errors, but it doesn't append the InvoiceMain
table. If I do it through a query instead of Sql, it works fine. The
only thing I can see that's different is that i'm using the me.quoteid
instead of the forms!... But when I put a break in the code, the
me.quoteid value is right so I should be able to use that syntax,
right?? I have code just like this elsewhere in my db and haven't had
a problem. What am I doing wrong?

Private Sub cmdOrder_Click()
Dim sql As String
If IsNull(Me.InvoiceID) Then
Me.InvoiceID = Nz(DMax("invoiceid", "invoicemain"), 2000) + 1

'create invoice
sql = "INSERT INTO InvoiceMain ( CoID, InvoiceID, ContactID,
ShippingMethodID, FreightAmt, ReqDate, InvoiceNotes ) " _
& "SELECT QuoteMain.CoID, QuoteMain.InvoiceID,
QuoteMain.ContactID, QuoteMain.ShippingMethodID, QuoteMain.FreightAmt,
QuoteMain.ReqDate, QuoteMain.QuoteNotes " _
& "FROM QuoteMain WHERE (((QuoteMain.QuoteID)=" & Me.QuoteID &
"));"

CurrentDb.Execute sql, dbfailonerror

End If
End Sub

TIA!
Angi

 

[Allen Browne]

To debug this, test the RecordsAffected property to see what the append
query actually did:
Dim db As DAO.Database
Set db = CurrentDb()
sql = "INSERT ...
db.Execute sql, dbFailOnError
Debug.Print db.RecordsAffected & " inserted for quote " & Me.QuoteID

If this is a bound form, you may need to save the record before you run that
code:
If Me.Dirty Then Me.Dirty = False

 

[Angi]

Allen,
Thanks for the reply. I'm getting the following error:

Compile error:
User-defined type not defined.

it highlights db as DAO.Database

I'm using Acc 2002 with 2000 format.

I just added the following under the Me.InvoiceID =
Nz(DMax("invoiceid", "invoicemain"), 2000) + 1 line:

docmd.runcommand acCmdSaveRecord...it works now. Thanks for the help!

Ang

 

[Allen Browne]

Good: you have a workaround.

Access complained about the Database object from the DAO library because you
do not have a reference to the DAO 3.6 library. You can add one by opening
any code window, and choosing References on the Tools menu. More info:
http://allenbrowne.com/ser-38.html