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:
Code:
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:
Code:
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?