Solved Filter subform so total quantity in subform matched the main form quantity (1 Viewer)

[Poco_90]

Hi,
Just wondering if I am going the right way about this and if someone could point me in the right direction? I have a main form and subform. The main form shows the master quantity e.g. 80 and then the subform shows the individual boxes of 8.

Can I get the subform to filter to show only 10 records (totaled to 80) with the oldest create date? Not sure how to handle the onload event of the subform to get the oldest and to just total 80. The total is throwing me.

If this is achievable and lists my 10 oldest records totaling 80. Can I then put an event on the "Scan BoxID" field, where the user scans the BoxID, and if in the subform, the row will highlight and the user will have the option to scan the masterASN and BoxSerial before moving onto a new box. The user may no be scanning the boxes in order.

I have been looking at this for a bit too long and want to make sure I am going in the correct route.

Thanks in advance.

 

[June7]

Is subform linked to main form with Master/Child Links set? Do you really need to filter or just "go to" record and size subform so only 10 records display?

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[Pat Hartman]

The record in the main form should not be storing any quantity. It should be calculated as needed. It should sum the quantity for the active (whatever that means) records.

Can I get the subform to filter to show only 10 records (totaled to 80) with the oldest create date?

The problem is that the subform record doesn't seem to have a field to indicate that it has been "used" or "sold" or whatever would cause you to not want to count it.

In order to do what you are requesting, you would have to assume that the quantity in the box NEVER, EVER deviates so don't plan on making different size boxes. Then you would divide the saved quantity (which shouldn't even be there) by EIGHT and using the answer, create an SQL string using the TOP predicate. The final step is to replace the RecordSource property of the subform with the new SQL string.

It is a bad idea forced by an incorrect design. It would be better to fix the schema and find some way to mark boxes as they are used. You would also have to have an option that lets old data show.

 

[GPGeorge]

The record in the main form should not be storing any quantity. It should be calculated as needed. It should sum the quantity for the active (whatever that means) records.

The problem is that the subform record doesn't seem to have a field to indicate that it has been "used" or "sold" or whatever would cause you to not want to count it.

In order to do what you are requesting, you would have to assume that the quantity in the box NEVER, EVER deviates so don't plan on making different size boxes. Then you would divide the saved quantity (which shouldn't even be there) by EIGHT and using the answer, create an SQL string using the TOP predicate. The final step is to replace the RecordSource property of the subform with the new SQL string.

It is a bad idea forced by an incorrect design. It would be better to fix the schema and find some way to mark boxes as they are used. You would also have to have an option that lets old data show.

Inventory in NW Developers edition is based on "units" with one or more "containers" per unit. The prime example is beer. There are 24 bottles of beer per unit, which is a familiar enough concept to many people.

We also implemented the concept of regular stock takes, which is derived in part from Allen Browne's description a dozen years or more ago.

And, in a series of recent videos, I've demonstrated an approach to using a barcode scanner for the Stock Take function, which includes some incidental discussion of this aspect of the situation.

Video 1
Video 2
Video 3

One of the points made in the second video is that if you have two different unit size packages (24 bottle cases, and 6 packs of beer, for example), you will actually have to create two different product codes, or SKU's, for those two differently packaged unit sizes. The unit size can be encoded in the SKU along with other values to differentiate products.

Getting bogged down trying to count individual packages, or bottles, or whatever, is bound to drive you completely around the bend.

 

[Poco_90]

Thanks for all your replies. I appreciate it.

June7
The master Child links are set with the Part Number. I will attach the DB. There is noting confidential in it, just mock data. Apologies it is rough and ready.

Pat
I had thought of adding this flag you suggested. While there isn’t a field to flag “used” or “Sold” on my subform, I had planned to use MasterASN AND BoxSerial as this flag. If these fields in the subform are empty the box is “used” else if these fields are not empty the box is “Sold”, would this be good enough? I was thinking this way they can be filtered out from future events. I can add this field if you think is best, with a VB event to change the status as "Sold” once my other 2 fields are field or the box is scanned.

The quantity will always be 8. The part number can never be sold in smaller quantities. This is what I have been told, and I have the big stick ready if someone tells me otherwise

George,
Thanks for the videos I am going through them now.

 

[arnelgp]

i created query1 and use it on the Current event of the main form.

 

[Poco_90]

Thanks Arnelgp that is a great help.

 

[Poco_90]

I have 2 more questions I hope some one can help with?

I now have an event on a combobox that takes in a BoxID and highlights MasterASN field on the corresponding row in the subform. (Borrowed arnelgp's code from another post ). I tried to write an if statement around the code below, i.e. if the BoxID entered is not found to trigger a message. Looking at other posts recordsets are mentioned, is there any other way?

Private Sub ComboID_AfterUpdate()
Me.BoxSerial.SetFocus
    DoCmd.FindRecord FindWhat:=Me.ComboID, _
                     Match:=acAnywhere, _
                     MatchCase:=False, _
                     Search:=acSearchAll, _
                     FindFirst:=True, _
                     OnlyCurrentField:=True
End Sub

Finally is there any way to trigger an event when the MasterASN and BoxSerial fields are all full in the subform? Not quite sure what I want to do, maybe a pop up message and close the form

Thanks again.

 

[Pat Hartman]

If these fields in the subform are empty the box is “used” else if these fields are not empty the box is “Sold”, would this be good enough?

Just add the correct criteria to the query and that will limit the rows shown in the subform.

 

[Poco_90]

I am back again! The code above in post 8 works perfectly when there is a match, but when there is not a match is still highlights one of the rows which can lead to a user error. Tried the IF statements but no joy, so went down the Recordset route, which I am stuck on. Am I missing something here, or should I be able to use the recordset function below to find my record and highlight the field? It keeps failing on this line: rs.FindFirst "[MasterASN] = " & Me.ComboID

Is it something sill like mismatched datatype?
Thanks in advance.


P.S. Run-time error '3464' Data type mismatch in criteria expression.

Private Sub ComboID_AfterUpdate()
Me.MasterASN.SetFocus

' Find the record based on ComboID
Dim rs As Recordset
Set rs = Me.RecordsetClone

Debug.Print "Field Name: " & rs.Fields("[MasterASN]").Name
Debug.Print "ComboID Value: " & Me.ComboID

rs.FindFirst "[MasterASN] = " & Me.ComboID

If rs.NoMatch Then
    ' Record was not found, display a message
    MsgBox "Record not found.", vbExclamation, "Search Result"
End If

' Optional: Move the form's recordset to the found record
If Not rs.NoMatch Then
    Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing


End Sub

 

[arnelgp]

maybe your combo has String values:

rs.FindFirst "[MasterASN] = '" & Me.ComboID & "'"

 

[Poco_90]

Thanks arnelgp that was it. Not the first time that has caught me. But I was looking up the wrong field ! I have been looking at this for too long! Don't know my left from my right at this stage Anyway I got sorted, and the code is doing what I need. Thanks again for all your help

Private Sub ComboID_AfterUpdate()
Me.MasterASN.SetFocus

' Find the record based on ComboID
Dim rs As Recordset
Set rs = Me.RecordsetClone

Debug.Print "Field Name: " & rs.Fields("[MasterASN]").Name
Debug.Print "ComboID Value: " & Me.ComboID

rs.FindFirst "[BoxID] = " & Me.ComboID

If rs.NoMatch Then
    ' Record was not found, display a message
    MsgBox "Record not found.", vbExclamation, "Search Result"
    Me.Controls("ComboID").SetFocus
   
Else
    ' Record was found, highlight the fields
    Me.Bookmark = rs.Bookmark
    Me.Controls("MasterASN").SetFocus
End If

Set rs = Nothing


End Sub