Beginner's trivial problem: selecting a record and displaying data

[Guest]

I am trying to get my head round Access - and failing miserably. I am trying
to do
what seems like a trivial operation, but I have been unable to get it to work.

I have a table where each record has two fields: a number and some text.
I would like a form with two text boxes: A and B, such that when I put a
number
into A, B will display the text for the record with the given number. How
difficult
can that be?

I can get something like that to work (almost), but it's incredibly
complicated and
B is only ever refreshed when I select design view and then switch back to
form
view. As usual, the help explains how to do complicated things, but seems to
be
utterly silent on something as boring as this.

Can anyone offer real help?

 

[Guest]

There's lots of ways to do this, but what works pretty well for me is the
following:

In the afterupdate event of A put in the following code:

sub A_AfterUpdate()
Dim dbs as database, rst as recordset, sql$
set dbs = currentdb
sql = "Select [Field_for_B] from [YourTable] where [Field_for_A] = ' " & _
me.A.value & " ' "
set rst = dbs.openrecordset(sql)
me.b.value = rst.fields(0).value
rst.close
set dbs = nothing
end sub

This is just off the top of my head, and you may have to adjust the code for
different field types, but this should get you what you want.

Good Luck!

 

[Larry Linson]

Let me suggest a simpler solution. Create a Form that is bound to your Table
or to a Query against your Table. Create a TextBox for displaying Field B.
Make certain the "magic wand" symbol in your Toolbox appears pressed down.
Place a ComboBox on your Form for Field A... use the option in the Combo
Wizard to locate the record selected. And, voila, you will have what you
want without writing any code or jumping through any hoops.

Larry Linson
Microsoft Access MVP

 

[Guest]

Thanks for your solution but I could not make it do anything like what I
need. I get a combo box that list all of the records in the table; it appears
to be completely independent of the text box.

Let me try to explain again exactly what I need.

I have a table with thousands of records, each with a numeric NUMBER field
and a textual DATA field.

I need a form with two boxes, A and B.

If I type 1001 in box A, I want box B to display the DATA field from the
record in my table where the NUMBER field=1001. I do not want to have to
select a record by scrolling down a potentially huge list. I would even be
happy if I needed to click another button on the form to indicate that A has
changed. Neither box should have a list of any kind.

 

[Albert D.Kallal]

two solutions.

Simply base the new form a query that joins in the 2nd table..and include
the textual data field.

This query needs to be a left join for this to work.

A left join means that a query will return the "parent" records when the
child table HAS NO correspond record.

To make a left join, you drop in the tables (in the query builder), 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

The is the one we want. Now, on your form, simply place two textboxes based
on the two fields. When you type in the number, the description will appear.
Not one line of code need be written here.

A 2nd non code approach is to use the combo box wizard. Build a combo box
based on the other table..and include BOTH COLUMNS..the number and the text
description part. YOU DO NOT HAVE TO EXPAND the combo box here, and the user
can simply type in the number. Right beside this comb o box, place another
text box, and simply put in as the data source for this text box the
following

=([mycomboBox].column(1))

(note 0 = frst collum...so, 1 is used to get the 2nd column from the combo).

Thus, if you type in a number in the first text box...the 2nd text box will
display the text description. And, if you wish, you can drop down the combo
box to browse for a correct number..and it will display both columns when
the combo box is dropped down (however, as mentioned, you DO NOT actually
have to open the combo box..and can just type in he number).

I can think of about 5 more ways to do this..but the above are easy..and
certainly do not requite any code skills on your part...