Pull values from 2nd table but store in form source table

[marisakay]

I am trying to use a form to enter few peices of information and store in Table1(Response Table). I have a second table (Comment Table) that I am using to store frequently used comments, to be triggered by an assigned comment code. I want the user to be able to select the comment code from a combo box on the form and then store this information in the Response table. However, the user should also be able to enter a new comment, which doesn't need to be saved in the Comment table, just the Response table. I have tried using a subform and linking the two tables by comment code, but I have the problem of storing the entered information in the Response, not the Comment table. Also, I don't want the user to corrupt the data in the Comment table when they try to enter a new comment. They shouldn't be able to permanently change this table from the form. Maybe I'm going about this all wrong -- any suggestions would be greatly appreciated.

Thanks!

 

[quest4]

marisakay, try linking your tables, CommentCode, be sure to check Referential Integerty and the two Cascade check boxes. On your form, your have a couple of ways to do this. First is a combobox with the CommentCodes as a pull-down and a textbox, which is unbound and you can use DLookup for the CommentText. Or you can use the combobox, bound column 1 and column count 2, to display both. Then I would add a command button, and set it to add record, when you get a new record you want to save click and enter it, When youget a new record you don't want to keep, create a record Blah and CommentText blank. Use that to keep none permanent comments. That should get you close to what you want. A couple of VB codes can automate the rest. Hope this helps you out, good luck.