corentin
New member
- Local time
- Today, 16:17
- Joined
- Jan 22, 2020
- Messages
- 12
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
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