How to find a record using an event

[BFP]

Basically, I'd like to be able to double-click a few fields in my form and have Access prompt for text (or a number in one case), and, based on the value entered, bring up records one at a time that match the entered value.&nbsp;&nbsp;I thought this may be &quot;doable&quot; with a macro, but I haven't had much success yet.&nbsp;&nbsp;Perhaps VBA code is required.&nbsp;&nbsp;<br><br>Any suggestions?<br><br>Thanks,<br><br>--Dan

 

[Amiel]

Sure it is. Do something like this.<br><br>Global strSQL as string<br>Global WhereExists as Boolean<br><br>Sub Form_Open()<br>&nbsp;&nbsp;&nbsp;strSQL = &quot;SELECT * FROM table1&quot;<br>&nbsp;&nbsp;&nbsp;WhereExists = false<br>End Sub<br><br><br>Sub tablefieldname1_DblClick()<br>&nbsp;&nbsp;strBuff = InputBox(&quot;Enter Value&quot<br>&nbsp;&nbsp;if WhereExists = True Then<br>strSQL = &quot; AND tablefieldname1= &quot;&quot;&quot; &&nbsp;&nbsp;strBuff & &quot;&quot;&quot;&quot;<br>&nbsp;&nbsp;else <br>strSQL = &quot; WHERE tablefieldname1= &quot;&quot;&quot; &&nbsp;&nbsp;strBuff & &quot;&quot;&quot;&quot;<br>&nbsp;&nbsp;end&nbsp;&nbsp;if<br>WhereExists = True<br><br>'Open a recordset based on your query (strSQL) and<br>'set the 'forms recordsource to the recordset. <br>'Be sure to call refresh for the form.<br>End&nbsp;&nbsp;Sub<br><br><br>Sub tablefieldname2_DblClick()<br>&nbsp;&nbsp;strBuff = InputBox(&quot;Enter Value&quot<br>&nbsp;&nbsp;if WhereExists = True Then<br>strSQL = &quot; AND tablefieldname2= &quot; & val(strBuff) 'a number<br>&nbsp;&nbsp;else <br>strSQL = &quot; WHERE tablefieldname2= &quot;& val(strBuff) 'a number<br>&nbsp;end&nbsp;&nbsp;if<br>&nbsp;&nbsp;WhereExists = True<br><br>'Open a recordset based on your query (strSQL) and<br>'set the 'forms recordsource to the recordset. <br>'Be sure to call refresh for the form.<br>End Sub<br><br>Sub ClearSelection()<br>&nbsp;&nbsp;strSQL =&nbsp;&nbsp;&quot;SELECT * FROM table1&quot;<br>&nbsp;&nbsp;WhereExists = false<br>&nbsp;&nbsp;'Open a recordset based on your query (strSQL) and <br>&nbsp;&nbsp;'set the 'forms recordsource to the recordset. <br>&nbsp;&nbsp;'Be sure to call refresh for the form.<br>End Sub<br><br><br>You'll have to validate the numeric values prior to use to avoid errors. Further you should plan to trap Jet errors relating to malformed queries in general, and prompt users when values are not in&nbsp;&nbsp;range or are invalid.<br><br>There may be some functionality missing here, but not much, I'm sure. have fun with it.<br><br> <p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br>