insert statment not saving data to tables (1 Viewer)

[brainox]

I am trying to create a form in which users can save new supplier data to a database. I am using two insert statements which insert similar data in to two similar tables. The insert statements appear to have no problems and no errors are produced upon execution yet the new data doesn't save into the tables. Here's the code;

Private Sub Command14_Click()
Dim sqlstr As String
Dim dbs As Database
Set dbs = CurrentDb
Dim sqlstr2 As String
'Save a reading into the readings table and refresh sub form
sqlstr = "INSERT INTO tbl_SuppliersList ( Supplier_Name, Address1, Address2, Address3, Postcode, Phone_Number, Email  )" _
& " SELECT '" & Nz(Me!Sup_name, "") & "' AS Expr1, '" & Nz(Me!Sup_Ad1, "") & "' AS Expr2, '" & Nz(Me!Sup_Ad2, "") & "' AS Expr3, '" & Me!Sup_Ad3 & "' as expr4, '" & Me!sup_post & "' as expr5, '" & Me!Sup_telephone & "' as expr6, '" & Me!Sup_email & "' as expr7"
dbs.Execute (sqlstr)
sqlstr2 = "INSERT INTO tbl_meter_Suppliers ( Meter_ID, Start_date, End_date, Active )" _
& " SELECT '" & Me!Sup_MeterID & "' As expr1, #" & Format(Nz(Me!sup_Sdate, date), "dd/mmm/yyyy") & "# as expr2, #" & Format(Nz(Me!sup_Edate, date), "dd/mmm/yyyy") & "# as expr3, '" & Me!sup_active & "' as expr4"
dbs.Execute (sqlstr2)
 
 
DoCmd.close
Forms!frm_meters.frm_suppliers_sub.Requery
MsgBox "Supplier saved successfully"
End Sub

Any ideas?
Cheers

 

[plog]

Are sqlstr and sqlstr2 valid SQL statements? That's semi-rhetorical--I don't expect you to know the answer. So go find out the answer--what exactly do sqlstr and sqlstr2 contain?

Just to be clear, don't tell me what you expect them to contain (I can discern that from the code), go find out what they actually contain.

 

[brainox]

Are sqlstr and sqlstr2 valid SQL statements? That's semi-rhetorical--I don't expect you to know the answer. So go find out the answer--what exactly do sqlstr and sqlstr2 contain?

Just to be clear, don't tell me what you expect them to contain (I can discern that from the code), go find out what they actually contain.

Sorry, I don't quite understand what you're getting at?
Digress?
I have used sqlstr before and had no problems
Many thanks

 

[plog]

Before executing the queries (db.Execute) do something that lets you confirm what SQL you are actually going to execute (MsgBox(sqlstr)).

 

[DrallocD]

Are these local access tables or linked tables?

Do the tables that you are inserting into have primary key index defined in MS Access?

You may be able to troubleshoot silent failures using the following code:

dbs.Execute sqlstr, dbFailOnError + dbSeeChanges
debug.Print dbEngine.Errors.Count

If there are errors, look in dbEngine.Errors.Description

 

[brainox]

Are these local access tables or linked tables?

Do the tables that you are inserting into have primary key index defined in MS Access?

You may be able to troubleshoot silent failures using the following code:

dbs.Execute sqlstr, dbFailOnError + dbSeeChanges
debug.Print dbEngine.Errors.Count

If there are errors, look in dbEngine.Errors.Description

Ah yes the table I am inserting into is a linked table, what do I need to change for it to work?
Many thanks

 

[DrallocD]

What type of database are you linked to?

You need to make sure that you have a primary key in Access for this table.

Did you try using:

dbs.Execute sqlstr, dbFailOnError + dbSeeChanges
debug.Print dbEngine.Errors.Count

If so, was the error count 0?

 

[brainox]

What type of database are you linked to?

You need to make sure that you have a primary key in Access for this table.

Did you try using:

dbs.Execute sqlstr, dbFailOnError + dbSeeChanges
debug.Print dbEngine.Errors.Count

If so, was the error count 0?

Ok I tried using the code you gave me and the error count was three andI got a run time error '3146' ODBC--Call failed

The table I am trying to save the data to has a primary key which is added to the new record automatically as it is an autonumber

Thanks for your help

 

[DrallocD]

What were the three errors? You can see them with :

? dbEngine.Errors(0).Description
? dbEngine.Errors(1).Description
? dbEngine.Errors(2).Description

Does Access know about the primary key? If not you need to use something like:

CurrentDb.Execute "CREATE UNIQUE INDEX PK_" & TableName & " On " & TableName & "(" & Fields & ")"

 

[brainox]

What were the three errors? You can see them with :

? dbEngine.Errors(0).Description
? dbEngine.Errors(1).Description
? dbEngine.Errors(2).Description

Does Access know about the primary key? If not you need to use something like:

CurrentDb.Execute "CREATE UNIQUE INDEX PK_" & TableName & " On " & TableName & "(" & Fields & ")"

Access knows about the primary key.

Do I put that code into the immediate window to see the results or?

Thankyou for your help but I am really struggling with this, I have a deadline to have this working by the end of this week and it's giving me a nightmare.

Cheers

 

[DrallocD]

Try something like this:

Private Sub Command14_Click()
On Error GoTo ErrorHappened
    Dim sqlstr As String
    Dim dbs As Database
    Set dbs = CurrentDb
    Dim sqlstr2 As String
    'Save a reading into the readings table and refresh sub form
    sqlstr = "INSERT INTO tbl_SuppliersList ( Supplier_Name, Address1, Address2, Address3, Postcode, Phone_Number, Email  )" _
    & " SELECT '" & Nz(Me!Sup_name, "") & "' AS Expr1, '" & Nz(Me!Sup_Ad1, "") & "' AS Expr2, '" & Nz(Me!Sup_Ad2, "") & "' AS Expr3, '" & Me!Sup_Ad3 & "' as expr4, '" & Me!sup_post & "' as expr5, '" & Me!Sup_telephone & "' as expr6, '" & Me!Sup_email & "' as expr7"
    dbs.Execute sqlstr, dbFailOnError + dbSeeChanges
    sqlstr2 = "INSERT INTO tbl_meter_Suppliers ( Meter_ID, Start_date, End_date, Active )" _
    & " SELECT '" & Me!Sup_MeterID & "' As expr1, #" & Format(Nz(Me!sup_Sdate, Date), "dd/mmm/yyyy") & "# as expr2, #" & Format(Nz(Me!sup_Edate, Date), "dd/mmm/yyyy") & "# as expr3, '" & Me!sup_active & "' as expr4"
    dbs.Execute sqlstr2, dbFailOnError + dbSeeChanges
    DoCmd.Close
    Forms!frm_meters.frm_suppliers_sub.Requery
    MsgBox "Supplier saved successfully"
ExitNow:
    Exit Sub
ErrorHappened:
    Dim i As Integer
    Debug.Print Err.Number & " - " & Err.Description
    For i = 0 To DBEngine.Errors.Count - 1
        Debug.Print DBEngine.Errors(i).Description
    Next i
    Resume ExitNow
End Sub

 

[plog]

Me again (first respondent). Have you yet to see if the SQL you are trying to execute is in fact valid SQL?