HELP FAMOUSB!!!

[HerickP]

Sorry for so many questions, but this forum has been so far my only solution; I have a question regarding entering Data in Multiple tables using one Form:<br><br>How can I create a Form where I will be entering data in tables ( all common fields, except that each table is for a specific region) where in this form, I could like, for example, select in a combo Box, the table to where the data will be going, and then, just fill out the other fields and update the specific table chosen in the combo box??? Thanks again guY

 

[famousb]

ok, this may work or may not - never tried it, never had to think about it, but i'm thinking this should work.<br><br>on your form with the combo-box with table names (or i'm hoping they are the table names), let's call that combo box's name &quot;RegionCombo&quot;<br>in the properties of the form itself, put the record source as <b>=[RegionCombo]</b><br><br>like i said, i don't know if this will work, but i'm sure there is some variation on this that will do the trick for you.<br><br> <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[famousb]

in the after update property of the RegionCombo you may also want to put this statement in code.<br><br>Me.Refresh<br><br>to make sure the answers go in the right table. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[HerickP]

when I run the form and&nbsp;&nbsp;click on the ComboBox, it says &quot;control cant be edited; its bound to expression &quot;=[RegionCombo]&quot; . Whats that?

 

[famousb]

ok, is the combo box bound to anything itself?<br>make sure it is unbound, and the values for it are a Value List of table names that you have typed into the row source yourself (ie Table1; table2; table3; etc...)<br><br>i told you i'm not sure if this will work or not, we may need to use more elaborate code. or have the combo box come up in a form first, and then after update of the combo-box the other form is pulled up.&nbsp;&nbsp;(this method would probably work). <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[HerickP]

Still doing it....when I add the comboBox, it says Unbound, but if I add =[regioncombo] to the Control Source, when I run the form I still get the msge. and I have name the combobox regioncombo, so I dunno it this works. Any other code my friend??? thanks a lot for trying, I will vote for u.

 

[famousb]

ok do this.&nbsp;&nbsp;start by creating a new form - call it &quot;frmTableControl&quot;.&nbsp;&nbsp;create the RegionCombo box on this form.&nbsp;&nbsp;it's row source should be the list of tables as previously mentioned.<br>in the After Update property of RegionCombo put this statement:<br><b>docmd.OpenForm &quot;YourFormName&quot;,acNormal,,,acFormAdd,acWindowNormal</b><br><br>put the other form's name where the previous statement says &quot;YourFormName&quot;&nbsp;&nbsp;this will bring up that form.<br><br>now on that form, in the Record Source put this statement:<br><b>=Forms!frmTableControl![RegionCombo]</b><br><br>try that. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[HerickP]

&quot;Your Form Name&quot; should be &quot;FrmTableControl&quot; ??????

 

[famousb]

no, YourFormName should be the name of the other form that is going to be opened.<br>frmTableControl is going to be opened first, and then it's going to open the form with that you want to have multiple tables as recordsources for. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[HerickP]

It says&quot; Cannot open macro &quot;Docmd&quot; hmmmm.???????

 

[famousb]

oh, i'm sorry, don't just paste it into the AfterUpdate property, click on that build button next to it, then choose Code Builder, and paste it in there. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[HerickP]

Same with Record source for the other form??? I keep getting an error &quot;The Microsoft Jet Engine cannot find the imput table &quot;=forms!frmtablecontrol![regioncombo]'.....and I have named the combo box regioncombo and the other form frmTableControl......

 

[famousb]

ok, let me try something, i'll get back to you in a minute. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[HerickP]

<br>Thanks!!!! ))))

 

[famousb]

ok, this works, i just tested it.<br>in the form that is getting opened, place this statement in the On Open property by using the code builder method like before:<br><b>Me.RecordSource = [Forms]![frmTableControl]![RegionCombo]</b><br><br>this should do the trick, it'll open the table to a new record, but if you want to open it in edit mode, just change the code on the AfterUpdate property of RegionCombo to:<br><b>docmd.OpenForm &quot;YourFormName&quot;,acNormal,,,acFormEdit,acWindowNormal</b> <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[HerickP]

which is the form thats getting open: FrmTablecontrol or My form?

 

[famousb]

the first form that you open is the one i instructed you to create &quot;frmTableControl&quot; in the AfterUpdate property of RegionCombo on this form you need to put the command:<br><b>docmd.OpenForm &quot;YourFormName&quot;,acNormal,,,acFormEdit,acWindowNormal <br></b><br>but substitute in your other forms name.<br><br>in the On Open property of your other form that is getting opened please put in the command:<br><b><br>Me.RecordSource = [Forms]![frmTableControl]![RegionCombo]</b><br> <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>