Can't insert record into a table

[Dou]

In a table, There are Numeric, date and text fields,if I open table
using the UI and enter a new record leaving the
Numeric and date fields empty, It can insert records into a table.
But It can't insert a record into the table using INSERT INTO statement
when input data on the form and leaving the Numeric and date fields
empty. I have set numeric and date field's Required property to No.
How to solve this problem? thanks.

My table structure is:

GroupCategory text 50

EquipNo text 15

EquipDesc text 200

AssetNo text 50

PartNo text 50

ModelNo text 50

SerialNo text 50

EquipDetails Memo -

OriginalCost Single 4

Location text 50

PurchasedDate Date/Time 8

InstalledDate Date/Time 8

WarranteeUntil Date/Time 8

TechnicalFile text 100

Manufacturer text 200

OutOfService Integer 2

OutUntil Date/Time 8

TakenOut Date/Time 8

LastReadingDate Date/Time 8

CurrentHours Single 4

CurrentMiles Single 4

CurrentKM Single 4



My SQL statement is:

MySQL = "INSERT INTO [Equipment] VALUES('" & Me![cbGroupCat] & "','" &
Me![tbEquipNo] & "',""" & Me![tbEquipDesc] & """,""" & Me![tbAssetNo] &
""",""" & Me![tbPartNo] & """,""" & Me![tbModelNo] & """,""" &
Me![tbSerialNo] & """,""" & Me![tbDetails] & """," & tbOriginCost & ",'" &
tbLocation & "',#" & tbPurchasedDate & "#,#" & tbInstalledDate & "#,#" &
tbWarranteeUntil & "#,""" & tbTechFile & """,""" & tbMFR & """,1,#" &
tbOutUntil & "#,#" & tbTakenOut & "#,#" & tbLastReading & "#," &
tbCurrentHours & "," & tbCurrentMiles & "," & tbCurrentKM & ");"

 

[John Vinson]

In a table, There are Numeric, date and text fields,if I open table
using the UI and enter a new record leaving the
Numeric and date fields empty, It can insert records into a table.
But It can't insert a record into the table using INSERT INTO statement
when input data on the form and leaving the Numeric and date fields
empty. I have set numeric and date field's Required property to No.
How to solve this problem? thanks.

What's the Primary Key of the table? No field which makes up part of
the PK may be left NULL. What specific error message are you getting?

 

[Dou]

The PK is EquipNo. When I left Numeric and date fields empty. The error
message is

INSERT INTO statement syntax error

If I don't left them empty, then It can insert record into the table. Why?

 

[John Vinson]

The PK is EquipNo. When I left Numeric and date fields empty. The error
message is

INSERT INTO statement syntax error

Hmm... let's see:

MySQL = "INSERT INTO [Equipment] VALUES('" & Me![cbGroupCat] & "','" &
Me![tbEquipNo] & "',""" & Me![tbEquipDesc] & """,""" & Me![tbAssetNo]
& """,""" & Me![tbPartNo] & """,""" & Me![tbModelNo] & """,""" &
Me![tbSerialNo] & """,""" & Me![tbDetails] & """," & tbOriginCost &
",'" & tbLocation & "',#" & tbPurchasedDate & "#,#" & tbInstalledDate
& "#,#" & tbWarranteeUntil & "#,""" & tbTechFile & """,""" & tbMFR &
""",1,#" & tbOutUntil & "#,#" & tbTakenOut & "#,#" & tbLastReading &
"#," & tbCurrentHours & "," & tbCurrentMiles & "," & tbCurrentKM &
");"

I suspect that you need to explicitly list all the fieldnames into
which you wish to insert data. Since you're using the

INSERT INTO [Equipment] VALUES(...

syntax, Access will insert values into the fields as defined in the
table definition; if there are fields out of order or fewer VALUES()
than there are fields, you'll get an error.

You should also step through the code in debug mode and display the
value of MySQL after this line. With all the quotemarks it's hard to
see what's actually being built!

Just from curiosity - why the INSERT from a form? If you're using a
bound form it's not necessary; if you're using an unbound form it
might be simpler to use Recordset operations.

 

[Dou]

I am using an unbound form, How to use Recordset operations.
I think I use SQL, This will be easy to change the backend database from
Access into SQL server.

 

[Dou]

If I don't left the numeric and date empty, it can insert a record.
Like the following statement it can work.

INSERT INTO [Equipment]
VALUES('', '5765476', '54765476', '', '', '', '', '', 56476, '',
#2004-1-20#, #2004-1-13#, #2004-1-21#, '', '', 1, #2004-1-29#, #2004-1-29#,
#2004-1-21#, 546, 456, 46);

the following statement can't work:

INSERT INTO [Equipment]
VALUES('', '5765476', '54765476', '', '', '', '', '', , '', ##, ##, ##, '',
'', 1, ##, ##, ##, , ,);


Why?

 

[John Vinson]

I am using an unbound form, How to use Recordset operations.
I think I use SQL, This will be easy to change the backend database from
Access into SQL server.

Take a look at the online help in the VBA editor for "Recordset".
You'll need several steps, but essentially you'll need to open a
Recordset based on the target table, use its AddNew operation, set
each field to a desired value, and then use the Update method.

It's not going to be any easier (or any harder!) in SQL, I don't
think!