New record for with fields from a different table

[cwebb]

I am trying to go to a new record form carrying the Business Name and Business ID over from the previous form. The table that this form saves to is being added on to. The table (BusContacts) will not always have the Business Name and ID. Those are found in another table (BusDev). I need the new form to find those and bring them into the new record form and then allow me to save the new form and information into the other table (BusContacts). The user needs to be able to see the Business Name at all times.

 

[DougP]

Put a button on the form you are leaving which takes you to the new form. and put this code in it.<br>
------------------------------<br>
Private Sub Command4_Click()<br>
On Error GoTo Err_Command4_Click<br>
<br>
Dim stDocName As String<br>
Dim stLinkCriteria As String<br>
<br>
stDocName = &quot;YourFormHere&quot; '&lt; Name of form to open<br>
<br>
stLinkCriteria = &quot;[ID]=&quot; & &quot;'&quot; & Me![ID] & &quot;'&quot; <br>
DoCmd.OpenForm stDocName, , , stLinkCriteria<br>
Forms!YourFormHere.Form![ID] = Me!BusinessID '<br>
<br>
Exit_Command4_Click:<br>
Exit Sub<br>
Err_Command4_Click:<br>
MsgBox Err.Description<br>
Resume Exit_Command4_Click<br>
End Sub<br>
--------------------------<br>
If you have a field on your second from which matches the first form use the &quot;stLinkCriteria&quot; line otherwise leave it out.<br>
The line Forms!YourFormHere where &quot;YourFormHere&quot; is the form you are opening from the other form.<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[elizabeth]

If you use the Lookup Wizard in the DataType column (design view) for the Business Contacts table, you can make the ID appear as the Business Name. This will carry through to all forms you create *after* you make the design change in the table.