using the openform method with a combobox value

[BigNasty]

i Have a form that allows the user to select a value from a combo box.
The combo box references a table that does a lookup in a another table
to get its value. When the user selects the value from the combo box,
they would hit the search button and a openform method would open the
original form and allow the user to only see the records that match the
criteria from the combo box.

The problem that im having is that when the user selects the combo box,
and i place that value into the search string. The value returend from
the combo box is the numeric value from the lookup table. How do i
convert the value back to the friendly nbame or am i going about this
all wrong?

On Error GoTo Err_Command12_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim stSelection As String


stDocName = "Engagement"
Combo1.SetFocus
stSelection = Combo1.Text


'stLinkCriteria = "[Line of Business]=stSelection"



DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Marshall Barton]

i Have a form that allows the user to select a value from a combo box.
The combo box references a table that does a lookup in a another table
to get its value. When the user selects the value from the combo box,
they would hit the search button and a openform method would open the
original form and allow the user to only see the records that match the
criteria from the combo box.

The problem that im having is that when the user selects the combo box,
and i place that value into the search string. The value returend from
the combo box is the numeric value from the lookup table. How do i
convert the value back to the friendly nbame or am i going about this
all wrong?

On Error GoTo Err_Command12_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim stSelection As String

stDocName = "Engagement"
Combo1.SetFocus
stSelection = Combo1.Text

'stLinkCriteria = "[Line of Business]=stSelection"
DoCmd.OpenForm stDocName, , , stLinkCriteria

It's not clear if you are doing this the wrong way or not.
Normally, you want the PK value from the lookup table
because the friendly name should only be used for user
display purposes. Unless you are using natural keys (in
which case you would not be using the lookup field), your
tables should be related on fields with the numeric values
(that can not be changed for any reason). If your tables
are properly normalized, I think you can solve your issue by
using a different field in the search criteria. You should
not be using the combo box's Text property, which is very
unusual.

I would have to see your table relationships to be sure, but
the normalized way to do this is more like:

Dim stDocName As String
Dim stLinkCriteria As Stringst
DocName = "Engagement"
stLinkCriteria = "[foreign key]=" & Combo1.Value
DoCmd.OpenForm stDocName, , , stLinkCriteria