Full Version: after update on txtacctnum
UtterAccess Forums > Microsoft® Access > Access Forms
doran_doran
Display account rep name "txtRepName" after I update a text box (txtAcctNum) with account number. I have a table that list account reps by account # (where account ends with last 2 digits). For account number ends with 00 to 12 it assign to 1st rep, 13 to 25 assign to 2nd rep. see table "tblSpecialAccts" below.
tartNum EndNum RepName
00 12 Monica Johnson
13 25 Jerry Warner
26 38 Montery Jackleburger
39 50 Sal Sundar
51 63 Walters Roxanne
64 76 Debbie Stone
77 89 Clifton House
90 99 John Abraham
Dont want Dlookup because there are over 5 or 6 millions accounts. Please suggest.
doran_doran
Here is the dlookup version but I really dont want to use dlookup
Lookup("[RepName]", "tblSpecialAccts", "[StartNum] <= '" & Right(rs![AcctNum], 2) & "' AND [EndNum] >= '" & Right(rs![AcctNum], 2) & "'")
works too.
Jerry Dennison
The DLookUp() function would not be used against the 5 or 6 million records but only on the number of records in the tblSpecialAccts table. What exactly are you trying to do?
doran_doran
after user puts account number on the form i want to populate rep name "txtRepName" control box with rep name.
Jerry Dennison
I understand that much. I assume that the control is an unbound control. So what is the problem?
doran_doran
I want to find alternative to dlookup. It's slow.
Jerry Dennison
Slow in what way? Where and how are you using this? What EXACTLY are you trying to do?
If you are using the DLookUp() function in an unbound control on a single record form then there is no way it can be as slow as you are saying unless there is something else going on. Attach an example of your table structure and what you're trying to do.
doran_doran
attachment is missing .
Jerry Dennison
Attachments must be zipped and less than 500k in size.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.