Validate entry by comparing to table

[ed333]

What can I say, my users are idjits and so the boss is requiring the following of my db:

There is a form used to enter charges. The first two fields are FILENO and CLIENTNO. What is the best way to

a) ensure that each number exists in their respective tables before accepting the entered values

b) ensure that the fileno and clientno match up as entered. (There is a table of Files, and each fileno has a clientno associated with it here)

Thanks so much for any help.


Ed

 

[llkhoutx]

Select the clientNo and fileNo with combo boxes so that your idiots never seen the numbers, only the client name and file name.

 

[ed333]

I've got this code so far:

Private Sub FILENO_LostFocus()

Dim msg As Integer
Dim compare As Variant

compare = DLookup("[FILENO]", "tblFiles", "FileNo = '" & Me.FILENO & "'")

If IsNull(compare) Then
msg = MsgBox("You must enter a valid filenumber!", 16)
Me!FILENO.SetFocus
End If

End Sub


It works, except that it does not return the focus back to FILENO. What am I doing wrong?


Oh, thanks for the idea about the Combo Box, but there are WAY too many files and clients for that.

 

[llkhoutx]

Set focus to another field before resetting focus to FileNo.

Note that dlookups are very very slow.

 

[ed333]

Thanks, that did it!

I know DLookUp is slow, but I've got about 50k file numbers and about 10k client numbers to validate from. Do you have any other suggestions?

Ed

 

[llkhoutx]

Take a look at the attached combobox solution.

It has the added advantage of easliy adding clients and files.

Not the VBA code behind the form. Client names are unique, no duplicates, file names can be duplicated.

I think that I sorted the file combo box names, but not the client name combo box. Both should be sorted.

You'll be suprises how fast this is, even with 50,000 names. I hope that you have a SQL Server backed.

 

[ed333]

Thanks a lot, man. I am going to try this out. We don't have SQL Server, however. The users have local front ends with a shared backend (Access 2002). It's got to be faster than DLookUp, anyway.


Ed