Auto populate form control on data entry

alphaomega3

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

msquared

Quote:

Originally Posted by alphaomega3

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

 
Private Sub VendNo_AfterUpdate()
 
   Me.VendName = DLookup("[VendName]","tblVendors","[VendNo]=" Me.VendNo
 
End Sub

Mary

msquared

"The title of this thread has been changed to make the problem clearer to experts and others searching for similar threads."

MODERATOR

alphaomega3

Quote:

Originally Posted by mmccarthy

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

Expand|Select|Wrap|Line Numbers

 
Private Sub VendNo_AfterUpdate()
 
   Me.VendName = DLookup("[VendName]","tblVendors","[VendNo]=" Me.VendNo
 
End Sub

I copied and pasted this directly as shown and I get a syntax error.
AO3

alphaomega3

Quote:

Originally Posted by alphaomega3

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

  Private Sub VendNo_AfterUpdate()

Me.VendName = DLookup("[VendName]", "tblVendors", " [VendNo]= Me.VendNo")
 
End Sub

msquared

Quote:

Originally Posted by alphaomega3

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

 
Private Sub VendNo_AfterUpdate()
 
   Me.VendName = DLookup("[VendName]","tblVendors","[VendNo]=" & Me.VendNo)
 
End Sub

Mary

alphaomega3

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

  Private Sub RecQTY_AfterUpdate()

Me.ReqAQL = DLookup("[ReqAQL]", "tblAQL", "[SampMin]>=" & Me.RecQTY)
 
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.

msquared

Try this ...

Expand|Select|Wrap|Line Numbers

 
Private Sub RecQTY_AfterUpdate()
 
   Me.ReqAQL = DLookup("[ReqAQL]", "tblAQL", "[SampMin]>=" & Me.RecQTY & " AND " & Me.RecQTY & "<=

[SampMax]")
 
End Sub

Mary

alphaomega3

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.

msquared

It's definately not the last quotation mark. Did you copy and paste it exactly as it is below?

Expand|Select|Wrap|Line Numbers

 
Private Sub RecQTY_AfterUpdate()
 
   Me.ReqAQL = DLookup("[ReqAQL]", "tblAQL", "[SampMin]>=" & Me.RecQTY & " AND " & Me.RecQTY & "<=

[SampMax]")
 
End Sub

alphaomega3

I copied and pasted exactly as below

Expand|Select|Wrap|Line Numbers

  Private Sub RecQTY_AfterUpdate()
 
     Me.ReqAQL = DLookup("[ReqAQL]", "tblAQL", "[SampMin]>=" & Me.RecQTY & " AND " & Me.RecQTY & "<=[SampMax]")
 
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

msquared

Try this ...

Expand|Select|Wrap|Line Numbers

  
Private Sub RecQTY_AfterUpdate()
 
   Me.ReqAQL = DLookup("[ReqAQL]", "tblAQL", "[SampMin]<=" & Me.RecQTY & " AND [SampMax]>=" & Me.RecQTY)
 
End Sub

Mary