Drop down lists

[Amit112]

Hi Guys,
I have a field that says Bore size on my form. It consists of a drop down list. I have two other fields on my form, one is part number one is part number 2. If something is picked from Bore size, I want the other two to automatically fill in a specific value that I have given it. Here is an example:

Bore size : 24 part number 1 : 12 part number 2: 23
: 34 : 14 : 45

if i pick 24 as the bore size, the other field will automatically fill in 12 and 23. Anyone know how I can do this. Thanks for the help.

 

[Cbrine]

Here's some direction.

1. Build as query, using the bore size field as the criteria that returns your other 2 fields.
2. Add a subform to your form, associated to the new query, and drop the 2 extra fields onto to this sub form.

This should accomplish what you want. Post back if you details on how to go about this.

HTH
Cal

 

[Amit112]

Hi, thanks for your help. I am not sure exactly what you mean though,,,
thanks

 

[Cbrine]

Amit,
Do you know how to build queries?

1. Create a new query in design mode.
2. Add the table that has your Bore size and the corresponding Part Number 1 and Part Number 2.
3. Add all three fields to the design grid.
4. In the criteria for the Bore Size, do a right click and select expression builder. (Make sure your form is open)
5. In first bottom window, click on the forms...Opened forms... your form.
6. In the second window select the field name of your drop down combobox, by double click on it.
7. Click OK.
8. Save the query.
9. Add a sub form to the orginal form(Delete the 2 fields you currently have), associate the subform to the new query and add the Part Number 1 and PArt Number 2 to the design grid for the sub form.
10. Close the and save the form.
11. Open the form again, and select a value in the Bore size, you should get the corresponding PN1 and PN2 from your table.

HTH
Cal

 

[Amit112]

Hi Cal,
Thanks. I keep trying it but it says invalid syntax when I save it. Any suggestions?

 

[Cbrine]

I'm making an assumption that this is caused by the criteria in the query. I believe the expression builder sometimes puts

expr<< >> in front of the criteria. You need to delete that. Your criteria should look like this.

[FormNam]![FieldName]

Cal

 

[Amit112]

Hi Cal,
I'm sorry, its just not working right. I will try to explain it better:

Bore Size = 58mm part number 1 = f003 part number 2 = g001
65mm f004 = g002
72mm f005 g003
77mm f006 g004
f007 g005
g006

if bore size = 58mm then part number 1 = f003 and part number 2 = g001

if bore size = 58 mm it could also = f004 g002
if bore size = 65mm then part number = f005 g003

basically i want it to do that. So reading bore size, it would automatically fill the rest in. Thanks for your help and patience Cal.

 

[Cbrine]

Amit 112,
You are going to have a problem with the Bore Size 58. If it has two possible answers for the two parts. If you have one selection combobox where you select bore size, and you want the part numbers ID'ed in the two other fields, you will need to have a one to one ratio of bore sizes to part numbers. So to make this work Bore Size 58 Must only have One PN1 and One PN2. If it has two PN1's and 2 PN2's, that equals 4 values, how do you display four values in two fields? It's impossible to make that work.
Could you have a Bore Size 58A and 58B?

Cal

 

[Amit112]

I was giving theoretical problems to simplfiy it but I will give the precise problem here.

There are 3 fields:
1. Part Number
2. Bore Size
3. GM Part Number

if Part Number = 600002 then
Bore size = 58
GM part number = 12591004

if part number = 600003
Bore size = 58
GM part number = 12593214

if part number = N/A
bore size = 65
GM part number = 12589382

if part number = 600004
bore size = 72
GM part number = 12591846

if part number = 600000
bore size = 77
GM part number = 12588244

if part number = 600001
bore size = 72
GM part number 12593591

- so I am actually basing it on Part number
is there a simple way to do this
thanks again

 

[Cbrine]

Amit,
Can you PM your Email to me. I have a mocked up copy of what I am talking about that I can send to you.

Cal