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!

On Jun 6, 11:08=A0pm, laavista <laavi...@discussions.microsoft.com>
wrote:
> I need to set a string variable to a lookup field on a form.
>
> I have two tables
>
> "tblCase" =A0with fields:
> =A0 =A0 =A0CaseID =A0autonumber (key)
> =A0 =A0 =A0CaseType, text, =A015 char
>
> "tblClient " =A0with fields:
> =A0 Last_name
> =A0 First_ name
> =A0 CaseType, number, long =A0 =A0 (lookup to table tblCase)
> =A0 =A0 =A0etc.
>
> In the "client" form, case type correctly displays the case type text. =
=A0I
> need to set a string variable to the "case type" text =A0in the form. =A0
>
> The following returns the CaseTypeID which is a number, not the case type
> text.
> strCaseType =3D Forms!f_Client![case type] =A0
>
> The following did not work:
> strCaseType =3D "[tblCase].[CaseType] FROM tblCase where (([tblCase.[Case=
ID])
> =3D 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:
=3Dme.cboCase.Column(1)  (Since column collections are zero-based).

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



"pietlinden@hotmail.com" wrote:

> On Jun 6, 11:08 pm, laavista <laavi...@discussions.microsoft.com>
> wrote:
> > 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).
> .
>