Searching subforms 1

[wingenet]

I have a database which details paging equipment manufactured by my employer. The main form shows all the aspects of the parent equipment and on it are subforms that detail sub-assemblies, mostly radio pagers, associated with that parent equipment.<br><br>My problem is that I can search the tables on which the subforms are based but then cannot force the main form to move to and display the relevant parent equipment record.<br><br>Each of the sub-assembly tables is linked to the main table using 1:M relationships based on the serial number of the parent equipment.<br><br>I have tried using several types of query & dlookup() but just don't seem to be able to pass the resulting info, in this case the parent equipment serial number, back to where I might make use of it. Help please.

 

[DougP]

I use this code snippet all the time to &quot;sync&quot; a subform to a main form. So if I click on a particular item on a subform the main form will sync to it. It uses the Book Mark of a record on a subform to find an corresponding bookmark in the main form and moves to that record.<br><br>So in the Click event of the subform<br>you will have this VBA code<br>-------------------<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim FormName As String, SyncCriteria As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim f As Form, rs As Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' Main form name to be syncronized<br>&nbsp;&nbsp;&nbsp;&nbsp;FormName = &quot;AutoCAD&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;'Define the from object and recordset object for the AutoCAD form<br>&nbsp;&nbsp;&nbsp;&nbsp;Set f = Forms(FormName)<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rs = f.RecordsetClone<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' define the criteria used for the sync<br>&nbsp;&nbsp;&nbsp;&nbsp;SyncCriteria = &quot;[Part_num]=&quot; & Chr$(39) & Me![Part_num] & Chr$(39)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' find the corresponding record in the Parts table<br>&nbsp;&nbsp;&nbsp;&nbsp;rs.FindFirst SyncCriteria<br>&nbsp;&nbsp;&nbsp;&nbsp;f.Bookmark = rs.Bookmark<br>--------------------------------<br>Note: Chr$(39) is a single quote to be used if the SyncCriteria field is a Text. Or in my case [Part_num] was a text field type not a number.<br>Remove these two Chr$(39) if the field type is a number field type.<br>like so:<br>SyncCriteria = &quot;[Part_num]=&quot; & Me![Part_num] <br><br>Now in my case Part_num is a field on both forms.<br>If say, you have Part_num on the main form and and PagerNum on the subform then it would look like this:<br>SyncCriteria = &quot;[Part_num]=&quot; & Me![PagerNum] <br><br>Also the click event is tripped ONLY when you click in the little gray box in the far left side of the subform.<br>There is one little box next to every record.<br>This is just outside the text area.<br><br>OK<br><br><br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.