Problem to insert last autonumber record ID into an inster into statement in a loop ...

[corentin]

Good morning users,

I am facing to a hard problem (beginner in VBA code).

What I need is to get the last record ID "Purchase_REF" field (autonumber incremented, primary key) from table "tbl_Production_Order" and put that value into an other table "tbl_PO_Process" (into a text field, primary key).

The strange thing is that, this processus works if I put a MsgBox that keeps the last ID ... I think there is a problem to refresh data because this process must be done for n records and is already in a loop.

Here is my code : (I put in red the raws with the insert into statement that doesn't work and in orange the lines that should get the last ID (Purchase_REF) values ...)

Dim RS As Object
Dim n As Integer

Dim RecordSet As DAO.RecordSet
Dim RecordSet2 As DAO.RecordSet
Dim lastID As Variant
Dim strSQL1 As String
Dim Last_PO_nr As String

DoCmd.Save acForm, "New_Purchase_Order"
'First, the current form is recorded in order to register the PuO's ref into table "tbl_Purchase_Order"
Set RS = Me.tbl_Purchase_Order_subform.Form.RecordSet

With RS
.MoveFirst
Do While Not .EOF
If Not IsNull(tbl_Purchase_Order_subform.Form!Qty) Then
Else: MsgBox ("Please fill each record with parts quantity. Your record has not been registered !"), vbInformation
Exit Sub
End If
.MoveNext
Loop
.MoveFirst
Do While Not .EOF

strSQL = "INSERT INTO tbl_Production_Order (PuO_ID,Customer_ID,Part_ID,Delivery_Date) VALUES ('" & tbl_Purchase_Order_subform.Form.PuO_ID.Value & "','" & Me.Customer_ID & "','" & tbl_Purchase_Order_subform.Form.Part_ID.Value & "','" & tbl_Purchase_Order_subform.Form.Delivery_Date.Value & "');"


n = tbl_Purchase_Order_subform.Form!Qty

For n = 1 To n
Me.Refresh
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.Requery
lastID = DMax("Purchase_REF", "tbl_Production_Order")

strSQL3 = "INSERT INTO tbl_PO_Process (ID_PO,Statut) VALUES (" & lastID & ",'Ordered');"
CurrentDb.Execute strSQL3, dbFailOnError

strSQL1 = "SELECT tbl_Item_QTY.Item_QTY_eq,tbl_Item_QTY.Item_ID FROM tbl_Item_QTY WHERE ((tbl_Item_QTY.Part_ID)= '" & tbl_Purchase_Order_subform.Form.Part_ID.Value & "');"

Set RecordSet = CurrentDb.OpenRecordset(strSQL1)

Do While Not RecordSet.EOF
IT_QTY = RecordSet("Item_QTY_eq") * -1
IT_ID = RecordSet("Item_ID")
strSQL2 = "INSERT INTO tbl_Stock_Management (Item_ID,Qty) VALUES ('" & IT_ID & "','" & IT_QTY & "');"
' MsgBox (strSQL2)
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL2
' MsgBox (IT_QTY)
' MsgBox (IT_ID)
RecordSet.MoveNext

Loop

RecordSet.Close
Set RecordSet = Nothing

Next n

.MoveNext
Loop

'Loop that allows to register each PO for each entry in the subform that the PuO contains
End With
MsgBox ("The Parts into the Purchase Order " & PuO_ID & " have been sucessfully registered !"), vbInformation



Thanks a lot for any help and sorry for my english ...

corentin

 

[arnelgp]

can't help but notice.
change RecordSet variable to something else (recordset is a reserved word).
Requerying will get the latest record and you will loose the position of your recordset.
that means your record pointer will always be pointing to the first record.

 

[corentin]

can't help but notice.
change RecordSet variable to something else (recordset is a reserved word).
Requerying will get the latest record and you will loose the position of your recordset.
that means your record pointer will always be pointing to the first record.

Thanks for your help arnelgp. I did what you adviced me but still the problem happens. Here I get : Run time error 3022

The problem is on : "CurrentDb.Execute strSQL3, dbFailOnError

I am sure the problem come from the fact that system can not get the last ID proprely ... But can't solve the update or refresh the record I am totally confused with these notions in VBA

 

[ebs17]

Problem to insert last autonumber record ID

Research examples for @@IDENTITY.

Otherwise, your code is very confusing for me and has some major weaknesses: I prefer a (correct) query over so many loops to append multiple data records. This makes one append query per table.
Duplicates and thus triggered errors can be avoided by incorporating appropriate inconsistency checks into such append queries.

 

[moke123]

Some other observations:

DoCmd.Save acForm, "New_Purchase_Order"
This is saving the form object, not the data.

DoCmd.SetWarnings False
You never set warnings back to true which can cause problems.

 

[corentin]

Some other observations:

DoCmd.Save acForm, "New_Purchase_Order"
This is saving the form object, not the data.

DoCmd.SetWarnings False
You never set warnings back to true which can cause problems.

Thank you I have modified the code in order to be more lisible. I just can't populate the table "tbl_PO_Process" with the last ID from table "tbl_Production_Order" ... But the rest of code is working.

What I want to do is populate a table with the last Production Order ID that has been automaticelly generated by autonumber for each Production Order AND for each Part Reference.

As you can see on the following picture, all datas from the subform (table) go to table "tbl_Production_Order"

Here is my code :

'VBA Code to register a new Purchase Order into table "tbl_Purchase_Order" and the related Production Order into table "tbl_Production_Order"
Private Sub Register_Part_into_PuO_Click()
Dim RS As Object
Dim n As Integer

Dim RecordSet As DAO.RecordSet
Dim strSQL1 As String

' DoCmd.Save acForm, "New_Purchase_Order"
'First, the current form is recorded in order to register the PuO's ref into table "tbl_Purchase_Order"
Set RS = Me.tbl_Purchase_Order_subform.Form.RecordSet

With RS
.MoveFirst
Do While Not .EOF
If Not IsNull(tbl_Purchase_Order_subform.Form!Qty) Then
Else: MsgBox ("Please fill each record with parts quantity. Your record has not been registered !"), vbInformation
Exit Sub
End If
.MoveNext
Loop
.MoveFirst
Do While Not .EOF

strSQL = "INSERT INTO tbl_Production_Order (PuO_ID,Customer_ID,Part_ID,Delivery_Date) VALUES ('" & tbl_Purchase_Order_subform.Form.PuO_ID.Value & "','" & Me.Customer_ID & "','" & tbl_Purchase_Order_subform.Form.Part_ID.Value & "','" & tbl_Purchase_Order_subform.Form.Delivery_Date.Value & "');"
strSQL1 = "SELECT tbl_Item_QTY.Item_QTY_eq,tbl_Item_QTY.Item_ID FROM tbl_Item_QTY WHERE ((tbl_Item_QTY.Part_ID)= '" & tbl_Purchase_Order_subform.Form.Part_ID.Value & "');"

n = tbl_Purchase_Order_subform.Form!Qty

For n = 1 To n
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True


Set RecordSet = CurrentDb.OpenRecordset(strSQL1)
Do While Not RecordSet.EOF
IT_QTY = RecordSet("Item_QTY_eq") * -1
IT_ID = RecordSet("Item_ID")
strSQL2 = "INSERT INTO tbl_Stock_Management (Item_ID,Qty) VALUES ('" & IT_ID & "','" & IT_QTY & "');"
' MsgBox (strSQL2)
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL2
DoCmd.SetWarnings True
' MsgBox (IT_QTY)
' MsgBox (IT_ID)
RecordSet.MoveNext
Loop
RecordSet.Close
Set RecordSet = Nothing


Next n

.MoveNext
Loop
'Loop that allows to register each PO for each entry in the subform that the PuO contains
End With
MsgBox ("The Parts into the Purchase Order " & PuO_ID & " have been sucessfully registered !"), vbInformation

End Sub

 

[corentin]

Ok I didn't find the issue to that problem but my main problem is solved. Thank you to help me !

 

[moke123]

Glad you got it sorted out.
Good luck on your project.