Create Update query in Access 2003

[kbsudhir]

Hi All,

I have to create a query to update a field for the records which match the criteria entered by the User.

User will enter the criteria in the form.
Once the data for the criteris is pulled. Then the user will change the field & update it.

Please guide on how to go about doing this.

:dunno :dunno :dunno :dunno

Regards
Sudhir

[OBP]

What you are describing "Then the user will change the field & update it" is not actually an Update Query.
You could have on your Form an Unbound field to allow the User to enter what they want the field to be updated to and then use that in your Update Query to update the Records.

[kbsudhir]

That means I should:

1. Create a query which will take parameter from the form control (I already have this query just have to tweak a little bit) so that desired records can be pulled.

2. Then give the user a listbox from which he can select the value he want to update in the field ( as I want them update only from the give option) for the records pulled by the previous query.

If I am right till here. Then

3. Update query should get the selected value from the list box & update them in the records pulled.

AM I getting the concept right OBP...??


Thanks
Sudhir

[OBP]

Well, it is one way to do it.
You could also just have a the Criteria to select records and the Listbox to select the update value and use a VBA Recordset to update the Table Directly.
Or if you are going to get the Required Records in a Query you could actually display them on the form and update on there.

[kbsudhir]

Is it possible to select the records which I want to update from the bunch of records displayed in the subform. I wil only select the value in the listbox & clieck a button & all the selected reords shoudl get updated...????

[OBP]

You can use the Subform's Filter to obtain just the records you want updated and then select your new value and have it applied by the VBA to all the records in the subform.
It would work just as well using a VBA Recordset.

[kbsudhir]

Ok, OBP.

Thanks a lot. I will try this out & let you know how it worked

Sudhir

[OBP]

Would you like the VBA to set the Form or Subform Filter?

[CreganTur]

A little performance note:

If the backend of your database is on a shared drive (so you can have multiple users), then you should know that an update query is slower than using the .Update method of a recordset, or even slower than Access' native ability of updating table values whenever a bound object's value is changed (and then the record is saved either by VBA command or moving to the next record/new record).

[kbsudhir]

Ok,

When I created a query to get the records as per the parameter & displayed it in the Subform & manually changed the field/fields & gain sun the query the query will display the updated data. That means I have a major loophole here. So how to disable the subform so that nobody can make any changes.

[kbsudhir]

can you please provide more insight form/subform filter. The info available on Access Help is no good enough for me.

[OBP]

You can stop Data being changed in the subform if you wish, just set the Form's properties to "Allow Edits" = No.

[kbsudhir]

OBP, this feature is not available in access 2003.

[OBP]

I think it should be, have you checked the Form's Properties "All" tab, the 10th item down should be Allow edits and under that Allow Deletions and then Allow Additions.