473,320 Members | 2,122 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

detecting to calculate

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_AfterUpdate()
Dim MyDB As Database
Dim MyRS As Recordset

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("T1", dbOpenSnapshot)
If Me!ContNo=MyRS.ContNo and Me!IDPro=MyRS!IDPro 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

Sep 21 '06 #1
6 2054
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_AfterUpdate()
Dim MyDB As Database
Dim MyRS As Recordset

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("T1", dbOpenSnapshot)
If Me!ContNo=MyRS.ContNo and Me!IDPro=MyRS!IDPro 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.OpenRecordset("T1", dbOpenSnapshot)
If MyRS.RecordCount 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
Sep 21 '06 #2
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

Sep 22 '06 #3
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

Sep 22 '06 #4
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 & "#"

Sep 22 '06 #5
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_BeforeUpdate() routine you could enter
If IsNull(Me.Combo1) Or IsNull(Me.Combo2) 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_AfterUpdate()
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.OpenRecordset(strSQL, dbOpenSnapshot)

If MyRS.RecordCount 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

Sep 22 '06 #6
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 !! :-)

Sep 23 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Jole | last post by:
Hi I'm writing a program that needs to read from a file. In order for the program to be robust, it should somehow check that the file isn't corrupt, or stuffed in any way. For example, that...
15
by: Jay | last post by:
I'm sure this is a really dumb question, but how do you detect a variable type in Python? For example, I want to know if the variable "a" is a list of strings or a single string. How do I do...
3
by: raptor | last post by:
hi, how to detect opera..it seems that even opera8 doesnt support xmlhttp fully (.i.e. sendRequestHeader). I ask this 'cause opera seems to mimic IE, at least in the preferences ?! I havent...
1
by: Hadi | last post by:
Hello, I have to DateTime structure, how do I calculate if date A is on the same week with date B or last week of date B? Thanks, Hadi
9
by: D. Shane Fowlkes | last post by:
I'm using SQL Server 2000 and on my page, I'm simply creating a SQLDataReader and filling in Labels with the retrieved (single) record. However, how can I prevent from getting errors when a field...
3
by: regtrashcan | last post by:
I have a webpage that detects whether Shockwave Player is installed and the version number. The javascript/vbscript that I use has worked fine until the latest release of the Shockwave Player. I am...
79
by: VK | last post by:
I wandering about the common proctice of some UA's producers to spoof the UA string to pretend to be another browser (most often IE). Shouldn't it be considered as a trademark violation of the...
5
by: Z.K. | last post by:
In C#, using the StreamReader, how do I detect when you get to the end of line. I am reading a text file using the Read() function and I need to detect the \n\r, but everything I try does not...
1
by: wwwords | last post by:
Is there a general method for detecting that a user has changed the record currently visible on a form, whether this is by hitting PgUp or PgDn or clicking on a navigation button, even if no change...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.