Query not running(/not doing what it's supposed to) using VBA

[DonkeyKong]

I have a section of code that I have running as soon as the db is opened. It is supposed to refresh the tables so that they are populated with the most current data. I keep having an issue with my JOIN queries not running correctly or if they do run correctly, not inserting the values pulled into the tables like it's supposed to. I'm stumped. If this won't work the whole db is essentially worthless.

When I run the query in access it works for both the REGNLOC and HOLDERS tables. I get two pop ups. One says "Are you sure you want to add all these records." The other says "Access set (0) to Null and didn't add (1) record because of a type conversion failure."

I have my warnings turned off and application.echo as false so I figured this would take care of these warnings. Could this be what is causing the failure. Thanks for any help.

VBA code:

Option Compare Database
Option Explicit
Private Sub UserForm_Activate()
Dim prevDate As Date
Dim strSQL, tbl, fld As String
Dim counter, x As Integer
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
On Error Resume Next
DoCmd.SetWarnings False
'Check date on form to determine if update is necessary
 
prevDate = Forms![Holders Database]!DateLabel.Caption
'Hide update actions from user
Application.Echo False
frmStatus.status = "Exporting..."
    'Export previous days' data for backup purposes
    ExportPrevious
    frmStatus.status = "Getting updated CUSIP values..."
    'Query to update CUSIP table
    DoCmd.OpenQuery ("VALS_CUSIP_TABLE")
 
    frmStatus.status = "Getting updated ACCOUNT values..."
 
    'Query to update ACCOUNT table
    DoCmd.OpenQuery ("VALS_ACCOUNT_TABLE")
 
    frmStatus.status = "Getting updated HOLDING values part 1..."
 
[B]    'Query to update REGNLOC table[/B]
[B]    DoCmd.OpenQuery ("VALS_REGNLOC_TABLE")[/B]
 
    frmStatus.status = "Getting updated HOLDING values part 2..."
 
[B]    'Query to update HOLDING table[/B]
[B]    DoCmd.OpenQuery ("VALS_HOLDING_TABLE")[/B]
 
    frmStatus.status = "Getting trade data..."
 
    'Query to get purchases, receives, sales, distributions
    DoCmd.OpenQuery ("ORIGINAL_FACE_DIFFERENCES")
    strSQL = "INSERT INTO OFDIFF SELECT TRIM(CUSIP), TRIM(ACCOUNT), CURRENT_OF, PREVIOUS_OF, " & _
         DATE & " AS CURRENT_DATE " & prevDate & " AS PREVIOUS_DATE FROM VALS_CUSIP_TABLE"
    CurrentDb.Execute strSQL, dbFailOnError
    DoEvents
    DoCmd.Close acQuery, "ORIGINAL_FACE_DIFFERENCES", acSaveYes
 
    frmStatus.status = "Updating Tables..."
 
    'Insert previous days HOLDERS table data to PHTV table
 
    strSQL = "DELETE FROM PHTV WHERE CUSIP <> ''"
    CurrentDb.Execute strSQL, dbFailOnError
    DoEvents
 
    strSQL = "INSERT INTO PHTV SELECT * FROM HOLDERS"
    CurrentDb.Execute strSQL, dbFailOnError
    DoEvents
 
    'Clear previous days' data from tables
    For x = 1 To 5
        If x = 1 Then
            tbl = "HOLDERS"
            fld = "CUSIP"
        ElseIf x = 2 Then
            tbl = "ACCOUNT"
            fld = "ACCOUNT"
        ElseIf x = 3 Then
            tbl = "CUSIP"
            fld = "CUSIP"
        ElseIf x = 4 Then
            tbl = "REGNLOC"
            fld = "CUSIP"
        Else
            tbl = "DATE_LABEL"
            fld = "DATE"
            strSQL = "DELETE FROM " & tbl & " WHERE " & fld & " <> #1/1/1999#"
            CurrentDb.Execute strSQL, dbFailOnError
            DoEvents
            Exit For
        End If
        strSQL = "DELETE FROM " & tbl & " WHERE " & fld & " <> ''"
        CurrentDb.Execute strSQL, dbFailOnError
        DoEvents
    Next x
 
    'Insert current data to CUSIP table
    strSQL = "INSERT INTO CUSIP SELECT * FROM VALS_CUSIP_TABLE"
    CurrentDb.Execute strSQL, dbFailOnError
    DoEvents
 
    'Insert current data to ACCOUNT table
    strSQL = "INSERT INTO ACCOUNT SELECT * FROM VALS_ACCOUNT_TABLE"
    CurrentDb.Execute strSQL, dbFailOnError
    DoEvents
 
    'Insert current data to REGNLOC table
[B]    strSQL = "INSERT INTO REGNLOC SELECT * FROM VALS_REGNLOC_TABLE WHERE CUSIP <> ''"[/B]
[B]    CurrentDb.Execute strSQL, dbFailOnError[/B]
[B]    DoEvents[/B]
 
    'Insert current data to HOLDERS table
[B]    strSQL = "INSERT INTO HOLDERS SELECT * FROM VALS_HOLDING_TABLE WHERE CUSIP <> ''"[/B]
[B]    CurrentDb.Execute strSQL, dbFailOnError[/B]
[B]    DoEvents[/B]
 
    frmStatus.status = "Ending..."
    DoCmd.Close acQuery, "VALS_CUSIP_TABLE", acSaveYes
    DoCmd.Close acQuery, "VALS_ACCOUNT_TABLE", acSaveYes
    DoCmd.Close acQuery, "VALS_REGNLOC_TABLE", acSaveYes
    DoCmd.Close acQuery, "VALS_HOLDING_TABLE", acSaveYes
'Enable application views so user can use Access again
Application.Echo True
'Update the date on the Form
Forms![Holders Database]!DateLabel.Caption = DATE
    'Add the date to the DATE_LABEL table so that the date does not revert in the label box
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("DATE_LABEL", dbOpenDynaset)
 
    rst.AddNew
    rst![DATE] = DATE
    rst.Update
 
    Forms![Holders Database]!DateLabel.Caption = DATE
DoCmd.SetWarnings True
Unload frmStatus
End Sub
Sub ExportPrevious()
    Dim outputFileName As String
 
    outputFileName = "I:\P & I - CMO Bond Group\Data\CMO_DATA_DB " & Format(DATE, "MM-DD-YYYY") & ".xlsx"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "CUSIP", outputFileName, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "ACCOUNT", outputFileName, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "HOLDERS", outputFileName, True
 
End Sub

VALS_REGNLOC_TABLE SQL in Access:

SELECT Trim(dbo_ASSET.CUSIP_ID) AS CUSIP, Trim(dbo_TAX_DETAIL.Account_ID) AS ACCOUNT, Trim(dbo_TAX_DETAIL.Registration_Cd) AS REG, Trim(dbo_TAX_DETAIL.Location_Cd) AS LOC, Sum(dbo_TAX_DETAIL.Shares_Par_Value_Qty) AS CURRENT_FACE, Sum(dbo_TAX_DETAIL.Original_Face_Val) AS ORIGINAL_FACE
FROM dbo_TAX_DETAIL INNER JOIN dbo_ASSET ON dbo_TAX_DETAIL.Property_Num = dbo_ASSET.Property_Num
WHERE (((dbo_TAX_DETAIL.Sale_Dt) Is Null) AND ((dbo_ASSET.Security_Tp)=32 Or (dbo_ASSET.Security_Tp)=30 Or (dbo_ASSET.Security_Tp)=42))
GROUP BY dbo_ASSET.CUSIP_ID, dbo_TAX_DETAIL.Account_ID, dbo_TAX_DETAIL.Registration_Cd, dbo_TAX_DETAIL.Location_Cd, dbo_TAX_DETAIL.Property_Num
ORDER BY dbo_ASSET.CUSIP_ID, dbo_TAX_DETAIL.Account_ID;

VALS_HOLDING_TABLE SQL in Access:

SELECT Trim(dbo_ASSET.CUSIP_ID) AS CUSIP, Trim(dbo_TAX_DETAIL.Account_ID) AS ACCOUNT, Sum(dbo_TAX_DETAIL.Shares_Par_Value_Qty) AS CURRENT_FACE, Sum(dbo_TAX_DETAIL.Original_Face_Val) AS ORIGINAL_FACE
FROM dbo_TAX_DETAIL INNER JOIN dbo_ASSET ON dbo_TAX_DETAIL.Property_Num = dbo_ASSET.Property_Num
WHERE (((dbo_TAX_DETAIL.Sale_Dt) Is Null) AND ((dbo_ASSET.Security_Tp)=32) or (dbo_ASSET.Security_Tp)=30 or (dbo_ASSET.Security_Tp)=42)
GROUP BY dbo_ASSET.CUSIP_ID, dbo_TAX_DETAIL.Account_ID, dbo_TAX_DETAIL.Property_Num
ORDER BY dbo_ASSET.CUSIP_ID, dbo_TAX_DETAIL.Account_ID;

 

[JHB]

Comment out all the error handling, DoCmd.SetWarnings and
Application.Echo until you program runs without problem.
On of the worst error handling is On Error Resume Next, because you'll not get any error message, when an error occur, the program will only continuer as no problem where raised.

 

[DonkeyKong]

Thanks JHB. You're right. I was covering up errors. Its getting hung up on a NULL in the primary key. Is there a way I can handle this error? Like turn the NULL value to a 0?

On a side note, I'm getting a recordset not updateable for a UNION ALL query. Can I not run a UNION query through VBA? This refers to the ORIGINAL_FACE_DIFFERNECES query in the code.

SELECT HOLDERS.CUSIP, HOLDERS.ACCOUNT, HOLDERS.ORIGINAL_FACE AS CURRENT_OF, HTV.ORIGINAL_FACE AS PREVIOUS_OF
FROM HOLDERS LEFT JOIN HTV ON HOLDERS.CUSIP = HTV.CUSIP
WHERE HOLDERS.ORIGINAL_FACE <> HTV.ORIGINAL_FACE
AND HOLDERS.ACCOUNT = HTV.ACCOUNT
UNION ALL
SELECT HOLDERS.CUSIP, HOLDERS.ACCOUNT, HOLDERS.ORIGINAL_FACE, HTV.ORIGINAL_FACE
FROM HOLDERS LEFT JOIN HTV ON HOLDERS.CUSIP = HTV.CUSIP
WHERE HTV.CUSIP IS NULL
UNION ALL SELECT HTV.CUSIP, HTV.ACCOUNT, HOLDERS.ORIGINAL_FACE, HTV.ORIGINAL_FACE
FROM HTV LEFT JOIN HOLDERS ON HTV.CUSIP = HOLDERS.CUSIP
WHERE HOLDERS.CUSIP IS NULL;

 

[JHB]

Thanks JHB. You're right. I was covering up errors. Its getting hung up on a NULL in the primary key.

If you want to use error handling, then use "on error goto YourErrorHandling", because then you can trap by err.number, for some errors you know would raise, (and have a message box popping up for all other type of errors). Then you have much more hand of what happens in your code. (And will never get into the mood you where in for a couple hours ago, that you will give up your program, because you couldn't see what happen in your code )

Is there a way I can handle this error? Like turn the NULL value to a 0?

Use the Nz function, (look in the Help-file):
Nz([YouRfieldName], 0)

On a side note, I'm getting a recordset not updateable for a UNION ALL query. Can I not run a UNION query through VBA? This refers to the ORIGINAL_FACE_DIFFERNECES query in the code.

No - a Union query taken different field(s) from different tables and get it looks like it is coming from one, MS-Access doesn't know which table to update, (and you do either ).
If you want it to be update able - then you have to put the result into a (temporary) table.