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
mgbgolferAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
kamallConnect With a Mentor Commented:
Well..., here is what you are looking fort:
This example will add the values in the filed 'Amount' of all records that exist in the 'MySQL' SQL query, and show the result in Text1 text box.

    Dim Total As Long
   
    With MySQL
        Do While Not (.Recordset.EOF)
            Total = Total + CLng(.Recordset("Amount"))
            .Recordset.MoveNext
        Loop
    End With
    Text1.Text = Format(Total, "#,#")

Hope that helps.
Regards
0
 
mgbgolferAuthor Commented:
no comment.  Just restate the urgency.  Wished I'd found the site earlier.
0
 
kamallCommented:
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.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mgbgolferAuthor Commented:
am trying to put the code in:  two quick follow-up questions.  under what event do I put it and MySQL please devine.
0
 
kamallCommented:
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
0
 
mgbgolferAuthor Commented:
get following error message using    With MySQL

With object must be user-defined type, object, or variant.
0
 
kamallCommented:
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?
0
 
mgbgolferAuthor Commented:
am using a data control

datOrder.RecordSource = "select * from tblOrder where [Customer Num] =" & Val(txtCustomer_Number.Text)
    datOrder.Refresh
  This is the code I am using and I have it in the' datCustomer_Reposition' sub form
 
0
 
kamallCommented:
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, "#,#")

0
 
kamallCommented:
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.
0
 
mgbgolferAuthor Commented:
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
0
 
kamallCommented:
Lifesaver?..., ha ha ha ha :-))
You are welcome mgbgolfer.
By the way, where are you from? US?, which state?
0
 
mgbgolferAuthor Commented:
from Richmond, Va,
Ya dun good
0
 
kamallCommented:
Lifesaver?..., ha ha ha ha :-))
You are welcome mgbgolfer.
By the way, where are you from? US?, which state?
0
 
mgbgolferAuthor Commented:
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?
0
 
kamallCommented:
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
0
 
mgbgolferAuthor Commented:
sounds ok to me.  
mgbgolfer@aol.com
0
 
kamallCommented:
Note that there is no '.' (dot) between the 'RecordSet' and the ("[Total Invoices]").
0
 
kamallCommented:
I mean there should not be a dot.
0
 
mgbgolferAuthor Commented:
There isn't.  It was a typo.  I do that sometimes.
0
 
mgbgolferAuthor Commented:
Just moved the code to probably the proper data control and have received an out of stack space error  ????
0
 
kamallCommented:
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.
0
 
mgbgolferAuthor Commented:
ok thanks.  Will get out of your hair for now.
0
 
mgbgolferAuthor Commented:
your grade has gone up to an A++ for helpfulness
0
 
kamallCommented:
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.
0
 
mgbgolferAuthor Commented:
But the B was before you were really helpful.  Sorry

0
 
kamallCommented:
Nevermind man. Its OK. Was just kidding :-)
I just would be happy to see your job done. Thats all.
Regards
0
 
mgbgolferAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.