Solved

putting info and doing math from a data base

Posted on 1998-07-25
28
141 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 100 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
 

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now