Look up question

[pavewayammo]

Ok I have look through the FAQ's and searched the Forums for this answer.

These are the fields I need for the drop down list.
SCL Text
20mm Number
Chaff Number
Flare Number
9's Number
120's Number

Now in a differant table I will have the same fields. I would like to be able to select the SCL and have the rest of the row change also. The drop down list will have as many as 30 differant rows.

Need it to do it like this so I can have a total for each column using a query for that table.





paveway

Looking for help check the FAQ's first then do a search then ask. Worked for me.

 

[LittleSmudge]

Sorry pavewayammo - not following you OM.

WHY, if you have the data in one table, would you even countenance the idea of copying the same data into another table !!!!


If you have all these fields listed as columns in a combo box then when you select one row the entire row is selected - but the combo only DISPLAYS the first visible column.

What are you wanting to do with the data in the other columns ?





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[pavewayammo]

The SCL is a bunch of numbers a letters that stand for the stuff in the other columns.
For example 2ABM2I in the SCL would be:
SCL 20mm Chaff Flare Cap-9 120
2ABM2I 1 1 1 1 2
2ABCM2I 1 2 1 1 2

There will be several lines like the above. The SCL will have several differant combos. I just want to be able to select the SCL and have the rest fill in. Now the Chaff and Flare # are muliplied by other numbers. For example 1 Chaff = 30. This part I have down already but can't figure out how to to go about having the other boxes fill in.

This is kinda hard to explain without knowing why I need this. Just tring to make my job easier. I can explain it better in an email. Here is my address: james.taylor2(NOSPAM)@hill.af.mil
Remove the (NOSPAM).

Thanks for taking the time to answer.

paveway

Looking for help check the FAQ's first then do a search then ask. Worked for me.

 

[LittleSmudge]

So !

Are you saying that there is a direct and calculatable relationship between the SCL and the contents of the other fields ?

If so then you should NOT be storing the other fields in the table in the first place.


If Not - but you want to populate the record with 'first guess' values that the user can amend individually then your best approach would be to bind the fields in the table to controls on the form ( the same form that has the SCL selection combo box on it )
Then in the SCL_AfterUpdate event you can populate the controls on the form based on the calculations made from the SCL chosen.
Then, when the record is saved from the form, all of the other values will be saved too.


'ope-that-'elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[LittleSmudge]

NOW I'm getting closer I think

So you have a Lookup table that has the fields
SCL Text
20mm Number
Chaff Number
Flare Number
9's Number
120's Number

And you want to populate a Main Table with the values based on the selected SCL


Okay.

Create a form bound to the Main table with all the fields bound to appropriate controls ( text boxes )

Then have the combo box, call it cboPickSCL , with
ControlSource = blank ( unbound control )
RowSource = "SELECT SCL, 20mm, Chaff, Flare, No9, No120 FORM tblSCLLookUp"
Column Count = 6
Bound Column = 1
ColumnWidth = 2cm;0;0;0;0;0 << This make the SCL the only VISIBLE column

In the comboBox AfterUpdate event you put code that goes

txt20mm = cboPickSCL.Column(1)
txtChaff = cboPickSCL.Column(2)
etc..

Where txtXXXX are the text box control names on the main form.
The .Column(X) does not care that the column is not visible
Note that the (X) is a Zero based index so cboPickSCL is the same as cboPickSCL.Column(0) and will yield the SCL selected.


HOWEVER, I come back to my earlier point.
If your user will have the option to change the values of the numbers in the main form after selecting the SCL then I see a point of doing the above.

If the user never gets to change the values - then the 20mm, Chaff, 9's values are all directly linked to the SCL code. In that case you should NOT be storing the 20mm, Chaff, 9's numbers etc in the table that underlies ( is bound to ) the Main Form. In this case make the txt20mm, txtChaff etc UNBOUND controls and make the cboPickSCL bound to an SCL field in the main table.

You'll then need to populate the unbound controls in the form's On-Current event in a similar way to the above - but the structure will be right and the data more robust.


'ope-that-'elps.





G LS
( Ex BAe {AW Divn.} - so I do understand a bit of your subject matter. )



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.