By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Auto populate form control on data entry

P: 78
I have a form called frmIlogEntry. this form is based on the table tblILOG. I have a fields in the table and on the form VendNo and VendName. On the form I want when the user types in the VendNo the field VendName to auto-populate. I am sure this is probably an easy function but for the life of me I can't get it. VendNo and VendName are stored in tblVendors. Please help....AO3
Dec 11 '06 #1
Share this Question
Share on Google+
11 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
I have a form called frmIlogEntry. this form is based on the table tblILOG. I have a fields in the table and on the form VendNo and VendName. On the form I want when the user types in the VendNo the field VendName to auto-populate. I am sure this is probably an easy function but for the life of me I can't get it. VendNo and VendName are stored in tblVendors. Please help....AO3
In the After Update event of the control VendNo you can put the following code.

Expand|Select|Wrap|Line Numbers
  1. Private Sub VendNo_AfterUpdate()
  2.  
  3.    Me.VendName = DLookup("[VendName]","tblVendors","[VendNo]=" Me.VendNo
  4.  
  5. End Sub
  6.  
Mary
Dec 11 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
"The title of this thread has been changed to make the problem clearer to experts and others searching for similar threads."

MODERATOR
Dec 11 '06 #3

P: 78
In the After Update event of the control VendNo you can put the following code.

Expand|Select|Wrap|Line Numbers
  1. Private Sub VendNo_AfterUpdate()
  2.  
  3.    Me.VendName = DLookup("[VendName]","tblVendors","[VendNo]=" Me.VendNo
  4.  
  5. End Sub
  6.  
I copied and pasted this directly as shown and I get a syntax error.
AO3
Dec 11 '06 #4

P: 78
I copied and pasted this directly as shown and I get a syntax error.
AO3
I have since changed it to look like this now I get a runtime error 2001 You have canceled the previous operation.

Expand|Select|Wrap|Line Numbers
  1.  Private Sub VendNo_AfterUpdate()
  2. Me.VendName = DLookup("[VendName]", "tblVendors", " [VendNo]= Me.VendNo")
  3.  
  4. End Sub 
Dec 11 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
I copied and pasted this directly as shown and I get a syntax error.
AO3
Sorry my error. See the following ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub VendNo_AfterUpdate()
  2.  
  3.    Me.VendName = DLookup("[VendName]","tblVendors","[VendNo]=" & Me.VendNo)
  4.  
  5. End Sub
Mary
Dec 11 '06 #6

P: 78
Thanks Mary For the assistance!! it worked great.

just a quick question on Dlookup though......Is it possible to Lookup between a range of numbers? in the same form I want to look up an AQL level by the number of samples submitted. i have a table AQL with MinSamp and MaxSamp. i have replicated the info you gave me earlier using the following
Expand|Select|Wrap|Line Numbers
  1.  Private Sub RecQTY_AfterUpdate()
  2. Me.ReqAQL = DLookup("[ReqAQL]", "tblAQL", "[SampMin]>=" & Me.RecQTY)
  3.  
  4. End Sub 
however I keep getting inaccurate results unless I type in the specific number in SampMin. the table is as specified

SampMin SampMax ReqAQL
1 1 1
2 8 2
9 15 3
16 25 5


so on and so forth. If I type in 9 in RecQTy then ReqAQL populates with 3 but if i type in 10 then it populates with 5. I know in excel how I would do this....unfortunately this is not excel. Thanks for any help you can provide. I am just a newbie and I apologize if I am annoying.
Dec 12 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub RecQTY_AfterUpdate()
  2.  
  3.    Me.ReqAQL = DLookup("[ReqAQL]", "tblAQL", "[SampMin]>=" & Me.RecQTY & " AND " & Me.RecQTY & "<=
  4. [SampMax]")
  5.  
  6. End Sub 
  7.  
Mary
Dec 12 '06 #8

P: 78
Nope..... it still returns the wrong number...unless I am doing something wrong.. When I copied and pasted I had one too many quotation marks at the end of the statement so I deleted them and it would compile. if I type the exact SampMin then I get the correct number but anything in the range up to the next SampMin I get the next value up in the sequence as before.
Dec 12 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
It's definately not the last quotation mark. Did you copy and paste it exactly as it is below?

Expand|Select|Wrap|Line Numbers
  1. Private Sub RecQTY_AfterUpdate()
  2.  
  3.    Me.ReqAQL = DLookup("[ReqAQL]", "tblAQL", "[SampMin]>=" & Me.RecQTY & " AND " & Me.RecQTY & "<=
  4. [SampMax]")
  5.  
  6. End Sub 
  7.  
Dec 12 '06 #10

P: 78
I copied and pasted exactly as below

Expand|Select|Wrap|Line Numbers
  1.  Private Sub RecQTY_AfterUpdate()
  2.  
  3.      Me.ReqAQL = DLookup("[ReqAQL]", "tblAQL", "[SampMin]>=" & Me.RecQTY & " AND " & Me.RecQTY & "<=[SampMax]")
  4.  
  5. End Sub
Table data is as follows

SampMin SampMax ReqAQL
2 8 2
9 15 3
16 25 5
26 50 8
51 90 13
91 150 20
151 280 32
281 500 50
501 1200 80
1201 3200 125
3201 10000 200
10001 35000 315
35001 150000 500
150001 500000 800
500001 999999999 1250
1 1 1

When I type 280 in RecQTY the result is 50 and should be 32. if I type 279 I get 50. If I type 151 I get 32.....

AO3
Dec 12 '06 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub RecQTY_AfterUpdate()
  3.  
  4.    Me.ReqAQL = DLookup("[ReqAQL]", "tblAQL", "[SampMin]<=" & Me.RecQTY & " AND [SampMax]>=" & Me.RecQTY)
  5.  
  6. End Sub
  7.  
Mary
Dec 12 '06 #12

Post your reply

Sign in to post your reply or Sign up for a free account.