Updating Yes/No Field

Have a table named "DownloadFromScanner", need based on some filters (s or r) and (VendorID) to set Yes/No field either to Yes or No.

I made a form "ScannerUpdate) and subform "ScannerUpdateSubform" this subform is based in a query name ScannerUpdate Query".

In the form I have 2 buttons one is named "Yes"and the code is CurrentProject.Connection.Execute "UPDATE [ScannerUpdateQuery] SET [RecordRead]=True".
The other button is named "No" and the code is CurrentProject.Connection.Execute "UPDATE [ScannerUpdateQuery] SET [RecordRead ]=False".

Once I open the Form and subform and applied the filters with the records I want to update I either press yes or no buttons.

Problem is that all the records in the query are updated regardless whether they are in the filter selection or not.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
That is because you are not setting any conditions in your update syntax.

Try this:

in the Yes or No button click events:
dim strSQL as string

strSQL = "UPDATE [ScannerUpdateQuery] SET [RecordRead]=-1 "
if me.subFormControlName.form.FilterOn AND me.subformControName.form.filter <> "" Then
    strSQL = strSQL & " WHERE " & me.subformControlName.Form.Filter

CurrentProject.Connection.Execute strsql

Open in new window

That should pull whatever filter you have established on the subform into your update query.  You will need to change "subformControlName" in the code above to the actual name of the subform control (not the source object).
Rey Obrero (Capricorn1)Commented:
you have to revise your SQL statement to include a WHERE condition

"UPDATE [ScannerUpdateQuery] SET [RecordRead ]=False  WHERE <criteria here>"
The query doesn't use the form filter.  You need to include criteria in the update query.  You can build the SQL on the fly with VBA or you can use querydefs with variable arguments (which is my method).

You will need to add the filter fields to the form's header either as text boxes or combos.  The update query will reference those filter fields.

The querydef:
UPDATE [ScannerUpdateQuery] SET [RecordRead]= Forms!yourform!ReadOption
Where fldA = Forms!yourform!searchfldA AND fldB = Forms!yourform!searchfldB;
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

dailymeatAuthor Commented:
I get a message, see attached
Dale FyeOwner, Developing Solutions LLCCommented:
then RecordRead is not the name of the subform "control".

Open the main form in design view
Click on the subform control.
Open the properties window and look at the Name property.  This is the name of the subform control.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dailymeatAuthor Commented:
Great work!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.