Full Version: populate text field
UtterAccess Forums > Microsoft® Access > Access Forms
Display account rep name. I have a table that list account reps by account #. For account number begins with 00 to 12 it assign to 1st rep, 13 to 25 assign to 2nd rep. see table "tblSpecialAccts" below.
ange 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
Now on the form I have a text box "txtAcctRep"; "ON Open Event" I would like this text field to be populated with the correct RepName from above table. There is a text field called "AcctNo".
Use DLookup(). But you might have to play with the syntax or change the format of your lookup table.
DLookup("RepName", "tblSpecialAccts", Left(Me.AcctNo,2) & " Between Val(Left([Range],2)) And Val(Right([Range],2))")
Kool. I will try this. I am not a big fan of Dlookup because there are over million records and Dlookup is super slow. Thanks for your suggestion. Is there another method ? (like looping through)
Yes, you could probably do this with code. But the first thing I would probably do is modify your table structure to break the Range into two fields: Low and High or Start and End, so it would be easier to evaluate the account number against it. Hope that makes sense. Good luck.
DB Guy,
That makes sense. do you have a sample snippet ? table has been re-structured.
anyone else please.
What are the fields called now, and what are their data types?
startNum 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
Here is the table structure. StartNum and EndNum is test field cause we need to store 00 in the startNum
If you just want to show the Account Rep on a single form, I still think that you will be fine with using DLookup because, although you may have millions of account records, you only have 8 account reps, so DLookup shouldn't slow you down too much.
Are you thinking about storing the Account Rep ID in the account records? If not, you can modify your DLookup statement like this:
=DLookup("RepName", "tblSpecialAccts", Val(Left(AcctNo,2)) & " Between Val([stratNum]) And Val([EndNum])")
Thank you.
You're welcome. Good luck.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.