Update Records

[lzook88]

Hey Guys.. Once again back asking for some help.. Ive got my code to work. its more of a matter of fine tuning it.

I have this code:

Private Sub Command61_Click()
Dim DBSS As Database
Dim rs As DAO.Recordset
Dim varFrom As Variant
Dim StrFrom As String
Dim prod As String
Dim StrCriteria As String
Dim i As Integer
Dim j As Integer

Set DBSS = CurrentDb
Set rs = CurrentDb.OpenRecordset("SELECT ProductName, OldLocation, NewLocation, Used FROM tblTransfers")


For i = 1 To 20
    If Me.Controls("item" & i) & "" = "" Or Me.Controls("n" & i) = 0 Then
        Exit For
    Else
        With rs
            For j = 1 To Me.Controls("n" & i).Value
                StrCriteria = "[ProductName] = " & Me.Controls("item" & i) & " And [NewLocation] = " & Me.txtFrom.Column(0) & " And [Used] = " & 0
                Debug.Print StrCriteria
                .FindFirst StrCriteria
                .Edit
                !Used = -1
                .Update
            Next j
        End With
    End If
Next i

How ever.. I want it to look at a query and see if there are already that man marked used. If there are I want it to exit the for and move on to the next one. If there isnt I want it to do what is says to do. Edit Used to Yes.

the query used to get the number of that item marked used is called Used Stock and its lay out is as such:

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.Used)=True) AND (([type]=[type_fk])=-1))
ORDER BY tblLocations.PLocation;

Thanks Again

Zook

[June7]

What does 'that man marked used' mean - did you mean 'that many marked used'?

The query Used Stock is a saved query object? You want to count how many records are in that query? Could use a DCount() function.

[lzook88]

I don't think I explained well enough.

Used is a Yes/No field.

The idea is that for each item in physical possession is its own record. When that item is used it would be marked as USED.

The goal here is that when inventory is done, if what they currently have is less than what they used to have. It would look to see how many are marked as used already and then if there are more to be marked as used. it would mark them them as used.

Not sure if that makes any sense to you?

Thanks again
Zook

[June7]

Conventional approach to stock control db is to enter transactions of items received and items used/sold. Then run queries that calculate the difference of their sums to determine balance on hand.

[lzook88]

Yes that is understandable. However this inventory control is monitoring multiple locations that hold the same product and in different quantities. I have talked to a friend who does DB programming for a living and he suggested the way it is done, is the best solution at hand for what I have.

:-)

So now let me attach this database for you and see if you can see what I mean. the query I want to use for the look up is Current User Stock and to look at the Item Name and the HAVE columns to see if that person currently has any of that product, if so how many. then if Have > what they put in the text box it will go in to the tbl called transfers and find the item for that user that is not used and mark it used.

[orange]

What other advice did your friend have on your current database (which isn't attached by the way)?

I have talked to a friend who does DB programming for a living and he suggested the way it is done, is the best solution at hand for what I have.

[lzook88]

Haha sorry about that.. busy at work and didn't realize I forgot to attach it.....

He advised doing the individual record for each item would be best for the type of inventory i am working with and it would enable a better count and ability to track each individual piece easier for the multiple locations.

As well this time, I did attach the data base..

Attachment 22088

[orange]

What is suppose to happen with Inventory form???
How does it get populated?

[lzook88]

The user would put in any thing that is depleted.. its based on their logged in user name. (go to locations and change Louis Zook to your computers user name) it will populate the data to the form.

with the inventory form.... if they pick an item and change the HAVE to less than what they currently show having it will go to the table transfer and mark more items used to match the number they HAVE.

[lzook88]

EDIT:::::: change it from EI***B to your computers user name.. sorry..

[June7]

I don't see how multiple locations makes the transactions approach any less practical.

[lzook88]

@June7

I am not sure.

But I was having major issues setting the database up using the transactions and monitoring what came and went where. With the way I am doing it, i got the forms and reports to work quite well.. Just having issue with this last form. The inventory form. I have the form done.. its the submit button giving me the issue..

[June7]

So you want to pull from query a value from specific record or do a count of records?

Exactly which query (name please) and what do you want to pull from it?

A domain aggregate function in the VBA might serve (DLookup, DCount).

[lzook88]

I want to pull the value of " CountOfTrackingID " from the query called " Used Stock " for the specific location and product mentioned on the inventory form. Then compare that value to the " NEED " text box on the inventory form. If the CountOfTrackingID is less than the NEED text box. I want it to go to tblTransfers and find that product for that location (NEW LOCATION) and mark it as USED as many times as the difference of the two are and if the NEED is less than or equal to the CountOfTrackingID do nothing.

I have attached the most recent copy of the database to this post. I have finished the design on the inventory sheet so the ComboBox Unit/Location will display the items assigned to each location.

EMT Master.zip

[June7]

The Inventory form has 20 sets of UNBOUND controls. User will input data to each of these sets and then code loops through the controls and this is where you want to do the lookup and update? Something like:

intCountID = Nz(DLookup("CountOfTrackingID", "Used Stock", "PLocation=" & Me.txtTo & " AND ID=" & Me.Controls("item" & i)),0)
If Me.Controls("n" & i) > intCountID Then
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblTransfers WHERE NewLocation=" & Me.txtTo & " AND ProductName=" & Me.Controls("item" & i))
While Not rs.EOF
For x = 1 to Me.Controls("n" & i) - intCountID
rs.Edit
rs!Used = True
rs.Update
rs.MoveNext
Next
Wend
rs.Close
End If

What I don't understand is purpose of the Used field in tblTransfers. Should this field be set to False for all records before this procedure is run? Is this a multi-user database and could multiple users be updating this table at the same time? If so, this will not work.