asked on

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.

Dale Fye

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
endif

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)

you have to revise your SQL statement to include a WHERE condition

"UPDATE [ScannerUpdateQuery] SET [RecordRead ]=False WHERE <criteria here>"

PatHartman

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;

dailymeat

ASKER

Dale,
I get a message, see attached
Untitled.jpg

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dailymeat

ASKER

Great work!!
Thanks