detecting to calculate

[luanhoxung]

dear all!!
i met the headache problem in setting value for some controls in my form.
i have 2 combo box in form F1.
i want to set value for some textbox in F1 when i choose value from 2
combo box.
the value calculated of these textbox base on some fields of table T1 in
condition: the same value between 2 combox-F1 and 2 fields-T1.
hereunder is my code:
Private Sub PACKAGE_AfterUp date()
Dim MyDB As Database
Dim MyRS As Recordset

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecord set("T1", dbOpenSnapshot)
If Me!ContNo=MyRS. ContNo and Me!IDPro=MyRS!I DPro then
Me!QTITY = Me!PACKAGE * MyRS!PKG / MyRS!QTITY
Me!Price.value = MyRS!Price
End If
End Sub

it just find out the 1st row of table T1 and do the code.
but i want to find out though all the rows of T1, if they satisfy the
condition, they will do set value as the code.
any loop are there or ideas abt solving this !!
i get the help from Tom but it seem not better because maybe i show my
question in bad words.
Pls give me a hand.
thanks

[salad]

luanhoxung wrote:

dear all!!
i met the headache problem in setting value for some controls in my form.
i have 2 combo box in form F1.
i want to set value for some textbox in F1 when i choose value from 2
combo box.
the value calculated of these textbox base on some fields of table T1 in
condition: the same value between 2 combox-F1 and 2 fields-T1.
hereunder is my code:
Private Sub PACKAGE_AfterUp date()
Dim MyDB As Database
Dim MyRS As Recordset

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecord set("T1", dbOpenSnapshot)
If Me!ContNo=MyRS. ContNo and Me!IDPro=MyRS!I DPro then
Me!QTITY = Me!PACKAGE * MyRS!PKG / MyRS!QTITY
Me!Price.value = MyRS!Price
End If
End Sub

it just find out the 1st row of table T1 and do the code.
but i want to find out though all the rows of T1, if they satisfy the
condition, they will do set value as the code.
any loop are there or ideas abt solving this !!
i get the help from Tom but it seem not better because maybe i show my
question in bad words.
Pls give me a hand.
thanks

If your above example, you open the recordset and whatever record you
are sitting on is the value you use in your computations.

You might want to create a SQL statement for your recordset. Ex:
Dim strSQL As STring
'assume ContNo and IDPro as numeric. Surround with quotes
'if charater, # if dates.
strSQL = "Select PKG, QTITY From T1 " & _
"Where ContNo = " & Me.ContNo And " & _
"IDPro = " & Me.IDPro
Set MyRS = MyDB.OpenRecord set("T1", dbOpenSnapshot)
If MyRS.RecordCoun t 0 then
'record found. Assumes only 1 found
Me!QTITY = Me!PACKAGE * MyRS!PKG / MyRS!QTITY
Me!Price.value = MyRS!Price
Endif
MYRs.close
set MYRs = nothing

[luanhoxung]

dear Salad !!
thanks for ur help !!
i nearly catch the good result.
but i get errored message "type mismatch"
i forgot that IDPro and ContNo are text(not numeric) !!
Pls show me the way how i can overcome the errored !!
thank u.
luan

[luanhoxung]

hi Salad !!
i just sent u reply but i seeked my fail in our code !!
although, i get the trouble as the last paragraph that i said in my first
mail.
it just seem to find out the first row of the table "T1", and it set value
to the 2nd record of the form as the 1st one of the form.
---The action--------the result(QTITY of the form)
1/ ContNo:a1...... .. ok: it cal. as my intend
IDPro:b1
2/ ContNo:a2
IDPro:b2 .........it calculate base on value a1 & b1 of table T1.

i try to do with help of VBA but i cant see anycode that i need.
can u give ur idea of this !!
i try to do through last nigh
Pls give me a hand !!
thanks...
luan from cantho-vietnam

IDPro:b2

[salad]

luanhoxung wrote:

dear Salad !!
thanks for ur help !!
i nearly catch the good result.
but i get errored message "type mismatch"
i forgot that IDPro and ContNo are text(not numeric) !!
Pls show me the way how i can overcome the errored !!
thank u.
luan

If your table is a string, surround by quotes. Ex:
Dim var As String
var = "test"
"Where Fieldname = '" & var & "'"
or
"Where Fieldname = """ & var & """"
Check that out in a debug window.

If numeric
Dim var As Long
var = 2
"Where Fieldname = " & var

If Date
Dim var As Date
var = Date()
"Where Fieldname = #" & var & "#"

In the debug window, enter something like
var = Date()
? "Where Fieldname = #" & var & "#"

[salad]

luanhoxung wrote:

hi Salad !!
i just sent u reply but i seeked my fail in our code !!
although, i get the trouble as the last paragraph that i said in my first
mail.
it just seem to find out the first row of the table "T1", and it set value
to the 2nd record of the form as the 1st one of the form.
---The action--------the result(QTITY of the form)
1/ ContNo:a1...... .. ok: it cal. as my intend
IDPro:b1
2/ ContNo:a2
IDPro:b2 .........it calculate base on value a1 & b1 of table T1.

i try to do with help of VBA but i cant see anycode that i need.
can u give ur idea of this !!
i try to do through last nigh
Pls give me a hand !!
thanks...
luan from cantho-vietnam

IDPro:b2

You stated earlier:

>i have 2 combo box in form F1.

Let's call them Combo1 and Combo2 in my response. Substitue for what
you have.

>i want to set value for some textbox in F1 when i choose value from 2

combo box.

>the value calculated of these textbox base on some fields of table T1

in condition: the same value between 2 combox-F1 and 2 fields-T1.

So you want to create a value for a form's Price field once you have
selected values from the combo field and the Package field.

In the Package_BeforeU pdate() routine you could enter
If IsNull(Me.Combo 1) Or IsNull(Me.Combo 2) Then
msgbox "Please select items from both combo boxes"
Cancel = True
Endif

This will stop the calculation process until both Combo1 and Combo2 have
values selected.

Now let's look at the following code, using stuff from my prior post.

Private Sub PACKAGE_AfterUp date()
Dim MyRS As Recordset
Dim strSQL As String

'build SQL statement to select record that
'matches the ContNo and IDPro.
strSQL = "Select PKG, QTITY, Price From T1 " & _
"Where ContNo = '" & Me.ContNo & "' And " & _
"IDPro = '" & Me.IDPro & "'"

'next line for debugging purposes. Comment out
'if you'd like.
Msgbox strSQL

'open up the recordset
Set MyRS = Currentdb.OpenR ecordset(strSQL , dbOpenSnapshot)

If MyRS.RecordCoun t 0 then
'record found. Assumes only 1 found
Me!QTITY = Me!PACKAGE * MyRS!PKG / MyRS!QTITY
Me!Price = MyRS!Price
Else
msgbox "Record to calculate not found."
Me.QTity = 0
Me.Price = 0
Endif
MYRs.close
set MYRs = nothing
End Sub

[luanhoxung]

hi Salad !!
great help!!
many many many with thanks !!
u pulled me from marsh.
in spite of, there still have a small problem.
I think i can solve it alone.

thanks !!
hope see u in other harder question !! :-)