Populate information on Form from Query

[Guest]

Within a Form I have a Command button and when clicked on it runs a Parameter
query. The user then has to type in a Product identification number. The
Query will then display one or a number of products related to that number.
This is because a product can various pack sizes. What I want to be able to
do is select a row from the Query and automatically populate the text boxes
in my Form – where the Fields in the Query are related to the text boxes on
the Form. Is this possible??

 

[Arvin Meyer [MVP]]

There are no events associated with queries that can run code. You can base
a continuous form or datasheet form on your query and use the click event to
open a bound or unbound form and populate the textboxes. Why don't you
explain what you are trying to do.

 

[Guest]

The purpose of the Database is to prepare a Batch Processing sheet when
products are ready for labelling.

Therefore I have created a Look up table so users open a Form and do a Find
in the identification number text box (ECMA). This should then populate other
related fields – Pack size, Strength, File Name link. The problem is that a
product can have the same identification number but different pack sizes.

So I require a table to show all the related products to that ECMA. Then the
user can select which product they want from the table to be displayed on the
Form.

 

[Arvin Meyer [MVP]]

You can use the rowsource of a combo or list box or the recordsource of
subform to get what you want. Typically, a combobox uses the least screen
real estate and is therefore the best choice for a lookup. You can use
either a saved query or a select statement for the combobox's rowsource.
Have the first column contain the ECMA, and the other columns can be hidden
by setting their column widths to zero (0). Make sure the column count
property is correct though. Also, the array of columns starts with zero (0)
so you refer to you combo's second column like:

Me.cboECMA.Column(1)

Now use the AfterUpdate event of the combobox to fill the record something
like (aircode):

Sub cboECMA_AfterUpdate()

Me.txtPackSize = Me.cboECMA.Column(2)
Me.txtStrength = Me.cboECMA.Column(3)

End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com