Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

putting info and doing math from a data base

Posted on 1998-07-25
28
Medium Priority
?
150 Views
Last Modified: 2010-05-03
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
0
Comment
Question by:mgbgolfer
  • 14
  • 14
28 Comments
 

Author Comment

by:mgbgolfer
ID: 1466675
no comment.  Just restate the urgency.  Wished I'd found the site earlier.
0
 
LVL 7

Accepted Solution

by:
kamall earned 300 total points
ID: 1466676
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
 
LVL 7

Expert Comment

by:kamall
ID: 1466677
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:mgbgolfer
ID: 1466678
am trying to put the code in:  two quick follow-up questions.  under what event do I put it and MySQL please devine.
0
 
LVL 7

Expert Comment

by:kamall
ID: 1466679
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
 

Author Comment

by:mgbgolfer
ID: 1466680
get following error message using    With MySQL

With object must be user-defined type, object, or variant.
0
 
LVL 7

Expert Comment

by:kamall
ID: 1466681
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
 

Author Comment

by:mgbgolfer
ID: 1466682
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
 
LVL 7

Expert Comment

by:kamall
ID: 1466683
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
 
LVL 7

Expert Comment

by:kamall
ID: 1466684
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
 

Author Comment

by:mgbgolfer
ID: 1466685
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
 
LVL 7

Expert Comment

by:kamall
ID: 1466686
Lifesaver?..., ha ha ha ha :-))
You are welcome mgbgolfer.
By the way, where are you from? US?, which state?
0
 

Author Comment

by:mgbgolfer
ID: 1466687
from Richmond, Va,
Ya dun good
0
 
LVL 7

Expert Comment

by:kamall
ID: 1466688
Lifesaver?..., ha ha ha ha :-))
You are welcome mgbgolfer.
By the way, where are you from? US?, which state?
0
 

Author Comment

by:mgbgolfer
ID: 1466689
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
 
LVL 7

Expert Comment

by:kamall
ID: 1466690
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
 

Author Comment

by:mgbgolfer
ID: 1466691
sounds ok to me.  
mgbgolfer@aol.com
0
 
LVL 7

Expert Comment

by:kamall
ID: 1466692
Note that there is no '.' (dot) between the 'RecordSet' and the ("[Total Invoices]").
0
 
LVL 7

Expert Comment

by:kamall
ID: 1466693
I mean there should not be a dot.
0
 

Author Comment

by:mgbgolfer
ID: 1466694
There isn't.  It was a typo.  I do that sometimes.
0
 

Author Comment

by:mgbgolfer
ID: 1466695
Just moved the code to probably the proper data control and have received an out of stack space error  ????
0
 
LVL 7

Expert Comment

by:kamall
ID: 1466696
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
 

Author Comment

by:mgbgolfer
ID: 1466697
ok thanks.  Will get out of your hair for now.
0
 

Author Comment

by:mgbgolfer
ID: 1466698
your grade has gone up to an A++ for helpfulness
0
 
LVL 7

Expert Comment

by:kamall
ID: 1466699
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
 

Author Comment

by:mgbgolfer
ID: 1466700
But the B was before you were really helpful.  Sorry

0
 
LVL 7

Expert Comment

by:kamall
ID: 1466701
Nevermind man. Its OK. Was just kidding :-)
I just would be happy to see your job done. Thats all.
Regards
0
 

Author Comment

by:mgbgolfer
ID: 1466702
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

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question