Update a table when form is saved

[RCheesley]

Probably a simple solution but can't figure out the syntax!

I have a form which is being completed when a particular questionnaire is returned. I have the name/address of all those it was sent to in one table, and their questionnaire responses are being saved to a separate table.

When the user enters the data into the form, it saves no problem, but what I would like to do is to be able to populate a field on their name/address table called "BaselineCompleted" with "Yes" when the form is completed (and maybe set the default to No).

The reason behind this is I want to do a pie chart to show proportions outstanding - I can't think of any other way to do this though!

Ruth

 

[garyholc]

I would do an UPDATE statement when the form is saved which updates the value in the other table. Ideally the name of the person has a unique number (such as an auto increment number) as its primary key, so you can tell it to UPDATE the value where the persons ID number on the questionnaire form matches that in the Name/Address table...

Alternatively create an update query and get a macro to run it when the form is saved which sets the Baselinecompleted field to true for that person based on their ID number.

 

[RCheesley]

Thanks gary, I was already thinking along those lines but just can't get it to work.

My latest attempts were using this:

CurrentDb.Execute "Update [tblCareHome] set [CareHomeBaselineCompleted]=#" & Yes & "#WHERE [CareHomeID] = '" & Me.CareHome.Value&";"

Where tblcarehome is where the name/address is stored, carehomebaselinecompleted is the field to be updated with yes/no and CareHomeID is the PK in tblcarehome, Me.Carehome.Value is the care home selected in a combo box on the form (first field is the carehomeID).

I've made so many changes now I'm completely lost, googling and searching forums and whatnot for various ideas.

Keep coming up with expected end of statement or semicolon missing whatever I do .. just about fed up now!

Ruth

 

[garyholc]

I tend to use the

Docmd.runSql

Try googling this command with the update statement and you should find some examples.