simultaneously update a field on multiple records

[rbeach]

I have a query that selects records in a database. These are listed on a
subform. Is there any way to update one of the fields in all of these records
simultaneously? I have a column named "Billed" to be updated. This is a
"Yes/No" Field and I would like to update this field to yes on all records.
This would need to be a button.

 

[John W. Vinson]

I have a query that selects records in a database. These are listed on a
subform. Is there any way to update one of the fields in all of these records
simultaneously? I have a column named "Billed" to be updated. This is a
"Yes/No" Field and I would like to update this field to yes on all records.
This would need to be a button.

The records aren't "in" the subform - they're in a table, and the subform is
just a window displaying them.

You should consider using an Update Query to update the table; you would apply
the same criteria on the Update Query as are used to select the records for
the subform (the subform's Master/Child Link Field presumably). This query can
reference some form control as a criterion, and can be executed from a command
button.

On the other hand, if it's possible to determine from the values of other
fields in the table whether or not an item has been billed, then you should
use those fields and not store a redundant yes/no field at all.

 

[John W. Vinson]

Hi Rick,

Make a copy of your query and name it QryUpdateBilledField. (Keep the same
criteria) Open the new query in design view. Click on the Query Types button
at the top of the screen and change your query to an update query. In the
"Update To" row of your Yes/No field type in Yes. Put a button on your form
and put the following code in the click event:

DoCmd.SetWarnings False
DoCmd.OpenQuery "QryUpdateBilledField"
DoCmd.SetWarnings True

Steve

You may want to add an additional criterion - use the Master Link Field
control on the mainform as a criterion on the Child Link Field in the
subform's query. Otherwise it may update every record in the table, rather
than just those few displayed on the subform.

Also consider using the query's Execute method (see the online help), which
does not require the SetWarnings, runs faster, and allows you to trap errors:

On Error GoTo Proc_Err
CurrentDb.Querydefs("qryUpdateBilledField").Execute dbFailOnError
Proc_Exit:
Exit Sub
Proc_Err:
<do appropriate error handling here>

 

[rbeach]

I appreciate all the help. This is just what is needed.

Thanks,