Form Control - Query, then adjust

[jclover]

This might be a freshman question, but is there a way to have a field in a form be populated by a query, and then be adjusted by the user?

I want to have a sub-form that populates data automatically based on matching criteria in the form, but gives the user the option of "overwriting". One they close the form, any changes are written as a new record to a specified table (that part I can do).

[Kevin Wilcox]

This might be a freshman question, but is there a way to have a field in a form be populated by a query, and then be adjusted by the user?

I want to have a sub-form that populates data automatically based on matching criteria in the form, but gives the user the option of "overwriting". One they close the form, any changes are written as a new record to a specified table (that part I can do).

I'm just about to do something just like this with a continuous form; the context is that I'm creating a health clinic diary with multiple practitioners/clinics where the appointment slots vary from week to week. I store appointment data in tblDiary. When adding in future clinics I want to use a default clinic template for each practitioner, stored in tblDiaryTemplate (imaginative, isn't it!). Then I want the user to be able to use the template as a basis but change details like appointmet slot times, clinician, location and so on; and then append the end result to the tblDiary; and if desired, overwrite the current template with this new clinic definition in tblDiaryTemplate; and then revisit that revised template later on. I don't want users making direct, running changes to the underlying tables as they really need to be able to see the whole clinic before saving changes. So I want users working outside of those two tables. Is this similar to your context? If so....

My plan is to set up unbound fields in a my form to match the tables I'm talking about, populate the unbound fields by (in VBA) using the CreateTableDef method (covered in Help) to to create a temporary table holding the queried tblDiaryTemplate data, changing the forms recordsource to the temptable at that point which will allow me to edit the results; then append the edited data to tblDiary via the on_click event of a cmdButton; if user wishes, run a delete query to remove previous template from tblDiaryTemplate, and an append query (Insert Into) to replace with the new clinic definiton. Though I'll use buttons this could all be done on e.g. the forms afterupdate event or other appropriate event.

HTH's
Kevin