mgbgolfer
asked on
putting info and doing math from a data base
Preface. This is a school project and the instructor has give the only SQL statement we are to use. So I have to total a field and put it into a text box on the form. My problem is that for the life of me, I can't figure how get variable names to work when adding the field values to an array so that I can manipulate the data. I know this is the hard way to do this but it is what the instructor wants. It must be in my text but I need some hints. Thanks. ps really need the answer before 7/27
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just one more thing:
The filed 'Amount' is of string type. Thats why we are using the 'CLng' function to convert the string to a number.
The filed 'Amount' is of string type. Thats why we are using the 'CLng' function to convert the string to a number.
ASKER
am trying to put the code in: two quick follow-up questions. under what event do I put it and MySQL please devine.
You can put the code under a command button click event for example, so that when you click the command button, you get the total of your field.
Do not forget to move to the first record in your SQL recordset before executing the code, so that all records are taken into consideration. This can be done by:
MySQL.RecordSet.MoveFirst
Regards
Do not forget to move to the first record in your SQL recordset before executing the code, so that all records are taken into consideration. This can be done by:
MySQL.RecordSet.MoveFirst
Regards
ASKER
get following error message using With MySQL
With object must be user-defined type, object, or variant.
With object must be user-defined type, object, or variant.
Well...,
The MySQL is the name of the SQL query from which you are getting your data from the database. You must declare it as a global variable on in your form declarations section.
Can you give me here the code by which you open your database and get the data?
Are you using a Data control or just data objects?
The MySQL is the name of the SQL query from which you are getting your data from the database. You must declare it as a global variable on in your form declarations section.
Can you give me here the code by which you open your database and get the data?
Are you using a Data control or just data objects?
ASKER
am using a data control
datOrder.RecordSource = "select * from tblOrder where [Customer Num] =" & Val(txtCustomer_Number.Tex
datOrder.Refresh
This is the code I am using and I have it in the' datCustomer_Reposition' sub form
datOrder.RecordSource = "select * from tblOrder where [Customer Num] =" & Val(txtCustomer_Number.Tex
datOrder.Refresh
This is the code I am using and I have it in the' datCustomer_Reposition' sub form
Very well....
So your code should look like this:
(Just change the MySQL with datOrder as shown below)
Dim Total As Long
With datOrder
Do While Not (.Recordset.EOF)
Total = Total + CLng(.Recordset("Amount"))
.Recordset.MoveNext
Loop
End With
Text1.Text = Format(Total, "#,#")
So your code should look like this:
(Just change the MySQL with datOrder as shown below)
Dim Total As Long
With datOrder
Do While Not (.Recordset.EOF)
Total = Total + CLng(.Recordset("Amount"))
.Recordset.MoveNext
Loop
End With
Text1.Text = Format(Total, "#,#")
And don't forget to change the 'Amount' field name in the above code with the actual name of your field for which you want to sum the values.
ASKER
Thank you very much. You are a lifesaver. I haven't input the last of the code yet, but with this much help,I should be able to get it from here. I know I probably sound like a dummy but this is a 1st semester course crammed ito 10 weeks with an adjunct professor who doesn't have office hours and likes torture students. Again thank you very much
Lifesaver?..., ha ha ha ha :-))
You are welcome mgbgolfer.
By the way, where are you from? US?, which state?
You are welcome mgbgolfer.
By the way, where are you from? US?, which state?
ASKER
from Richmond, Va,
Ya dun good
Ya dun good
Lifesaver?..., ha ha ha ha :-))
You are welcome mgbgolfer.
By the way, where are you from? US?, which state?
You are welcome mgbgolfer.
By the way, where are you from? US?, which state?
ASKER
still got a problem.
The recordset."amount" needs to the column head of the data base and it is "[Total Invoices]" and the error message is that Recordset.("[Total Invoices]") is not found in the collection. Is this another whole can of worms?
The recordset."amount" needs to the column head of the data base and it is "[Total Invoices]" and the error message is that Recordset.("[Total Invoices]") is not found in the collection. Is this another whole can of worms?
Oh sorry, comment reposted by mistake.
--------------------------
mgbgolfer,
If you don't mind, can we talk by e-mail? I would like to ask you few things.
Please leave me your e-mail address.
Regards
--------------------------
mgbgolfer,
If you don't mind, can we talk by e-mail? I would like to ask you few things.
Please leave me your e-mail address.
Regards
ASKER
sounds ok to me.
mgbgolfer@aol.com
mgbgolfer@aol.com
Note that there is no '.' (dot) between the 'RecordSet' and the ("[Total Invoices]").
I mean there should not be a dot.
ASKER
There isn't. It was a typo. I do that sometimes.
ASKER
Just moved the code to probably the proper data control and have received an out of stack space error ????
mgbgolfer,
Probably, this is because you have the datOrder code in the repoisition event of the other data control. The reposition event is entered whenever the data control is initialized, repositioned, etc. which will recursively execute the code that exist there. I think that you better find another logic for executing your datOrder code.
Probably, this is because you have the datOrder code in the repoisition event of the other data control. The reposition event is entered whenever the data control is initialized, repositioned, etc. which will recursively execute the code that exist there. I think that you better find another logic for executing your datOrder code.
ASKER
ok thanks. Will get out of your hair for now.
ASKER
your grade has gone up to an A++ for helpfulness
Form where would I get the A++ now ?? :-)
I think you gave me a B grade. Nevermind :-)
Well..., I should also go to bed now because it is 4:30am here in Greece and I am still like crazy hunting people here on EE :-) (to help them)
So, have a nice day mgbgolfer.
I will try to e-mail you within a couple of days.
I think you gave me a B grade. Nevermind :-)
Well..., I should also go to bed now because it is 4:30am here in Greece and I am still like crazy hunting people here on EE :-) (to help them)
So, have a nice day mgbgolfer.
I will try to e-mail you within a couple of days.
ASKER
But the B was before you were really helpful. Sorry
Nevermind man. Its OK. Was just kidding :-)
I just would be happy to see your job done. Thats all.
Regards
I just would be happy to see your job done. Thats all.
Regards
ASKER
ok would like to see it finished myself. Simple project made tough by intensive summer session and another programming class. This is my final or VB. Then have to start a web site for HTML final. Thanks again c Ya
ASKER