Text Box Binding

[Bryan Brassell]

Is it possible to have a text box which is bound to a
field called "Company" in one table, also
show "CompanyName" from another table.

For example,

Table A
Company = MP
(this is the field the text box is bound to and will make
changes to from the form)

Table B
CompanyName = Morgan Plant
(This table has "Company" as a key field - it relates to
Table A 'one to many' on the Company field (foreign in
Table A)).

I want the text box to show "AL - Morgan Plant" but have
its value as far as writing to the main table as "AL". I
just want the description visible for the ease of the user.

Possible?

 

[Rick Brandt]

Is it possible to have a text box which is bound to a
field called "Company" in one table, also
show "CompanyName" from another table.

For example,

Table A
Company = MP
(this is the field the text box is bound to and will make
changes to from the form)

Table B
CompanyName = Morgan Plant
(This table has "Company" as a key field - it relates to
Table A 'one to many' on the Company field (foreign in
Table A)).

I want the text box to show "AL - Morgan Plant" but have
its value as far as writing to the main table as "AL". I
just want the description visible for the ease of the user.

A ComboBox can do this, not a TextBox. A TextBox can use a Lookup function to
retrieve remote data or it can be bound to local data. It can't do both at the same
time. You could of course use two TextBoxes (one for each).

 

[Howard Brody]

I would keep the TextBox (txtCompany) for Company only and
put a Label (lblCompanyName) beside it. When a value is
entered in the TextBox, look up the apprpriate name and
populate the Label. The following code should do it but
still check the Help file for DLookUps:

= = = = = = = = = = = = = = = = = = = =
Dim strCompID as String
Dim strCompName as String

strCompID = [txtCompany]
strCompName = DLookUp("[CompanyName]", "TableB", "[Company]
='" & strCompID & "'")

lblCompanyName.Caption = strCompName
= = = = = = = = = = = = = = = = = = = =

You'll still need to validate that the Company entered is
valid - if you're going to be using the form for updating
only and not for initial entry, I'd use a ComboBox instead
to reduce the possibilities for miskeys.

Hope this helps!

Howard Brody