Runtime Error 3001 HELP I Am loosing my mind (1 Viewer)

[fjell]

Hi guys i am loosing my mind here and really need some help i dont understand what is going on

so here is the situation i have a table that contains materialnumbers
now i have created a form that will add a new material to the table local materialnumber table and also updates the online materialnumber table.
the problem is this when i try to upload the data i enounter the runtime error 3001. is use the exact same code in another form where i am modyfying a Material out of the table and it works perfectly fine.

this is the code that triggers the runtime error 3001
Private Sub Command220_Click()

Dim tablename As String
Dim newtablename As String
tablename = "tblMastersheetPartsList"
newtablename = "tblMastersheetPartsList1"
Dim MaterialNumber As String
Dim Station As String

MaterialNumber = Me.MaterialNumber.value
Station = Me.Station.value
MaterialNumber = " & MaterialNumber & "


DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdSaveRecord

Dim strSQL As String

' On Error GoTo ErrorHandler
DoCmd.SetWarnings False

' Set the db object to the current database
strSQL = "INSERT INTO tblMastersheetPartsList1 SELECT * FROM tblMastersheetPartsList " & _
"WHERE MaterialNumber = '" & MaterialNumber & "'"

DoCmd.RunSQL strSQL

MsgBox "hurray it worked"

Exit Sub

ErrorHandler:

strSQL = "INSERT INTO tblMaterialUpdateBuffer (Materialnumber, Station, New) VALUES ('" & MaterialNumber & "', '" & Station & "', True)"
DoCmd.RunSQL strSQL

' Clean up and release the db object


End Sub
the strange thing is that as soon as I dont try to set the Materialnumber dynamically it works

this is the code that works.

Private Sub Command220_Click()

Dim tablename As String
Dim newtablename As String
tablename = "tblMastersheetPartsList"
newtablename = "tblMastersheetPartsList1"
Dim MaterialNumber As String
Dim Station As String

MaterialNumber = "00001-0258"
Station = Me.Station.value
MaterialNumber = " & MaterialNumber & "


DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdSaveRecord

Dim strSQL As String

' On Error GoTo ErrorHandler
DoCmd.SetWarnings False

' Set the db object to the current database
strSQL = "INSERT INTO tblMastersheetPartsList1 SELECT * FROM tblMastersheetPartsList " & _
"WHERE MaterialNumber = '00001-0258'"


DoCmd.RunSQL strSQL

MsgBox "hurray it worked"

Exit Sub

ErrorHandler:

strSQL = "INSERT INTO tblMaterialUpdateBuffer (Materialnumber, Station, New) VALUES ('" & MaterialNumber & "', '" & Station & "', True)"
DoCmd.RunSQL strSQL
End Sub

here is the code form the other form that works too as a reference

Private Sub Command220_Click()
Dim controlCount As Integer
Dim ctl As Control
Dim ID As Long
Dim MaterialNumber As String
Dim Station As String
Dim strSQL As String
Dim MaterialNumberNew As String ' modifying a Material probably results in a new variation or Materialnumber new = MaterialNumberOld -01 for instance Problem without having the old materialnumber we cannot delet the old material out of the online system
MaterialNumberNew = Me.MaterialNumberReal.value

ID = Me.ID.value
MaterialNumber = Me.MaterialNumber.value
Station = Me.Station.value

DoCmd.RunCommand acCmdSaveRecord

For Each ctl In Me.Detail.Controls
If Not (ctl.Name Like "Station*" Or ctl.Name Like "MaterialNumber*") And Not TypeOf ctl Is CommandButton Then
ctl.visible = False
End If
Next ctl

On Error GoTo ErrorHandler
strSQL = "DELETE FROM tblMastersheetPartsList1 WHERE ID = " & ID
DoCmd.RunSQL strSQL

' Insert the current record into tblMastersheetPartList1
strSQL = "INSERT INTO tblMastersheetPartsList1 SELECT * FROM tblMastersheetPartsList " & _
"WHERE MaterialNumber = '" & MaterialNumberNew & "' AND Station = '" & Station & "'"
DoCmd.RunSQL strSQL
MsgBox "we did it"

Exit Sub

ErrorHandler:
MsgBox "Offline"
' If an error occurs during insert, update tblMaterialUpdateBuffer
strSQL = "INSERT INTO tblMaterialUpdateBuffer (MaterialNumberNew ,Materialnumber, Station, Modify) VALUES ('" & MaterialNumberNew & "', '" & MaterialNumber & "', '" & Station & "', True)"
DoCmd.RunSQL strSQL
End Sub

i dont understand the problem i am trying to fix this issue for the last past 3 hours now and i dont make any progress please help me

 

[Gasman]

Please post code with code tags, that keeps the indentation, which I hope you are using?
Especially for the amount of code given.

What is error 3001?
On what line does it occur?

 

[fjell]

Private Sub Command220_Click()

Dim tablename As String
Dim newtablename As String
tablename = "tblMastersheetPartsList"
newtablename = "tblMastersheetPartsList1"
Dim MaterialNumber As String
Dim Station As String

MaterialNumber = Me.MaterialNumber.value
Station = Me.Station.value
MaterialNumber = " & MaterialNumber & "


DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdSaveRecord

Dim strSQL As String

' On Error GoTo ErrorHandler
DoCmd.SetWarnings False

' Set the db object to the current database
strSQL = "INSERT INTO tblMastersheetPartsList1 SELECT * FROM tblMastersheetPartsList " & _
"WHERE MaterialNumber = '" & MaterialNumber & "'"

DoCmd.RunSQL strSQL

MsgBox "hurray it worked"

Exit Sub

ErrorHandler:

strSQL = "INSERT INTO tblMaterialUpdateBuffer (Materialnumber, Station, New) VALUES ('" & MaterialNumber & "', '" & Station & "', True)"
DoCmd.RunSQL strSQL

' Clean up and release the db object


End Sub

'the strange thing is that as soon as I dont try to set the Materialnumber dynamically it works
this is the code that works.

Private Sub Command220_Click()

Dim tablename As String
Dim newtablename As String
tablename = "tblMastersheetPartsList"
newtablename = "tblMastersheetPartsList1"
Dim MaterialNumber As String
Dim Station As String

MaterialNumber = "00001-0258"
Station = Me.Station.value
MaterialNumber = " & MaterialNumber & "


DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdSaveRecord

Dim strSQL As String

' On Error GoTo ErrorHandler
DoCmd.SetWarnings False

' Set the db object to the current database
strSQL = "INSERT INTO tblMastersheetPartsList1 SELECT * FROM tblMastersheetPartsList " & _
"WHERE MaterialNumber = '00001-0258'"


DoCmd.RunSQL strSQL

MsgBox "hurray it worked"

Exit Sub

ErrorHandler:

strSQL = "INSERT INTO tblMaterialUpdateBuffer (Materialnumber, Station, New) VALUES ('" & MaterialNumber & "', '" & Station & "', True)"
DoCmd.RunSQL strSQL
End Sub

'here is the code form the other form that works too as a reference

Private Sub Command220_Click()
Dim controlCount As Integer
Dim ctl As Control
Dim ID As Long
Dim MaterialNumber As String
Dim Station As String
Dim strSQL As String
Dim MaterialNumberNew As String ' modifying a Material probably results in a new variation or Materialnumber new = MaterialNumberOld -01 for instance Problem without having the old materialnumber we cannot delet the old material out of the online system
MaterialNumberNew = Me.MaterialNumberReal.value

ID = Me.ID.value
MaterialNumber = Me.MaterialNumber.value
Station = Me.Station.value

DoCmd.RunCommand acCmdSaveRecord

For Each ctl In Me.Detail.Controls
If Not (ctl.Name Like "Station*" Or ctl.Name Like "MaterialNumber*") And Not TypeOf ctl Is CommandButton Then
ctl.visible = False
End If
Next ctl

On Error GoTo ErrorHandler
strSQL = "DELETE FROM tblMastersheetPartsList1 WHERE ID = " & ID
DoCmd.RunSQL strSQL

' Insert the current record into tblMastersheetPartList1
strSQL = "INSERT INTO tblMastersheetPartsList1 SELECT * FROM tblMastersheetPartsList " & _
"WHERE MaterialNumber = '" & MaterialNumberNew & "' AND Station = '" & Station & "'"
DoCmd.RunSQL strSQL
MsgBox "we did it"

Exit Sub

ErrorHandler:
MsgBox "Offline"
' If an error occurs during insert, update tblMaterialUpdateBuffer
strSQL = "INSERT INTO tblMaterialUpdateBuffer (MaterialNumberNew ,Materialnumber, Station, Modify) VALUES ('" & MaterialNumberNew & "', '" & MaterialNumber & "', '" & Station & "', True)"
DoCmd.RunSQL strSQL
End Sub

[ICODE][/ICODE]





the error says invalid argument
also it happens where i try to execute the SQL Statement

strSQL = "INSERT INTO tblMastersheetPartsList1 SELECT * FROM tblMastersheetPartsList " & _
"WHERE MaterialNumber = '" & MaterialNumber & "'"

DoCmd.RunSQL strSQL

 

[fjell]

Please post code with code tags, that keeps the indentation, which I hope you are using?
Especially for the amount of code given.

What is error 3001?
On what line does it occur?

by the way thanks for the fast reply

 

[Gasman]

by the way thanks for the fast reply

Speed is no good unless you get the answer in that time.
debug.print that strSQL and paste it back here.

You can also paste the output into the SQL code window and see what it complains about.

 

[moke123]

MaterialNumber = " & MaterialNumber & "

this doesnt look right.

Debug.print your strSQL and see what it looks like.

 

[fjell]

i have not

MaterialNumber = " & MaterialNumber & "

this doesnt look right.

Debug.print your strSQL and see what it looks like.

you are right that was not the way i initially programmed it and already deleted it out of the code again but it still does not work

 

[fjell]

this i what i get as an output for the debug.prin.

the printed out stuff is from another field of my form
do i have to place the DEbug.Print some where else?
also what is the SQL code window?

 

[Gasman]

Cannot read that, way off the screen

Copy the output and paste here, again preferably within code tags, as that always appears to be clearer to me, especially with "'"
The SQL code window is one of the windows for a query, you get Design View and SQL View (amongst others), the SQL view does what it says on the tine, it allows you to view the SQL, and amend if needed.
You can also run your pasted strSQL if it is correct, and often highlights where it is not.

When posting pics for code, that is one of the times when 'a picture is worth a thousand words' is not true.
We cannot copy that code and experiment with it ourselves, and noone wants to type all that in, when it is already available.

 

[fjell]

INSERT INTO tblMastersheetPartsList1 SELECT * FROM tblMastersheetPartsList WHERE MaterialNumber = '00001-0258'
INSERT INTO tblMastersheetPartsList1 SELECT * FROM tblMastersheetPartsList WHERE MaterialNumber = '9098'
INSERT INTO tblMastersheetPartsList1 SELECT * FROM tblMastersheetPartsList WHERE MaterialNumber = '67676767'
INSERT INTO tblMastersheetPartsList1 SELECT * FROM tblMastersheetPartsList WHERE MaterialNumber = '67676767'

that is the debug.Print strSQL output

 

[Gasman]

So do each of those lines work in the SQL view?
Are the fields exactly the same and in the same order?

vba runtime error 3001 on insert statement - Google Search

Not even going to ask ATM why you are copying data from one table to another? Having two copies of data can go adrift.

The DVLA in the UK have done that. A friends car is shown as Black on one DB (and it was) and White on another?

 

[fjell]

i did not finde the SQL view i am using maccess 2017
they are i double checked i even created different id fields in the two tables so that we dont copy data in a autonumber field that has the primary key

 

[moke123]

Gasman is referring to query design screen.

go to Create > QueryDesign and then choose SQL View.

Paste the debugged string into it and see if it runs.

 

[Gasman]

Well idm as autonumber will be long.
What is idm that is not an autonumber?
You have no title in one table?

So they are not identical.

You will need to specify the correct fields, in the correct order.
What was that output in that pic you posted which was all generic label and text names?

Edit: I see you do have title, but not in same position? So that would be an issue as well, not for this problem, but you would be mapping to incorrect fields.

 

[ebs17]

runtime error 3001 => Invalid argument

INSERT INTO tblMastersheetPartsList1 SELECT * FROM ...

Something like that is always rubbish, the floodgates are wide open to chance.

Even if it is more work, you should always list the fields so that a mapping from source field to target field is clear. Without this assignment one hopes that all fields have the same designations and are present in the same number.
In your case, this hope does not seem to be completely fulfilled.

INSERT INTO tblMastersheetPartsList1 (Field1, Field2, ...) SELECT FieldA, FieldB, ... FROM ...