Populate sub form with data from a table/query

[Damo10]

Hi,

I have a form with a sub form, on the main form I want the user to select a machine number and then when they have selected it I want to populate the required data into the sub form.

In the sub form I have a field called "Seal" and another called "Quantity" what I require is to get the results from either a query or table and list all the seals & quantities for that machine.

I have a table called "Machine" which has all the machine numbers and also fields called "Seal1", "Seal1Qty","Seal2, "Seal2Qty" all the way to Seal 10.

I do not know how the best way to get this data into the sub form, any help would be great.

Regards,

 

[AlexandraT]

Sounds like you need a lookup. I recommend vlookup or index/match. This would basically look for the machine number inputted and return values from the Machine table. For an exact formula, please post a sample.
Alex

 

[Damo10]

Hi,

In that "Machine" table there are fields for Machine Number, Seal1.Seal1Qty,Seal2,Seal2Qty etc up to seal 10.
In the sub form there is a fiels called Seal, Quantity

What I need is when the user selects the machine number that the sub form looks at all the seal fields for that machine and if there is a seal then insert the seal and the quantity into the sub form and do this for all 10 seals.

Example

Machine table

Machine Seal1 Seal1Qty Seal2 Seal2qty
1 1" 2 2" RJT 6

Sub form

Seal Quantity
1" 2
2" RJT 6

 

[AlexandraT]

<title>Excel Jeanie HTML</title>If this is the Machine table in sheet1:

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

1

Machine

Seal1

Seal1Qty

Seal2

Seal2Qty

Seal3

Seal3Qty

Seal4

Seal4Qty

Seal5

Seal5Qty

Seal6

Seal6Qty

Seal7

Seal7Qty

Seal8

Seal8Qty

Seal9

Seal9Qty

Seal10

Seal10Qty

2

1

1"

2

2"

RJT6

3"

a

4"

b

5"

c

6"

d

7"

e

8"

f

9"

g

10"

h

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

And the sub form in sheet2:

*	A	B	C
1	Machine	Seal	Quantity
2	1	1"	2
3	*	2"	RJT6
4	*	3"	a
5	*	4"	b
6	*	5"	c
7	*	6"	d
8	*	7"	e
9	*	8"	f
10	*	9"	g
11	*	10"	h

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 =VLOOKUP(Sheet2!$A$2,Sheet1!A:U,2) C2 =VLOOKUP(Sheet2!$A$2,Sheet1!A:U,3) B3 =VLOOKUP(Sheet2!$A$2,Sheet1!A:U,4) C3 =VLOOKUP(Sheet2!$A$2,Sheet1!A:U,5) B4 =VLOOKUP(Sheet2!$A$2,Sheet1!A:U,6) C4 =VLOOKUP(Sheet2!$A$2,Sheet1!A:U,7) B5 =VLOOKUP(Sheet2!$A$2,Sheet1!A:U,8) C5 =VLOOKUP(Sheet2!$A$2,Sheet1!A:U,9) B6 =VLOOKUP(Sheet2!$A$2,Sheet1!A:U,10) C6 =VLOOKUP(Sheet2!$A$2,Sheet1!A:U,11) B7 =VLOOKUP(Sheet2!$A$2,Sheet1!A:U,12) C7 =VLOOKUP(Sheet2!$A$2,Sheet1!A:U,13) B8 =VLOOKUP(Sheet2!$A$2,Sheet1!A:U,14) C8 =VLOOKUP(Sheet2!$A$2,Sheet1!A:U,15) B9 =VLOOKUP(Sheet2!$A$2,Sheet1!A:U,16) C9 =VLOOKUP(Sheet2!$A$2,Sheet1!A:U,17) B10 =VLOOKUP(Sheet2!$A$2,Sheet1!A:U,18) C10 =VLOOKUP(Sheet2!$A$2,Sheet1!A:U,19) B11 =VLOOKUP(Sheet2!$A$2,Sheet1!A:U,20) C11 =VLOOKUP(Sheet2!$A$2,Sheet1!A:U,21) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Alex
<!-- ######### End Created Html Code To Copy ########## -->

 

[Damo10]

Hi,

The machine table and the form are in access not excel

 

[AlexandraT]

Oh sorry. Didn't see that.

Alex