I have a click button on a form which outputs a form and an email – which works great. However, What I want to do is add the ability to run an update query at the end of the routine.
The form is populated with data from another query (QOpenIssues), and contains multiple records of different siteID’s. At the moment, I have in the query itself, in the siteid field a user prompt to enter the siteID. Once entered, the other criteria is status <> “Closed”. This updates, but requires the user to keep entering the different siteId’s as the loop works through the data.
Is it possible (and hopefully someone will give some example!) that as part of the VBA, I could change the “DoCmd.OpenQuery "updateopenrecords"” with something that will filter the update query with the current siteid, ie., somehow adding the “me!siteid” to the docmd ?
I have tried all sorts of ways, and either the update query closes all records, or it doesn’t close anything!?
Any advice or examples would be gratefully received!
The issue I hve is that opening the form provides 10000 records a day, and effectively there could be 2+ more records for the same siteID with the data. what I have is through VBA is that it produces a report and includes all the "new" records on one report for single siteid. What I then need to do is change the status to closed the current record (and any matching siteID's in the dataset), so that when the user goes to the next record, there are no duplications left in the dataset presented.
The only common factor of the open records at the point of the user click is the siteid = 1234 & status = new.
What does siteid have to do with the update? Could it be that you are simply updating everything status New to Closed? If that is the case, you can remove siteid from the query. Otherwise, is there a date field that you can use at all? Any other common field?
I use the siteid field to update, purely from a user point of view - sort of - update as you go. As its taking so long each day to get through the records, the user may close and re-open the database several times a day, and therefore, at present, it will "start from where you left off"
The date idea is a non runner, as they will often be transactions data/timed different -the only common field I have of all the records is the siteid (which is the customer no in my instance).
Through VBA, Would I be able to add a filter to the record set to equal site id (therefore displaying all the records = 4567) and then run the update against the filtered records? I would then need to be able to remove the filter (I suppose a requery would do) - any thoughts/suggestions here?