Bulk Edit A Sinlge Field of Queried Records with Checkboxes

[chemguy120]

I’ve been searching online but can’t seem to find something I can use. I’m pretty novice but I can usually break simple examples down to piece together something. What I am trying to do is as follows:
I have two tables and a query with the following relevant fields:

tbl_SHIP: ShipID, TRACKING
tbl_RETURN: ReturnID, SERIAL, ShipFK
qry_EDIT: ShipID, TRACKING, SERIAL, ShipFK

The query is set to filter out all TRACKING records with a valid tracking number, leaving only the ones with a placeholder dummy number entered.

The purpose of this form would be for bulk editing of the ShipFK field in the RETURN records.

What I need is a form with a text control I can scan a serial number into and it will toggle a checkbox to “ON” in the RETURN records. I need to do this multiple times in a session. There also needs to be another control (ideally a combo box) to select a TRACKING record from the query results. Finally, a button should initiate an overwrite of the ShipFK in the RETURN records with the selected ShipID in the combo box.

My thought is to have a main form with the text control and the combo box with a sub-form in datasheet view with the queried records. The workflow would be as follows:

1. Select the desired TRACKING record from the combo box.
2. Scan the SERIAL data into the text control (from a printed barcode sheet I already have designed. If you need to know, the data is alphanumeric.).
   
   1. After each individual scan, the record in subform should be checked. After the record is checked the text control should be cleared in preparation for the next scan. An error should be returned if a record for the scanned serial number does not exist.
   
   
3. Repeat step 2 as many times as needed.
4. After step 3 is finished there should be a button that will overwrite the existing ShipFK in the checked RETURN records with the ShipID selected in the combo box.

The checkbox data is not required beyond the purpose of this specific workflow and the checkbox status should not be stored if possible.

It all seems so simple in my head, but I am starting to think I’m pushing the limits of both myself and Access! Thanks for any and all help!

[June7]

Normally, a record cannot be 'checked' unless there is a field to store this.

Record search and edit could be performed with each scan, don't need to wait until a bunch are processed.

Could provide db for analysis. Follow instructions at bottom of my post.

[chemguy120]

Thanks for your reply!

The db is a bit more complicated than the simple structure I outlined above so uploading it may actually make matters more confused. For this specific form I'm only going to be using the simplified table structure I provided above. The purpose of the utility is to bulk edit return records that were accidentally assigned to the wrong shipment record. Including a checkbox in the RETURN table itself would be acceptable if there is a way of just not saving that data once I've run the utility, or at least reset every record that was "checked" back to "unchecked" when I exit the form. Holding the update until the end rather than editing with each scan gives for a chance to double check what has been flagged before committing to the data edit.

[June7]

Can run an UPDATE action SQL to set yes/no field for all records to False. However, use of this record flag field is only practical if db has only one user at a time running this process. Simultaneous users will conflict.

Is your db a split design? Alternative could involve writing record ID to a temp table which is located in frontend.

[chemguy120]

Yes, the db is split, however there is only going to be one user at a time anyway. It's mainly split because I'm not always working at the same terminal all the time so it was more convenient to split it. I think I've got a good grasp on how the workflow should be structured, I'm just unsure of the underlying VBA that would be required to for things to flow correctly. Most of the similar examples I'm finding online are confusing me more than helping at the moment!

[June7]

Possibly use textbox AfterUpdate event to run validation and field edit code. Does your scanner allow for setting it to send a carriage return? If not, have to Tab or Enter from textbox to trigger AfterUpdate. Alternative, click a command button to run code.

The actual code can vary in methodology.

[chemguy120]

Yes, I typically use a TAB after a scan but can easily switch it to CR when needed

[June7]

TAB or CR, either should work, whatever scanner offers.

This code can be quite simple.

In the textbox AfterUpdate, like:

Dim db As DAO.Database
Set db = CurrentDb
db.Execute "UPDATE tablename SET fieldname=True WHERE SERIAL='" & Me.tbxSer & "'"
If db.RecordsAffected = 0 Then
    MsgBox "Record does not exist."
End If
Me.tbxSer = Null

I suppose subform RowSource would be designed to only display these flagged records. Add a line to the above to Requery subform.
Me.subformname.Requery

And then in some other event, maybe form Open or Close:

CurrentDb.Execute "UPDATE tablename SET fieldname=False"

I am not understanding to what and when ShipFK should be updated.

[chemguy120]

So far so good. I added a checkbox field to the RETURN table. I had to remove the first line of the AfterUpdate code. The "Dim db As DAO.Database" line kept giving a "User-defined type not defined" error. Once I removed that first line the error went away. The AfterUpdate event for the unbound textbox is properly toggling the checkboxes and the OnClose event for the form properly toggles everything to false.

I just realized I never mentioned the table relationship. It is a one shipment to many returns linked via ShipID -> ShipFK.

For editing the ShipFK field I'm hoping for this. I have an unbound combo box on the main form where I can select the TRACKING value. I need the ShipID from the record selected in the combo box to replace the ShipFK in the return records with the checkbox toggled to true. Ideally I'm hoping a button's OnClick event can accomplish this.

I hope that made sense!

[June7]

Sounds like you do not have Option Explicit at the top of code module. If you did, the compiler would bug on the db variable not being declared with a Dim line. That Dim statement should definitely work - it does for me. Review https://www.accessforums.net/showthread.php?t=69816

Try:
Dim db As Database
or
Dim db As Object

I had presumed that was the table relationship.

To update ShipFK after records are reviewed, run another UPDATE action SQL.

CurrentDb.Execute "UPDATE tbl_RETURN SET ShipFK=" & Me.cbxShip & " WHERE checkfield=True"

If you want to continue process with another TRACKING selection, then follow that with the SQL to reset checkfield.

Combobox should have settings like:
RowSource: SELECT ShipID, TRACKING FROM tbl_Ship; (or whatever query you need to provide required list but still include these two fields)
ColumnCount: 2
ColumnWidths: 0";1"
BoundColumn: 1


Consider not using all upper case in naming convention as it is harder to read.

[chemguy120]

Thanks for the info, I'll keep that in mind moving forward. I'm planning on refreshing a lot of my tools I created in this db over the summer (the one I've using now is very experimental) so this will come in handy when I sit down to incorporate everything I've learned so far.

I attempted to put the code into the OnClick event for a button but I got an "Expected: end of statement" I updated it to this and it worked fine (for anyone else reading this looking for similar solutions):

CurrentDb.Execute "UPDATE tbl_RETURN SET ShipFK=" & Me.cbxShip & " WHERE checkfield=True"

An afterthought, how would I adjust the AfterUpdate code on the textbox so when a serial is scanned a second time it toggles the check false? Basically, the scan should just toggle the check to the opposite of it's current state. I'm assuming some sort of IF/THEN statements?

For refreshing the form for a new round after an update (in general for my any of my editing forms) a button that closes and reopens the form works well for me. Provide I have all my OnClose events set up properly!

Thanks for everything so far!

[June7]

Sorry, left out & character. Edited post.

To reverse current value of yes/no field:

db.Execute "UPDATE tablename SET fieldname = NOT fieldname WHERE SERIAL='" & Me.tbxSer & "'"

[chemguy120]

I'm not sure how to properly write that out so the textbox toggles the checkbox on or off depending on it's current state. I'm seeing some stuff on IIF statements but I'm a bit confused on properly writing it out

[Micron]

Did you try it? Basically it's saying SET something to the opposite of whatever it is. So if it's True, it's set to NOT True (False). If it's False, it's set to NOT False (True). Thus the value ends up being the opposite of whatever it currently is. Obviously this can only work when there are 2 possibilities.

[chemguy120]

Ahhhh! I'm being pulled in a few different directions today and was overthinking it. Yes, REPLACING the original code with that works perfectly! With a bit of fine tuning I got the form working exactly as I need it to and it's given me an idea for another tool I'll make later that will make my work a lot easier. Thanks for all the help!