Updating a field in a table from a command button on a report

[ebrommhead]

I have created an accounts receivable database to track balances owing, customer contact and customer responses. I have a form with a sub-form that allows looking up and display of customer information to make collection calls easier. I also have a sub-form to record promises made by the customer with a field for a follow up date if required. I store these promises to pay and the follow up date in a "Customer Collection Comments" table along with a blank date field called "resovled". When the database is opened each day, a list of customers that have been marked for follow up on that date along with their comments are displayed in a report "Customer Collection Comments" Report. I have a command button at the end of each record that says "Resovled".

What I would like to happen, is that when the person following up on the file clicks on this "Resolved" button, it adds the current date into the corresponding record and "resolved" date field in the "Customer Collection Comments" table. This would then enable me to exclude these records from the next morning's report. Note: the "Customer Collection Comments" table has a sequential "ID" that is also included in the report layout.

I am rather poor with Access code so the more detail the better.



Thanks for any assistance.

[June7]

You are opening a report or form?

You want to update two fields with the same data? Sounds like duplication.

Code in Resolved button click event on form could be simply:

Me!Resolved = Date()

Want to provide db for analysis? Follow instructions at bottom of my post.

[ebrommhead]

Thanks for your response.

I have a report open with a control button at the bottom of each record. If the button is clicked, I would like to put a "resolved date" into the "Customer Collection Comments" table for that particular record so that I can exclude it fromthe next report for all unresolved comments.

I have my db linked to a SQL database. I'm not so sure how useful it would be to attach it.

[June7]

You would have to make an Access backend and import data from the SQL and provide that with the frontend so could test code with data.

If the field is included in the RecordSet of form the code is behind then the simple code I suggested should work. If the field is in a table not included in the form RecordSource then need SQL action statement (UPDATE or INSERT).

CurrentDb.Execute "UPDATE tablename SET fieldname=Date() WHERE ID=" & Me.ID

CurrentDb.Execute "INSERT INTO tablename(fieldname) VALUES Date()"

[ebrommhead]

Thanks June. Copy and pasted you code to my command button but it didn't work. I am going to try to attach my database. I am linked to many files on my server but the "main menu" ---> "follow up on collection" should still bring up the report with the button that I am trying to get to work. The source for this report is the "Customer Collection Comments" table.

I appreciate your efforts.

ThanksAttachment 9976