Full Version: Mark Filtered Records On Datasheet
UtterAccess Forums > Microsoft® Access > Access Forms
lizba
Hi Guys
Owonder if someone can give me some advice.
I have a form (datasheet) which is a list of stock. This list can sometimes be 400 records long and has 25 columns. The user then filters the list based on the data in one or more of these columns (Variety, size, etc). He filters it until he has a shortlist (normally 20 - 25 stock items). This are the items that he chooses to go on a specific shipment. What I would like to do is to find way to tag these items with the proposed shipment code. So basically what I would like to do is update a certain control (lets say "shipmentCode") for only the visible records. The idea is that after he has inserted this code in the visible records, he can then unfilter all the columns, then filter out the records that have the code inserted and continue till he has his next shipment. Any ideas on how to achieve this?
Thanks
Lizba
doctor9
Lizba,
I'm making some guesses here, but this should get you moving forward.
First, I'm assuming your datasheet form is a subform on another form, which allows the user to select a the "shipment code" that will be assigned to the current set of filtered records. On that same form, add a command button with VBA along these lines in the On Click event:
CODE
Private Sub cmdAssignShipmentCode_Click()
    Dim rst As DAO.Recordset
    
    Set rst = Me.frmNameOfShipmentsSubform.Form.RecordsetClone
    rst.MoveFirst
    If rst.RecordCount > 0 Then
        While Not rst.EOF
            rst.Edit
            rst!shipmentCode = Me.cmbSelectedShipmentCode
            rst.Update
            rst.MoveNext
        Wend
        Me.frmNameOfShipmentsSubform.Requery
    End If
    rst.Close
    Set rst = Nothing
End Sub

This code will clone the recordset of the datasheet subform (named "frmNameOfShipmentsSubform" in my example). Then, the code loops through each record in the cloned recordset, setting the value of the field "shipmentCode" to the value of the main form's control named "cmbSelectedShipmentCode". Then, the subform is requeried, so the user can see the shipment code in the appropriate column.
Hope this helps,
Dennis
lizba
Thanks Denns, that is exactly what I was looking for. I never really understood what a recordset clone was but now that I see it working, I understand much better.
lizba
Hi Dennis
Thanks for your help yesterday. I got it working but for some reason it is not very reliable. I adapted the code you gave to work where the code not attached to the form in question. In orther words, when the user has filtered the records to his liking he then clicks on a command button which opens up a popup form. Here he creates the shipment (chooses the client and allocates a code) then he clicks on a command button which will then insert the code into the original form.
But now, I seem to encounter two intermittant errors:
1. Every now and then it causes a debug on the "rst!Allocated = Me.ShpMntID" and says - Item not found in this collection.
2. The other wierd thing it did today was that it changed some data display :
The user filtered down to 88 records based on size and variety. These 88 records included 5 records of brand A and 83 records of brand B. The code worked properly and entered the right info into the ShipCode and Allocated control of all 88 records. But when you scrolled through the records, it changed the brand name of the 5 Brand A products to Brand B. When I checked the source table the actual data is still correct ( 5 where brandID = 3 and 83 where brandID = 7) but when you look at the StockSelling form they are all Brand B - I have no idea how to sort ths out. The fact that the source data is still correct means that it is not too much of a problem but the user is having a hernia!!
Any suggestions would be most welcome.
Thanks
Lizba
This is the finished code:
Dim rst As DAO.Recordset

Set rst = Forms.StockSelling.RecordsetClone
rst.MoveFirst
If rst.RecordCount > 0 Then
While Not rst.EOF
rst.Edit
rst!ShipCode = Me.ShipCode
rst!Allocated = Me.ShpMntID
rst.Update
rst.MoveNext
Wend
Me.Requery
End If
rst.Close
Set rst = Nothing
doctor9
Lizba,
Regarding your first problem, I would go into Debug mode, and hover your cursor over both "rst!Allocated" and "Me.ShpMntID" on the line of code that's highlighted, to see if either of them show an error message for the contents. That should give you an idea of what's going wrong.
By the way, you should really avoid naming the unbound control "ShipCode", since you're already using that for a field name. This is particularly important because the combobox isn't bound to that field - or at least, I don't think it should be. That's why I suggested "cmbSelectedShipmentCode" in my original reply - "cmb" lets you know it's a combobox, and "SelectedShipmentCode" describes exactly what the combo box is for.
HAs to the second problem, the fact that you added a second field to be updated to my originally posted code makes me wonder if THAT's what is causing the problem. Unfortunately, it's hard to know without knowing more information about your database. You didn't mention anything beyond updating the shipment code in your original post, so this is kind of out of the blue right now.
What is the "rst!Allocated = Me.ShpMntID" line supposed to be accomplishing? What does the field "Allocated" represent? What does the control "ShpMntID" represent?
Dennis
lizba
Dennis
I have attached the relevant parts of the database.
The info form explains everything.
thanks for your time
Liz
doctor9
Lizba,
kay, I think I figured it out. Here's what I did:
1. Change the form's recordsource so it's just the table, filtered to the non-null values, instead of a query with a bunch of linked tables.
2. Changed the design view so the linked fields were instead comboboxes that use a field from the main table as their controlsource, instead of the linked value.
3. Changed the "Me." reference to a reference to the form (you didn't mention that you were running this code from a second form, which is why "Me." doesn't refer to the correct form).
The code SEEMS to work. The popup form still needs tweaking - should it really be bound to anything?
But the button seems to update the records properly now. See what you think.
Hope this helps,
Dennis
lizba
thanks Dennis for your input.
I'll put it on my database and let u know.
Regards
Lizba
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.