Combobox event code

[Guest]

Hello all,
The following code is returning the same data, the item description in the
first row of the lookup table, no matter what I change Combo14 to... what
have I done wrong?


Private Sub Combo14_Change()

Dim varDesc As Variant
varDesc = DLookup("ItemDesc", "lkpItems", ItemNum = Combo14)
If (Not IsNull(varDesc)) Then Me![ItemDesc] = varDesc

End Sub

 

[Arvin Meyer [MVP]]

Use the AfterUpdate event of the combo, not the change event.

 

[Guest]

Now nothing is happening, Me![ItemDesc] remains blank.

Use the AfterUpdate event of the combo, not the change event.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hello all,
The following code is returning the same data, the item description in the
first row of the lookup table, no matter what I change Combo14 to... what
have I done wrong?


Private Sub Combo14_Change()

Dim varDesc As Variant
varDesc = DLookup("ItemDesc", "lkpItems", ItemNum = Combo14)
If (Not IsNull(varDesc)) Then Me![ItemDesc] = varDesc

End Sub

 

[Douglas J. Steele]

You need some quotes in the Where condition of the DLookup:

varDesc = DLookup("ItemDesc", "lkpItems", "ItemNum = " & Combo14)

assuming ItemNum is a numeric field. If it's text, you need

varDesc = DLookup("ItemDesc", "lkpItems", "ItemNum = '" & Combo14 & "'")

Exagerated for clarity, that's

varDesc = DLookup("ItemDesc", "lkpItems", "ItemNum = ' " & Combo14 & " ' ")

Now, the fact that you're not getting an error on what you had indicates to
me that you haven't told Access to require the declaration of all variables.
That means it saw ItemNum as a variable (which would be initialized to
Null), and was comparing the value of the variable to the value of the combo
box. In my opinion, you should turn on mandatory declaration. You do this by
going into the VB Editor, selecting Tools | Options from the menu and
ensuring that the "Require Variable Declaration" check box on the Module tab
is checked. What this will do is add a line "Option Explicit" near the top
of each new module. (It won't do it for existing modules, though: you'll
need to go in and add that line yourself to each existing module)

Arvin's advice about putting it in the AfterUpdate event still holds.

 

[Guest]

Coming at it another way, I changed the Row Source of Combo14 to:

SELECT [lkpItems].[ItemID], [lkpItems].[ItemNum], [lkpItems].[ItemDesc] FROM
lkpItems;

And after update to simply:

Private Sub Combo14_AfterUpdate()

Me![ItemDesc] = Combo14.Column(2)

End Sub

This is working. I note that the looked up ItemDesc is being strored in all
its verbosity in my main table, while I believe I only want to store the
ItemID in my main table. This is the way Northwind is doing it, but that
seems like it will create an unneccesarily large database...

Any suggestions?

Thanks

Use the AfterUpdate event of the combo, not the change event.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hello all,
The following code is returning the same data, the item description in the
first row of the lookup table, no matter what I change Combo14 to... what
have I done wrong?


Private Sub Combo14_Change()

Dim varDesc As Variant
varDesc = DLookup("ItemDesc", "lkpItems", ItemNum = Combo14)
If (Not IsNull(varDesc)) Then Me![ItemDesc] = varDesc

End Sub

 

[Guest]

ItemNum is text. The new code does nothing to ItemDesc, it remains unchanged.

Private Sub Combo14_AfterUpdate()

Dim varDesc As Variant
varDesc = DLookup("ItemDesc", "lkpItems", "ItemNum = '" & Combo14 & "'")
If (Not IsNull(varDesc)) Then Me![ItemDesc] = varDesc

'Me![ItemDesc] = Combo14.Column(2)

End Sub

The only thing I've gotten to work is to change the Row Source of Combo14 to
include the third column, and enable the simple value assignment you see
remarked out in the code above.

Is there a problem with doing it this way?

(I made the options changes you recommended, thank you.)

You need some quotes in the Where condition of the DLookup:

varDesc = DLookup("ItemDesc", "lkpItems", "ItemNum = " & Combo14)

assuming ItemNum is a numeric field. If it's text, you need

varDesc = DLookup("ItemDesc", "lkpItems", "ItemNum = '" & Combo14 & "'")

Exagerated for clarity, that's

varDesc = DLookup("ItemDesc", "lkpItems", "ItemNum = ' " & Combo14 & " ' ")

Now, the fact that you're not getting an error on what you had indicates to
me that you haven't told Access to require the declaration of all variables.
That means it saw ItemNum as a variable (which would be initialized to
Null), and was comparing the value of the variable to the value of the combo
box. In my opinion, you should turn on mandatory declaration. You do this by
going into the VB Editor, selecting Tools | Options from the menu and
ensuring that the "Require Variable Declaration" check box on the Module tab
is checked. What this will do is add a line "Option Explicit" near the top
of each new module. (It won't do it for existing modules, though: you'll
need to go in and add that line yourself to each existing module)

Arvin's advice about putting it in the AfterUpdate event still holds.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello all,
The following code is returning the same data, the item description in the
first row of the lookup table, no matter what I change Combo14 to... what
have I done wrong?


Private Sub Combo14_Change()

Dim varDesc As Variant
varDesc = DLookup("ItemDesc", "lkpItems", ItemNum = Combo14)
If (Not IsNull(varDesc)) Then Me![ItemDesc] = varDesc

End Sub

 

[Douglas J. Steele]

Using the Column collection would, in fact, be the recommended approach.

The only thing I can think of for why the DLookup didn't work is that the
bound column of Combo14 isn't returning what you think it is.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

ItemNum is text. The new code does nothing to ItemDesc, it remains
unchanged.

Private Sub Combo14_AfterUpdate()

Dim varDesc As Variant
varDesc = DLookup("ItemDesc", "lkpItems", "ItemNum = '" & Combo14 & "'")
If (Not IsNull(varDesc)) Then Me![ItemDesc] = varDesc

'Me![ItemDesc] = Combo14.Column(2)

End Sub

The only thing I've gotten to work is to change the Row Source of Combo14
to
include the third column, and enable the simple value assignment you see
remarked out in the code above.

Is there a problem with doing it this way?

(I made the options changes you recommended, thank you.)

You need some quotes in the Where condition of the DLookup:

varDesc = DLookup("ItemDesc", "lkpItems", "ItemNum = " & Combo14)

assuming ItemNum is a numeric field. If it's text, you need

varDesc = DLookup("ItemDesc", "lkpItems", "ItemNum = '" & Combo14 & "'")

Exagerated for clarity, that's

varDesc = DLookup("ItemDesc", "lkpItems", "ItemNum = ' " & Combo14 & " '
")

Now, the fact that you're not getting an error on what you had indicates
to
me that you haven't told Access to require the declaration of all
variables.
That means it saw ItemNum as a variable (which would be initialized to
Null), and was comparing the value of the variable to the value of the
combo
box. In my opinion, you should turn on mandatory declaration. You do this
by
going into the VB Editor, selecting Tools | Options from the menu and
ensuring that the "Require Variable Declaration" check box on the Module
tab
is checked. What this will do is add a line "Option Explicit" near the
top
of each new module. (It won't do it for existing modules, though: you'll
need to go in and add that line yourself to each existing module)

Arvin's advice about putting it in the AfterUpdate event still holds.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello all,
The following code is returning the same data, the item description in
the
first row of the lookup table, no matter what I change Combo14 to...
what
have I done wrong?


Private Sub Combo14_Change()

Dim varDesc As Variant
varDesc = DLookup("ItemDesc", "lkpItems", ItemNum = Combo14)
If (Not IsNull(varDesc)) Then Me![ItemDesc] = varDesc

End Sub

 

[Arvin Meyer [MVP]]

Unbind the ItemDesc textbox from the source, so that it only displays the
value, not stores it. You'll need to duplicate the code:

Me![ItemDesc] = Combo14.Column(2)

in the form's Current event as well so that it will display existing records
properly.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Coming at it another way, I changed the Row Source of Combo14 to:

SELECT [lkpItems].[ItemID], [lkpItems].[ItemNum], [lkpItems].[ItemDesc]
FROM
lkpItems;

And after update to simply:

Private Sub Combo14_AfterUpdate()

Me![ItemDesc] = Combo14.Column(2)

End Sub

This is working. I note that the looked up ItemDesc is being strored in
all
its verbosity in my main table, while I believe I only want to store the
ItemID in my main table. This is the way Northwind is doing it, but that
seems like it will create an unneccesarily large database...

Any suggestions?

Thanks

Use the AfterUpdate event of the combo, not the change event.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hello all,
The following code is returning the same data, the item description in
the
first row of the lookup table, no matter what I change Combo14 to...
what
have I done wrong?


Private Sub Combo14_Change()

Dim varDesc As Variant
varDesc = DLookup("ItemDesc", "lkpItems", ItemNum = Combo14)
If (Not IsNull(varDesc)) Then Me![ItemDesc] = varDesc

End Sub