Set Variable to lookup field

[laavista]

I need to set a string variable to a lookup field on a form.

I have two tables

"tblCase" with fields:
CaseID autonumber (key)
CaseType, text, 15 char

"tblClient " with fields:
Last_name
First_ name
CaseType, number, long (lookup to table tblCase)
etc.

In the "client" form, case type correctly displays the case type text. I
need to set a string variable to the "case type" text in the form.

The following returns the CaseTypeID which is a number, not the case type
text.
strCaseType = Forms!f_Client![case type]

The following did not work:
strCaseType = "[tblCase].[CaseType] FROM tblCase where (([tblCase.[CaseID])
= forms!f_client![casetype]

Any help would be greatly appreciated!

 

[pietlinden]

I need to set a string variable to a lookup field on a form.

I have two tables

"tblCase"  with fields:
     CaseID  autonumber (key)
     CaseType, text,  15 char

"tblClient "  with fields:
  Last_name
  First_ name
  CaseType, number, long     (lookup to table tblCase)
     etc.

In the "client" form, case type correctly displays the case type text.  I
need to set a string variable to the "case type" text  in the form.  

The following returns the CaseTypeID which is a number, not the case type
text.
strCaseType = Forms!f_Client![case type]  

The following did not work:
strCaseType = "[tblCase].[CaseType] FROM tblCase where (([tblCase.[CaseID])
= forms!f_client![casetype]

Any help would be greatly appreciated!

Why do you have caseType in both tables? Surely, it only describes
Case, so it belongs in the Case table ONLY. What the rest of the post
is about, I'm not even sure I follow. If you're trying to show data
from a related table, you can do it if you have a combobox that gets
data from that table (has that table as its control source). Say
"SELECT tblCase.CaseID, tblCase.CaseType FROM tblCase" Then the combo
would have 2 columns, with widths 1;0 (or something non-zero, and the
hidden column's width would be zero). Then you can refer to the
hidden column in the same form by using something like this in the
controlsource of your unbound textbox:
=me.cboCase.Column(1) (Since column collections are zero-based).

 

[laavista]

You're right. Good point. Thanks for the advice. I will apply it!

I need to set a string variable to a lookup field on a form.

I have two tables

"tblCase" with fields:
CaseID autonumber (key)
CaseType, text, 15 char

"tblClient " with fields:
Last_name
First_ name
CaseType, number, long (lookup to table tblCase)
etc.

In the "client" form, case type correctly displays the case type text. I
need to set a string variable to the "case type" text in the form.

The following returns the CaseTypeID which is a number, not the case type
text.
strCaseType = Forms!f_Client![case type]

The following did not work:
strCaseType = "[tblCase].[CaseType] FROM tblCase where (([tblCase.[CaseID])
= forms!f_client![casetype]

Any help would be greatly appreciated!

Why do you have caseType in both tables? Surely, it only describes
Case, so it belongs in the Case table ONLY. What the rest of the post
is about, I'm not even sure I follow. If you're trying to show data
from a related table, you can do it if you have a combobox that gets
data from that table (has that table as its control source). Say
"SELECT tblCase.CaseID, tblCase.CaseType FROM tblCase" Then the combo
would have 2 columns, with widths 1;0 (or something non-zero, and the
hidden column's width would be zero). Then you can refer to the
hidden column in the same form by using something like this in the
controlsource of your unbound textbox:
=me.cboCase.Column(1) (Since column collections are zero-based).
.