Opening a record from a form/subform with a syncronized combo box

[Garridon]

I have a subform that displays a list of records based on the selection of an item in a combo box (like having a company name, and a list of all their products). I'd like to be able to double-click on a specific record in the subform and have it open another form with information about that specific record. I've been experimenting with the GoTo function in a macro but haven't been able to figure out how to make it open the form to the specific record selected. Any suggestions would be very much appreciated!<br>
<br>

 

[DougP]

Make sure the &quot;Wizard&quot; button is depressed on the Toolbar.<br>
Create a New button on your form<br>
A Wizard box will pop up.<br>
In the &quot;Categories&quot; List pick &quot;Form Operations&quot;<br>
In the &quot;Actions&quot; List pick &quot;Open Form&quot;<br>
Click &quot;Next&quot; button (at bottom)<br>
Pick the form you want to open.<br>
Click &quot;Next&quot; button <br>
Click the &quot;Open the form and find specific data&quot; radio button.<br>
Click &quot;Next&quot; button <br>
Now then this next form allows you to choose which item on your current form will tell the next form to open which record to position itself on.<br>
So find the items in both lists and then click that &lt;-&gt; button in the middle it will put your choices in &quot;Matching fields&quot;.<br>
Click &quot;Next&quot; button <br>
Choose text or picture<br>
Click &quot;Next&quot; button <br>
Give your button a name.<br>
Click &quot;Finish&quot; button <br>
Now if you want to modify the code <br>
Right click on your new button and then click &quot;Build Event&quot;<br>
This will take you to the VBA code window.<br>
<br>
You can see what Access is doing.<br>
And modify it if necessary<br>
you can also do unlimited other things here such as passing values to the next form.<br>
<br>
------------------------------------------<br>
Or here is another method<br>
------------------------------------------<br>
<br>
Dim formname As String, Syncriteria As String<br>
Dim f As Form, rs As Recordset<br>
<br>
' form name to be syncronized<br>
formname = &quot;CreateNewRA&quot;<br>
<br>
' check to see if the from is open<br>
If Not SysCmd(SYSCMD_GETOBJECTSTATE, A_FORM, formname) Then<br>
DoCmd.OpenForm formname<br>
End If<br>
<br>
'Define the from object and recordset object for the Products form<br>
Set f = forms(formname)<br>
Set rs = f.RecordsetClone<br>
<br>
' define the criteria used for the sync<br>
SyncCriteria = &quot;[CUST_ID]=&quot; & Me![CUST_ID]<br>
<br>
<br>
' find the corresponding record in the Products table<br>
rs.FindFirst SyncCriteria<br>
f.bookmark = rs.bookmark<br>
<br>
<br>
<br>
<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[famousb]

If you add this to the ON_DOUBLECLICK property of the Combobox that you were referring to (modifying it to refer to your field and form names) it will open the specified form when you double-click in that field. or you can put it behind a button as DougP mentioned.<br>
<br>
<br>
Private Sub MemServ_DblClick()<br>
<br>
Dim stDocName As String<br>
Dim stLinkCriteria As String<br>
<br>
stDocName = &quot;MemServ&quot;<br>
<br>
stLinkCriteria = &quot;[FileNumber]=&quot; & Me![FileNumber]<br>
DoCmd.OpenForm stDocName, , , stLinkCriteria<br>
<br>
End Sub<br>
<br>
<br>
<br>
You can also add a DoCmd.Close statement to the end to close the original form if you do not wish to leave it open.<br>
<br>
<p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>