Dynamic Text Box ControlSource from multiple tables

[cjtezak]

Hello,



I am trying to update a form that currently pulls a "Comments" value from an existing table (items) and displays it in a text box the user can edit. I am trying to add a set of buttons that will allow the user to toggle between the existing "comments" value, and a new "job_notes" value that exists in a new table (job_data). The "job_notes" value is determined by a "job_type" value that is the primary key of (job_data) and is a value in the (items) table. When the user hits a button for either one of the notes options I would like the text box to be populated with the value, and also allow the user to edit the "Comments" and "job_notes" values.

I have been trying to do this by setting the ControlSource box through vba code to mixed results and have hit a wall. I keep running into problems trying to get the "job_notes" to populate the field and then let the user edit the contents. I thought I had a solution using dlookup to query the (job_data) table based on the "job_type" value that exists in the form, but using this to set the ControlSource does not allow the user to edit the field.

So my question is what solution (vba, better database design, other) would allow me to populate one text box with comment values from two tables and allow the user to edit those fields?

[John_G]

Hi -

Are you wanting to keep both the "Comments" and the "Job Notes" data? If so, you will need a field in Items for both. Whether you could change the control source on the text box between the two fields, and still retain the data for both, I can't say for sure.

If you don't want to keep both, but want to use data from Job_Data as a starting point, by clicking a button, you can use the On_Click event of the button:

me![mytextbox] = dlookup("Job_Notes", "Job_Data","Job_Type = " & Me!Job_Type)

Now, if you want to be able to toggle back and forth, it gets a lot trickier, because you will have to save the existing text box data (a string variable at the form level would do) before changing it. But that can all be done in the On_click events of the command buttons.


HTH

John

[cjtezak]

John,

I would like to keep both sets of data yes. These fields act as record tracking for a particular job so a user can add notes about work done, or queues for someone else. The two notes basically function as two levels, one for an individual item (comments) and one that applies to a broad class of jobs (job_notes).

I am not too wise with relationships, but is there a way to work the relationships to make this work out better?