CODEWITHSUNDEEP
ms-accessvbams-access-forms
ESmith
ESmith

Reputation: 3

MS Access Filter and make multiple checkboxs true / false based on buttons

I have a simple inventory database with a simple search text option that filters it down to those particular items. I would like to "check out / in" multiple items based on what is currently filtered.

So if I looked for "float", in the split form it will only show all the info for anything that had "float" in its description. From there I would like to have a button ("Check Out") that would check all of the results of this textbox filter true, rather than clicking down each item. I've attached a photo of what my form looks like.Access Example Photo

Upvotes: 0

Views: 448

Answers (2)

ESmith
ESmith

Reputation: 3

JShort set me onto the answer. This is how I did this incase there's another newbie out there.

As suggested I created two update queries (qry_CheckIn / qry_CheckOut).
In my field "Packed" (the checkbox) I set the Update To: 0 (Remove the checkmark) / -1 (Add the checkmark).

In my field "Item" I put:

    Like "*" & [Forms]![frm_inventory]![txtFilter] & "*"

Which looks in my forms at my text filter search box (see image in question section).

Next part: (If you have a newer version of access turn on "Show All Actions") I added the macro commands "on click" for each of my buttons in this order:

  1. SetWarnings

    Warnings On - No

  2. OpenQuery

    Query Name - qry_CheckIn (or Out)

  3. RunMenuCommand

    Command - Refresh

  4. SetWarnings

    Warning On - Yes

Thanks for the assist JShort.

Upvotes: 0

jshort
jshort

Reputation: 361

Add some code to those buttons that execute some SQL to update the status of the record behind your checkbox. Here's a sample with a print statement to show what the query string becomes before it is executed:

Private Sub CheckInButton_Click()

DoCmd.SetWarnings False

sqlString = "UPDATE tbl_inventory SET tbl_inventory.Packed = False WHERE tbl_inventory.Item like '" & Me.txtFilter & "'" 

debug.print sqlString

DoCmd.RunSQL sqlString 

DoCmd.SetWarnings True

Me.SubForm.Requery

End Sub

Upvotes: 1

Related Questions