HELP! Linking Controls

[alexi]

I have a form in datasheet view in which i need to enter information i am using in a quote report.
3 fields i am having trouble with.

Chemical Type from tblChemicals
Chemical Rate from tblChemicals
Chemical Volume on form
these are all being stored in tblJobEstimates

I need to select froma drop down the chem type, and have the rate come up in the chem rate field. This happens but only stores the chem ID not the Chem Rate associated with the type. How do i just store the Rate value rather than the ID. THis is so i can then multiply out the rate by what is in the volumn column.

Thanks,

 

[Jack Cowley]

In the After Update event of the Combo box put code like this:

Me![ChemicalRate] = Me.ComboBoxName.Column(1)

I am assuming that you have a control on your form called ChemicalRate. Do your multiplication if you like, but do not store the results in your table.

hth,
Jack

 

[alexi]

Another step?

Thats one step further thank you, however now it is instead of showing an individual record being a rate, it is showing the entire list of rates in the table
how do i stop this now?

 

[Jack Cowley]

Create your query using the Wizard. Select the first item on the first screen. Select the Chemical table. Select the ChemicalType and ChemicalRate as the two 'columns' in the combo box. Finish the Wizard but do not bind the combo box to a control on the form. Now your combo should show you the ChemicalType and ChemicalRate. Selecting a ChemicalType and using the code I gave you should result in the ChemicalRate for that ChemicalType being intered into to the Rate control on your form.

hth,
Jack

 

[alexi]

sorry to be a pain

sorry to be a pain. this has been anoying me for a while. it still wont work.
here it is then...
i have on my form in data sheet view,

ChemType (combo) info coming from tblChemicals
ChemRate (combo) info comin from tblChemicals

with your code, and thats it, i click on chem type and in the cbo box it shows the chemical type and the chem rate.
but after i click on it nothing comes into the chem rate box.
i had to link the ChemTYpe combo box to the table because otherwise it filled out all entries on the form with the same selection.
any idea? thanks for this

 

[Jack Cowley]

Since ChemicalType and ChemicalRate are coming from one table that tells me that for every ChemicalType there is ONE ChemicalRate. If this is NOT the case then your table is not normalized. If it is the case then you do not need the second combo box, ChemicalRate. In your form you should have a combo box for ChemicalType, as I explained earlier, and a control called ChemicalRate (or whatever) that is bound to the field in the table where you want to save the rate. Selecting a ChemicalType in the combo will fill in the ChemicalRate control with the value in the second column in the ChemicalType combo box. (Remember that combo boxes are zero based so physical column 2 is actually column 1.)

Jack

 

[alexi]

THANKYOU!

Thankyou very much mate. u saved me alot of trauma haha
thanks

 

[Jack Cowley]

You are welcome. Glad I was able to assist you...

Jack