Button to uncheck " Checkbox "

Question

Hello

I'm New in VBA and I have a question, I hope someone can help me

I have one Table, one Query, and one Form

Table “PMACustomer” contains all records for all Branches, (separate by Branch No. are like 01, 02, 03….)

By Making Query “PMACustomerQry “able to ask by Branch No. to show just that Branch records.

Form “PMACustomerQryFrm” is an end-user data Form entry.

If you open “PMACustomerQryFrm” form

1- 1st ask you Branch No. (Please Type 01)

2- It has a Red Button on this form (Upper Right)

3- I want when I press on this Button “Uncheck “all Checkbox name “Invoiced”

4- But I want just those checkbox showing in this form with that Branch No. get uncheck not others 

5- If Branch No. change to others still have checkbox clicked (not change other branch checkboxes)

If you open VBA on this Button I did some code but I cannot find the argument/code right for it too.

My Table contains:

PMA_ID         AutoNumber

PMAName      Short Text

PMABranch    Short Text

Invoiced          Yex/No

My Query

SELECT PMACustomer.PMA_ID,PMACustomer.PMAName, PMACustomer.PMABranch, PMACustomer.Invoiced

FROM PMACustomer

WHERE (((PMACustomer.PMABranch) Like [Please Type Your Branch No like 01, 02, 03]));

My Form: 

it has Just all field and Default View Continuous Forms

but I add a Botton in this form for unchecking all " Invoiced " Checkbox for each record.

my problem start when I used this button all record includes records belong to other Branch get uncheck, 

what I want each branch able to uncheck just own checkbox

Private Sub Command17_Click()

CurrentDb.Execute " update PMACustomer Set Invoiced = false"

where ?????????????

Me.Requery 

End Sub 

I know my code has missing where or when an argument

Could someone help me on this code

thank you all

Answer 1

OK, I would do it a bit differently. 

First, I would add a table for Branch codes. I would then add a combobox on your form to select the branch. Form there I would adjust your code as follows:

Private Sub Command17_Click()

Dim strSQL As String

strSQL = "UPDATE PMACustomer SET Invoiced = False " & _

               "WHERE PMABranch = '" & Me.cboBranch & "';"

CurrentDb.Execute strSQL, dbFailOnError

Me.Requery 

End Sub

Answer 2

OK, I would do it a bit differently. 

First, I would add a table for Branch codes. I would then add a combobox on your form to select the branch. Form there I would adjust your code as follows:

Private Sub Command17_Click()

Dim strSQL As String

strSQL = "UPDATE PMACustomer SET Invoiced = False " & _

               "WHERE PMABranch = '" & Me.cboBranch & "';"

CurrentDb.Execute strSQL, dbFailOnError

Me.Requery 

End Sub 

thank you Scottgem

it's working but when I edit mode working fine but when I run it as end-users give me the error,

do I need add something on my reference

thanks

Answer 3

this is the error

Answer 4

Hi Scottgem

I think I find why the system gives me an error

At home, I work with Access 2016, but my end-user using access 2013

is possible?

thanks

Answer 5

You must always develop in the earliest version that your users will use. Yes, your developing in 2016 can cause problems for a user that is using 2013.

Your options are to upgrade the user to 2016 or install the runtime version for 2016 on their machine (being careful to install in a different folder from the Office 2013 install).