Why does my INSERT INTO code not work?

[AlRojo]

First, apologies for all the code; I'm not sure what is most relevant to my problem.

I am trying to use the code below to select Records from an Access table called [Client_Details] and insert them into another table (in the same database) called [ClientMailMerge Selection]. Once there I will then run a mail merge operation.

The problem is, no Records are being inserted and no error message is ever displayed.

I have inherited the code and am effectively stuck with it, so suggestions for obvious errors in the existing code preferred please. I have little knowledge of SQL or VBA

Oh, and I am using Access 2002, but the database is formatted as Access 2000.

Many thanks for any help you can offer.

Private Sub btnSearch_Click()
Dim rcsLookUp As Recordset
Dim SQLOrderBy As String
Dim lCount As Long
 
    On Error GoTo Err_btnSearch_Click
    DoCmd.Hourglass True
    DoEvents
 
    SQLQ = "INSERT INTO ClientMailMergeSelection "
    SQLQ = SQLQ & "(CaseID, REF, E_DATE, SNAME, INITIAL, INITIAL1, TITLE, "
    SQLQ = SQLQ & "Sal_Too, Sal_Dear, ADD1, ADD2, ADD3, ADD4, PCODE, "
    SQLQ = SQLQ & "TEL, Email, HouseNumber, Flat, HouseName, StreetLine1 )"
    SQLQ = SQLQ & "SELECT "
    SQLQ = SQLQ & "Client_Details.CaseID, Client_Details.REF, Client_Details.E_DATE, Client_Details.SNAME, "
    SQLQ = SQLQ & "Client_Details.INITIAL, Client_Details.INITIAL1, "
    SQLQ = SQLQ & "Client_Details.TITLE, Client_Details.Sal_Too, "
    SQLQ = SQLQ & "Client_Details.Sal_Dear, Client_Details.ADD1, "
    SQLQ = SQLQ & "Client_Details.ADD2, Client_Details.ADD3, "
    SQLQ = SQLQ & "Client_Details.ADD4, Client_Details.PCODE, "
    SQLQ = SQLQ & "Client_Details.TEL, Client_Details.Email, "
    SQLQ = SQLQ & "Client_Details.HouseNumber, Client_Details.Flat, "
    SQLQ = SQLQ & "Client_Details.HouseName, Client_Details.StreetLine1 "
 
    SQLQ = SQLQ & "FROM Client_Details "
 
    SQLWhere = ""
 
    If ComboLender <> "" Then
        SQLWhere = "LenderID = " & ComboLender.Column(0) & " "
    End If
 
    ' status
    If Not IsNull(ComboStatus) Then
        If SQLWhere <> "" Then SQLWhere = SQLWhere & "AND "
        SQLWhere = SQLWhere & " Client_Details.[STATUS] = '" & ComboStatus & "' "
    End If
 
    If ComboMedia <> "" Then
        If SQLWhere <> "" Then
            SQLWhere = SQLWhere & "AND "
        End If
        SQLWhere = SQLWhere & "Media_ID = " & ComboMedia.Column(0) & " "
    End If
 
    ' security type
    If Not IsNull(ComboSec) Then
        If SQLWhere <> "" Then SQLWhere = SQLWhere & "AND "
        SQLWhere = SQLWhere & "[client_details].[sec] = " & ComboSec & " "
    End If
 
    If txtDateFrom <> "01/01/1998" Or txtDateTo <> Date Then
        ' only do date checking if theye have changed the dates otherwise
        ' no point we just get em all
        If Not IsNull(txtDateFrom) And Not IsNull(txtDateTo) Then
            If Not IsDate(Me.txtDateFrom) Then
                MsgBox "Invalid 'From' date", vbOKOnly + vbExclamation
                Me.txtDateFrom.SetFocus
                Exit Sub
            End If
            If Not IsDate(txtDateTo) Then
                MsgBox "Invalid 'To' date", vbOKOnly + vbExclamation
                txtDateTo.SetFocus
                Exit Sub
            End If
            If SQLWhere <> "" Then SQLWhere = SQLWhere & "AND "
            Select Case Frame51.Value
            Case 1
                SQLWhere = SQLWhere & "[E_Date] "
            Case 2
                SQLWhere = SQLWhere & "[Statusdate] "
            Case 3
                SQLWhere = SQLWhere & "[Date2] "
            End Select
            SQLWhere = SQLWhere & "BETWEEN #" & Format(txtDateFrom, "mm/dd/yyyy") & "# "
            SQLWhere = SQLWhere & "AND #" & Format(txtDateTo, "mm/dd/yyyy") & "# "
        End If
    End If
 
    If SQLWhere <> "" Then
        SQLWhere = "WHERE " & SQLWhere
    End If
    SQLOrderBy = "ORDER BY REF"
 
    CurrentDb.Execute "DELETE * FROM ClientMailMergeSelection", dbFailOnError
    CurrentDb.Execute SQLQ & SQLWhere & SQLOrderBy, dbFailOnError
    Me.ClientMailMergeSelection.Form.Requery
    DoEvents
    DisplayHeadings
    DoCmd.Hourglass False
    DoEvents
    Exit Sub
 
Err_btnSearch_Click:
    MsgBox Err.DESCRIPTION
    Resume Next
 
End Sub

[NeoPa]

I think we can help some. There may be a few things to deal with though, including some work for you to do finding info (we'll help here too of course).

I'll start with some advice about preparing and posting your code. If after trying to compile you find any issues you cannot deal with then let us know (with details) and we''l help you sort it out.

It is always a good idea to ensure that variable name checking is enabled, AND your code compiles (at least compilation has been attempted), before submitting a question (Require Variable Declaration).

This avoids asking questions which are much more easily resolved on your own PC than on a forum.

To ensure variable name checking is enabled for all new modules, go to - Tools / Options / Editor (from the VBA Editor window) and set Require Variable Declaration to True (checked). For existing modules, ensure that the Option lines at the very top include :

Option Explicit

[NeoPa]

Moving on, and assuming that you had no problems with the earlier post and have changed your code accordingly, let's look at the SQL that's created within your code and run it from within a QueryDef (saved Access query). That way, purely for testing/debugging purposes, we can see what the error messages may be. If there is none, then we can at least explore further by tweaking it more easily.

What I want you to do is to stop the code at line #86 (See Debugging in VBA for help with this) and display the contents of the variable SQLQ into the Immediate Pane of the Visual Basic Editor window. I can't see what type of variable SQLQ is (this was the main reason for my earlier post btw). When it is displayed in there I would like you to Copy it into the clipboard and Paste it into a new QueryDef (Create a new QueryDef - Insert \ Query from the Menu, Select SQL View - View \ SQL - from the menu, Paste in the value from SQLQ you copied earlier). If you run this from the Database window then you should see some messages displaying what is happening. Post this here so that we can give it some consideration.