Update Records

[lzook88]

The Inventory form has this



1 unbound combo box - select location to inventory
20 unbound combo boxes (labeled Item1 to Item 20) - select item from location selected in top combobox
20 unbound text boxes (labeled Quan1 to Quan20) - auto fills with current amount in stock when item from above is selected. (USER Can Change this)
20 unbound text boxes (labeled Req1 to Req20) - auto fills with the required amount for the item selected (User CANT Change this)
20 unbound text boxes (labels n1 to n20) - auto fills with the value of Req - Quan

Cancel & Submit Buttons

The goal of this form is for the user (1 user per location) to put in the stock that is depleted / used so it can be reOrdered.

They would open the form (They will select their Location/Unit - We will used EIAHU4B).
Then, The would select an item (we will say they used 5x Gauze pad - 4" x 4" - Non Sterile). They would select Gauze pad - 4" x 4" - Non Sterile
They would then change Have from 15 to 10.
The need column would auto populate they now need 5

They would hit submit.

when they hit submit, this is what I expect to happen..

It would open up USED STOCK query and look for Gauze pad - 4" x 4" - Non Sterile for location EIAHU4B and see how many are currently showing as USED (CountOfTrackingID).

If NEED (n1 to n20) > CountOfTrackingID then
NEED - CountOfTrackingID = X
Then open tbltransfers and find (Gauze pad - 4" x 4" - Non Sterile) for NEWLOCATION EIAHU4B that are NOT MARKED AS USED
Then mark X number of transactions USED = True.
Then move on to the next line item and do it again if need be otherwise exit and close the form.

Does this make a little more sense?

There will only be one user able to update one location. No two users will have access to the same location.

[orange]

You should research ReOrder Point and ReOrder Quantity - the concepts could be helpful in your automation.

Does the current database handle your test data and test scenarios? You are the only one who can determine that.

If you have multiple users, you should split your database into FE/BE.

[June7]

I offered code to do that in post 15.

[lzook88]

Here is how far I have gotten:

Private Sub Command61_Click()
Dim DBSS As Database
Dim rs As DAO.Recordset
Dim rsrs As DAO.Recordset
Dim i As Integer
Dim j As Integer

Set DBSS = CurrentDb


If Me.txtTo & "" = "" Then
    MsgBox "You Must Select a Location to Inventory", vbOKOnly + vbCritical, "Pick Location"
Else
    For i = 1 To 20
        If Me.Controls("item" & i) & "" = "" Or Me.Controls("n" & i) = 0 Then
            Exit For
        Else
            Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT Count(tblTransfers.TrackingID) AS CountOfTrackingID, tblTransfers.ProductName, tblProducts.PType, tblLocations.PLocation, tblTransfers.Used, tblProducts.ID " & _
                "FROM (tblProducts INNER JOIN tblRequiredStock ON tblProducts.ID = tblRequiredStock.Product_FK) INNER JOIN (tblLocations INNER JOIN tblTransfers ON tblLocations.LocationID = tblTransfers.NewLocation) ON tblProducts.ID = tblTransfers.ProductName " & _
                "GROUP BY tblTransfers.ProductName, tblProducts.PType, tblLocations.PLocation, tblTransfers.Used, tblProducts.ID, [type]=[type_fk] " & _
                "HAVING (((tblTransfers.ProductName) = " & Me.Controls("item" & i).Column(0) & " ) And ((tblTransfers.Used) = True) And (([Type] = [type_fk]) = -1)) " & _
                "ORDER BY tblLocations.PLocation;")
            If rs.EOF Then
                Set rsrs = CurrentDb.OpenRecordset("SELECT tblTransfers.TrackingID, tblTransfers.NewLocation, tblTransfers.ProductName, tblTransfers.Used FROM tblTransfers GROUP BY tblTransfers.TrackingID, tblTransfers.NewLocation, tblTransfers.ProductName, tblTransfers.Used HAVING (((tblTransfers.NewLocation)=" & Me.txtTo.Column(0) & " ) AND ((tblTransfers.ProductName)=" & Me.Controls("item" & i) & " ) AND ((tblTransfers.Used)=False));")
                For j = 1 To Me.Controls("n" & i).Value
                    With rsrs
                        .Edit
                        !Used = True
                        .Update
                    End With
                Next j
                rsrs.Close
                Set rsrs = Nothing
            ElseIf Me.Controls("n" & i) > rs![CountOfTrackingID] Then
                Set rsrs = CurrentDb.OpenRecordset("SELECT tblTransfers.TrackingID, tblTransfers.NewLocation, tblTransfers.ProductName, tblTransfers.Used FROM tblTransfers GROUP BY tblTransfers.TrackingID, tblTransfers.NewLocation, tblTransfers.ProductName, tblTransfers.Used HAVING (((tblTransfers.NewLocation)=" & Me.txtTo.Column(0) & " ) AND ((tblTransfers.ProductName)=" & Me.Controls("item" & i) & " ) AND ((tblTransfers.Used)=False));")
                MsgBox "test worked"
            End If
            rs.Close
            Set rs = Nothing
        End If
    Next i
End If

End Sub

But when i go to use it.. I get:
"Run-Time Error '3027': Cannot Update. Database or Object is read-only"

On debug it highlights .Edit

Thanks for the help

[June7]

Can't edit an aggregate GROUP BY query. Why are you grouping records?

[orange]

Please see this by Allen Browne for additional info re Query is Read Only

[lzook88]

Solved it.. Thanks June07 It was the grouping and Have code.. final code is

Private Sub Command61_Click()
Dim DBSS As Database
Dim rs As DAO.Recordset
Dim rsrs As DAO.Recordset
Dim i As Integer
Dim j As Integer
Dim x As Integer

Set DBSS = CurrentDb


If Me.txtTo & "" = "" Then
    MsgBox "You Must Select a Location to Inventory", vbOKOnly + vbCritical, "Pick Location"
Else
    For i = 1 To 20
        If Me.Controls("item" & i) & "" = "" Or Me.Controls("n" & i) = 0 Then
            Exit For
        Else
            Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT Count(tblTransfers.TrackingID) AS CountOfTrackingID, tblTransfers.ProductName, tblProducts.PType, tblLocations.PLocation, tblTransfers.Used, tblProducts.ID " & _
                "FROM (tblProducts INNER JOIN tblRequiredStock ON tblProducts.ID = tblRequiredStock.Product_FK) INNER JOIN (tblLocations INNER JOIN tblTransfers ON tblLocations.LocationID = tblTransfers.NewLocation) ON tblProducts.ID = tblTransfers.ProductName " & _
                "GROUP BY tblTransfers.ProductName, tblProducts.PType, tblLocations.PLocation, tblTransfers.Used, tblProducts.ID, [type]=[type_fk] " & _
                "HAVING (((tblTransfers.ProductName) = " & Me.Controls("item" & i).Column(0) & " ) And ((tblTransfers.Used) = True) And (([Type] = [type_fk]) = -1)) " & _
                "ORDER BY tblLocations.PLocation;")
            If rs.EOF Then
                Set rsrs = CurrentDb.OpenRecordset("SELECT tblTransfers.TrackingID, tblTransfers.NewLocation, tblTransfers.ProductName, tblTransfers.Used FROM tblTransfers WHERE (((tblTransfers.NewLocation)=" & Me.txtTo.Column(0) & " ) AND ((tblTransfers.ProductName)=" & Me.Controls("item" & i) & " ) AND ((tblTransfers.Used)=False));")
                For j = 1 To Me.Controls("n" & i).Value
                    With rsrs
                        .Edit
                        !Used = True
                        .Update
                    End With
                Next j
                rsrs.Close
                Set rsrs = Nothing
            ElseIf Me.Controls("n" & i) > rs![CountOfTrackingID] Then
                Set rsrs = CurrentDb.OpenRecordset("SELECT tblTransfers.TrackingID, tblTransfers.NewLocation, tblTransfers.ProductName, tblTransfers.Used FROM tblTransfers WHERE (((tblTransfers.NewLocation)=" & Me.txtTo.Column(0) & " ) AND ((tblTransfers.ProductName)=" & Me.Controls("item" & i) & " ) AND ((tblTransfers.Used)=False));")
                For j = 1 To (Me.Controls("n" & i) - rs![CountOfTrackingID])
                    With rsrs
                        .Edit
                        !Used = True
                        .Update
                    End With
                Next j
                rsrs.Close
                Set rsrs = Nothing
            End If
            rs.Close
            Set rs = Nothing
        End If
    Next i
End If

'---------- Thanks for your help

DoCmd.Close

End Sub