Need Form Help!

[GoBrowns!]

My form asks the users to add a "reason code" for the late delivery to each
line item. I want the user to be able to choose the reason code from a
predefined list, then have the form populate the "reason code description"
into the next field in the form. The table that drives the form and the
reference table containing the reason codes and descriptions are not linked.
Is there a way to have the "reason code description" field look up the proper
description from the reference table using the reason code that the user
clicked on in the form?

In an ideal world, the user would also be able to type directly into the
"reason code description" box when they choose reason code "OTHER", but I
will settle for having the form do the first part.

Can anyone help?

 

[John W. Vinson]

My form asks the users to add a "reason code" for the late delivery to each
line item. I want the user to be able to choose the reason code from a
predefined list, then have the form populate the "reason code description"
into the next field in the form. The table that drives the form and the
reference table containing the reason codes and descriptions are not linked.
Is there a way to have the "reason code description" field look up the proper
description from the reference table using the reason code that the user
clicked on in the form?

If you JUST want to display it, include both fields in the combo box's
rowsource query and put a textbox on the form with a control source

=comboboxname.Column(1)

to display the second column (the description) from the combo box. Note that
this will truncate to 255 characters if the description is a Memo field.

In an ideal world, the user would also be able to type directly into the
"reason code description" box when they choose reason code "OTHER", but I
will settle for having the form do the first part.

The cost for doing this is that the user will be able to override the stored
description, and everything but the "OTHER" descriptions will be stored
redundantly; but you could "push" the description into a bound textbox in the
combo box's AfterUpdate event:

Private Sub cboReasonCode_AfterUpdate()
If Me!cboReasonCode = "Other" Then
MsgBox "Explain the reason:", vbOKOnly
Me!txtReasonCodeDescription.SetFocus
Else
Me!txtReasonCodeDescription = DLookUp("ReasonCodeDescription", _
"[Reference table name]", "[ReasonCode] = '" & Me!cboReasonCode & "'")
End If
End Sub

 

[Miss Teacher]

"If you JUST want to display it, include both fields in the combo box's
rowsource query and put a textbox on the form with a control source

=comboboxname.Column(1)

to display the second column (the description) from the combo box. Note that
this will truncate to 255 characters if the description is a Memo field."

So is the alternative it that field IS a memo field?

My form asks the users to add a "reason code" for the late delivery to each
line item. I want the user to be able to choose the reason code from a
predefined list, then have the form populate the "reason code description"
into the next field in the form. The table that drives the form and the
reference table containing the reason codes and descriptions are not linked.
Is there a way to have the "reason code description" field look up the proper
description from the reference table using the reason code that the user
clicked on in the form?

If you JUST want to display it, include both fields in the combo box's
rowsource query and put a textbox on the form with a control source

=comboboxname.Column(1)

to display the second column (the description) from the combo box. Note that
this will truncate to 255 characters if the description is a Memo field.

In an ideal world, the user would also be able to type directly into the
"reason code description" box when they choose reason code "OTHER", but I
will settle for having the form do the first part.

The cost for doing this is that the user will be able to override the stored
description, and everything but the "OTHER" descriptions will be stored
redundantly; but you could "push" the description into a bound textbox in the
combo box's AfterUpdate event:

Private Sub cboReasonCode_AfterUpdate()
If Me!cboReasonCode = "Other" Then
MsgBox "Explain the reason:", vbOKOnly
Me!txtReasonCodeDescription.SetFocus
Else
Me!txtReasonCodeDescription = DLookUp("ReasonCodeDescription", _
"[Reference table name]", "[ReasonCode] = '" & Me!cboReasonCode & "'")
End If
End Sub

 

[John W. Vinson]

"If you JUST want to display it, include both fields in the combo box's
rowsource query and put a textbox on the form with a control source

=comboboxname.Column(1)

to display the second column (the description) from the combo box. Note that
this will truncate to 255 characters if the description is a Memo field."

So is the alternative it that field IS a memo field?

If the description is stored in a Text field it can be included in the combo
box's RowSource query and displayed in this way.

If it's in a Memo field you'll need to use a different method; for instance
you could have a textbox with a control source

=DLookUp("[Description]", "[tablename]", "[IDfield] = " & [comboname])