Calculate values of 2 controls based on the values of 3 other cont

[broncojim]

I need to automatically populate values of two controls on a form based on
values that are input into 3 other controls on the same form. The values of
the 3 other controls each have a query as their source. It would go
something like this:

3 main controls: cmbKVA (17 possible choices), cmbSECVOLT (8 possible
choices), cmbTYPE (2 possible choices)

Once the data is populated into these 3 controls, then I would like the form
to calculate the value for the other two controls. They are txtITEM (41
possible choices that would be narrowed down to one based on values input
into the 3 main controls), and txtLOCATION (5 possible choices that would be
narrowed down to one based on values input into the 3 main controls).

I am fairly new to VBA, but I do have some experience creating simple code.
I would appreciate it if someone would give me advice on not only the code to
use, but also how to structure the data that the calculation would pull from
(ie can all of the values be inserted into VBA or should they be pulled from
a table or query).

If it matters, my table that these values will be stored in is actually an
ODBC-linked Oracle table.

Thanks,

Jim

 

[dymondjack]

Run a check on the OnChange event of each combo box... make sure each combo
has data, then call a function that's private to the form's module to set the
value of the two calculated controls.

Private Sub cmbName_Change
'Put this in the onchange for each combo
If (Len(Nz(Me.cmbname1, "")) > 0) And _
(Len(Nz(Me.cmbName2, "")) > 0) And _
(Len(Nz(Me.cmbName3, "")) > 0) Then
'Make sure each combo has a value

'Call the private function:
pfSetValues
End If
End Sub

Private Function pfSetValues()
Me.CalculatedValue1 = <expression>
Me.CalculatedValue2 = <expression>
End Function



So, every time a combo changes, if it calculates the values via the private
function. You might have to change some code around depending on the
datatypes of the combos/calculated fields, but this should get you started.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[dymondjack]

(ie can all of the values be inserted into VBA or should they be pulled from

a table or query).

It sounds like the best thing to do here would be to build a dynamic SQL
string off the data from the first three combos, and set it as the rowsource.


strSQL = "SELECT * FROM <table> WHERE " _
& "([Criteria1] = '" & Me.cmbName1 & "') And _"
etc, etc..

Me.txtITEM.RowSource = strSQL



Something along those lines anyway, hard to say without some more
information. If it's only 5 or 3 choices, it might be easier just to update
the valuelist. But anyway, you can use something like this in the
pfSetValues function from my previous post.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[broncojim]

Jack,

I actually only need values from 2 boxes to determine the values of 2 text
boxes. With that said, I have the following code in the first combo box:

Private Sub cmbKVA_Change()
'Put this in the onchange for each combo
If (Len(Nz(Me.cmbKVA, "")) > 0) And _
(Len(Nz(Me.cmbSECVOLT, "")) > 0) Then
'Make sure each combo has a value

'Call the private function:
pfSetValues
End If
End Sub


and I have this code in the second combo box

Private Sub cmbSECVOLT_Change()
'Put this in the onchange for each combo
If (Len(Nz(Me.cmbKVA, "")) > 0) And _
(Len(Nz(Me.cmbSECVOLT, "")) > 0) Then
'Make sure each combo has a value

'Call the private function:
pfSetValues
End If
End Sub

and this code in the form (this code is not associated with
any event on either the form or the text box, is this correct?)

Private Function pfSetValues()
Me.COMMENT_ = strSQL = "SELECT [ITEM] FROM CONTROLS2 WHERE " _
& "([KVA]='" & Me.cmbKVA & "') And" _
& "([SECVOLT]='" & Me.cmbSECVOLT & "')"
End Function



When the values are input into the 2 combo boxes,
a value of 0 is assigned to the text box.
This value should be an 8 digit number from the ITEM field in THE CONTROLS2
table. One thing that I thought of is cmbSECVOLT shows a text
value but what is actually stored in the table is a
code for that value. Could this be my problem?