Insert Requisition Details into multiple Purchase Orders

edgaro · Jul 10, 2016

Hi, forum. Good day to you all.

I'm having some issues regarding the insertion of a Requisition details table into an Order details table. For me, a requisition is an internal move of my company, where you send a document to someone in another team who's meant to authorize the purchase of goods.

This is my case:
In order to avoid making several requisitions, one for every provider, the user creates ONE requisition in which he enters different kinds of items, specifying the provider of each item. Once finished (and authorized), the user would press a button to "send" all of these items to different purchase orders, one for every provider this time is OK. So, for short, here's an example:

Requisition 1:
Cereal box - 145 - Wallmart
Coffee bag - 160 - Wallmart
Sugar - 5 - CostCo

Is transformed into
Purchase Order 1 for Wallmart:
Cereal box - 145
Coffee bag - 160

Purchase Order 2 for Costco:
Sugar - 5

For this example, I have:
tbProviders(ProviderID, ProviderName),
tbProducts(ProductID, ProductName),
tbRequisitions(RequisitionID, RequisitionNumber),
tbRequisitionDetails(ReqDetailsID, ProductID*, Quantity, RequisitionID*),
tbOrders(OrderID, OrderNumber, ProviderID*),
tbOrderDetails(OrderDetailsID, ProductID*, Quantity,OrderID*)
where * = foreign keys

Can this be done in one move? If so, could you give me an example?

sneuberg · Jul 10, 2016

Something seems to be missing. There doesn't seem to be anything in these tables that relates a product to to a provider. How from the data in these table would I know that the sugar comes from Costco.

sneuberg · Jul 10, 2016

Maybe the tbRequisitions needs the ProviderID as a foreign key.

sneuberg · Jul 10, 2016

I noticed now that the order numbers are not the requisition numbers. Don't you want to keep track of what requisitions the orders came from?

edgaro · Jul 11, 2016

Dear sneugberg, you've been very attentive and I'm sorry I didn't reply before.

You are completely right, I forgot to mention a couple of things. These are the corrected tables:

tbProviders(ProviderID, ProviderName),
tbProducts(ProductID, ProductName),
tbRequisitions(RequisitionID, RequisitionNumber),
tbRequisitionDetails(ReqDetailsID, ProductID*, Quantity, RequisitionID*,ProviderID*),
tbOrders(OrderID, OrderNumber, ProviderID*),
tbOrderDetails(OrderDetailsID, ProductID*, Quantity,OrderID*,RequisitionID*)
where * = foreign keys

I modified tbRequisitionDetails, I added the ProviderID. I also added RequisitionID to the table tbOrders. Any product can have any number of providers, that's why the only link from a product to a provider lives inside the purchase orders in tbOrders.

Thanks for your insights.

sneuberg · Jul 11, 2016

Getting back to your originally question. No. I don't think this can be done with one move. It will take at least two because you need to populate the tblOrders before you can add to the tbOrderDetails.

I was working on something for you, but I don't know how you are assigning Order Numbers. How are you going to do that? Note that if you just want to get the next highest by using DMAX that this can cause duplicates in a multiuser environment that you will need to address. Could you possibly make the Order Number the requisition number? That would simplify this.

edgaro · Jul 11, 2016

There's only one person filling the orders right now. The way I'm assingning the order number is by looking at the maximum number in the tbOrders. If you think that way of doing it can create a problem, I completely understand, I could modify it, no problem.

sneuberg · Jul 11, 2016

I could not find a way to do this in less that two step for each provider, i.e., order. The limiting factor is having to get the order number for each order. The code that follows is what I came up with and can be found in the attached database. This code uses DMax to get the next order number. If two users click on the Place Order button within a few milliseconds of each other you could end up with duplicate order numbers. To handle this unlikely event I put a unique index on the OrderNumber field which causes an error to be raised if a duplicate order number is inserted. This is handled in the error handler.

Code:

Private Sub PlaceOrder_Click()
On Error GoTo PlaceOrder_Click_Err

Dim strSQL As String
Dim lngNextOrderNumber As Long
Dim lngOrderID As Long
Dim rs As DAO.Recordset
strSQL = "SELECT DISTINCT tbRequisitionDetails.ProviderID " & _
        "FROM tbRequisitions INNER JOIN tbRequisitionDetails ON tbRequisitions.RequisitionID = tbRequisitionDetails.RequisitionID  " & _
        "WHERE tbRequisitions.RequisitionNumber = " & Me.RequisitionNo
Set rs = CurrentDb.OpenRecordset(strSQL)
Do While Not rs.EOF
    'get the next order number as one more than the last
    lngNextOrderNumber = Nz(DMax("[OrderNumber]", "[tbOrders]")) + 1
    'insert requisition in the orders table
    CurrentDb.Execute "INSERT INTO tbOrders (ProviderID, OrderNumber) VALUES (" & rs!ProviderID & ", " & lngNextOrderNumber & ");", dbFailOnError
    lngOrderID = DLookup("[OrderID]", "[tbOrders]", "[OrderNumber] = " & lngNextOrderNumber)
    'insert the requistion details in into the order details
    CurrentDb.Execute "INSERT INTO tbOrderDetails ( OrderID, RequisitionID, ProductID, Quantity )" & _
         " SELECT " & lngOrderID & " AS OrderID, tbRequisitionDetails.RequisitionID, tbRequisitionDetails.ProductID, tbRequisitionDetails.Quantity " & _
        " FROM tbRequisitionDetails " & _
        " WHERE tbRequisitionDetails.ProviderID = " & rs!ProviderID & ";", dbFailOnError
    rs.MoveNext
Loop

PlaceOrder_Click_Exit:
    Exit Sub

PlaceOrder_Click_Err:
    'This error could occur in the unlikely event that two users got the same next order number
    If Err.Number = 3022 Then
        MsgBox "Duplicate Order Number.  Try again"
    Else
        MsgBox Error$
    End If
    Resume PlaceOrder_Click_Exit

End Sub

You will need to do more than what this code does. Somehow you will have to mark the requisition as being ordered so they don't get ordered twice. This really should be done as a transaction so that if something fails everything is rolled back.

edgaro · Jul 11, 2016

Oh, wow

That's just so wonderful and beautiful. Uhm. With your permission, I will adapt this to my database right now. It does EXACTLY what I need. That's what I meant by "one move", just a button click. Wow. They're gonna love this.

I don't know how to thank you more. I'll post back with your code adapted to my database. For now, thanks, huge THANKS.

sneuberg · Jul 11, 2016

edgaro said:
Oh, wow

That's just so wonderful and beautiful. Uhm. With your permission, I will adapt this to my database right now..

You don't need my permission. I'm glad it helps, but please keep in mind it needs to be enhanced quite a bit.

edgaro · Jul 11, 2016

I think I've covered the basics with the code. Here's my rendition of your code in my database. I added an UPDATE statement to change the status of my items to "sent to purchase order" and I also added another WHERE clause to avoid sending those items with a "Sent" status. I already tested it and it's working fine.

Code:

Private Sub btnColocarOrden_Click()
'Code provided by Sneuberg http://www.access-programmers.co.uk/forums/showthread.php?t=287903
On Error GoTo btnColocarOrden_Click_Err

Dim strSQL As String
Dim strSQL_OC As String
Dim strSQL_OCDet As String
Dim lngNextOrderNumber As Long
Dim lngOrderID As Long
Dim FcHoy As Date
Dim FcMañana As Date
Dim rs As DAO.Recordset

strSQL = "SELECT DISTINCT tbRequisicionesDetalle.ProveedorID, tbRequisicionesDetalle.ItemStatusID " & _
        "FROM tbRequisiciones INNER JOIN tbRequisicionesDetalle ON tbRequisiciones.RequisicionID = tbRequisicionesDetalle.RequisicionID " & _
        "WHERE tbRequisiciones.NumReq = " & Me.RequisicionID & " AND Not tbRequisicionesDetalle.ItemStatusID = 7"

Set rs = CurrentDb.OpenRecordset(strSQL)
Do While Not rs.EOF
    'get the next order number as one more than the last
    lngNextOrderNumber = Nz(DMax("[NumOC]", "[tbOrdenesDeCompra]")) + 1
    
    'insert requisition in the orders table
    FcHoy = Date
    FcMañana = Date + 1
    CurrentDb.Execute "INSERT INTO tbOrdenesDeCompra (NumOC, ContratoID, ElaboraID, AutorizaID, RecibeID, FcSolicitud, FcEntrega, ItemStatusID, ProveedorID) VALUES (" & lngNextOrderNumber & ", " & Me.ContratoID & ", " & Me.ElaboraID & ", " & Me.AutorizaID & ", " & Me.SolicitaID & ", " & "#" & Format(FcHoy, "yyyy/mm/dd") & "#" & ", " & "#" & Format(FcMañana, "yyyy/mm/dd") & "#" & ", " & 8 & ", " & rs!ProveedorID & ");", dbFailOnError
    lngOrderID = DLookup("[OrdenID]", "[tbOrdenesDeCompra]", "[NumOC] = " & lngNextOrderNumber)
    
    'insert the requistion details into the order details
    CurrentDb.Execute "INSERT INTO tbOrdenesDeCompraDetalle ( OrdenID, ReqDetID, PrecioU, Cantidad, RemisionID)" & _
         " SELECT " & lngOrderID & " AS OrdenID, tbRequisicionesDetalle.ReqDetID, tbRequisicionesDetalle.PrecioU, tbRequisicionesDetalle.Cantidad, 0 " & _
        " FROM tbRequisicionesDetalle " & _
        " WHERE tbRequisicionesDetalle.ProveedorID = " & rs!ProveedorID & "AND Not tbRequisicionesDetalle.ItemStatusID = 7;", dbFailOnError
    
    'change item status
    CurrentDb.Execute "UPDATE tbRequisicionesDetalle SET ItemStatusID = 7 WHERE RequisicionID = " & Me.RequisicionID & " AND ItemStatusID = 6;", dbFailOnError
    
    rs.MoveNext
Loop

btnColocarOrden_Click_Exit:
    Exit Sub

btnColocarOrden_Click_Err:
    'This error could occur in the unlikely event that two users got the same next order number
    If Err.Number = 3022 Then
        MsgBox "Orden duplicada, intenta nuevamente"
    Else
        MsgBox Error$
    End If
    Resume btnColocarOrden_Click_Exit

End Sub

Thanks a lot. If you have, by chance, another suggestion, I'll gladly take it into consideration.

sneuberg · Jul 11, 2016

Thanks for letting me know you got it working. I do have another suggestion.

Your code is a classic example of where transactions should be used. If your computer crashes while this is running a requisition could be half filled or in any case the state of things left inconsistent. Please look at https://msdn.microsoft.com/en-us/library/bb243806(v=office.12).aspx and consider adding a transaction to this. It's only about five lines of code that you can copy and paste from this site into your code.

Note the wrkCurrent.Rollback could go just before the Resume btnColocarOrden_Click_Exit in the error handler.

edgaro · Jul 15, 2016

I tried the transaction method you mentioned but all I got is "Invalid Use of Null". For some reason, that I wanted to inspect, anyway.

And so, to see what was going on I added a few debug.print instructions before the currentdb.execute instructions and I couldn't get the application to return anything into the immidiate window. I don't know why either.

That's when I decided make a few tests... I also found out that when ProviderID = 1, it appends the details of all of the other Orders where ProviderID = 1.

I really have no idea what's going on. This is the code I'm currently using:

Code:

Private Sub btnColocarOrden_Click()
On Error GoTo btnColocarOrden_Click_Err

Dim strSQL As String
Dim strSQL_OC As String
Dim strSQL_OCDet As String
Dim lngNextOrderNumber As Long
Dim lngOrderID As Long
Dim FcHoy As Date
Dim FcMañana As Date
Dim rs As DAO.Recordset

strSQL = "SELECT DISTINCT tbRequisicionesDetalle.ProveedorID, tbRequisicionesDetalle.ItemStatusID " & _
        "FROM tbRequisiciones INNER JOIN tbRequisicionesDetalle ON tbRequisiciones.RequisicionID = tbRequisicionesDetalle.RequisicionID " & _
        "WHERE tbRequisiciones.NumReq = " & Me.RequisicionID & " AND Not tbRequisicionesDetalle.ItemStatusID = 7"

Set rs = CurrentDb.OpenRecordset(strSQL)
Do While Not rs.EOF
    'get the next order number as one more than the last
    lngNextOrderNumber = Nz(DMax("[NumOC]", "[tbOrdenesDeCompra]")) + 1
    
    'insert requisition in the orders table
    FcHoy = Date
    FcMañana = Date + 1
    CurrentDb.Execute "INSERT INTO tbOrdenesDeCompra (NumOC, ContratoID, ElaboraID, AutorizaID, RecibeID, FcSolicitud, FcEntrega, ItemStatusID, ProveedorID) VALUES (" & lngNextOrderNumber & ", " & Me.ContratoID & ", " & Me.ElaboraID & ", " & Me.AutorizaID & ", " & Me.SolicitaID & ", " & "#" & Format(FcHoy, "yyyy/mm/dd") & "#" & ", " & "#" & Format(FcMañana, "yyyy/mm/dd") & "#" & ", " & 8 & ", " & rs!ProveedorID & ");", dbFailOnError
    lngOrderID = DLookup("[OrdenID]", "[tbOrdenesDeCompra]", "[NumOC] = " & lngNextOrderNumber)
    
    'insert the requistion details into the order details
    CurrentDb.Execute "INSERT INTO tbOrdenesDeCompraDetalle ( OrdenID, ReqDetID, PrecioU, Cantidad, RemisionID)" & _
         " SELECT " & lngOrderID & " AS OrdenID, tbRequisicionesDetalle.ReqDetID, tbRequisicionesDetalle.PrecioU, tbRequisicionesDetalle.Cantidad, 0 " & _
        " FROM tbRequisicionesDetalle " & _
        " WHERE tbRequisicionesDetalle.ProveedorID = " & rs!ProveedorID & "AND Not tbRequisicionesDetalle.ItemStatusID = 7;", dbFailOnError
    
    'change item status
    CurrentDb.Execute "UPDATE tbRequisicionesDetalle SET ItemStatusID = 7 WHERE RequisicionID = " & Me.RequisicionID & " AND ItemStatusID = 6 AND tbRequisicionesDetalle.ProveedorID = " & rs!ProveedorID & ";", dbFailOnError
    
    rs.MoveNext
Loop

btnColocarOrden_Click_Exit:
    Exit Sub

btnColocarOrden_Click_Err:
    'This error could occur in the unlikely event that two users got the same next order number
    If Err.Number = 3022 Then
        MsgBox "Orden duplicada, intenta nuevamente"
    Else
        MsgBox Error$
    End If
    Resume btnColocarOrden_Click_Exit

End Sub

There was another bizarre problem a few moments ago, the whole code stopped working after I added a listbox into my form. It was not related whatsoever, it just simply did nothing all of a sudden. Luckily I have a lot of copies of this test database and I just grabbed the one where everything was working and started again.

Mind if you explain to me, briefly, how this code is working? Specially how it chooses which Order the Details go to... I'm confused.

Thank you in advance.

sneuberg · Jul 15, 2016

The idea behind the code was to get the distinct provider ids as the orders are divided up as one order per provider. In your code you have

Code:

SELECT DISTINCT tbRequisicionesDetalle.ProveedorID, tbRequisicionesDetalle.ItemStatusID

if the addition of tbRequisicionesDetalle.ItemStatusID causes this query to return duplicate ProveedorIDs then this isn't going to work. I think you will need to go back to the beginning of this and explain what role this ItemStatusID has.

As far as the order numbers go one is created for each provider id by the line:

Code:

lngNextOrderNumber = Nz(DMax("[NumOC]", "[tbOrdenesDeCompra]")) + 1

So the next order number is one added to the largest NumOC. When this order is inserted in the tbOrdenesDeCompra a OrdenID is assigned to it as an Autonumber. To insert the related records into the tbOrdenesDeCompraDetalle table we need to get that to insert as the foreign key. We get that with the following statement

Code:

  lngOrderID = DLookup("[OrdenID]", "[tbOrdenesDeCompra]", "[NumOC] = " & lngNextOrderNumber)

You can see that the result, lngOrderID, is concatenated into the select part of the insert statement that adds the records to the tbOrdenesDeCompraDetalle table.

edgaro · Jul 17, 2016

Hi again.

I understood your explanation and was able to correct my SQL inserts based on that. Took me a while, though, sorry about that. Just a little last question. I'm really liking the Transaction method, but I don't seem to make it work. I still keep getting an "Invalid use of Null" error after correcting my SQL statements. Am I placing the transaction instructions in the right position? do I need to close the recordset or something?

Code:

Private Sub btnColocarOrden_Click()

On Error GoTo btnColocarOrden_Click_Err

'declare variables
Dim wrkCurrent As DAO.Workspace
Dim strSQL As String
Dim strSQL_OC As String
Dim strSQL_OCDet As String
Dim lngNextOrderNumber As Long
Dim lngOrderID As Long
Dim FcHoy As Date
Dim FcMañana As Date
Dim rs As DAO.Recordset

Set wrkCurrent = DBEngine.Workspaces(0)
'initial query to find the distinct providers
strSQL = "SELECT DISTINCT tbRequisicionesDetalle.ProveedorID, tbRequisicionesDetalle.ItemStatusID " & _
        "FROM tbRequisiciones INNER JOIN tbRequisicionesDetalle ON tbRequisiciones.RequisicionID = tbRequisicionesDetalle.RequisicionID " & _
        "WHERE tbRequisiciones.RequisicionID = " & Me.RequisicionID & " AND tbRequisicionesDetalle.ItemStatusID = 6"

'initiate recordset

Set rs = CurrentDb.OpenRecordset(strSQL)

wrkCurrent.BeginTrans

Do While Not rs.EOF

'get the next order number as one more than the last
    lngNextOrderNumber = Nz(DMax("[NumOC]", "[tbOrdenesDeCompra]")) + 1
    
'define date variables
    FcHoy = Date
    FcMañana = Date + 1
    
'insert requisition in the orders table
    CurrentDb.Execute "INSERT INTO tbOrdenesDeCompra (NumOC, ContratoID, ElaboraID, AutorizaID, RecibeID, FcSolicitud, FcEntrega, ItemStatusID, ProveedorID) VALUES (" & lngNextOrderNumber & ", " & Me.ContratoID & ", " & Me.ElaboraID & ", " & Me.AutorizaID & ", " & Me.SolicitaID & ", " & "#" & Format(FcHoy, "yyyy/mm/dd") & "#" & ", " & "#" & Format(FcMañana, "yyyy/mm/dd") & "#" & ", " & 8 & ", " & rs!ProveedorID & ");", dbFailOnError
    lngOrderID = DLookup("[OrdenID]", "[tbOrdenesDeCompra]", "[NumOC] = " & lngNextOrderNumber)
    
'insert the requistion details into the order details
    CurrentDb.Execute "INSERT INTO tbOrdenesDeCompraDetalle ( OrdenID, ReqDetID, PrecioU, Cantidad, RemisionID)" & _
         " SELECT " & lngOrderID & " AS OrdenID, tbRequisicionesDetalle.ReqDetID, tbRequisicionesDetalle.PrecioU, tbRequisicionesDetalle.Cantidad, 0 " & _
        " FROM tbRequisiciones INNER JOIN tbRequisicionesDetalle ON tbRequisiciones.RequisicionID = tbRequisicionesDetalle.RequisicionID" & _
        " WHERE tbRequisiciones.RequisicionID = " & Me.RequisicionID & " AND tbRequisicionesDetalle.ProveedorID = " & rs!ProveedorID & "AND tbRequisicionesDetalle.ItemStatusID = 6;", dbFailOnError
    
'change item status
    CurrentDb.Execute "UPDATE tbRequisicionesDetalle SET ItemStatusID = 7 WHERE RequisicionID = " & Me.RequisicionID & " AND ItemStatusID = 6 AND tbRequisicionesDetalle.ProveedorID = " & rs!ProveedorID & ";", dbFailOnError
    
    rs.MoveNext
Loop

   If MsgBox("Save all changes?", vbQuestion + vbYesNo) = vbYes Then
      wrkCurrent.CommitTrans
   Else
      wrkCurrent.Rollback
   End If

btnColocarOrden_Click_Exit:
    Exit Sub

btnColocarOrden_Click_Err:
    'This error could occur in the unlikely event that two users got the same next order number
    If Err.Number = 3022 Then
        MsgBox "Orden duplicada, intenta nuevamente"
    Else
        MsgBox Error$
    End If
    Resume btnColocarOrden_Click_Exit

End Sub

I'm sorry for abusing of your help, thanks a lot for your assistance, sneuberg.

sneuberg · Jul 17, 2016

Yes sorry about that. I see I didn't have a rs.Close in my code. You should close the recordset and the workspace as they show in the example in this site where they also show setting them to nothing. I've see arguments that this isn't necessary, but I guess it doesn't hurt.

The transactions begin, commit and rollback position look ok but I wouldn't bother the user with the message (unless you want that) and would just commit the transaction. I'd put a rollback in the error handler just before the the line:

Code:

Resume btnColocarOrden_Click_Exit

But failure to close a recordset or workspace will not cause an "Invalid Use of Null". Something else is wrong. I can't see what is so I need you to to put a single quote in front of the line

Code:

On Error GoTo btnColocarOrden_Click_Err

, i.e., make it a comment until we track this down. That will turn off the error handler so that when you run the code the debugger will show you which line in your code is causing the problem. So please do that and let me know what line it is.

sneuberg · Jul 17, 2016

It might save a lot of time if you could upload your database or at least this part of it. Debugging something via back and forth on this forum can take a very long time.

edgaro · Jul 18, 2016

OK, I already checked and it first gave me the error on the Dlookup, I added a NZ before the lookup and it stopped telling me anything about the invalid use of Null. Then, it gave me an error telling me that it can't add an order detail record if it still doesn't have the record from the Dlookup.

I'm attaching the DB. Here's the instructions on how to open it:
1. the user login is "edgar" and the password is "asdasd"
2. the form is located at the "Compras" tab
3. the button is the little shopping cart

Here's the code I used:

Code:

Private Sub btnColocarOrden_Click()

'On Error GoTo btnColocarOrden_Click_Err

'declare variables
Dim wrkCurrent As DAO.Workspace
Dim strSQL As String
Dim strSQL_OC As String
Dim strSQL_OCDet As String
Dim lngNextOrderNumber As Long
Dim lngOrderID As Long
Dim FcHoy As Date
Dim FcMañana As Date
Dim rs As DAO.Recordset

Set wrkCurrent = DBEngine.Workspaces(0)
'initial query to find the distinct providers
strSQL = "SELECT DISTINCT tbRequisicionesDetalle.ProveedorID, tbRequisicionesDetalle.ItemStatusID " & _
        "FROM tbRequisiciones INNER JOIN tbRequisicionesDetalle ON tbRequisiciones.RequisicionID = tbRequisicionesDetalle.RequisicionID " & _
        "WHERE tbRequisiciones.RequisicionID = " & Me.RequisicionID & " AND tbRequisicionesDetalle.ItemStatusID = 6"

'initiate recordset

Set rs = CurrentDb.OpenRecordset(strSQL)

wrkCurrent.BeginTrans

Do While Not rs.EOF

'get the next order number as one more than the last
    lngNextOrderNumber = Nz(DMax("[NumOC]", "[tbOrdenesDeCompra]")) + 1
    
'define date variables
    FcHoy = Date
    FcMañana = Date + 1
    
'insert requisition in the orders table
    CurrentDb.Execute "INSERT INTO tbOrdenesDeCompra (NumOC, ContratoID, ElaboraID, AutorizaID, RecibeID, FcSolicitud, FcEntrega, ItemStatusID, ProveedorID) VALUES (" & lngNextOrderNumber & ", " & Me.ContratoID & ", " & Me.ElaboraID & ", " & Me.AutorizaID & ", " & Me.SolicitaID & ", " & "#" & Format(FcHoy, "yyyy/mm/dd") & "#" & ", " & "#" & Format(FcMañana, "yyyy/mm/dd") & "#" & ", " & 8 & ", " & rs!ProveedorID & ");", dbFailOnError
    lngOrderID = Nz(DLookup("[OrdenID]", "[tbOrdenesDeCompra]", "[NumOC] = " & lngNextOrderNumber))
    
'insert the requistion details into the order details
    CurrentDb.Execute "INSERT INTO tbOrdenesDeCompraDetalle ( OrdenID, ReqDetID, PrecioU, Cantidad, RemisionID)" & _
         " SELECT " & lngOrderID & " AS OrdenID, tbRequisicionesDetalle.ReqDetID, tbRequisicionesDetalle.PrecioU, tbRequisicionesDetalle.Cantidad, 0 " & _
        " FROM tbRequisiciones INNER JOIN tbRequisicionesDetalle ON tbRequisiciones.RequisicionID = tbRequisicionesDetalle.RequisicionID" & _
        " WHERE tbRequisiciones.RequisicionID = " & Me.RequisicionID & " AND tbRequisicionesDetalle.ProveedorID = " & rs!ProveedorID & "AND tbRequisicionesDetalle.ItemStatusID = 6;", dbFailOnError
    
'change item status
    CurrentDb.Execute "UPDATE tbRequisicionesDetalle SET ItemStatusID = 7 WHERE RequisicionID = " & Me.RequisicionID & " AND ItemStatusID = 6 AND tbRequisicionesDetalle.ProveedorID = " & rs!ProveedorID & ";", dbFailOnError
    
    rs.MoveNext
Loop

   If MsgBox("Save all changes?", vbQuestion + vbYesNo) = vbYes Then
      wrkCurrent.CommitTrans
   Else
      wrkCurrent.Rollback
   End If

rs.Close
wrkCurrent.Close

Set rs = Nothing
Set wrkCurrent = Nothing

btnColocarOrden_Click_Exit:
    Exit Sub

btnColocarOrden_Click_Err:
    'This error could occur in the unlikely event that two users got the same next order number
    If Err.Number = 3022 Then
        MsgBox "Orden duplicada, intenta nuevamente"
    Else
        MsgBox Error$
    End If
    Resume btnColocarOrden_Click_Exit

End Sub

If there's a value I should use as default for the NZ function in front of the Dlookup, what would that value be?

sneuberg · Jul 18, 2016

I'm having a time getting the database to work at all. I keep getting

in several places and when I run the program it fails because of missing RequisicionID, ContratoID, etc. Please give me a short explain of how this is to be used.

sneuberg · Jul 18, 2016

Ok I see the problem. The demonstrates that the transaction is working but unfortunately it's working against us. The statement

Code:

lngOrderID = Nz(DLookup("[OrdenID]", "[tbOrdenesDeCompra]", "[NumOC] = " & lngNextOrderNumber))

Is suppose to get the OrdenID from the record that was just saved to the tbOrdenesDeCompra table. We need that to insert the records into the tbOrdenesDeCompraDetalle table as that's the foreign key but since there's a transaction is progress the record is not saved to the tbOrdenesDeCompra table at this point and won't be until the transaction is committed.

As it is this code won't work with transactions. We need to know the OrdenID before the transaction is started. I'll see if there's a way to redesign the code so that this will work, but for now I suggest taking out the tranactions Also the statement

Code:

lngOrderID = Nz(DLookup("[OrdenID]", "[tbOrdenesDeCompra]", "[NumOC] = " & lngNextOrderNumber))

isn't helped by the Nz function as it needs to return the OrdenID and if it doesn't the detail records cannot be added.

Sorry about leading you astray on this. I should have tested this with transactions before I suggested that you put them in.

Insert Requisition Details into multiple Purchase Orders

Registered User.

AWF VIP

AWF VIP

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Attachments

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

AWF VIP

Registered User.

Attachments

AWF VIP

Attachments

AWF VIP

Users who are viewing this thread