How to Pass Value from a Combo Box as Variable in Function

[sara]

I am learning how to use simple functions to make my apps more powerful
and efficient.

On one screen, I want to populate field B: ItemDescription by looking
up the ItemDescription in the Items Table. I would use the key to the
Items Table from the selection the user made in the combo box.

SO: User chooses an item from the combo box whose value is "3". I
want to look up the ItemDescription of the Item with the Key "3" and
show that to the user in another field at the bottom of the form.

Here's what I have:

The control Source of the field ItemDescription is:
=fcnGetItemDescrip([cboGarment].[Column](0))
(The combo box is named cboGarment and the ItemKey is hidden in the
first column. The combo box is populating properly.)

The problem (I think) is that lngCustItemID is null. When I ask for its
value in the immediate window, I get NULL. Same as when I hover over
lngCustItemID in the code.

However, if I put another field on the form and have as its control
source the value of Column 0, it and the field ItemDescription both
populate properly.

Public Function fcnGetItemDescrip(lngCustItemID) As String

' Returns the Item Description based on the custItemID selected
fcnGetItemDescrip = DLookup _
("[ItemDescription]", "[tblCustomerItems]", "[CustItemID]= " &
lngCustItemID)
End Function

Thanks very much
Sara

[Allen Browne]

Hi Sara. I take it that the purpose of this is a learning exercise.

In a real database, you would not have an ItemDescription field in your
orders table, unless you wanted the user to be able to type into this text
box so they could:
a) enter a description for something that is not a product, or
b) describe a "widget" as a "watzit" for this particular order.

The problem with the code is probably a timing one. After you select an item
in the combo, Access does not update the calcuated field. You can force it
to do so, with:
Forms![NameOfYourFormHere].Recalc
At this point, the calculated field based on your function should call the
function again.

BTW, the name lngCustItemID suggests that this is a Long, but to get a Long,
you would need to code:
Public Function fcnGetItemDescrip(lngCustItemID As Long) As String
That would fail when the combo is Null, because a Long in VBA cannot be
Null. You need the argument to be a Variant, so it would be better to use:
Public Function fcnGetItemDescrip(varCustItemID As Variant) As String
When the value is Null, the 3rd argument in your DLookup() expression
becomes just:
[CustItemID]=
which does not make sense to Access, so you will get an error.
To avoid that, test it before the DLookup() line:
If Not IsNull(varCustItemID)
or at least supply something for null in the 3rd argument:
"[CustItemID] = " & Nz(varCustItemID, 0)

Of couse, if you were not practicing with code, you could just use a Control
Source of:
=DLookup("[ItemDescription]", "[tblCustomerItems]", "[CustItemID]= " &
Nz([cboGarment],0))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"sara" <sa*******@yahoo.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

I am learning how to use simple functions to make my apps more powerful
and efficient.

On one screen, I want to populate field B: ItemDescription by looking
up the ItemDescription in the Items Table. I would use the key to the
Items Table from the selection the user made in the combo box.

SO: User chooses an item from the combo box whose value is "3". I
want to look up the ItemDescription of the Item with the Key "3" and
show that to the user in another field at the bottom of the form.

Here's what I have:

The control Source of the field ItemDescription is:
=fcnGetItemDescrip([cboGarment].[Column](0))
(The combo box is named cboGarment and the ItemKey is hidden in the
first column. The combo box is populating properly.)

The problem (I think) is that lngCustItemID is null. When I ask for its
value in the immediate window, I get NULL. Same as when I hover over
lngCustItemID in the code.

However, if I put another field on the form and have as its control
source the value of Column 0, it and the field ItemDescription both
populate properly.

Public Function fcnGetItemDescrip(lngCustItemID) As String

' Returns the Item Description based on the custItemID selected
fcnGetItemDescrip = DLookup _
("[ItemDescription]", "[tblCustomerItems]", "[CustItemID]= " &
lngCustItemID)
End Function

Thanks very much
Sara