Converting an IDNum of a Combo or ListBox to its corresponding text for a TextBox

  • Thread starter Thread starter John S. Ford, MD
  • Start date Start date
J

John S. Ford, MD

I have a form (frmSearch) for entering search criteria which is then used to
filter a second form (frmData) prior to opening it.

The criteria of frmSearch is selected by some ComboBoxes and ListBoxes and I
wish to populate some TextBoxes on frmData with this information (to
visually remind the user which criteria was used to select the data he or
she is working with on frmData.

I know how to send the IDNum's of each parameter to frmSearch (using the
OpenArgs property) and I know how to parse the values back for entry into
the TextBoxes. What I don't understand is how to "convert" these IDNum's
back into the text that each IDNum corresponds to on the original ComboBox
or ListBox.

Any ideas how to do this?

Thanks,
John
 
Assuming that "MOST" combo boxes have two columns like

select ID, Description from tblInventory

Simply place a text box on the form, and for the data source, then


=([Combo4].[Column](1))

Note that the "column" feature is zero based, so 1 = 2nd column...

The above assumes that the combo box has more then one column.

In the above example, the text box could be placed beside the combo box on
the form and it would display the description when a combo selection is
made.
 
Albert,

Yes, my ComboBoxes and ListBoxes are set up with two fields: IDNum, text.

What do I do if the ComboBox is on frmSearch and the TextBox is on frmData?
I understand how to pass the IDNum of ComboBox1 of frmSearch to TextBox1 of
frmData.
 
John S. Ford said:
Albert,

Yes, my ComboBoxes and ListBoxes are set up with two fields: IDNum, text.

What do I do if the ComboBox is on frmSearch and the TextBox is on
frmData?
Click to expand...

Well, does the target form have that combo box on it? also?

If not, then you have several more approaches here.

Simply based that "target" form on a query in which you join in the 2nd
table.

What this simply means is that if you have 3, 4 or even more ID's from 3-4
different tables in which you want the description text to be pulled into
the form from those tables, then simply base the form on a query in which
you drop in those additona tables. You ***MUST*** use a left join when you
do this else the table will NOT be updatable. Also note that the query will
NOT be updatable unless the "id" value in those TARGET tables are a primary
key. (if they're not a primary key, then you can still display the data
using this trick of a query, but you'll not be able to update the query in a
form -- however this can be useful to have no one understand when building
reports that have locked a little descriptions that also come from other
tables.

A left join means that a query will return the "parent" records when the
child table HAS NO correspond record. You might have quite a few cases in
which were Eckerd's day to show on a report, but those combo box does nave
not been filled out yet.

So, if we have Customers, and Invoices tables, a left join would give us:

CustomerName InvoiceNumber
AppleBee
Donought Shop 1234
Doughnut Shop 1344

Note how AppleBee does NOT yet have a invoice number in the invoices
table..but the query still returns the record. You have to use left joins
for lookup values when you drop in many tables (cant use standard joins in
this case).

So, with a left join, the corresponding child record DOES NOT have to exist.
Just think of "left" side can exist...but the right side does NOT have to !

A middle join, or so called inner join is the standard join, and BOTH tables
have to have a value for the join. The above would produce:

CustomerName InvoiceNumber
Dounought Shop 1234
Doughutn Ship 1344

So, in the above inner join, our customer name of Applebee does not show,
since that customer does NOT yet have a invoice record in the invoice table.

To make a left join, you drop in the tables (in the query builder, or the
relationship designer), and draw the join line to the appropriate filed
between each table. You then double click on the join line. You then click
on the join type button

You get three options:

Only include rows where the joined fields from both tables are equal
(this standard default inner join)

Include ALL records from "Customers" and only those records from
"Invoices" where the joined fields are equal

(this is our left join. So, our main table Customers will be returned in
this query, REGARDLESS if the child records (invoices in this example)
exist, or not!. This is left join

Include ALL records from "Invoices" and only those records from
"Customers" where the joined fields are equal
This sis obviously a right join....



So, try the above idea. what this means is that when you open up the form in
design view, you'll have a list of fields including those looked up text
descriptions, and you can simply drag that from the list of fields onto your
form and you'll get the descriptions displayed.


You can also use the dlookup() function in place of query. however I find
that I can just sit back and drink coffee and use drag and drop in the query
builder and it's a lot less typing and also was easier than using dlookup().

However, you can also place a text box on the form, and for the data source
of the text box go:

=(dlookup("field name","table name","id = " & me!IDNUM))

Also, if you use the dlookup() idea, then make sure you don't accidentally
name this text box the same as any underlying field in the date set for the
form.

If use the query idea, then you'll notice you can simply drag the field from
the field list on to the form when in design mode. Also don't forget to
disabled the text box for editing because if use the query left join idea
the user will be able to MODIFY that description text. You don't want this
because then every single record of data with the same ID will now have this
new description text. On the other hand in some cases you actually want this
functionality so keep this idea in mind.
 
Thank you very much for that Albert. You've definitely given me a lot to
think about. I think the

=(dlookup("field name","table name","id = " & me!IDNUM))

code appears to be something I can use to get what I want. These TextBoxes
on the second form (frmData) are simply created to mirror the criteria
selected in the calling form (frmSearch). They are NOT meant to be linked
to frmData's underlying datasource.

I'm going to work through your detailed post.

Thanks,
John

--
Confidentiality Notice: The information contained in this electronic e-mail
and any accompanying attachment(s) is intended only for the use of the
intended recipient and may be confidential and/or privileged. If any reader
of this communication is not the intended recipient, unauthorized use,
disclosure or copying is strictly prohibited, and may be unlawful. If you
have received this communication in error, please immediately notify the
sender by return e-mail, and delete the original message and all copies from
your system. Thank you.
 
Back
Top