Updating records in a form

[alanponeill]

I have a form that is using a table as a data source.

I want to be able to filter the form based on various criteria (using the buttons on the toolber) and then at some point be able to set the value of a Y/N field on each data record included in the filtered list to -1.

I have created on action button with code which will be executed on Double Click of the mouse and reckon that I should define a record set as the filtered form list but do not know how to set the filtered form data source as the recordset source.

Am I going about it the best way or is there a better way? If so, how do I proceed? If not, what should I do instead?

All advice appreciated.

 

[Joe4]

I am not sure I understand exactly what you are doing, but this may help.

I have used forms before to set criteria for an Update Query. In the form, I choose my criteria through a set of of combo boxes, text boxes, radio buttons etc. Then, I have VBA behind the scenes that takes the criteria and builds the SQL needed to run an Update Query, which updates the filtered values in my original table.

I don't how comfortable you would be doing all that, but it really is powerful stuff. And you really don't even really need to know much SQL, because if you build what you want the query to look like in the Query Builder, you can simply go to SQL View and "steal" the code you need from there. Of course, a little knowledge of VBA is required.

At the very least, if you are not familiar with how they work, check out "Update Queries" in Access Help. I believe you can find it under "Actrion Queries".

 

[alanponeill]

Thanks for the input but unfortunately that won't work. The users need to be able to run the filters from within the form and make an elective decision if the related records are to be updated.

Anyone else know how to set the contenets of a form's filtered record source as the source of a recordset.

Thanks,

Alan

 

[Joe4]

Why not just base the form on a parameter query? Then when the users open the form, the will be prompted for their filters and then can update the fields the want to update accordingly.

 

[alanponeill]

The users are going to need to analyse the tranactions to determine which transactions bellong together. They will not know up front what the various filter criteria will be until they analyse the data. Parameter queries are definitely not the way to go.

Thanks for trying.

Alan

 

[Joe4]

I still think however you do it, a query has to be involved. Essentially, that is what a query is, a recordset which can be a filtered subset of your table.

I have never down anything with creating a toolbar, to update it "on the fly", but if I were you I would first approach this by having the form display the results of a query based on your table (and not the actual table itself), and get your action buttons to modify the criteria of the query on the fly.

Good luck!