Insert statement failing

[BrianBurgit]

I have an Access 97 database and have a Form where a user clicks a Button that runs an INSERT statement. The code executes with no errors, but the records do not get inserted into the Table. The Table I am inserting into is linked from a database on a network share. I went into this network database and made a copy of the table in question, gave it a different name, then linked that new table to my database and ran the INSERT on that table and it worked. Anyone have any idea what may be going on?

Thanks,

Brian

 

[dhookom]

Please share your code, table and field names, field data types, any constraints, and error message(s).

Duane
Hook'D on Access
MS Access MVP

 

[BrianBurgit]

I am trying to insert into a Table named SDetails. I inherited this system, the Tables are a bit ugly,

The Fields in this Table are

ResNum - number
SSNumber - number
NumA - number
NumC - number
SCost - currency
SSCommission - currency
Adult_Cost - currency
Child_Cost - cirrency
EventDate - date/time
EventTime - text
Confirmed_By - text
Confirmed_Date - date/time
Confo_# - text
SSLean_Name - text
SCancel - Yes/no
SPaid - Yes/No
SPayDate - date/time
SNet - currency
SInvoice# - text
GCarrier - text
GInflight# - text
GArrivaldate - date/time
GArrivalTime - text
GOutflight# - text
GDepartDate - date/time
GDepartTime - text
GPickup - text
GDropoff - text
Adult_Net - currency
Child_Net - currency
Segnum - number
VendorID - number
NumStudents - number
NumSeniors - number
Senior_Cost - currency
Senior_Net - currency
Student_Cost - currency
Student_Net - currency
Status - number

There are no constraints, and I receive no error messages. I can step thru the code in debug mode and all of the statements execute.



Here is my code

If rsEventSegments.EOF = False Then

With rsEventSegments

.MoveFirst

Do While Not .EOF

sql = "INSERT INTO SDetails (ResNum, SSNumber, NumA, NumC, NumStudents, SCost, Adult_Cost, Child_Cost, " & _
"Student_Cost, EventDate, SSLead_Name, VendorID, Status, CorrectEventDate, SNet, Adult_Net, " & _
"Child_Net, Student_Net) " & _
"VALUES(" & Me.ResNum & ", " & .Fields("SSNumber") & ", " & .Fields("NumA") & "," & .Fields("NumC") & "," & _
.Fields("NumStudents") & "," & .Fields("SCost") & "," & .Fields("Adult_Cost") & "," & _
.Fields("Child_Cost") & "," & .Fields("Student_Cost") & "," & .Fields("EventDate") & ",'" & _
Me.ResClientFirst & " " & Me.ResClientLast & "'," & .Fields("VendorID") & "," & _
1 & "," & 1 & "," & .Fields("SNet") & "," & .Fields("Adult_Net") & "," & .Fields("Child_Net") & _
"," & .Fields("Student_Net") & ")"




CurrentDb.Execute sql

.MoveNext

Loop

End With

 

[Golom]

Try

On Error Resume Next
CurrentDb.Execute sql[red], dbFailOnError[/red]
If Err.Number <> 0 then
    Debug.Print Err.Number & " - " & Err.Description
End If

The default action for Execute is to supress errors unless the dbFailOnError parameter is included.

 

[BrianBurgit]

Thanks Golom, that helped, it is throwing an error, and there is indeed a constraint on the Table.

 

[dhookom]

BrianBurgit,
I hope you can see now that your first post provided little or no information to allow us to make any suggestions. After providing the table and field information as well as the code, Golom was able to quickly suggest some trouble-shooting steps.

Duane
Hook'D on Access
MS Access MVP