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
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