How to use a recordset to validate and entry? Steve in MN 12/10/2008 4:16:08 AM I have a form where user enters a BOL number (bill of lading) and on lost focus of that field i would like it to be able to go out and look at another table to see if that BOL is a valid number. Can someone give me an idea on how to do this?

Re: How to use a recordset to validate and entry? "Allen Browne" <AllenBrowne[ at ]SeeSig.Invalid> 12/10/2008 4:39:09 AM Use DLookup() in the AfterUpdate event procedure of this text box. If you need help, see: Getting a value from a table: DLookup() at: http://allenbrowne.com/casu-07.html Use the text box's BeforeUpdate event (and set Cancel=True) if you want to jam the user in this text box until they enter a valid number or undo (by pressing Esc.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Steve in MN" <SteveinMN[ at ]discussions.microsoft.com> wrote in message news:E2CA086D-199C-4B08-8CD7-6759EDD2D8EE[ at ]microsoft.com... [Quoted Text] >I have a form where user enters a BOL number (bill of lading) and on lost > focus of that field i would like it to be able to go out and look at > another > table to see if that BOL is a valid number. > Can someone give me an idea on how to do this?

Re: How to use a recordset to validate and entry? Mark Grizzle <mgrizzle[ at ]oeh.com> 12/10/2008 2:22:21 PM Use the lost focus event for the control to open a recordset to seach for the bol number. Psuedo code follows... Sub field_lost_focus On error goto LocalError Dim cnn as adodb.connection Dim rs as adodb.recordset Dim sql as string Sql = "select count(1) from bol_table where bolfield = '" & me.bolnumber & "' " 'load an ado connection Rs.open sql, cnn If rs.fields(0) = 1 then 'record found Else 'record not found End if Rs.close Set rs = nothing ExitHere: exit sub LocalError: 'handle error resume exithere End sub Hope this helps, mark On 12/9/08 11:16 PM, in article E2CA086D-199C-4B08-8CD7-6759EDD2D8EE[ at ]microsoft.com, "Steve in MN" <SteveinMN[ at ]discussions.microsoft.com> wrote: [Quoted Text] > I have a form where user enters a BOL number (bill of lading) and on lost > focus of that field i would like it to be able to go out and look at another > table to see if that BOL is a valid number. > Can someone give me an idea on how to do this?

Re: How to use a recordset to validate and entry? Steve in MN 12/10/2008 8:58:01 PM Thanks for the help guys! Much appreciated. Steve "Mark Grizzle" wrote: [Quoted Text] > Use the lost focus event for the control to open a recordset to seach for > the bol number. Psuedo code follows... > > Sub field_lost_focus > > On error goto LocalError > > Dim cnn as adodb.connection > Dim rs as adodb.recordset > Dim sql as string > > Sql = "select count(1) from bol_table where bolfield = '" & me.bolnumber & > "' " > > 'load an ado connection > > Rs.open sql, cnn > > If rs.fields(0) = 1 then > 'record found > Else > 'record not found > End if > > Rs.close > Set rs = nothing > > ExitHere: > exit sub > > LocalError: > 'handle error > resume exithere > > End sub > > Hope this helps, > mark > > On 12/9/08 11:16 PM, in article > E2CA086D-199C-4B08-8CD7-6759EDD2D8EE[ at ]microsoft.com, "Steve in MN" > <SteveinMN[ at ]discussions.microsoft.com> wrote: > > > I have a form where user enters a BOL number (bill of lading) and on lost > > focus of that field i would like it to be able to go out and look at another > > table to see if that BOL is a valid number. > > Can someone give me an idea on how to do this? > >