Full Version: populate text field
UtterAccess Forums > Microsoft® Access > Access Forms
doran_doran
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".
theDBguy
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))")
HTH.
doran_doran
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)
theDBguy
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.
doran_doran
DB Guy,
That makes sense. do you have a sample snippet ? table has been re-structured.
thanks
doran_doran
anyone else please.
theDBguy
What are the fields called now, and what are their data types?
doran_doran
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
Thanks
theDBguy
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])")
HTH.
doran_doran
Thank you.
theDBguy
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.