Query with current form data in access 2003

[David]

How do I perform a Query of a table using data that was manually entered in
the previous field?


For Example:

The first field is a shortname code for the material. I would like the
second field to do a query of the material data in the database to bring
back the material long name.

Thanks in advance for any help.

 

[Guest]

For the first field use a bound combo box with a RowSource that returns both
the short and long name columns from the referenced Materials table, e.g.

SELECT ShortName, LongName
FROM Materials
ORDER BY ShortName;

Set the combo box's ColumnCount property to 2.

For the long name use an unbound text box whose ControlSource references the
second column of the combo box, e.g.

=cboShortName.Column(1)

The Column property is zero-based, so Column(1) is the second column,
LongName.

Do not store the long name as a column in the form's underlying table, only
the short name. To store both would introduce redundancy into the table and
leave it at risk to update anomalies. The relevant long name value is always
available via the relationship between the form's table and the Materials
table on the ShortName columns.

Ken Sheridan
Stafford, England

 

[Steve Schapel]

David,

There are a number of approaches that can be taken to this. A few of
them are discussed in this article...
http://accesstips.datamanagementsolutions.biz/lookup.htm

 

[David]

Thanks for the advice Ken.

I tried what you said. I made the shortname a combo box that pulls the data
from a table. It is pulling the data correctly and only displaying the
first column. I have to scroll it over to view the second column. I tried
to make the textbox for the longname and I gave it a source of
=cbo.MATERIAL.column(1)

MATERIAL is the name of the shortname field. All I get is #Name? though.
Any ideas?

Thanks,

Dave

 

[David]

Nevermind. I got it. I needed to remove the cbo. LOL I'm new to getting
in depth with Access. Thank you for the help!!