Dlookup & Combobox problem

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

Hi everyone!
Thanks, btw for all the great previous help, ya'll are a
great tool in learning the in & outs of Access. And I hope
you all will also be able to help me once again.

Problem:
I have a combobox & several textboxes on a subform. I'd
like for the select in the combobox to "automatically"
fill-in the correct data in the correct text boxes. I have
the code below, but it seems to be giving me some issues.
Namely entering the incorrect data in the textboxes (it
seems to be stuck on showing just one or two entries
regardless of what is chosen in the combobox.

Combobox name: Product Name
Textbox1: Description
Textbox2: UnitPrice
The table in which I'd like to 'pull' all the info
is: 'Product'

Code:

Private Sub ProductName_AfterUpdate()
' Update Description controls based on value selected in
ProductName combo box.

Dim varDesc As Variant

varDesc = DLookup("[Description]", "Product")
varUnitPrice = DLookup("UnitPrice", "Product")
If (Not IsNull(varDesc)) Then Me![Description] = varDesc
If (Not IsNull(varUnitPrice)) Then Me![UnitPrice] =
varUnitPrice

End Sub


Thanks for all your help! =)!!
sam
 
Hi Sam,

You need to add criteria to your Dlookup function so that
it finds the appropriate record in your Products table.
Otherwise it will just return the first record. The
criteria is listed as the third argument in the
function. You have to build the string by combining a
string with the value of a field on your form. This will
vary a little depending if your key for the Product table
is a string or is numeric. For example, if using a
numeric ID:

varDesc = DLookup("[Description]", "Product","[ProductID]
=" & Me.[Product Name])

But, if a string, you need to include apostrophe's in
your statement as follows:

varDesc = DLookup
("[Description]", "Product","[ProductName] = '" & Me.
[Product Name] & "'")

Note that for both of the above I just assumed a field
name for the ID field, you would have to adjust this to
match the field name in your Products table that contains
the key value that you are comparing to your combo box
value.

FYI, there is another way to do this which would be to
have all of the fields that you need included in the
combo box rowsource. Then, after the combo box is
updated you assign the combo box column 1 value to one of
the other fields, the column 2 value to another field,
etc (the combo box is bound to column 0).

Hope this helps.

-Ted
 
The syntax for your DLookup function is technically
correct, but you haven't specified which product to lookup.
Instead your DLookup() should have criteria from which to
choose the correct record, without this criteria - you are
getting only the first record it finds. In your code below
"Product" is the name of the table - this may or may not
be true. The structure is something like this:

Dlookup( field w/ value you want , <- comma
name of table or query , <- comma
criteria string)

EXAMPLE:
(I think) what you want to do is look up the value in the
field (Description) that is in the record in your table
(Table1) where the field (Product) is equal to the value
that was selected in your combobox (cboProduct).

varDesc = DLookup("[Description]", "Table1", _
"[Product] = " & Me!cboProduct )

Here the value in cboProduct is assumed to be a string. I
am using concatenation to combine the literal string with
the value in the combo box. Depending on the data type,
you may have to use single quotes in the criteria string.

See Access help for further explanation of the DLookup()
function and you may want to look up Criteria > Visual
Basic expressions > Quotation Marks in Strings.

jmonty
 
Sam said:
Hi everyone!
Thanks, btw for all the great previous help, ya'll are a
great tool in learning the in & outs of Access. And I hope
you all will also be able to help me once again.

Problem:
I have a combobox & several textboxes on a subform. I'd
like for the select in the combobox to "automatically"
fill-in the correct data in the correct text boxes. I have
the code below, but it seems to be giving me some issues.
Namely entering the incorrect data in the textboxes (it
seems to be stuck on showing just one or two entries
regardless of what is chosen in the combobox.

Combobox name: Product Name
Textbox1: Description
Textbox2: UnitPrice
The table in which I'd like to 'pull' all the info
is: 'Product'

Code:

Private Sub ProductName_AfterUpdate()
' Update Description controls based on value selected in
ProductName combo box.

Dim varDesc As Variant

varDesc = DLookup("[Description]", "Product")
varUnitPrice = DLookup("UnitPrice", "Product")
If (Not IsNull(varDesc)) Then Me![Description] = varDesc
If (Not IsNull(varUnitPrice)) Then Me![UnitPrice] =
varUnitPrice

End Sub
Click to expand...

There is no need for this code, simply build your combobox using the "find a
record based on..." (something like that), Access will build the one line
that is needed to move to that record.
 
Sam, I have had to do the same as you. What I did was
build a query with the fields from the table(s) that I
needed. Attach the query to the combobox rowsource. In
the AfterUpdate event of the combobox indicate the txtbox
and the column of the query that applies. Query columns
start with zero and go from there. It has been generally
a short one liner for the txtbox info.
Whenever I change the info in the combobox, so does the
info in the other txtboxes.
*** John
 
Use John's method.

It is much better and quicker because the data has already been queried when
the user selects a value in the combobox.

Each DLookUp creates additional work.

Rod Scoullar
 
Just an update, I'm stil struggling with the proper
coding, so I'll re-post if I give up and compeletly can't
figure it out. Mike, I tried your idea, and it didn't
work. But thanks for all of your help! =)
sam
-----Original Message-----

Hi everyone!
Thanks, btw for all the great previous help, ya'll are a
great tool in learning the in & outs of Access. And I hope
you all will also be able to help me once again.

Problem:
I have a combobox & several textboxes on a subform. I'd
like for the select in the combobox to "automatically"
fill-in the correct data in the correct text boxes. I have
the code below, but it seems to be giving me some issues.
Namely entering the incorrect data in the textboxes (it
seems to be stuck on showing just one or two entries
regardless of what is chosen in the combobox.

Combobox name: Product Name
Textbox1: Description
Textbox2: UnitPrice
The table in which I'd like to 'pull' all the info
is: 'Product'

Code:

Private Sub ProductName_AfterUpdate()
' Update Description controls based on value selected in
ProductName combo box.

Dim varDesc As Variant

varDesc = DLookup("[Description]", "Product")
varUnitPrice = DLookup("UnitPrice", "Product")
If (Not IsNull(varDesc)) Then Me![Description] = varDesc
If (Not IsNull(varUnitPrice)) Then Me![UnitPrice] =
varUnitPrice

End Sub
Click to expand...

There is no need for this code, simply build your combobox using the "find a
record based on..." (something like that), Access will build the one line
that is needed to move to that record.


.
Click to expand...
 
Sam,
Look at the column property of combo boxes. This test worked on onof my
forms.

Private Sub Combo43_AfterUpdate()
Text52 = Combo43.Column(1)
End Sub
BR
Ted Stilwell
Sam said:
Just an update, I'm stil struggling with the proper
coding, so I'll re-post if I give up and compeletly can't
figure it out. Mike, I tried your idea, and it didn't
work. But thanks for all of your help! =)
sam
-----Original Message-----

Hi everyone!
Thanks, btw for all the great previous help, ya'll are a
great tool in learning the in & outs of Access. And I hope
you all will also be able to help me once again.

Problem:
I have a combobox & several textboxes on a subform. I'd
like for the select in the combobox to "automatically"
fill-in the correct data in the correct text boxes. I have
the code below, but it seems to be giving me some issues.
Namely entering the incorrect data in the textboxes (it
seems to be stuck on showing just one or two entries
regardless of what is chosen in the combobox.

Combobox name: Product Name
Textbox1: Description
Textbox2: UnitPrice
The table in which I'd like to 'pull' all the info
is: 'Product'

Code:

Private Sub ProductName_AfterUpdate()
' Update Description controls based on value selected in
ProductName combo box.

Dim varDesc As Variant

varDesc = DLookup("[Description]", "Product")
varUnitPrice = DLookup("UnitPrice", "Product")
If (Not IsNull(varDesc)) Then Me![Description] = varDesc
If (Not IsNull(varUnitPrice)) Then Me![UnitPrice] =
varUnitPrice

End Sub
Click to expand...

There is no need for this code, simply build your combobox using the "find a
record based on..." (something like that), Access will build the one line
that is needed to move to that record.


.
Click to expand...
Click to expand...
 
So, using your example, would the Text52
say '=Combo43.column(1)', 'boundname' (from the
table/query that the form is bult from, the one your
trying to find) in the Control Source? or should it be
unbound?

so I'll give your idea a go, thanks for you help! =).
sam
-----Original Message-----
Sam,
Look at the column property of combo boxes. This test worked on onof my
forms.

Private Sub Combo43_AfterUpdate()
Text52 = Combo43.Column(1)
End Sub
BR
Ted Stilwell
Just an update, I'm stil struggling with the proper
coding, so I'll re-post if I give up and compeletly can't
figure it out. Mike, I tried your idea, and it didn't
work. But thanks for all of your help! =)
sam
-----Original Message-----

Hi everyone!
Thanks, btw for all the great previous help, ya'll are a
great tool in learning the in & outs of Access. And I hope
you all will also be able to help me once again.

Problem:
I have a combobox & several textboxes on a subform. I'd
like for the select in the combobox to "automatically"
fill-in the correct data in the correct text boxes. I have
the code below, but it seems to be giving me some issues.
Namely entering the incorrect data in the textboxes (it
seems to be stuck on showing just one or two entries
regardless of what is chosen in the combobox.

Combobox name: Product Name
Textbox1: Description
Textbox2: UnitPrice
The table in which I'd like to 'pull' all the info
is: 'Product'

Code:

Private Sub ProductName_AfterUpdate()
' Update Description controls based on value selected in
ProductName combo box.

Dim varDesc As Variant

varDesc = DLookup("[Description]", "Product")
varUnitPrice = DLookup("UnitPrice", "Product")
If (Not IsNull(varDesc)) Then Me![Description] = varDesc
If (Not IsNull(varUnitPrice)) Then Me![UnitPrice] =
varUnitPrice

End Sub

There is no need for this code, simply build your combobox using the "find a
record based on..." (something like that), Access will build the one line
that is needed to move to that record.


.
Click to expand...
Click to expand...


.
Click to expand...
 
Yea!!! *hugs* thanks Ted, you're a life saver! =)
(it worked using your code and the bound textbox, btw)
I was trying to use the:
Me!Textbox = Combobox.Column(1)
silly me... much thanks again!!
Sam
-----Original Message-----
Sam,
Look at the column property of combo boxes. This test worked on onof my
forms.

Private Sub Combo43_AfterUpdate()
Text52 = Combo43.Column(1)
End Sub
BR
Ted Stilwell
Just an update, I'm stil struggling with the proper
coding, so I'll re-post if I give up and compeletly can't
figure it out. Mike, I tried your idea, and it didn't
work. But thanks for all of your help! =)
sam
-----Original Message-----

Hi everyone!
Thanks, btw for all the great previous help, ya'll are a
great tool in learning the in & outs of Access. And I hope
you all will also be able to help me once again.

Problem:
I have a combobox & several textboxes on a subform. I'd
like for the select in the combobox to "automatically"
fill-in the correct data in the correct text boxes. I have
the code below, but it seems to be giving me some issues.
Namely entering the incorrect data in the textboxes (it
seems to be stuck on showing just one or two entries
regardless of what is chosen in the combobox.

Combobox name: Product Name
Textbox1: Description
Textbox2: UnitPrice
The table in which I'd like to 'pull' all the info
is: 'Product'

Code:

Private Sub ProductName_AfterUpdate()
' Update Description controls based on value selected in
ProductName combo box.

Dim varDesc As Variant

varDesc = DLookup("[Description]", "Product")
varUnitPrice = DLookup("UnitPrice", "Product")
If (Not IsNull(varDesc)) Then Me![Description] = varDesc
If (Not IsNull(varUnitPrice)) Then Me![UnitPrice] =
varUnitPrice

End Sub

There is no need for this code, simply build your combobox using the "find a
record based on..." (something like that), Access will build the one line
that is needed to move to that record.


.
Click to expand...
Click to expand...


.
Click to expand...
 
Sam said:
Just an update, I'm stil struggling with the proper
coding, so I'll re-post if I give up and compeletly can't
figure it out. Mike, I tried your idea, and it didn't
work. But thanks for all of your help! =)
sam
Click to expand...

I'm not sure why it would not work, I use it all the time and your method is
strongly discouraged in relational design.
 
Back
Top