AutoFill Fields in Form

  • Thread starter Thread starter Pete
  • Start date Start date
P

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
 
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.
Click to expand...

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.
 
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
Click to expand...
("ProductName", "tblProductList", "StockID = " &
Me.StockID)
(if your StockID field is numeric, or)
varProductName = DLookup
Click to expand...
("ProductName", "tblProductList", "StockID = ' "
& Me.StockID & " ' ")
(if your StockID field is text)

HTH,
Nikos

Pete said:
I'm new to this and my opportunity is -

I have a form wherein the StockID field is linked to a
Click to expand...
table as a lookup
so
the stockID can be selected from a drop down list
Click to expand...
(combo box) . What I
want
to do is once the StockID is selected, upon exit -
Click to expand...
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
Click to expand...
("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
Click to expand...
StockID - The fields
are
being filled with the same information regardless of
Click to expand...
the StockID
selected.
What do I need to do to have the fields filled based on the StockID
selected.



Thanks

Pete
Click to expand...


.
Click to expand...
 
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
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.
Click to expand...

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?
 
Back
Top