export to excel not including all records

[mlrucci]

Good morning, here is one for you. I have racked my brain and cannot figure this one out. I have vba code to export a query using a parameter from a combo box on a form. In order for the code to work, I have to use the Eval function. Works great but having difficulty with exporting a record that has a false (checkbox) result. The information in the query works, it is the export that is having the problem. I believe it has to do with the function eval. From my understanding, it used to evaluate an expression that results in a text string or a numeric value. I am baffled. I tried to change the criteria in the query to include both true and false, no resolution.



query:

SELECT lnkComponentToMCField.ComponentName, lnkComponentToMCField.MCFieldName, lnkComponentToMCField.IsComponentFieldRequired, lkpValidationType.ValidationType, lnkComponentToMCField.[Audit Notes] AS AuditNotes, lnkComponentToMCField.ComponentValidationCondition, lnkComponentToMCField.ValidationTypeID
FROM lnkComponentToMCField INNER JOIN lkpValidationType ON lnkComponentToMCField.ValidationTypeID = lkpValidationType.ValidationTypeID
WHERE (((lnkComponentToMCField.ComponentName)=Eval("[Forms]![frmNavMain]![frmNavMain].[Form]![frmNavMgt].[Form]![cboCompName]")) AND ((lnkComponentToMCField.IsComponentFieldRequired)=True Or (lnkComponentToMCField.IsComponentFieldRequired)=False)) OR (((Eval("[Forms]![frmNavMain]![frmNavMain].[Form]![frmNavMgt].[Form]![cboCompName]")) Is Null))
ORDER BY lnkComponentToMCField.ComponentName;

VBA:

Private Sub cmdExport_Click()
    Call Export2XLS("SELECT ComponentName, MCFieldName, IsComponentFieldRequired, ValidationType, AuditNotes, ComponentValidationCondition FROM qryMgtComponant_MCField")
End Sub


Function Export2XLS(ByVal sQuery As String)
Const xlCenter = -4108

On Error Resume Next
    Set oExcel = GetObject(, "Excel.Application")    'Bind to existing instance of Excel

    'Start Excel
    If Err.Number <> 0 Then    'Could not get instance of Excel, so create a new one
        Err.Clear
        On Error GoTo Error_Handler
        Set oExcel = CreateObject("Excel.Application")
        bExcelOpened = False
    Else    'Excel was already running
        bExcelOpened = True
    End If
    On Error GoTo Error_Handler
    oExcel.ScreenUpdating = False
    oExcel.Visible = False   'Keep Excel hidden until we are done with our manipulation
    Set oExcelWrkBk = oExcel.Workbooks.Add()    'Start a new workbook
    Set oExcelWrSht = oExcelWrkBk.Sheets(1)

    'Open our SQL Statement, Table, Query
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sQuery, dbOpenSnapshot)
    With rs
          If .RecordCount <> 0 Then
            'Build our Header
            For iCols = 0 To rs.Fields.Count - 1
                oExcelWrSht.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
            Next
            With oExcelWrSht.Range(oExcelWrSht.Cells(1, 1), _
                                   oExcelWrSht.Cells(1, rs.Fields.Count))
                .Font.Bold = True
                .Font.ColorIndex = 2
                .Interior.ColorIndex = 1
                .HorizontalAlignment = xlCenter
            End With
            'Copy the data from our query into Excel
            oExcelWrSht.Range("A2").CopyFromRecordset rs 'Resize columns based on the headings and data fields
                            oExcelWrSht.Cells.EntireColumn.AutoFit
                            oExcelWrSht.Cells.EntireRow.AutoFit
            oExcelWrSht.Range("A1").Select  'Return to the top of the page
        Else
            MsgBox "There are no records returned by the specified queries/SQL statement.", vbCritical + vbOKOnly, "No data to generate an Excel spreadsheet with"
            GoTo Error_Handler_Exit
        End If
    End With

Error_Handler_Exit:
    On Error Resume Next
    oExcel.Visible = True   'Make excel visible to the user
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set oExcelWrSht = Nothing
    Set oExcelWrkBk = Nothing
    oExcel.ScreenUpdating = True
    Set oExcel = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Export2XLS" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Suggestions?

[mlrucci]

Update, so I was working on trying to fix the issue, I realized that I have the sql in vba that is different than the qry I am asking access to export to excel. Guess the question is going to be, how can I get the all the records including those that have false ck?

[Welshgasman]

TBH, I am not sure why you would need the Eval()?

[mlrucci]

I don’t disagree, but that is the only way the export code works. Not sure why, but don’t know how else to make it work.

[Minty]

If you remove the IsComponentFieldRequired criteria completely but display the field do you get True and False where you would expect to?

Is it a calculated field?
Are there Null values?

Is this an SQL Server linked table?

[Micron]

how can I get the all the records including those that have false ck

Remove the criteria/constraint? Is that this part?

AND ((lnkComponentToMCField.IsComponentFieldRequired)=True Or (lnkComponentToMCField.IsComponentFieldRequired)=False))

I could see the need for eval if you were trying to get a value from a combo column but not with that. Maybe it has something to do with the nested nav forms.

[mlrucci]

Micron, after you remark, I went back and looked at my original query. I have a table with a relationship to another table. I forgot to change the relationship to Left to Right. That was my bad. Appreciate the help. Now is works. It wasn't just the Is ComponentFieldRequired. Just didn't see the other fields that weren't included due to the relationship I set up.