AutoFill Fields in Form

[Pete]

I'm new to this and my opportunity is -

I have a form wherein the StockID field is linked to a table as a lookup so
the stockID can be selected from a drop down list (combo box) . What I want
to do is once the StockID is selected, upon exit - have 4 other fields auto
filled.

With the below code -

Option Compare Database

Private Sub StockID_Exit(Cancel As Integer)

Dim varProductName, ProductDescription, UofM, TempCtrl As Variant

varProductName = DLookup("ProductName", "tblProductList", "StockID =
[StockID]")

varProductDescription = DLookup("ProductDescription", "tblProductList",
"StockID = [StockID]")

varUofM = DLookup("UofM", "tblProductList", "StockID =[StockID]")

varTempCtrl = DLookup("TempCtrl", "tblProductList", "StockID
=[StockID]")

If (Not IsNull(varProductName)) Then Me![ProductName] = varProductName

If (Not IsNull(varProductDescription)) Then Me![ProductDescription] =
varProductDescription

If (Not IsNull(varUofM)) Then Me![UofM] = varUofM

If (Not IsNull(varTempCtrl)) Then Me![TempCtrl] = varTempCtrl

End Sub



I get the auto fill - but it does not fill based on StockID - The fields are
being filled with the same information regardless of the StockID selected.

What do I need to do to have the fields filled based on the StockID
selected.



Thanks

Pete

 

[Nikos Yannacopoulos]

Pete,

Change the criteria part of your DLookup expressions as follows:

varProductName = DLookup("ProductName", "tblProductList", "StockID = " &
Me.StockID)
(if your StockID field is numeric, or)
varProductName = DLookup("ProductName", "tblProductList", "StockID = ' "
& Me.StockID & " ' ")
(if your StockID field is text)

HTH,
Nikos

 

[John Vinson]

I'm new to this and my opportunity is -

I have a form wherein the StockID field is linked to a table as a lookup so
the stockID can be selected from a drop down list (combo box) . What I want
to do is once the StockID is selected, upon exit - have 4 other fields auto
filled.

Well... you may want to reconsider.

The whole POINT of a relational database is that it's a way of storing
data so that it avoids redundancy. If you have the ProductName and
Description in the product table, then there is no need - in fact many
drawbacks! - to copy them to any other table. Instead, you can use a
Query linking the two tables to bring the fields from the two tables
together.

The Lookup Wizard is (in my opinion) misdesigned and misleading. It
gives you the *false* impression that you can include a field from one
table in another table. What you see is NOT what's in the table! The
Lookup wizard also encourges you to use table datasheets routinely:
this is also unwise, as they are of very limited utility.

Use a Form instead; it can be based on a Query linking the two tables,
or you can use textboxes to pull fields directly from a combo box:

=cboStockID.Column(n)

where n is the zero-based subscript of the field in the combo's query
will display that field on the Form, without any need to store it
redundantly.

 

[Guest]

Nikos

Thank you very much. Your assistance is apprciated. Its
works well now. I just needed to type your information
correctly - a space makes all the difference. Thanks.

Pete

-----Original Message-----
Pete,

Change the criteria part of your DLookup expressions as follows:

varProductName = DLookup

("ProductName", "tblProductList", "StockID = " &

Me.StockID)
(if your StockID field is numeric, or)
varProductName = DLookup

("ProductName", "tblProductList", "StockID = ' "

& Me.StockID & " ' ")
(if your StockID field is text)

HTH,
Nikos

I'm new to this and my opportunity is -

I have a form wherein the StockID field is linked to a

table as a lookup
so

the stockID can be selected from a drop down list

(combo box) . What I
want

to do is once the StockID is selected, upon exit -

have 4 other fields
auto

filled.

With the below code -

Option Compare Database

Private Sub StockID_Exit(Cancel As Integer)

Dim varProductName, ProductDescription, UofM, TempCtrl As Variant

varProductName = DLookup ("ProductName", "tblProductList", "StockID =
[StockID]")

varProductDescription = DLookup

("ProductDescription",

"tblProductList",
"StockID = [StockID]")

varUofM = DLookup ("UofM", "tblProductList", "StockID =[StockID]")

varTempCtrl = DLookup ("TempCtrl", "tblProductList", "StockID
=[StockID]")

If (Not IsNull(varProductName)) Then Me! [ProductName] = varProductName

If (Not IsNull(varProductDescription)) Then Me! [ProductDescription] =
varProductDescription

If (Not IsNull(varUofM)) Then Me![UofM] = varUofM

If (Not IsNull(varTempCtrl)) Then Me![TempCtrl] = varTempCtrl

End Sub



I get the auto fill - but it does not fill based on

StockID - The fields
are

being filled with the same information regardless of

the StockID
selected.

What do I need to do to have the fields filled based on the StockID
selected.



Thanks

Pete

.

 

[Guest]

John
Thank you for the information. I will take you up on the
invite to the chap room at my first opportunity to learn
more- ho9wever I work most nights - when the chat is
ongoing.
Pete

 

[John Vinson]

John
Thank you for the information. I will take you up on the
invite to the chap room at my first opportunity to learn
more- ho9wever I work most nights - when the chat is
ongoing.

Erm? What timezone are you in? The chat is on Tuesday morning (in the
US), or Thursday afternoon. The Tuesday chat would be in the evening
in Europe - is that where you are?