• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1184
  • Last Modified:

Why is my database freezing?

I have a command button that executes a series of SQL based queries.  It appears as though everything is working fine, b/c no error pops up, it just freezes at the end, leaving me no option but to close the db entirely.  I am trying to open a query as the end result of clicking the button by using DoCmd.OpenQuery.  But the query never appears.  I even tried putting in message boxes before and after the open query line and even before the query code.  All the message boxes pop up, but the query still doesn't.

Any suggestions?

If you need to see the code, let me know.
0
pgerman
Asked:
pgerman
  • 12
  • 6
  • 2
1 Solution
 
jmoss111Commented:
post the db if there is no confidential data
0
 
pgermanAuthor Commented:
I will strip out the confidential data then attach it.
0
 
pgermanAuthor Commented:
I think the file is too big to attach.  Can I email it to you?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
pgermanAuthor Commented:
I had warnings and echos set to false.  When I keep echos on, it shows the query I am looking for at the end but it looks like it is in print preview mode.  Then it freezes
0
 
jmoss111Commented:
how big is it zipped?
0
 
pgermanAuthor Commented:
actually I figured it out.  Now i'm having another issue with it.  I'm getting 'Run time error 2001' 'You canceled the previous operation'
when I hit 'Update db' button

testdbfor-exp-exch.zip
0
 
pgermanAuthor Commented:
Please let me know what you think.  I am now focused on getting the 'update db' button on the main form to work, but please feel free to check out the other buttons/code to see if you have any other comments
0
 
rockiroadsCommented:
The error you got is due to you hitting cancel and not capturing it as it is treated as an error (error object has a value)

Either put a error handler in your code then handle this error, so continue running if err.number is 2001 or fail.

Also have u open a query, if you want to close it, you would be better of specifying it

last argumnet specifies whether to save or not. In this case, I have put no but you can put acSaveYes to save changes

DoCmd.Close acQuery, "queryname", acSaveNo    

0
 
pgermanAuthor Commented:
can you further explain the error handler?
0
 
rockiroadsCommented:
here you are

Public Sub SampleErrorHandling()
 
    On Error GoTo MessedUp
 
    Debug.Print "Line1"
   
    'If cancelled hit from this query, it will raise 2001 and goto code in MessedUp
    'Normal operations will resume and you will see Line2 appearing
    DoCmd.OpenQuery "queryname"
   
    Debug.Print "Line2"
   
    'If this fails then it will raise an error and goto code MessedUp
    'Currently it will stop (but you can code it to continue, add in Resume Next after msgbox)
    'Line3 will not appear, only on success will you see Line3
    CurrentDb.Execute "delete from dddhdhd"
   
    Debug.Print "Line3"
   
    'Important, you do not want to go to error handler so exit procedure
    Exit Sub
   
MessedUp:
    If err.Number > 0 And err.Number <> 2001 Then
        MsgBox err.Description
    Else
        Resume Next
    End If
End Sub

0
 
pgermanAuthor Commented:
I'm still not having any luck incorporating this into my code.  how do you use the 'Debug.Print "Line2" ' expressions?  Do you put something else in besides Line 1 2 or 3?
0
 
pgermanAuthor Commented:
Now I'm getting a Datatype mismatch error in Criteria, but it won't say what query it is.  Could this have to do with max Character length?
0
 
pgermanAuthor Commented:
Should the error handler be in a different sub?
0
 
rockiroadsCommented:
Ive taken the code from that and put in a error handler.
Always recommended to indent properly so it makes reading easier

You can use currentdb.execute "sql", dbFailOnError instead of docmd.runsql, saves u setting warnings and also gives you numer of rows affected

My preference is to define all variables at the start of the module
Also on the top of the code module to put option explicit. This will force you to define all variables you use


eg


Private Sub Update_db_DblClick(Cancel As Integer)
   
    'Dim db As DAO.Database
    'Dim DiscLend As DAO.QueryDef, DLSQL As String
    Dim db As DAO.Database
    Dim CountTOPSA As DAO.QueryDef, ctsSQL As String
    Dim CountTOPGA As DAO.QueryDef, ctgSQL As String
    Dim TOPnc As DAO.QueryDef, tncSQL As String
    Dim TOPother As DAO.QueryDef, toSQL As String
    Dim TOPtotal As DAO.QueryDef, ttSQL As String
    Dim TOPfidelity As DAO.QueryDef, tfSQL As String
    Dim TOPG1 As DAO.QueryDef, tgSQL As String
    Dim TOPdml As DAO.QueryDef, tdSQL As String
    Dim TOPslmu As DAO.QueryDef, tsSQL As String
    Dim CMRapplicable2TOP As DAO.QueryDef, c2tSQL As String
   
    Dim DiscLend As DAO.QueryDef, DLSQL As String
    Dim Excls As DAO.QueryDef, ESQL As String
    Dim NC As DAO.QueryDef, NCSQL As String
    Dim DIR As DAO.QueryDef, DSQL As String
    Dim total As DAO.QueryDef, TSQL As String
    Dim CountUP As DAO.QueryDef, CUPSQL As String
    Dim CountCI As DAO.QueryDef, CCISQL As String
    Dim CountAN As DAO.QueryDef, CANSQL As String
    Dim DIR_SECF As DAO.QueryDef, DSSQL As String
    'Dim CMRappTOP As DAO.QueryDef, caSQL As String
    Dim dateserial As String
    Dim sql As String
   
    Dim sql2 As String
   
On Error GoTo UpdateError

    Application.DoCmd.echo False
   
    '   DoCmd.RunSQL "DELETE CMR_BAnk_Earnings_Applicable_to_TOP.* FROM CMR_BAnk_Earnings_Applicable_to_TOP;"
    'Insert Time code to the other tables: Manual, Overall Commments table, Unresolved_Accounts_Trending_tbl, _
    New_Accounts_Trending_tbl, TOP_File_Summary_tbl, TOP_to_CMR_Comparison_tbl, MD_Summary_tbl, GEM_to_CMR_High_Level_Comp
   
    'Manual table
    'DoCmd.RunSQL "INSERT INTO Manual_tbl" & _
        "([TC_Fld]) " & "VALUES ('" & Time_Code_txt & "');"
    'Overall Comments table
    CurrentDb.Execute "INSERT INTO Overall_Comments_From_Main_Form_tbl" & _
        "([Time_Code_Fld]) " & "VALUES ('" & Time_Code_txt & "')", dbFailOnError
    Debug.Print "Number of records inserted into Overall_Comments_From_Main_Form_tbl is " & CurrentDb.RecordsAffected
   
    'UNRS tbl
    CurrentDb.Execute "INSERT INTO Unresolved_Accounts_Trending_tbl" & _
        "([Time_Code]) " & "VALUES ('" & Time_Code_txt & "'", dbFailOnError
    Debug.Print "Number of records inserted into Unresolved_Accounts_Trending_tbl is " & CurrentDb.RecordsAffected
   
    'New_Accounts_Trending_tbl
    CurrentDb.Execute "INSERT INTO New_Accounts_Trending_tbl" & _
        "([Time_Code]) " & "VALUES ('" & Time_Code_txt & "'", dbFailOnError
    Debug.Print "Number of records inserted into New_Accounts_Trending_tbl is " & CurrentDb.RecordsAffected
   
    'TOP_File_Summary_tbl
    'DoCmd.RunSQL "INSERT INTO TOP_File_Summary_tbl" & _
     '   "([Time_Code]) " & "VALUES ('" & Time_Code_txt & "');"
    'TOP_to_CMR_Comparison_tbl
    'DoCmd.RunSQL "INSERT INTO TOP_to_CMR_Comparison_tbl" & _
      '  "([Time_Code]) " & "VALUES ('" & Time_Code_txt & "');"
    'MD_Summary_tbl
    CurrentDb.Execute "INSERT INTO MD_Summary_tbl" & _
        "([Time_Code]) " & "VALUES ('" & Time_Code_txt & "'", dbFailOnError
    Debug.Print "Number of records inserted into MD_Summary_tbl is " & CurrentDb.RecordsAffected
   
    'GEM_to_CMR_High_Level_Comp
    'DoCmd.RunSQL "INSERT INTO GEM_to_CMR_High_Level_Comp" & _
     '   "([Time_Code]) " & "VALUES ('" & Time_Code_txt & "');"
   
    'set values for TOP file summary tbl and make a TOP file summary QUERY, see if that works
   
   
    Set db = CurrentDb
    'Set DiscLend = db.QueryDefs("qryDiscLend")
   
    Set CountTOPSA = db.QueryDefs("Count_of_Unq_SA_frm_TOP_for_Spc_TC")
    Set CountTOPGA = db.QueryDefs("Count_of_Unique_GA_from_TOP_for_Spec_TC")
    Set TOPnc = db.QueryDefs("Non_Custody_Bank_Earnings_Current_TC")
    Set TOPother = db.QueryDefs("Other_Bank_Earnings_Current_TC")
    Set TOPtotal = db.QueryDefs("Total_Bank_Earnings_Current_TC")
    Set TOPfidelity = db.QueryDefs("Fidelity_Bank_Earnings_Current_TC")
    Set TOPG1 = db.QueryDefs("GLOBAL1_Bank_Earnings_Current_TC")
    Set TOPdml = db.QueryDefs("DML_Bank_Earnings_Current_TC")
    Set TOPslmu = db.QueryDefs("SLMU_Bank_Earnings_Current_TC")
    Set CMRapplicable2TOP = db.QueryDefs("CMRbeat")
   
    'Discretionary only calculation qry
    'DLSQL = "Select Sum(masterdata.[" & Time_Code_txt & "]) from " & _
    '"masterdata WHERE masterdata.[Sub Prod Name]='SEC LENDING - DISCRETIONARY';"
    'DiscLend.sql = DLSQL
    'DoCmd.OpenQuery "qryDiscLend"
    'DoCmd.Close
   
    'Count of TOP Source Accounts qry
    ctsSQL = "SELECT Count(Unique_SA_List_TOP_For_Spec_TC.SourceAccount) AS CountOfSourceAccount,Unique_SA_List_TOP_For_Spec_TC.fldTIME_CODE " & _
            "FROM Unique_SA_List_TOP_For_Spec_TC " & _
            "GROUP BY Unique_SA_List_TOP_For_Spec_TC.fldTIME_CODE;"
    CountTOPSA.sql = ctsSQL
    DoCmd.OpenQuery "Count_of_Unq_SA_frm_TOP_for_Spc_TC"
   
   
    'Count of TOP Group Accounts qry
    ctgSQL = "SELECT Count(Unique_Group_Accounts_List_TOP_For_Spec_TC.GroupAccount) AS CountOfGroupAccount,Unique_Group_Accounts_List_TOP_For_Spec_TC.fldTIME_CODE  " & _
            "FROM Unique_Group_Accounts_List_TOP_For_Spec_TC " & _
            "GROUP BY Unique_Group_Accounts_List_TOP_For_Spec_TC.fldTIME_CODE;"
    CountTOPGA.sql = ctgSQL
    DoCmd.OpenQuery "Count_of_Unique_GA_from_TOP_for_Spec_TC"
    DoCmd.Close
   
    'TOP Non Custody Calculation
    tncSQL = "SELECT Sum(SumEarnings2009.BankEarnings) AS SumOfBankEarnings, TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
            "FROM TIME_CODE_REFERENCE_TABLE INNER JOIN SumEarnings2009 ON TIME_CODE_REFERENCE_TABLE.fldTOP_CYCLE_DATE = SumEarnings2009.CycleDate " & _
            "WHERE (((SumEarnings2009.Status)='T')) " & _
            "GROUP BY TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
            "HAVING (((TIME_CODE_REFERENCE_TABLE.fldTIME_CODE)=[Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt]));"
    TOPnc.sql = tncSQL
    DoCmd.OpenQuery "Non_Custody_Bank_Earnings_Current_TC"
    DoCmd.Close
   
    'TOP Other Calculation
    toSQL = "SELECT Sum(SumEarnings2009.BankEarnings) AS SumOfBankEarnings, TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
            "FROM TIME_CODE_REFERENCE_TABLE INNER JOIN SumEarnings2009 ON TIME_CODE_REFERENCE_TABLE.fldTOP_CYCLE_DATE = SumEarnings2009.CycleDate " & _
            "WHERE (((SumEarnings2009.LoanStatus)='A' Or (SumEarnings2009.LoanStatus)='S')) " & _
            "GROUP BY TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
            "HAVING (((TIME_CODE_REFERENCE_TABLE.fldTIME_CODE)=[Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt]));"
    TOPother.sql = toSQL
    DoCmd.OpenQuery "Other_Bank_Earnings_Current_TC"
    DoCmd.Close
   
    'TOP total Calculation
    ttSQL = "SELECT Sum(SumEarnings2009.BankEarnings) AS SumOfBankEarnings,TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
            "FROM TIME_CODE_REFERENCE_TABLE INNER JOIN SumEarnings2009 ON TIME_CODE_REFERENCE_TABLE.fldTOP_CYCLE_DATE = SumEarnings2009.CycleDate " & _
            "GROUP BY TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
            "HAVING (((TIME_CODE_REFERENCE_TABLE.fldTIME_CODE)=[Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt]));"
    TOPtotal.sql = ttSQL
    DoCmd.OpenQuery "Total_Bank_Earnings_Current_TC"
    DoCmd.Close
   
    'TOP fidelity Calculation
    tfSQL = "SELECT Sum(SumEarnings2009.BankEarnings) AS SumOfBankEarnings,TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
            "FROM TIME_CODE_REFERENCE_TABLE INNER JOIN SumEarnings2009 ON TIME_CODE_REFERENCE_TABLE.fldTOP_CYCLE_DATE = SumEarnings2009.CycleDate " & _
            "WHERE (((SumEarnings2009.LoanStatus)='B')) " & _
            "GROUP BY TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
            "HAVING (((TIME_CODE_REFERENCE_TABLE.fldTIME_CODE)=[Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt]));"
    TOPfidelity.sql = tfSQL
    DoCmd.OpenQuery "Fidelity_Bank_Earnings_Current_TC"
    DoCmd.Close
   
    'TOP Global1 Calculation
    tgSQL = "SELECT Sum(SumEarnings2009.BankEarnings) AS SumOfBankEarnings, TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
            "FROM TIME_CODE_REFERENCE_TABLE INNER JOIN SumEarnings2009 ON TIME_CODE_REFERENCE_TABLE.fldTOP_CYCLE_DATE = SumEarnings2009.CycleDate " & _
            "WHERE (((SumEarnings2009.LoanStatus)='G')) " & _
            "GROUP BY TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
            "HAVING (((TIME_CODE_REFERENCE_TABLE.fldTIME_CODE)=[Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt]));"
    TOPG1.sql = tgSQL
    DoCmd.OpenQuery "GLOBAL1_Bank_Earnings_Current_TC"
    DoCmd.Close
   
    'TOP DML Calculation
    tdSQL = "SELECT Sum(SumEarnings2009.BankEarnings) AS SumOfBankEarnings,TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
            "FROM TIME_CODE_REFERENCE_TABLE INNER JOIN SumEarnings2009 ON TIME_CODE_REFERENCE_TABLE.fldTOP_CYCLE_DATE = SumEarnings2009.CycleDate " & _
            "WHERE (((SumEarnings2009.LoanStatus)='I')) " & _
            "GROUP BY TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
            "HAVING (((TIME_CODE_REFERENCE_TABLE.fldTIME_CODE)=[Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt]));"
    TOPdml.sql = tdSQL
    DoCmd.OpenQuery "DML_Bank_Earnings_Current_TC"
    DoCmd.Close
   
    'TOP SLMU Calculation
    tsSQL = "SELECT Sum(SumEarnings2009.BankEarnings) AS SumOfBankEarnings,TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
            "FROM TIME_CODE_REFERENCE_TABLE INNER JOIN SumEarnings2009 ON TIME_CODE_REFERENCE_TABLE.fldTOP_CYCLE_DATE=SumEarnings2009.CycleDate " & _
            "WHERE (((SumEarnings2009.LoanStatus)='D')) " & _
            "GROUP BY TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
            "HAVING (((TIME_CODE_REFERENCE_TABLE.fldTIME_CODE)=[Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt]));"
    TOPslmu.sql = tsSQL
    DoCmd.OpenQuery "SLMU_Bank_Earnings_Current_TC"
    DoCmd.Close
   
    'CMR applicable to TOP calculation
    c2tSQL = "SELECT Sum([DML_Fld]+[GLOBAL1_Fld]+[SLMU_Fld]+[FONGADJS_Fld]+[FONGOTHER_Fld]-[ATT_fld]-[BOM_fld]-[JPMAM_fld]-[MANUALOTHER_fld]) AS Total_CMR_Bank_Earnings_Applicable_to_TOP, Fong_tbl.Time_Code_Fld " & _
            "FROM Fong_tbl INNER JOIN Manual_tbl ON Fong_tbl.Time_Code_Fld = Manual_tbl.TC_Fld " & _
            "GROUP BY Fong_tbl.Time_Code_Fld " & _
            "HAVING (((Fong_tbl.Time_Code_Fld)=[Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt]));"
    CMRapplicable2TOP.sql = c2tSQL
    DoCmd.OpenQuery "CMRbeat"
    DoCmd.Close
   
   
    'DoCmd.OpenQuery "DML_Bank_Earnings_Current_TC"
    'DoCmd.Close
    'DoCmd.OpenQuery "Count_of_Unique_GA_from_TOP_for_Spec_TC"
    'DoCmd.Close
    'DoCmd.OpenQuery "Count_of_Unq_SA_frm_TOP_for_Spc_TC"
    'DoCmd.Close
    'DoCmd.OpenQuery "GLOBAL1_Bank_Earnings_Current_TC"
    'DoCmd.Close
    'DoCmd.OpenQuery "Non_Custody_Bank_Earnings_Current_TC"
    'DoCmd.Close
    'DoCmd.OpenQuery "Other_Bank_Earnings_Current_TC"
    'DoCmd.Close
    'DoCmd.OpenQuery "SLMU_Bank_Earnings_Current_TC"
    'DoCmd.Close
    'DoCmd.OpenQuery "Total_Bank_Earnings_Current_TC"
    'DoCmd.Close
    'DoCmd.OpenQuery "Fidelity_Bank_Earnings_Current_TC"
    'DoCmd.Close
   
     'DoCmd.RunSQL "UPDATE TOP_File_Summary_tbl " & _
      '  "SET[SLMU_Bank_Earnings]=SLMU, [DML_Bank_Earnings]=DML, [GLOBAL1_Bank_Earnings]=GLOBAL1, [Fidelity_Bank_Earnings]=Fid, [Other_Bank_Earnings]=Other, [Non_Custody_Bank_Earnings]=NonCust, [Total_Bank_Earnings]=total, [Count_of_Group_Accounts]=Count_TOP_GA, [Count_of_Source_Accounts]=Count_TOP_SA " & _
       ' "WHERE[Time_Code]='" & Time_Code_txt & "';"
     
     'set values and update MD summary table
     'Dim db As DAO.Database
   
   
   
    Set db = CurrentDb
    Set DiscLend = db.QueryDefs("qryDiscLend")
    Set Excls = db.QueryDefs("qryExclusives")
    Set NC = db.QueryDefs("qryNonCustody")
    Set DIR = db.QueryDefs("qryDirected")
    Set total = db.QueryDefs("qryTotal")
    Set CountUP = db.QueryDefs("qryCountUP")
    Set CountCI = db.QueryDefs("qryCountCustID")
    Set CountAN = db.QueryDefs("qryCountAcctNum")
    Set DIR_SECF = db.QueryDefs("qryDirected_SECF")
    'Set CMRappTOP = db.QueryDefs("CMRbeat")
    dateserial = Nz(Me.Time_Code_txt.Value, "")
   
    'Discretionary only calculation qry
    DLSQL = "Select Sum(masterdata.[" & Time_Code_txt & "]) from " & _
            "masterdata WHERE masterdata.[Sub Prod Name]='SEC LENDING - DISCRETIONARY';"
    DiscLend.sql = DLSQL
    DoCmd.OpenQuery "qryDiscLend"
    DoCmd.Close
   
    'Exclusives only calculation qry
    ESQL = "Select Sum(masterdata.[" & Time_Code_txt & "]) from " & _
            "masterdata WHERE masterdata.[Sub Prod Name]='SEC LENDING - EXCLUSIVE';"
    Excls.sql = ESQL
    DoCmd.OpenQuery "qryExclusives"
    DoCmd.Close
   
    'Non Custody only calculation qry
    NCSQL = "Select Sum(masterdata.[" & Time_Code_txt & "]) from " & _
            "masterdata WHERE masterdata.[Sub Prod Name]='SEC LENDING - NON CUSTODY';"
    NC.sql = NCSQL
    DoCmd.OpenQuery "qryNonCustody"
    DoCmd.Close
   
    'Directed only calculation qry
    DSQL = "Select Sum(masterdata.[" & Time_Code_txt & "]) from " & _
            "masterdata WHERE masterdata.[Sub Prod Name]='SEC LENDING - DIRECTED';"
    DIR.sql = DSQL
    DoCmd.OpenQuery "qryDirected"
    DoCmd.Close
   
    'Directed_SECF calculation qry
    DSSQL = "Select Sum(masterdata.[" & Time_Code_txt & "]) from " & _
            "masterdata WHERE masterdata.[Sub Prod Name]='SEC LENDING - DIRECTED' AND masterdata.[Feeder ID]='JPMCSECF';"
    DIR_SECF.sql = DSSQL
    DoCmd.OpenQuery "qryDirected_SECF"
    DoCmd.Close
   
    'Total Discretionary, Exclusives and Noncustody calculation qry
    TSQL = "Select Sum(masterdata.[" & Time_Code_txt & "]) from " & _
            "masterdata WHERE(((masterdata.[Sub Prod Name])='SEC LENDING - NON CUSTODY' Or (masterdata.[Sub Prod Name])='SEC LENDING - DISCRETIONARY' Or (masterdata.[Sub Prod Name])='SEC LENDING - EXCLUSIVE'));"
    total.sql = TSQL
    DoCmd.OpenQuery "qryTotal"
    DoCmd.Close
   
    'Count of UltP IDs for Discretionary, Exclusives and Noncustody qry
    CUPSQL = "SELECT Count(masterdata.[ULTP ID]) AS [CountOfULTP ID] FROM( " & _
            "SELECT masterdata.[ULTP ID] FROM masterdata WHERE(((masterdata.[Sub Prod Name])='SEC LENDING - NON CUSTODY' " & _
            "Or (masterdata.[Sub Prod Name])='SEC LENDING - DISCRETIONARY' Or (masterdata.[Sub Prod Name])='SEC LENDING - EXCLUSIVE') " & _
            "AND ((masterdata.[" & Time_Code_txt & "])<>0)) " & _
            "GROUP BY masterdata.[ULTP ID]);"
    CountUP.sql = CUPSQL
    DoCmd.OpenQuery "qryCountUP"
    DoCmd.Close
   
    'Count of Cust IDs for Discretionary, Exclusives and Noncustody qry
    CCISQL = "SELECT Count(masterdata.[CUST ID]) AS [CountOfCUST ID] FROM( " & _
            "SELECT masterdata.[CUST ID] FROM masterdata WHERE(((masterdata.[Sub Prod Name])='SEC LENDING - NON CUSTODY' " & _
            "Or (masterdata.[Sub Prod Name])='SEC LENDING - DISCRETIONARY' Or (masterdata.[Sub Prod Name])='SEC LENDING - EXCLUSIVE') " & _
            "AND ((masterdata.[" & Time_Code_txt & "])<>0)) " & _
            "GROUP BY masterdata.[CUST ID]);"
    CountCI.sql = CCISQL
    DoCmd.OpenQuery "qryCountCustID"
    DoCmd.Close
   
    'Count of CAS Acct Nums for Discretionary, Exclusives and Noncustody qry
    CANSQL = "SELECT Count(masterdata.[CAS ACCT NUMBER]) AS [CountOfCAN] FROM( " & _
            "SELECT masterdata.[CAS ACCT NUMBER] FROM masterdata WHERE(((masterdata.[Sub Prod Name])='SEC LENDING - NON CUSTODY' " & _
            "Or (masterdata.[Sub Prod Name])='SEC LENDING - DISCRETIONARY' Or (masterdata.[Sub Prod Name])='SEC LENDING - EXCLUSIVE') " & _
            "AND ((masterdata.[" & Time_Code_txt & "])<>0)) " & _
            "GROUP BY masterdata.[CAS ACCT NUMBER]);"
    CountAN.sql = CANSQL
    DoCmd.OpenQuery "qryCountAcctNum"
    DoCmd.Close
   
    CurrentDb.Execute "UPDATE MD_Summary_tbl " & _
        "SET[Discretionary]=DL, [Exclusives]=EX, [NonCustody]=NC, [Directed_All]=DI, [Directed_SECF]=DIR_SECF, [Total_Disc_NC_Excl]=TO, [Count_of_UltP]=CU, [Count_of_Cust]=CC, [Count_of_CAN]=CC2 " & _
       "WHERE[Time_Code]='" & Time_Code_txt & "'", dbFailOnError
    Debug.Print "Number of Records Updated in MD_Summary_tbl is " & CurrentDb.RecordsAffected
   
    'DoCmd.OpenTable "MD_Summary_tbl"
   
    'Set values and update Total GEM to CMR comp tbl
    DoCmd.OpenQuery "Total_CMR_SPEC_TC"
   
'NOTE THIS CLOSE, NEED TO DETERMINE IF YOU WANT TO SAVE OR NOT AND APPLY TO OTHER CLOSES
    DoCmd.Close acQuery, "Total_CMR_SPEC_TC", acSaveYes
   
    DoCmd.OpenQuery "Total_GEM_SPEC_TC"
    DoCmd.Close
   
    DoCmd.OpenQuery "TOP_Summary_Append_Qry"
    DoCmd.Close
   
    'DoCmd.OpenQuery "TOP_to_CMR_APPEND_qry"
    'DoCmd.Close
    DoCmd.OpenQuery "Total_CMR_SPEC_TC"
    DoCmd.Close
   
    DoCmd.OpenQuery "Total_GEM_SPEC_TC"
    DoCmd.Close
   
    DoCmd.OpenQuery "GEM2CMR_APPEND"
    DoCmd.Close
   
    sql2 = "UPDATE GEM_to_CMR_High_Level_Comp " & _
            "SET[CMR_Total]=TCMR, [GEM_Total]=TGEM " & _
            "WHERE[Time_Code]='" & dateserial & "';"
       
    'caSQL = "SELECT Sum([DML_Fld]+[GLOBAL1_Fld]+[SLMU_Fld]+[FONGADJS_Fld]+[FONGOTHER_Fld]-[ATT_fld]-[BOM_fld]-[JPMAM_fld]-[MANUALOTHER_fld]) AS Total_CMR_Bank_Earnings_Applicable_to_TOP, Fong_tbl.Time_Code_Fld " & _
    '"FROM Fong_tbl INNER JOIN Manual_tbl ON Fong_tbl.Time_Code_Fld = Manual_tbl.TC_Fld " & _
    '"GROUP BY Fong_tbl.Time_Code_Fld " & _
    '"HAVING (((Fong_tbl.Time_Code_Fld)=[Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt]));"
    ' CMRappTOP.sql = caSQL
    'DoCmd.OpenQuery "CMRbeat"
    'DoCmd.Close
     
        ' Call MsgBox(dateserial)
    ' DoCmd.OpenQuery "CMR_BAnk_Earnings_Applicable_to_TOP"
     'DoCmd.Close
     
     'DoCmd.OpenQuery "Total_Bank_Earnings_Current_TC"
     'DoCmd.Close
     
        ' sql = "INSERT INTO TOP_to_CMR_Comparison_tbl ( Time_Code, Total_Bank_Earnings_TOP, Total_CMR_Bank_Earnings_Applicable_to_TOP ) " & _
        ' "SELECT CMRbeat.Time_Code_Fld AS Time_Code, Total_Bank_Earnings_Current_TC.SumOfBankEarnings AS Total_Bank_Earnings_TOP," & _
        ' "CMRbeat.Total_CMR_Bank_Earnings_Applicable_to_TOP AS " & _
        ' "Total_CMR_Bank_Earnings_Applicable_to_TOP " & _
        ' "FROM CMRbeat INNER JOIN Total_Bank_Earnings_Current_TC ON CMRbeat.Time_Code_Fld = Total_Bank_Earnings_Current_TC.fldTIME_CODE;"
     
        ' Call MsgBox(sql)
     
         'Call DoCmd.SetWarnings(False)
        ' Call DoCmd.RunSQL(sql)
    CurrentDb.Execute sql2, dbFailOnError
       
    Application.DoCmd.echo True
   
    Exit Sub
   
UpdateError:
    If err.Number > 0 Then
        If err.Number = 2001 Then
            MsgBox "Cancelled hit"
            If MsgBox("Do you wish to continue running?", vbYesNo + vbQuestion, "Confirm") = vbYes Then
                Resume Next
            End If
        Else
            MsgBox err.Description
        End If
    End If
End Sub

0
 
rockiroadsCommented:
The way I would of coded this is just have the one querydef object and keep reusing that. But dont make those changes now, first get it to work
Then look at how I closed the queries


The other way to do this without having to worry about updating and running queries is to put that sql in a string then use currentdb.execute

Also some of your queries dont seem to make sense, you are selecting but not doing much with it

eg

    ctsSQL = "SELECT Count(Unique_SA_List_TOP_For_Spec_TC.SourceAccount) AS CountOfSourceAccount,Unique_SA_List_TOP_For_Spec_TC.fldTIME_CODE " & _
            "FROM Unique_SA_List_TOP_For_Spec_TC " & _
            "GROUP BY Unique_SA_List_TOP_For_Spec_TC.fldTIME_CODE;"
    CountTOPSA.sql = ctsSQL
    DoCmd.OpenQuery "Count_of_Unq_SA_frm_TOP_for_Spc_TC"



So you run a query which produces a count, but what are you doing with it?

If u need the results you best create a recordset to handle this and to get the count
0
 
pgermanAuthor Commented:
I started this project 2 weeks ago with no VBA experience, so I realize it may not be perfect or 100% efficient but I just need to get it working.  It is still giving me problems.  I made some changes, for example to out the Update queries that refered back to textbox values on the form.  Then I put in your Update Error code.

Now it just gives me a bunch of pop ups, the last being 'data type mismatch' then it freezes up.

Here is the code.  Also see attachment.  Main form, update db button.

Private Sub Update_db_DblClick(Cancel As Integer)

Dim db As DAO.Database
Dim CountTOPSA As DAO.QueryDef, ctsSQL As String
Dim CountTOPGA As DAO.QueryDef, ctgSQL As String
Dim TOPnc As DAO.QueryDef, tncSQL As String
Dim TOPother As DAO.QueryDef, toSQL As String
Dim TOPtotal As DAO.QueryDef, ttSQL As String
Dim TOPfidelity As DAO.QueryDef, tfSQL As String
Dim TOPG1 As DAO.QueryDef, tgSQL As String
Dim TOPdml As DAO.QueryDef, tdSQL As String
Dim TOPslmu As DAO.QueryDef, tsSQL As String
Dim CMRapplicable2TOP As DAO.QueryDef, c2tSQL As String

Dim DiscLend As DAO.QueryDef, DLSQL As String
Dim Excls As DAO.QueryDef, ESQL As String
Dim NC As DAO.QueryDef, NCSQL As String
Dim DIR As DAO.QueryDef, DSQL As String
Dim total As DAO.QueryDef, TSQL As String
Dim CountUP As DAO.QueryDef, CUPSQL As String
Dim CountCI As DAO.QueryDef, CCISQL As String
Dim CountAN As DAO.QueryDef, CANSQL As String
Dim DIR_SECF As DAO.QueryDef, DSSQL As String
'Dim CMRappTOP As DAO.QueryDef, caSQL As String
Dim dateserial As String
'Dim sql As String

On Error GoTo UpdateError


'DoCmd.SetWarnings False
Application.DoCmd.Echo False
'On Error GoTo MessedUp

'   DoCmd.RunSQL "DELETE CMR_BAnk_Earnings_Applicable_to_TOP.* FROM CMR_BAnk_Earnings_Applicable_to_TOP;"
'Insert Time code to the other tables: Manual, Overall Commments table, Unresolved_Accounts_Trending_tbl, _
New_Accounts_Trending_tbl, TOP_File_Summary_tbl, TOP_to_CMR_Comparison_tbl, MD_Summary_tbl, GEM_to_CMR_High_Level_Comp

'Manual table
'DoCmd.RunSQL "INSERT INTO Manual_tbl" & _
    "([TC_Fld]) " & "VALUES ('" & Time_Code_txt & "');"
'Overall Comments table
DoCmd.RunSQL "INSERT INTO Overall_Comments_From_Main_Form_tbl" & _
    "([Time_Code_Fld]) " & "VALUES ('" & Time_Code_txt & "');"
'UNRS tbl
DoCmd.RunSQL "INSERT INTO Unresolved_Accounts_Trending_tbl" & _
    "([Time_Code]) " & "VALUES ('" & Time_Code_txt & "');"
'New_Accounts_Trending_tbl
DoCmd.RunSQL "INSERT INTO New_Accounts_Trending_tbl" & _
    "([Time_Code]) " & "VALUES ('" & Time_Code_txt & "');"
'TOP_File_Summary_tbl
'DoCmd.RunSQL "INSERT INTO TOP_File_Summary_tbl" & _
 '   "([Time_Code]) " & "VALUES ('" & Time_Code_txt & "');"
'TOP_to_CMR_Comparison_tbl
'DoCmd.RunSQL "INSERT INTO TOP_to_CMR_Comparison_tbl" & _
  '  "([Time_Code]) " & "VALUES ('" & Time_Code_txt & "');"
'MD_Summary_tbl
'DoCmd.RunSQL "INSERT INTO MD_Summary_tbl" & _
 '   "([Time_Code]) " & "VALUES ('" & Time_Code_txt & "');"
'GEM_to_CMR_High_Level_Comp
'DoCmd.RunSQL "INSERT INTO GEM_to_CMR_High_Level_Comp" & _
 '   "([Time_Code]) " & "VALUES ('" & Time_Code_txt & "');"

'set values for TOP file summary tbl and make a TOP file summary QUERY, see if that works

'Dim db As DAO.Database
'Dim DiscLend As DAO.QueryDef, DLSQL As String

Set db = CurrentDb
'Set DiscLend = db.QueryDefs("qryDiscLend")
Set CountTOPSA = db.QueryDefs("Count_of_Unq_SA_frm_TOP_for_Spc_TC")
Set CountTOPGA = db.QueryDefs("Count_of_Unique_GA_from_TOP_for_Spec_TC")
Set TOPnc = db.QueryDefs("Non_Custody_Bank_Earnings_Current_TC")
Set TOPother = db.QueryDefs("Other_Bank_Earnings_Current_TC")
Set TOPtotal = db.QueryDefs("Total_Bank_Earnings_Current_TC")
Set TOPfidelity = db.QueryDefs("Fidelity_Bank_Earnings_Current_TC")
Set TOPG1 = db.QueryDefs("GLOBAL1_Bank_Earnings_Current_TC")
Set TOPdml = db.QueryDefs("DML_Bank_Earnings_Current_TC")
Set TOPslmu = db.QueryDefs("SLMU_Bank_Earnings_Current_TC")
Set CMRapplicable2TOP = db.QueryDefs("CMRbeat")

'Discretionary only calculation qry
'DLSQL = "Select Sum(masterdata.[" & Time_Code_txt & "]) from " & _
'"masterdata WHERE masterdata.[Sub Prod Name]='SEC LENDING - DISCRETIONARY';"
'DiscLend.sql = DLSQL
'DoCmd.OpenQuery "qryDiscLend"
'DoCmd.Close

'Count of TOP Source Accounts qry
ctsSQL = "SELECT Count(Unique_SA_List_TOP_For_Spec_TC.SourceAccount) AS CountOfSourceAccount,Unique_SA_List_TOP_For_Spec_TC.fldTIME_CODE " & _
"FROM Unique_SA_List_TOP_For_Spec_TC " & _
"GROUP BY Unique_SA_List_TOP_For_Spec_TC.fldTIME_CODE;"
CountTOPSA.sql = ctsSQL
DoCmd.OpenQuery "Count_of_Unq_SA_frm_TOP_for_Spc_TC"
DoCmd.Close acQuery, "Count_of_Unq_SA_frm_TOP_for_Spc_TC", acSaveYes

'Count of TOP Group Accounts qry
ctgSQL = "SELECT Count(Unique_Group_Accounts_List_TOP_For_Spec_TC.GroupAccount) AS CountOfGroupAccount,Unique_Group_Accounts_List_TOP_For_Spec_TC.fldTIME_CODE  " & _
"FROM Unique_Group_Accounts_List_TOP_For_Spec_TC " & _
"GROUP BY Unique_Group_Accounts_List_TOP_For_Spec_TC.fldTIME_CODE;"
CountTOPGA.sql = ctgSQL
DoCmd.OpenQuery "Count_of_Unique_GA_from_TOP_for_Spec_TC"
DoCmd.Close acQuery, "Count_of_Unique_GA_from_TOP_for_Spec_TC", acSaveYes

'TOP Non Custody Calculation
tncSQL = "SELECT Sum(SumEarnings2009.BankEarnings) AS SumOfBankEarnings, TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
"FROM TIME_CODE_REFERENCE_TABLE INNER JOIN SumEarnings2009 ON TIME_CODE_REFERENCE_TABLE.fldTOP_CYCLE_DATE = SumEarnings2009.CycleDate " & _
"WHERE (((SumEarnings2009.Status)='T')) " & _
"GROUP BY TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
"HAVING (((TIME_CODE_REFERENCE_TABLE.fldTIME_CODE)=[Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt]));"
TOPnc.sql = tncSQL
DoCmd.OpenQuery "Non_Custody_Bank_Earnings_Current_TC"
DoCmd.Close acQuery, "Non_Custody_Bank_Earnings_Current_TC", acSaveYes
'TOP Other Calculation
toSQL = "SELECT Sum(SumEarnings2009.BankEarnings) AS SumOfBankEarnings, TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
"FROM TIME_CODE_REFERENCE_TABLE INNER JOIN SumEarnings2009 ON TIME_CODE_REFERENCE_TABLE.fldTOP_CYCLE_DATE = SumEarnings2009.CycleDate " & _
"WHERE (((SumEarnings2009.LoanStatus)='A' Or (SumEarnings2009.LoanStatus)='S')) " & _
"GROUP BY TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
"HAVING (((TIME_CODE_REFERENCE_TABLE.fldTIME_CODE)=[Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt]));"
TOPother.sql = toSQL
DoCmd.OpenQuery "Other_Bank_Earnings_Current_TC"
DoCmd.Close acQuery, "Other_Bank_Earnings_Current_TC", acSaveYes

'TOP total Calculation
ttSQL = "SELECT Sum(SumEarnings2009.BankEarnings) AS SumOfBankEarnings,TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
"FROM TIME_CODE_REFERENCE_TABLE INNER JOIN SumEarnings2009 ON TIME_CODE_REFERENCE_TABLE.fldTOP_CYCLE_DATE = SumEarnings2009.CycleDate " & _
"GROUP BY TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
"HAVING (((TIME_CODE_REFERENCE_TABLE.fldTIME_CODE)=[Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt]));"
TOPtotal.sql = ttSQL
DoCmd.OpenQuery "Total_Bank_Earnings_Current_TC"
DoCmd.Close acQuery, "Total_Bank_Earnings_Current_TC", acSaveYes

'TOP fidelity Calculation
tfSQL = "SELECT Sum(SumEarnings2009.BankEarnings) AS SumOfBankEarnings,TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
"FROM TIME_CODE_REFERENCE_TABLE INNER JOIN SumEarnings2009 ON TIME_CODE_REFERENCE_TABLE.fldTOP_CYCLE_DATE = SumEarnings2009.CycleDate " & _
"WHERE (((SumEarnings2009.LoanStatus)='B')) " & _
"GROUP BY TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
"HAVING (((TIME_CODE_REFERENCE_TABLE.fldTIME_CODE)=[Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt]));"
TOPfidelity.sql = tfSQL
DoCmd.OpenQuery "Fidelity_Bank_Earnings_Current_TC"
DoCmd.Close acQuery, "Fidelity_Bank_Earnings_Current_TC", acSaveYes

'TOP Global1 Calculation
tgSQL = "SELECT Sum(SumEarnings2009.BankEarnings) AS SumOfBankEarnings, TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
"FROM TIME_CODE_REFERENCE_TABLE INNER JOIN SumEarnings2009 ON TIME_CODE_REFERENCE_TABLE.fldTOP_CYCLE_DATE = SumEarnings2009.CycleDate " & _
"WHERE (((SumEarnings2009.LoanStatus)='G')) " & _
"GROUP BY TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
"HAVING (((TIME_CODE_REFERENCE_TABLE.fldTIME_CODE)=[Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt]));"
TOPG1.sql = tgSQL
DoCmd.OpenQuery "GLOBAL1_Bank_Earnings_Current_TC"
DoCmd.Close acQuery, "GLOBAL1_Bank_Earnings_Current_TC", acSaveYes

'TOP DML Calculation
tdSQL = "SELECT Sum(SumEarnings2009.BankEarnings) AS SumOfBankEarnings,TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
"FROM TIME_CODE_REFERENCE_TABLE INNER JOIN SumEarnings2009 ON TIME_CODE_REFERENCE_TABLE.fldTOP_CYCLE_DATE = SumEarnings2009.CycleDate " & _
"WHERE (((SumEarnings2009.LoanStatus)='I')) " & _
"GROUP BY TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
"HAVING (((TIME_CODE_REFERENCE_TABLE.fldTIME_CODE)=[Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt]));"
TOPdml.sql = tdSQL
DoCmd.OpenQuery "DML_Bank_Earnings_Current_TC"
DoCmd.Close acQuery, "DML_Bank_Earnings_Current_TC", acSaveYes

'TOP SLMU Calculation
tsSQL = "SELECT Sum(SumEarnings2009.BankEarnings) AS SumOfBankEarnings,TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
"FROM TIME_CODE_REFERENCE_TABLE INNER JOIN SumEarnings2009 ON TIME_CODE_REFERENCE_TABLE.fldTOP_CYCLE_DATE=SumEarnings2009.CycleDate " & _
"WHERE (((SumEarnings2009.LoanStatus)='D')) " & _
"GROUP BY TIME_CODE_REFERENCE_TABLE.fldTIME_CODE " & _
"HAVING (((TIME_CODE_REFERENCE_TABLE.fldTIME_CODE)=[Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt]));"
TOPslmu.sql = tsSQL
DoCmd.OpenQuery "SLMU_Bank_Earnings_Current_TC"
DoCmd.Close acQuery, "SLMU_Bank_Earnings_Current_TC", acSaveYes

'CMR applicable to TOP calculation
c2tSQL = "SELECT Sum([DML_Fld]+[GLOBAL1_Fld]+[SLMU_Fld]+[FONGADJS_Fld]+[FONGOTHER_Fld]-[ATT_fld]-[BOM_fld]-[JPMAM_fld]-[MANUALOTHER_fld]) AS Total_CMR_Bank_Earnings_Applicable_to_TOP, Fong_tbl.Time_Code_Fld " & _
"FROM Fong_tbl INNER JOIN Manual_tbl ON Fong_tbl.Time_Code_Fld = Manual_tbl.TC_Fld " & _
"GROUP BY Fong_tbl.Time_Code_Fld " & _
"HAVING (((Fong_tbl.Time_Code_Fld)=[Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt]));"
CMRapplicable2TOP.sql = c2tSQL
DoCmd.OpenQuery "CMRbeat"
DoCmd.Close acQuery, "CMRbeat", acSaveYes




'DoCmd.OpenQuery "DML_Bank_Earnings_Current_TC"
'DoCmd.Close
'DoCmd.OpenQuery "Count_of_Unique_GA_from_TOP_for_Spec_TC"
'DoCmd.Close
'DoCmd.OpenQuery "Count_of_Unq_SA_frm_TOP_for_Spc_TC"
'DoCmd.Close
'DoCmd.OpenQuery "GLOBAL1_Bank_Earnings_Current_TC"
'DoCmd.Close
'DoCmd.OpenQuery "Non_Custody_Bank_Earnings_Current_TC"
'DoCmd.Close
'DoCmd.OpenQuery "Other_Bank_Earnings_Current_TC"
'DoCmd.Close
'DoCmd.OpenQuery "SLMU_Bank_Earnings_Current_TC"
'DoCmd.Close
'DoCmd.OpenQuery "Total_Bank_Earnings_Current_TC"
'DoCmd.Close
'DoCmd.OpenQuery "Fidelity_Bank_Earnings_Current_TC"
'DoCmd.Close

 'DoCmd.RunSQL "UPDATE TOP_File_Summary_tbl " & _
  '  "SET[SLMU_Bank_Earnings]=SLMU, [DML_Bank_Earnings]=DML, [GLOBAL1_Bank_Earnings]=GLOBAL1, [Fidelity_Bank_Earnings]=Fid, [Other_Bank_Earnings]=Other, [Non_Custody_Bank_Earnings]=NonCust, [Total_Bank_Earnings]=total, [Count_of_Group_Accounts]=Count_TOP_GA, [Count_of_Source_Accounts]=Count_TOP_SA " & _
   ' "WHERE[Time_Code]='" & Time_Code_txt & "';"
 
 'set values and update MD summary table
 'Dim db As DAO.Database




Set db = CurrentDb
Set DiscLend = db.QueryDefs("qryDiscLend")
Set Excls = db.QueryDefs("qryExclusives")
Set NC = db.QueryDefs("qryNonCustody")
Set DIR = db.QueryDefs("qryDirected")
Set total = db.QueryDefs("qryTotal")
Set CountUP = db.QueryDefs("qryCountUP")
Set CountCI = db.QueryDefs("qryCountCustID")
Set CountAN = db.QueryDefs("qryCountAcctNum")
Set DIR_SECF = db.QueryDefs("qryDirected_SECF")
'Set CMRappTOP = db.QueryDefs("CMRbeat")
dateserial = Nz(Me.Time_Code_txt.Value, "")

'Discretionary only calculation qry
DLSQL = "Select Sum(masterdata.[" & Time_Code_txt & "]) from " & _
"masterdata WHERE masterdata.[Sub Prod Name]='SEC LENDING - DISCRETIONARY';"
DiscLend.sql = DLSQL
DoCmd.OpenQuery "qryDiscLend"
DoCmd.Close acQuery, "qryDiscLend", acSaveYes

'Exclusives only calculation qry
ESQL = "Select Sum(masterdata.[" & Time_Code_txt & "]) from " & _
"masterdata WHERE masterdata.[Sub Prod Name]='SEC LENDING - EXCLUSIVE';"
Excls.sql = ESQL
DoCmd.OpenQuery "qryExclusives"
DoCmd.Close acQuery, "qryExclusives", acSaveYes

'Non Custody only calculation qry
NCSQL = "Select Sum(masterdata.[" & Time_Code_txt & "]) from " & _
"masterdata WHERE masterdata.[Sub Prod Name]='SEC LENDING - NON CUSTODY';"
NC.sql = NCSQL
DoCmd.OpenQuery "qryNonCustody"
DoCmd.Close acQuery, "qryNonCustody", acSaveYes

'Directed only calculation qry
DSQL = "Select Sum(masterdata.[" & Time_Code_txt & "]) from " & _
"masterdata WHERE masterdata.[Sub Prod Name]='SEC LENDING - DIRECTED';"
DIR.sql = DSQL
DoCmd.OpenQuery "qryDirected"
DoCmd.Close acQuery, "qryDirected", acSaveYes

'Directed_SECF calculation qry
DSSQL = "Select Sum(masterdata.[" & Time_Code_txt & "]) from " & _
"masterdata WHERE masterdata.[Sub Prod Name]='SEC LENDING - DIRECTED' AND masterdata.[Feeder ID]='JPMCSECF';"
DIR_SECF.sql = DSSQL
DoCmd.OpenQuery "qryDirected_SECF"
DoCmd.Close acQuery, "qryDirected_SECF", acSaveYes

'Total Discretionary, Exclusives and Noncustody calculation qry
TSQL = "Select Sum(masterdata.[" & Time_Code_txt & "]) from " & _
"masterdata WHERE(((masterdata.[Sub Prod Name])='SEC LENDING - NON CUSTODY' Or (masterdata.[Sub Prod Name])='SEC LENDING - DISCRETIONARY' Or (masterdata.[Sub Prod Name])='SEC LENDING - EXCLUSIVE'));"
total.sql = TSQL
DoCmd.OpenQuery "qryTotal"
DoCmd.Close acQuery, "qryTotal", acSaveYes

'Count of UltP IDs for Discretionary, Exclusives and Noncustody qry
CUPSQL = "SELECT Count(masterdata.[ULTP ID]) AS [CountOfULTP ID] FROM( " & _
"SELECT masterdata.[ULTP ID] FROM masterdata WHERE(((masterdata.[Sub Prod Name])='SEC LENDING - NON CUSTODY' " & _
"Or (masterdata.[Sub Prod Name])='SEC LENDING - DISCRETIONARY' Or (masterdata.[Sub Prod Name])='SEC LENDING - EXCLUSIVE') " & _
"AND ((masterdata.[" & Time_Code_txt & "])<>0)) " & _
"GROUP BY masterdata.[ULTP ID]);"
CountUP.sql = CUPSQL
'DoCmd.SetWarnings False
DoCmd.OpenQuery "qryCountUP"
DoCmd.Close acQuery, "qryCountUP", acSaveYes

'Count of Cust IDs for Discretionary, Exclusives and Noncustody qry
CCISQL = "SELECT Count(masterdata.[CUST ID]) AS [CountOfCUST ID] FROM( " & _
"SELECT masterdata.[CUST ID] FROM masterdata WHERE(((masterdata.[Sub Prod Name])='SEC LENDING - NON CUSTODY' " & _
"Or (masterdata.[Sub Prod Name])='SEC LENDING - DISCRETIONARY' Or (masterdata.[Sub Prod Name])='SEC LENDING - EXCLUSIVE') " & _
"AND ((masterdata.[" & Time_Code_txt & "])<>0)) " & _
"GROUP BY masterdata.[CUST ID]);"
CountCI.sql = CCISQL
DoCmd.OpenQuery "qryCountCustID"
DoCmd.Close acQuery, "qryCountCustID", acSaveYes

'Count of CAS Acct Nums for Discretionary, Exclusives and Noncustody qry
CANSQL = "SELECT Count(masterdata.[CAS ACCT NUMBER]) AS [CountOfCAN] FROM( " & _
"SELECT masterdata.[CAS ACCT NUMBER] FROM masterdata WHERE(((masterdata.[Sub Prod Name])='SEC LENDING - NON CUSTODY' " & _
"Or (masterdata.[Sub Prod Name])='SEC LENDING - DISCRETIONARY' Or (masterdata.[Sub Prod Name])='SEC LENDING - EXCLUSIVE') " & _
"AND ((masterdata.[" & Time_Code_txt & "])<>0)) " & _
"GROUP BY masterdata.[CAS ACCT NUMBER]);"
CountAN.sql = CANSQL
DoCmd.OpenQuery "qryCountAcctNum"
DoCmd.Close acQuery, "qryCountAcctNum", acSaveYes

'DoCmd.RunSQL "UPDATE MD_Summary_tbl " & _
 '   "SET[Discretionary]=DL, [Exclusives]=EX, [NonCustody]=NC, [Directed_All]=DI, [Directed_SECF]=DIR_SECF, [Total_Disc_NC_Excl]=TO, [Count_of_UltP]=CU, [Count_of_Cust]=CC, [Count_of_CAN]=CC2 " & _
  ' "WHERE[Time_Code]='" & Time_Code_txt & "';"
'DoCmd.OpenTable "MD_Summary_tbl"
DoCmd.OpenQuery "MD_Summary_tbl_APPEND_qry"
DoCmd.Close acQuery, "MD_Summary_tbl_APPEND_qry", acSaveYes
'Set values and update Total GEM to CMR comp tbl
DoCmd.OpenQuery "Total_CMR_SPEC_TC"
DoCmd.Close acQuery, "Total_CMR_SPEC_TC", acSaveYes

DoCmd.OpenQuery "Total_GEM_SPEC_TC"
DoCmd.Close acQuery, "Total_GEM_SPEC_TC", acSaveYes

DoCmd.OpenQuery "TOP_Summary_Append_Qry"
DoCmd.Close acQuery, "TOP_Summary_Append_Qry", acSaveYes

'DoCmd.OpenQuery "TOP_to_CMR_APPEND_qry"
'DoCmd.Close
DoCmd.OpenQuery "Total_CMR_SPEC_TC"
DoCmd.Close acQuery, "Total_CMR_SPEC_TC", acSaveYes

DoCmd.OpenQuery "Total_GEM_SPEC_TC"
DoCmd.Close acQuery, "Total_GEM_SPEC_TC", acSaveYes

DoCmd.OpenQuery "GEM2CMR_APPEND"
DoCmd.Close acQuery, "GEM2CMR_APPEND", acSaveYes

DoCmd.OpenQuery "GEM_to_CMR_High_Level_Comp_APPEND_qry"
DoCmd.Close acQuery, "GEM_to_CMR_High_Level_Comp_APPEND_qry", acSaveYes

'Dim sql2 As String
 '   sql2 = "UPDATE GEM_to_CMR_High_Level_Comp " & _
  '  "SET[CMR_Total]=TCMR, [GEM_Total]=TGEM " & _
   ' "WHERE[Time_Code]='" & dateserial & "';"
   
'caSQL = "SELECT Sum([DML_Fld]+[GLOBAL1_Fld]+[SLMU_Fld]+[FONGADJS_Fld]+[FONGOTHER_Fld]-[ATT_fld]-[BOM_fld]-[JPMAM_fld]-[MANUALOTHER_fld]) AS Total_CMR_Bank_Earnings_Applicable_to_TOP, Fong_tbl.Time_Code_Fld " & _
'"FROM Fong_tbl INNER JOIN Manual_tbl ON Fong_tbl.Time_Code_Fld = Manual_tbl.TC_Fld " & _
'"GROUP BY Fong_tbl.Time_Code_Fld " & _
'"HAVING (((Fong_tbl.Time_Code_Fld)=[Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt]));"
' CMRappTOP.sql = caSQL
'DoCmd.OpenQuery "CMRbeat"
'DoCmd.Close
 
    ' Call MsgBox(dateserial)
' DoCmd.OpenQuery "CMR_BAnk_Earnings_Applicable_to_TOP"
 'DoCmd.Close
 
 'DoCmd.OpenQuery "Total_Bank_Earnings_Current_TC"
 'DoCmd.Close
 
    ' sql = "INSERT INTO TOP_to_CMR_Comparison_tbl ( Time_Code, Total_Bank_Earnings_TOP, Total_CMR_Bank_Earnings_Applicable_to_TOP ) " & _
    ' "SELECT CMRbeat.Time_Code_Fld AS Time_Code, Total_Bank_Earnings_Current_TC.SumOfBankEarnings AS Total_Bank_Earnings_TOP," & _
    ' "CMRbeat.Total_CMR_Bank_Earnings_Applicable_to_TOP AS " & _
    ' "Total_CMR_Bank_Earnings_Applicable_to_TOP " & _
    ' "FROM CMRbeat INNER JOIN Total_Bank_Earnings_Current_TC ON CMRbeat.Time_Code_Fld = Total_Bank_Earnings_Current_TC.fldTIME_CODE;"
 
    ' Call MsgBox(sql)
 
     'Call DoCmd.SetWarnings(False)
    ' Call DoCmd.RunSQL(sql)
    ' Call DoCmd.RunSQL(sql2)
   
'DoCmd.SetWarnings True
Application.DoCmd.Echo True
Exit Sub


UpdateError:
    If Err.Number > 0 Then
        If Err.Number = 2001 Then
            MsgBox "Cancelled hit"
            If MsgBox("Do you wish to continue running?", vbYesNo + vbQuestion, "Confirm") = vbYes Then
                Resume Next
            End If
        Else
            MsgBox Err.Description
        End If
    End If

   
End Sub

testdb.zip
0
 
pgermanAuthor Commented:
The logic behind all of my individual queries is to produce a result, once per month, referencing the time code on the main form.  I am then using append queries to pull in all the individual calculated query results into tables that are in the format of one record per time code/month.

When I started it, this was how I was getting it to work, I see now how it may not be perfect.  One other problem is I am trying to compare to tables of data that are in completley different format.  For example, 'SumEarnings2009' has a cycle date field, with thousands of entires per cycledate, while 'masterdata' has each month as an individual field.
0
 
pgermanAuthor Commented:
rockiroads,
Are you still working on this or should I move on?  Are there any other experts that may be able to help?

I don't mean to bother you, thank you for your help.
0
 
rockiroadsCommented:
Hi mate, sorry I got busy with something. Now back here.

Okay, no worries about your experience, its a lot to learn if you are not familiar with it.

The data type mismatch means you are either referring to a numeric but assigning a string or the other way round. Let me try your latest db and run it
0
 
rockiroadsCommented:
firstly did you see my code suggestions? using currentdb.execute instead of docmd.runsql, this gives you the number of records affected and also avoids that popup. That popup comes because docmd.setwarnings is not there anymore.

One thing, this variable "dateserial"

dateserial = Nz(Me.Time_Code_txt.Value, "")

this is a reserved word so I recommend you change it. Put an s in front would help

"sDateSerial" instead of "dateserial"


what value did u put in the time_code_txt field? I put in a value but getting an error so havent got as far as your datatype mismatch yet. I got it cant find some query.


Taking the first SQL

'Discretionary only calculation qry
DLSQL = "Select Sum(masterdata.[" & Time_Code_txt & "]) from " & _
"masterdata WHERE masterdata.[Sub Prod Name]='SEC LENDING - DISCRETIONARY';"

What is happening here is you are taking the value of Time_code_txt and treating it as a column in the table. I dont think that is a right design. Think of the constant changes you have to keep making to the table. Then what will you do when you can add any more columns?

Have u managed to identify the type mismatch?

One way is to do this

go to the first line of code, say a docmd.runsql and hit F9. This will put a red dot on the left hand side. This is a breakpoint

Now run your code, double click and it will stop at that line. Hit F8 and it will run the next line. Keep doing this until you identify the line where it fails.

then let me know




0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 12
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now