SUM query using a combo box

[kent christensen]

Heres what i have:

i have 3 tables set up that are identical. The only difference is that the tables have different titles as they are databases for different customers.

The table has the following fields:
"RMA #", "Quote ID", "Credit(USD)", "Credit(#)", "Credit(Euros)", "Credit(CAD)", and "Credit(YEN)"



I have a form called form1 that has:

A combo box (combo23) set up with the name of the three customer tables.


WHAT I WANT:
I was wondering if there was a way to be able to select the name of the customer table from the combo box,and have a command button query sum the following fields "Credit(USD)", "Credit(#)", "Credit(Euros)", "Credit(CAD)", and "Credit(YEN)". I would like this query to be tied to a command button.

Is this possible?

[MMcCarthy]

Hi Kent

OK I think what you want is to set up a dynamic query. I've left space for you to put in what you want to do with the query results as you didn't say.

Private Sub MyCmdButton_Click()
Dim qdf As QueryDef
Dim strSQL AS String
 
    strSQL = "SELECT Sum([Credit(USD)]) AS SumCreditUSD, Sum([Credit(#)]) AS [SumCredit#], " & _ 
        "Sum([Credit(Euros)]) AS SumCreditEuro, Sum([Credit(CAD)]) AS SumCreditCAD, " & _
        "Sum([Credit(YEN)]) AS SumCreditYen, Sum([Credit(AUD)]) AS SumCreditAUD " & _
        "FROM " & Me!Combo23
 
    With CurrentDb
        Set qdf = .CreateQueryDef("MyQuery", sqltext)
        DoEvents
 
 
        ' Do whatever you want with the query results here ....
 
        ' When you are finished delete the query object
        DoCmd.DeleteObject acQuery, "My_Query"
        qdf.Close
        Set qdf = Nothing
    End With
 
End Sub

[kent christensen]

MMcCarthy,

Once again thanks for the help. Sorry i forgot to tell you where i wanted the results to go. I would like the results to be updated into a table that i just created. The table is titled "Customer Table" with the following fields:
"Customer", "Customer ID", "Credit(USD)", "Credit(#)", "Credit(Euros)", "Credit(CAD)", and "Credit(YEN)"

I have all of the customers names in the customer field so is there a way that when the sum query runs it only updates the correct customer row (chosen from combo23 like before) and correct fields ("Credit(USD)", "Credit(#)", "Credit(Euros)", "Credit(CAD)", and "Credit(YEN)")

What would be the code for the sum query and the query that updates the "customer table". I would like to link it to a command button.

Thanks in advance

[MMcCarthy]

OK try this ...

Private Sub MyCmdButton_Click()
Dim qdf As QueryDef
Dim strSQL AS String
 
    strSQL = "SELECT Sum([Credit(USD)]) AS SumCreditUSD, Sum([Credit(#)]) AS [SumCredit#], " & _ 
        "Sum([Credit(Euros)]) AS SumCreditEuro, Sum([Credit(CAD)]) AS SumCreditCAD, " & _
        "Sum([Credit(YEN)]) AS SumCreditYen, Sum([Credit(AUD)]) AS SumCreditAUD " & _
        "FROM " & Me!Combo23
 
    With CurrentDb
        Set qdf = .CreateQueryDef("MyQuery", sqltext)
        DoEvents
 
 
        DoCmd.RunSQL "INSERT INTO [Customer Table] (Customer, [Credit(USD)], " & _ 
            "[Credit(#)], [Credit(Euros)], [Credit(CAD)], and [Credit(YEN)], [Credit(AUD)]) " & _
            "SELECT '" & Me!Combo23 & "' AS Customer, SumCreditUSD, [SumCreditC#], " & _
            "SumCreditEuro, SumCreditCAD, SumCreditYen, SumCreditAUD " & _
            "FROM MyQuery"
 
        ' When you are finished delete the query object
        DoCmd.DeleteObject acQuery, "My_Query"
        qdf.Close
        Set qdf = Nothing
    End With
 
End Sub

I've included CreditAUD as you had it in the original thread, you can remove it if you don't need it. I've also left out CustomerID as I'm not sure where you are getting it from.

Mary

[kent christensen]

Mary,

I did need CreditAUD so thanks for including that. The Customer ID is a column next to the "customer" field that is already been manually typed into the table for each customer. So im not very good at VB code so i just cut and pasted what you wrote but it doesn't seem to work. I get the following error message "Invalid SQL statement; expected 'Delete','Insert','Procedure','Select' or 'Update'". I have included the code for the command button below. Could you please help me fix the code.
Thanks in advance.

Private Sub Command26_Click()
On Error GoTo Command26_Click_Err
 
    Dim qdf As QueryDef
Dim strSQL As String
 
    strSQL = "SELECT Sum([Credit(USD)]) AS SumCreditUSD, Sum([Credit(#)]) AS [SumCredit#], " & _
        "Sum([Credit(Euros)]) AS SumCreditEuro, Sum([Credit(CAD)]) AS SumCreditCAD, " & _
        "Sum([Credit(YEN)]) AS SumCreditYen, Sum([Credit(AUD)]) AS SumCreditAUD " & _
        "FROM " & Me!Combo23
 
    With CurrentDb
        Set qdf = .CreateQueryDef("MyQuery", sqltext)
        DoEvents
 
 
        DoCmd.RunSQL "INSERT INTO [Customer Table] (Customer, [Credit(USD)], " & _
            "[Credit(#)], [Credit(Euros)], [Credit(CAD)], and [Credit(YEN)], [Credit(AUD)]) " & _
            "SELECT '" & Me!Combo23 & "' AS Customer, SumCreditUSD, [SumCreditC#], " & _
            "SumCreditEuro, SumCreditCAD, SumCreditYen, SumCreditAUD " & _
            "FROM MyQuery"
 
        ' When you are finished delete the query object
        DoCmd.DeleteObject acQuery, "My_Query"
        qdf.Close
        Set qdf = Nothing
    End With
 
 
Command26_Click_Exit:
    Exit Sub
 
Command26_Click_Err:
    MsgBox Error$
    Resume Command26_Click_Exit
 
End Sub
 

[MMcCarthy]

I can get the customer name from the drop down box but is the customerID in the drop down box as well. When I say where do I get it, I mean where on the form can I find that value to input it into the table.

[MMcCarthy]

In the meantime, I don't see any real problem with what you copied and pasted. However, I have added a line of code at line 20 which will print out the query to the "Immediate" window. We should have a better idea what's going on then. Assuming the code stops on error again. Copy the query from the immediate window and then open a new query in Access design view. Then change the view to sql and paste in the query code. The error message should tell us what's wrong.

Private Sub Command26_Click()
On Error GoTo Command26_Click_Err
Dim qdf As QueryDef
Dim strSQL As String
 
    strSQL = "SELECT Sum([Credit(USD)]) AS SumCreditUSD, Sum([Credit(#)]) AS [SumCredit#], " & _
        "Sum([Credit(Euros)]) AS SumCreditEuro, Sum([Credit(CAD)]) AS SumCreditCAD, " & _
        "Sum([Credit(YEN)]) AS SumCreditYen, Sum([Credit(AUD)]) AS SumCreditAUD " & _
        "FROM " & Me!Combo23
 
    With CurrentDb
        Set qdf = .CreateQueryDef("MyQuery", strSQL)
        DoEvents
 
        strSQL = "INSERT INTO [Customer Table] (Customer, [Credit(USD)], " & _
            "[Credit(#)], [Credit(Euros)], [Credit(CAD)], and [Credit(YEN)], [Credit(AUD)]) " & _
            "SELECT '" & Me!Combo23 & "', SumCreditUSD, [SumCreditC#], " & _
            "SumCreditEuro, SumCreditCAD, SumCreditYen, SumCreditAUD " & _
            "FROM MyQuery"
        Debug.Print strSQL
        DoCmd.RunSQL strSQL
 
        ' When you are finished delete the query object
        DoCmd.DeleteObject acQuery, "My_Query"
        qdf.Close
        Set qdf = Nothing
 
    End With
 
Command26_Click_Exit:
    Exit Sub
 
Command26_Click_Err:
    MsgBox Error$
    Resume Command26_Click_Exit
 
End Sub

[kent christensen]

Mary,

For the customer ID should i create a new combo box with customer and customer ID. If so it will be "Combo30"

As for the code, it still gives me an error message that says "object My Query Already exists".

It is not printing the query to the "Immediate" window so it seems as if the query is not even running. Any ideas what could cause this.

[MMcCarthy]

Delete MyQuery, it's left over from when the code errored out last time and the code that deletes it never ran.

In combo30 is the CustomerID in the first or second column?

[kent christensen]

the customer id is in the 2nd column, with the first column being the customer. So what would the new code be with the new combo box. I also just deleted myquery.

[MMcCarthy]

This should work ...

Private Sub Command26_Click()
On Error GoTo Command26_Click_Err
Dim qdf As QueryDef
Dim strSQL As String
 
    strSQL = "SELECT Sum([Credit(USD)]) AS SumCreditUSD, Sum([Credit(#)]) AS [SumCredit#], " & _
        "Sum([Credit(Euros)]) AS SumCreditEuro, Sum([Credit(CAD)]) AS SumCreditCAD, " & _
        "Sum([Credit(YEN)]) AS SumCreditYen, Sum([Credit(AUD)]) AS SumCreditAUD " & _
        "FROM " & Me!Combo30
 
    With CurrentDb
        Set qdf = .CreateQueryDef("MyQuery", strSQL)
        DoEvents
 
        strSQL = "INSERT INTO [Customer Table] (Customer, CustomerID, [Credit(USD)], " & _
            "[Credit(#)], [Credit(Euros)], [Credit(CAD)], and [Credit(YEN)], [Credit(AUD)]) " & _
            "SELECT '" & Me!Combo30 & "', " & Me!Combo30.Column(1) & ", SumCreditUSD, [SumCreditC#], " & _
            "SumCreditEuro, SumCreditCAD, SumCreditYen, SumCreditAUD " & _
            "FROM MyQuery"
        Debug.Print strSQL
        DoCmd.RunSQL strSQL
 
        ' When you are finished delete the query object
        DoCmd.DeleteObject acQuery, "My_Query"
        qdf.Close
        Set qdf = Nothing
 
    End With
 
Command26_Click_Exit:
    Exit Sub
 
Command26_Click_Err:
    MsgBox Error$
    Resume Command26_Click_Exit
 
End Sub

[kent christensen]

It still does not seem to be working. I now get a message of Sytax error in INSERT INTO statement. I tried looking through the code if anything was wrong and cant see anything.
Also in line 24, should the "_" be deleted?

[MMcCarthy]

Sorry about that, yes delete that underscore.

Did you copy the sql from the immediate window and paste it into a query?

[kent christensen]

Mary,
Im sorry but i can't copy the sql from the window because when i click the command button, the error"Sytax error in INSERT INTO statement" shows and then in my navigation pane 'myquery' is there. These are the only things that happen.
Also when i click on the myquery, it does sum the correct table, however all the summed numbers are not in a correct currency format. Is this because the customer table that this query is going into has different currency formats. For example, the credit(#) format is £#,###.##, the credit(Yen) format is ¥#,### and the credit(euro) format is "Euro". The rest of the credits are in basic USD currency.

However, when checking the customer table that the button was pulling from, the currency was all in the correct format.

Also the myquery is still not deleting. Can you please help. Sorry for all the trouble.

The code for myquery is below:

SELECT Sum([Credit(USD)]) AS SumCreditUSD, Sum([Credit(#)]) AS [SumCredit#], Sum([Credit(Euros)]) AS SumCreditEuro, Sum([Credit(CAD)]) AS SumCreditCAD, Sum([Credit(YEN)]) AS SumCreditYen, Sum([Credit(AUD)]) AS SumCreditAUD
FROM AXA;
 

[kent christensen]

In 'myquery', AXA is the name of one of the customer tables. This is the value pulled from combo30.

[MMcCarthy]

OK so the first part is working fine. Don't worry about MyQuery not deleting. This is because the code is breaking before we get to that part.

However, I don't understand why the sql code is not appearing in the Immediate window. The debug should be running before that error.

You will need to delete MyQuery manually before running the code again.

[MMcCarthy]

As far as the currency issue is concerned. Can you run this query and see what happens to the currency.

SELECT Sum([Credit(USD)]) AS SumCreditUSD, Sum([Credit(#)]) AS [SumCredit#],
Sum([Credit(Euros)]) AS SumCreditEuro, Sum([Credit(CAD)]) AS SumCreditCAD,
Sum([Credit(YEN)]) AS SumCreditYen, Sum([Credit(AUD)]) AS SumCreditAUD
FROM AXA

[kent christensen]

okay so i deleted the myquery but i had a question about the new query you just posted. SHould i run it using hte same command or create a new query and run using it as the SQL code?

[MMcCarthy]

Just create a new query and run it as the sql code. I just want to see what the query on it's own does regarding the currency types.

[kent christensen]

okay i just ran the query and it gave me the sum values of the table, but all in USD currency. Was this what you needed to see?

Thanks

[MMcCarthy]

Yes it seems there is a problem with the table structure.

So if you open the AXA table in design view and go to Credit(Euro) for example. What is the currancy format set to?

[MMcCarthy]

Kent can you attach a sample table and form for me. You can't attach an mdb/accdb file directly but you can zip one up and then attach it.

[kent christensen]

The data type that shows in the AXA table is euro.

As for the zipped file, i have it sitting on my desktop, how do i get it so you can recieve it?

[MMcCarthy]

When you post a reply click on the Go Advanced button. Scroll down to manage attachments.

[kent christensen]

here it is. Thanks.
TRP Model.zip

[MMcCarthy]

OK I found the problem in the INSERT INTO statement. There was an And in there that shouldn't have been. That's what I get for copying and pasting :D

Try this ...

Private Sub Command26_Click()
On Error GoTo Command26_Click_Err
Dim qdf As QueryDef
Dim strSQL As String
 
    strSQL = "SELECT Sum([Credit(USD)]) AS SumCreditUSD, Sum([Credit(#)]) AS [SumCredit#], " & _
        "Sum([Credit(Euros)]) AS SumCreditEuro, Sum([Credit(CAD)]) AS SumCreditCAD, " & _
        "Sum([Credit(YEN)]) AS SumCreditYen, Sum([Credit(AUD)]) AS SumCreditAUD " & _
        "FROM " & Me!Combo30
 
    With CurrentDb
        Set qdf = .CreateQueryDef("MyQuery", strSQL)
        DoEvents
 
        strSQL = "INSERT INTO [Customer Table] (Customer, CustomerID, [Credit(USD)], " & _
            "[Credit(#)], [Credit(Euros)], [Credit(CAD)], [Credit(YEN)], [Credit(AUD)]) " & _
            "SELECT '" & Me!Combo30 & "', " & Me!Combo30.Column(1) & ", SumCreditUSD, [SumCreditC#], " & _
            "SumCreditEuro, SumCreditCAD, SumCreditYen, SumCreditAUD " & _
            "FROM MyQuery"
 
        DoCmd.RunSQL strSQL
 
        ' When you are finished delete the query object
        DoCmd.DeleteObject acQuery, "My_Query"
        qdf.Close
        Set qdf = Nothing
 
    End With
 
Command26_Click_Exit:
    Exit Sub
 
Command26_Click_Err:
    MsgBox Error$
    Resume Command26_Click_Exit
 
End Sub
 

[MMcCarthy]

I know it's still showing errors. Working on it now.

[kent christensen]

thank you for your help. I would be lost on this without you. Just let me know when you get the code working. Thanks!

[MMcCarthy]

Hi Kent

We designed this as an INSERT query which would add a record to the Customer table. However you have set up the customer table with all the customers preloaded. This leads me to believe you actually want an update query which would update each of these records.

Can you confirm?

Mary

[kent christensen]

yes that is correct if you can do that. I wasn't sure if that was possible.

[MMcCarthy]

OK, this appears to be working now. Try it out ...

Private Sub Command26_Click()
On Error GoTo Command26_Click_Err
Dim qdf As QueryDef
Dim strSQL As String
 
    strSQL = "SELECT Sum([Credit(USD)]) AS SumCreditUSD, Sum([Credit(#)]) AS [SumCredit#], " & _
        "Sum([Credit(Euros)]) AS SumCreditEuro, Sum([Credit(CAD)]) AS SumCreditCAD, " & _
        "Sum([Credit(YEN)]) AS SumCreditYen, Sum([Credit(AUD)]) AS SumCreditAUD " & _
        "FROM " & Me!Combo30
 
    With CurrentDb
        Set qdf = .CreateQueryDef("MyQuery", strSQL)
        DoEvents
 
        strSQL = "UPDATE [Customer Table] " & _
            "SET [Customer Table].[Credit(USD)] = DLookUp('[SumCreditUSD]','MyQuery'), " & _
            "[Customer Table].[Credit(#)] = DLookUp('[SumCredit#]','MyQuery'), " & _
            "[Customer Table].[Credit(Euros)] = DLookUp('[SumCreditEuro]','MyQuery'), " & _
            "[Customer Table].[Credit(CAD)] = DLookUp('[SumCreditCAD]','MyQuery'), " & _
            "[Customer Table].[Credit(YEN)] = DLookUp('[SumCreditYen]','MyQuery'), " & _
            "[Customer Table].[Credit(AUD)] = DLookUp('[SumCreditAUD]','MyQuery') " & _
            "WHERE [Customer Table].Customer='" & Me!Combo30 & "' " & _
            "AND [Customer Table].[Customer ID]='" & Me!Combo30.Column(1) & "';"
 
        DoCmd.RunSQL strSQL
 
        ' When you are finished delete the query object
        DoCmd.DeleteObject acQuery, "MyQuery"
        qdf.Close
        Set qdf = Nothing
 
    End With
 
Command26_Click_Exit:
    Exit Sub
 
Command26_Click_Err:
    MsgBox Error$
    Resume Command26_Click_Exit
 
End Sub
 

[kent christensen]

It works!!! Thank you for everything. Would it be okay to link this code to the other button on the form?

[MMcCarthy]

Just copy and paste out the body of the code ignoring the error handling code and it should be fine.

[kent christensen]

Great! Thank you very much. I have one further question about my database but ill create a new question. once again thanks!