I think I don't understand Boolean functions. You helped me here last night so here I am again. My function does what it's supposed to do but it doesn't return anything. It creates a query -- qryOut filled with a recordset. Then I want to export it. I need the boolean part to error out when there are too many records or no fields or anything else that might go wrong. I thought if it was successful it would return yes but it never does. It always returns fail when I know it didn't fail when going back to the calling sub. What is wrong here?
Public Function GetSql() As Boolean On Error GoTo errHandler Dim i As Integer Dim sFields As String Dim sFilter As String Dim rsTmp As DAO.Recordset Dim db As Database Dim sSource As String Dim lngNumRecords As Long For i = 0 To lstExportFields.ListCount - 1 If Trim$(sFields) <> "" Then sFields = sFields & "," End If sFields = sFields & "[" & lstExportFields.ItemData(i) & "]" Next If sFields = "" Then MsgBox "Please select some fields to export first" Exit Function End If If IsTableOrQuery(txtSource) Then txtSource = "[" & txtSource & "]" Else txtSource = "(" & Replace$(txtSource, ";", "") & ")" End If sSource = "SELECT " & sFields & " FROM " & Me!txtSource If Nz(txtFilter) = "" Then i = MsgBox("Are you sure you want to export all records?", _ vbQuestion + vbOKCancel, "Export All Records?") If i = vbCancel Then Exit Function End If Else sFilter = " WHERE " & RemoveFormRef(txtFilter) sSource = sSource & sFilter End If Set db = CurrentDb Set rsTmp = db.OpenRecordset( _ Name:=sSource, _ Type:=dbOpenSnapshot, _ Options:=dbReadOnly) lngNumRecords = rsTmp.RecordCount rsTmp.Close Set rsTmp = Nothing If lngNumRecords = 0 Then MsgBox "no Matching Records found", vbOKOnly, "No Records Found" DoCmd.Close acForm, "frmExport" Else If IsNull(DLookup("name", "msysobjects", "name='qryOut'")) Then CurrentDb.CreateQueryDef "qryOut", sSource Else Dim qd As QueryDef Set qd = CurrentDb.QueryDefs("qryOut") qd.SQL = sSource Set qd = Nothing End If End If Set db = Nothing Exit_Function: Exit Function errHandler: MsgBox "ERROR # " & Err.Number & vbCrLf & _ Err.Description, vbCritical + vbOKOnly, _ "Error in Export Function" Resume Exit_Function End Function Private Sub cmdExport_Click() On Error GoTo Err_cmdExport Dim strPath As String Dim qryOut As QueryDef If GetSql() Then strPath = CurrentProject.Path & _ "\" & Me.FileName & Me.Extension DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _ qryOut, strPath, True MsgBox "Done!" DoCmd.Close acForm, "frmExport" Else MsgBox "Something here isn't right. " _ & "Either exit or try again.", vbOKOnly, "Failed Export" End If Exit_cmdExport: Exit Sub Err_cmdExport: MsgBox "ERROR # " & Err.Number & vbCrLf & _ Err.Description, vbCritical + vbOKOnly, _ "Error in Export Function" Resume Exit_cmdExport End Sub