I need a quick, easy way to search a database for a value (e.g., SSN) before the entire multiple field record has been entered. This has to be done so that we don't have any duplicate records in the database. Yes, I know, I can set 'no dupes' on that field's property - but they can still go ahead and enter the entire record before finding out (when saving) that it's a dupe SSN.<br><br>My idea was to have the user enter the SSN of the "new" customer, do a quick search of existing records and, if no match was found, bring up the data entry form. If a match IS found in the search, I want to bring up the existing record for edit.<br><br>Any suggestions? I've written a bunch of (convoluted!) code, tested several things, and still don't have it "quite right" for the user.<br><br><sigh><br><br>Thanks in advance!<br><br>Tom
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Searching Before Record Is Entered 1
- Thread starter tbohon
- Start date
DiscoDuane
MIS
set up a after_update procedure for the ssn textbox <br>this DAO let me know if you are using ADO<br><br>dim intSSN as integer, recA as recordset<br><br>intSSN = me.[your textbox]<br><br>Set recA =CurrentDb).OpenRecordset"yourtable",dbOpenDynaset)<br>' Set txtNum = Me.txtTag<br>Me.RecordsetClone.FindFirst "[ssnfield] = " & intSSn<br>If Me.RecordsetClone.NoMatch Then<br>' place code that rec doesnt exist<br>else ' code for does exist<br><br>End If
tbohon,<br>I think Duane meant to do the findfirst on RecA instead of recordsetclone, since the form's recordset may be only a subset of the entire table.<br>In any case, searching on the table will do it, and for even faster searches, you could open RecA as dbOpenTable, set the index property to the indexname for ssn (you <i>should</i> index ssn with no dups). Then do this<br>RecA.seek "=", intSsn<br>Depending on the size of the table, you will see a significant performance increase here.<br>--Jim
-
1
- #4
Cornerstone
MIS
Private Sub yourcontrol_BeforeUpdate(Cancel As Integer)<br> Dim dbs As Database<br> Dim rst As Recordset<br> <br> Set dbs = CurrentDb()<br> Set rst = dbs.OpenRecordset("yourtable"
<br> <br> Do Until rst.EOF<br> If rst!yourfield= Forms![yourform]!yourctrl Then<br> GoTo result<br> Else<br> rst.MoveNext<br> End If<br> Loop<br> Exit Sub<br>theend:<br> MsgBox "Duplicate value"<br> Cancel = True<br><br>End Sub<br>
<br> <br> Do Until rst.EOF<br> If rst!yourfield= Forms![yourform]!yourctrl Then<br> GoTo result<br> Else<br> rst.MoveNext<br> End If<br> Loop<br> Exit Sub<br>theend:<br> MsgBox "Duplicate value"<br> Cancel = True<br><br>End Sub<br> Quicker Still..<br>Use a Combo box for SSN and as you type it will start selecting the SSN numbers as you key them in. <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.
CharlesDFW
Programmer
I agree with DougP. This is what the combobox was made for. And if you set it to "limit to list" and attach code for the "on notinlist" event, that code can call up your data entry form. (Don't forget to set the "Response" flag in that code).
Halifax
Programmer
How about:<br>DCount("ID", "MyTable", "SSN = 12345678"<br>If the result = 0 then let the user in<br>If not zero = don't let them in, give message box that returns persons name, and employee id etc etc.<br><br>Put the code in an if statement in the OnOpen event of the form like so:<br><br>Private Sub Form_Open(Cancel As Integer)<br>If DCount("ID", "MyTable", "SSN = 12345678" = 0 Then<br> MsgBox "can't do that"<br> Cancel = True 'Form does not open<br>End If<br>'Form will open if it gets to here<br><br>End Sub<br>
<br>If the result = 0 then let the user in<br>If not zero = don't let them in, give message box that returns persons name, and employee id etc etc.<br><br>Put the code in an if statement in the OnOpen event of the form like so:<br><br>Private Sub Form_Open(Cancel As Integer)<br>If DCount("ID", "MyTable", "SSN = 12345678" = 0 Then<br> MsgBox "can't do that"<br> Cancel = True 'Form does not open<br>End If<br>'Form will open if it gets to here<br><br>End Sub<br> The combo box will only be easier if the table is relatively small. Depending on the size of the table, a combobox can be excruciatingly slow, and as a rule I'd avoid using it as a 'uniqueness validation' tool, it really isn't made for that.<br>--Jim
elizabeth,<br>Yes, several thousand records I'll start to see it bog...and that number will be easily surpassed for say, a Customer table, where (as in tbohon's question) SSN would be needed to be checked. An employee table would probably be small enough to use the combo box, though, but my personal preference is not to use a combo box for that kind of validation--the purpose of that 'select as you type' feature of combo boxes I feel is more suited for a 'lookup' table, where you're entering a Product Code, or something like that. In addition, that combo box must be requeried then after every new record, which is another performance hit.<br>--Jim
- Status
Similar threads
- Locked
- Question
- Locked
- Question