CurrentDb.Execute "INSERT INTO (2 Viewers)

rowfei · Feb 8, 2012

Using the following codes to insert fields into another table. The codes works fine for all fields on the form, except for [Order ID], which is the autoNumber.

CurrentDb.Execute "INSERT INTO [Inventory Transactions] ([Product ID], [Order ID])" _
& "VALUES (' " & Me.Product_ID & "', '" & Me.Order_ID & "');"

How can I insert the [Order ID] to [Order ID] field of the table?

WayneRyan · Feb 8, 2012

rowfei,

You don't insert the Order_ID, it will be assigned.

Wayne

rowfei · Feb 8, 2012

The [Order ID] is the primary key of table A, which is the data set of the form. If I don't assigned, how can I insert it to [Order ID] on another table, which is [Inventory Transactions]?

WayneRyan · Feb 8, 2012

r,

It will be populated automatically.

If it was on a form, you'd see it filled in.
If it was in a recordset, the field would also be filled.

If you have a single-user database, you can use the DMax function to retrieve
the value that it just inserted.

Dim lngPK As Long
lngPK = DMax("[Order_ID]", "TableA")

If you have a multi-user database, you can use ADO (or DAO I think) to retrieve
the @@Identity value.

Wayne

rowfei · Feb 8, 2012

Sorry, still no clear. I have attached the database, hope you can help to modify the codes... Thanks.

WayneRyan · Feb 8, 2012

r,

You don't need any code.

Your form will fill in the Order_ID field when you start entering the other field(s).

Wayne

rowfei · Feb 8, 2012

I know, but I want to insert the Order_ID value to the same Order_ID field on another table.

WayneRyan · Feb 8, 2012

r,

Your current syntax is fine, but the Order_ID is CAN'T be a PRIMARY Key, or an
autonumber, in the other table.

Wayne

Galaxiom · Feb 8, 2012

As Wayne says the autonumber field will be assigned itself.
If you are inserting a value to that field then it should not be autonumber.

Indeed, since you are using the field as Order Number and hence it has real meaning to the user then you should not use an autonumber field.

The actual reason you query fails is that you have surrounded the value to be inserted with quote marks. This designates it as a string and hence it will cause a type mismatch when trying to insert into a numeric field.

BTW:
It is possible to insert into an autonumber field so long as the number is not already used. However this can cause the autonumber seed to continue from the inserted record so it can cause a problem if the inserted records are not the largest number.

rowfei · Feb 8, 2012

Sorry, maybe I didn't provide the clear info.

The source field Order_ID on the form is the autonumber assigned itself.

The target field Order_ID on the table is not autonumber field. It's just number field.

Therefore, I still don't understand why I can insert the source field value to target field, since the source field will be assign the number once I start entry on the form.....

Galaxiom · Feb 8, 2012

rowfei said:
I still don't understand why I can insert the source field value to target field, since the source field will be assign the number once I start entry on the form.....

I assume you meant "can't".

As I pointed out previously:
The actual reason you query fails is that you have surrounded the value to be inserted with quote marks. This designates it as a string and hence it will cause a type mismatch when trying to insert into a numeric field.

Remove the red quote marks.
Possibly those around Me.Product_ID too.

Code:

CurrentDb.Execute "INSERT INTO [Inventory Transactions]([Product ID], [Order ID])" _
& "VALUES (' " & Me.Product_ID & "', [COLOR=red][B]'[/B][/COLOR]" & Me.Order_ID & "[COLOR=red][B]'[/B][/COLOR]);"

Also note you should include the second argument of the Execute Method.
dbFailOnError

Without it any errors are quietly ignored.

RainLover · Feb 9, 2012

rowfei said:
Using the following codes to insert fields into another table. The codes works fine for all fields on the form, except for [Order ID], which is the autoNumber.

CurrentDb.Execute "INSERT INTO [Inventory Transactions] ([Product ID], [Order ID])" _
& "VALUES (' " & Me.Product_ID & "', '" & Me.Order_ID & "');"

How can I insert the [Order ID] to [Order ID] field of the table?

I would like to see a copy in Access 2003. Most people like me still use this.

As far as I can see you do not need to insert Order Id or ProductID.

What you need to do is to fix your relationships but as I can't see them this is only an educated guess.

vbaInet · Feb 9, 2012

You're also missing spaces or have too many spaces in some places so see below.

If ProductID is a Numeric type:

Code:

CurrentDb.Execute "INSERT INTO [Inventory Transactions]([Product ID], [Order ID]) " & _
                  "VALUES (" & Me.Product_ID & ", " & Me.Order_ID & ");", dbFailOnError

If ProductID is Text:

Code:

CurrentDb.Execute "INSERT INTO [Inventory Transactions]([Product ID], [Order ID]) " & _
                  "VALUES ('" & Me.Product_ID & "', " & Me.Order_ID & ");", dbFailOnError

You can also see the dbFailOnError Galaxiom spoke of.

bvijay · May 3, 2016

Re: CurrentDb.Execute "INSERT INTO statement not updating values in tabel PLz Help..

strSQL = "INSERT INTO DATA (Dealer, Sev, Vin, Engine_no, Color, REgno, Var, Mileage, Repair_dt, Sale_date, Part_no, Comp, Opcondition, inv_det, Tech_ob, Susp, Corr, Taccomm, Finalcomm,filename,defect,category,model,prod_dt,BP,partname,step1,step2,step3,step4,step5,step6_1,step6_1d,step6_2,step6_2d,step6_3,step6_3d,step6_4,step6_4d,step7,step8) values ('" & dlr.Value & "','" & Sev.Value & "','" & Vin.Value & "','" & Eng.Value & "','" & Color.Value & "','" & reg.Value & "') " & _
Var.Value & "','" & mil.Value & "',#" & Format(repdt.Value, "MM/DD/YY") & "#,#" & Format(dos.Value, "MM/DD/YY") & "#,'" & part.Value & "','" & Comp.Value & "','" & opc.Value & "','" & inv.Value & "','" & techob.Value & "','" & Susp.Value & "','" & Corr.Value & "','" & taccom.Value & "','" & fincom.Value & "','" & FILEPATHLOAD & "','" & Combo44.Value & "','" & CAT.Value & "','" & mdl.Value & "',#" & Format(proddt.Value, "mm/dd/yy") & "#," & bps & ",'" & Text61.Value & "','" & Step1.Value & "','" & step1d.Value & "','" & step2.Value & "','" & step2d.Value & "','" & step3.Value & "','" & step3d.Value & "','" & Step4.Value & "', '" & step4d.Value & "','" & step5.Value & "','" & step5d.Value & "','" & _
step6_1.Value & "','" & step6_1d.Value & "','" & step6_2.Value & "','" & step6_2d.Value & "','" & step6_3.Value & "','" & step6_3d.Value & "','" & step6_4.Value & "‘,'" & step6_4d.Value & "','" & step7.Value & "','" & step8.Value & "')"

CurrentDb.Execute (strSQL), dbFailOnError

CJ_London · May 3, 2016

@bvijay - appreciate your keenness to help, but you are responding to a 3-4 year old post. But welcome to the forum

CurrentDb.Execute "INSERT INTO (2 Viewers)

rowfei

Registered User.

WayneRyan

AWF VIP

rowfei

Registered User.

WayneRyan

AWF VIP

rowfei

Registered User.

Attachments

WayneRyan

AWF VIP

rowfei

Registered User.

WayneRyan

AWF VIP

Galaxiom

Super Moderator

rowfei

Registered User.

Galaxiom

Super Moderator

RainLover

VIP From a land downunder

vbaInet

AWF VIP

bvijay

New member

CJ_London

Super Moderator

Similar threads

Users who are viewing this thread