Insert one record

[MrsMope]

How do I insert just one record into a table? I have a button on a form, I want to insert a record into the table, when I write my insert statement it wants to append a record for each record in the table, here is my query:

INSERT INTO tblOrder (Part_Manufactur_Id,Units_Ordered, Date_Ordered, Expected_Delivery_Date,PurchaseOrderNumber )VALUES '505','5','12:00:00 AM','1/25/2005','12345'

 

[PHV]

when I write my insert statement
In which event procedure ?
Anyway, no syntax error od data type mismatch ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MrsMope]

I am writing my insert statement in a public function, no there is not an error message, I think I have the code now:

Const strSQL5 = "INSERT INTO tblOrder (Units_Ordered, Date_Ordered, Expected_Delivery_Date," & _
                "PurchaseOrderNumber )" & _
                "VALUES '"
Const strSQL6 = "' WHERE Part_Manufactur_ID= '"
Const strSQL7 = "',"
Const strSQL8 = "' AND Date_Ordered=(select max(date_Ordered) FROM tblOrder"
Public Function SaveOrder(Units As Integer, OrderDate As Date, PO As Integer, ExpectDel As Date, ID As Integer)
'Inserts an order record into the orders table

strsql = strSQL5 & Units & strSQL7 & OrderDate & strSQL7 & ExpectDel & strSQL7 & PO & strSQL6 & ID & strSQL8

Debug.Print (strsql)
DoCmd.RunSQL (strsql)
End Function
[red]RESULTS IN IMMEDIATE WINDOW:INSERT INTO tblOrder (Units_Ordered, Date_Ordered, Expected_Delivery_Date,PurchaseOrderNumber )VALUES '0', '12:00:00 AM', '12:00:00 AM', '0' WHERE Part_Manufactur_ID= '505' AND Date_Ordered=(select max(date_Ordered) FROM tblOrder[/RED]

I recieve: Syntax error in INSERT INTO Statement

 

[MrsMope]

Update:
Change the code so the results are :

strsql = strSQL5 & Units & strSQL7 & OrderDate & strSQL7 & ExpectDel & strSQL7 & PO & strSQL6 & ID & strSQL8

results:

INSERT INTO tblOrder (Units_Ordered, Date_Ordered, Expected_Delivery_Date,PurchaseOrderNumber )VALUES ('0', '12:00:00 AM', '12:00:00 AM', '0') WHERE Part_Manufactur_ID= '505' AND Date_Ordered=(select max(date_Ordered) FROM tblOrder);

MESSAGE: Missing semi colon at the end of SQL statement

 

[lespaul]

doesn't the value list also need to be in parens?
[core]
INSERT INTO tblOrder (Units_Ordered, Date_Ordered, Expected_Delivery_Date,PurchaseOrderNumber )VALUES ('0', '12:00:00 AM', '12:00:00 AM', '0') WHERE Part_Manufactur_ID= '505' AND Date_Ordered=(select max(date_Ordered) FROM tblOrder
[/code]

Leslie

 

[PHV]

I guess you want something like this:
UPDATE tblOrder SET Units_Ordered=5, Expected_Delivery_Date=#01/25/2005#, PurchaseOrderNumber=12345 WHERE Part_Manufactur_ID='505' AND Date_Ordered=(select max(date_Ordered) FROM tblOrder);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MrsMope]

OK so made yet another change, but I now receive a data type mismatch error:

INSERT INTO tblOrder ( Part_Manufactur_ID, Units_Ordered, Date_Ordered, Expected_Delivery_Date,PurchaseOrderNumber )SELECT tblOrder.Part_Manufactur_ID,'0', '12:00:00 AM', '12:00:00 AM', '0' FROM tblOrder WHERE Part_Manufactur_ID= '505' AND Date_Ordered=(select max(date_Ordered) FROM tblOrder);

 

[MrsMope]

No I do not want to update a current record I want to append a record, see previous post for the query I have created, but as I stated, I receive a data mismatch error, I am thinking it is the date fields, but I am unsure

 

[lespaul]

have you tried with a calculated date time field instead of your test data?

INSERT INTO tblOrder ( Part_Manufactur_ID, Units_Ordered, Date_Ordered, Expected_Delivery_Date,PurchaseOrderNumber )SELECT tblOrder.Part_Manufactur_ID,[COLOR=blue]'0'[/color], [COLOR=red]Now(), Now()[/color], '0' FROM tblOrder WHERE Part_Manufactur_ID= '505' AND Date_Ordered=(select max(date_Ordered) FROM tblOrder);

Is PurchaseOrderNumber a string field? If it's a number you need to remove the ' ' from around the 0.

Leslie