Solved Record source vs filter source (1 Viewer)

ClaraBarton

Registered User.
Local time
Today, 02:18
Joined
Oct 14, 2019
Messages
423
I have a button that pops up a form for exporting records.
The open event is:
Code:
txtSource = Screen.ActiveForm.RecordSource
If Screen.ActiveForm.FilterOn Then
    txtFilter = Screen.ActiveForm.Filter
End If

From this I do a little cleanup and create a Sql statement.
My problem is this Sql returns a statement where the recordsource is the query that fills the form but the filter is the name of the form. I think this is where I'm getting a 3061 Too few parameters error.
Code:
SELECT [ContactName],[ContactsID],[Ck],[Company],[LastName],[FirstName] FROM (SELECT * FROM qryContacts ) WHERE ([frmList].[Ck] Not In (0))

How do I fix this?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:18
Joined
Oct 29, 2018
Messages
21,357
How exactly are you using that SQL that you're getting an error? Are you creating a recordset with it?
 

ClaraBarton

Registered User.
Local time
Today, 02:18
Joined
Oct 14, 2019
Messages
423
Well, ultimately, I'm creating a qrydef. I'm not sure how much you want here... maybe I'm wrong elsewhere also. You guys always seem to make me look the idiot :mad: but eventually things start working.

Code:
For i = 0 To lstExportFields.ListCount - 1
        If 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 Right(Trim(txtSource), 1) = ";" Then
        txtSource = Mid(txtSource, 1, Len(Trim(txtSource)) - 1)
    ElseIf InStr(txtSource, " ") <> 0 And Left(txtSource, 1) <> "[" Then
        txtSource = "[" & txtSource & "]"
    End If

  sSource = "SELECT " & sFields & " FROM (" & 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 " & txtFilter
        sSource = sSource & sFilter           'or sSql?
      
    End If
    Set db = CurrentDb
    Set rsTmp = db.OpenRecordset( _
        Name:=sSource, _
        Type:=dbOpenSnapshot, _
        Options:=dbReadOnly)
'   MsgBox rsTmp!LastName
             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
            CurrentDb.QueryDefs("qryOut").SQL = sSource
        End If
    End If
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:18
Joined
Oct 29, 2018
Messages
21,357
Well, ultimately, I'm creating a qrydef. I'm not sure how much you want here... maybe I'm wrong elsewhere also. You guys always seem to make me look the idiot :mad: but eventually things start working.

Code:
For i = 0 To lstExportFields.ListCount - 1
        If 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 Right(Trim(txtSource), 1) = ";" Then
        txtSource = Mid(txtSource, 1, Len(Trim(txtSource)) - 1)
    ElseIf InStr(txtSource, " ") <> 0 And Left(txtSource, 1) <> "[" Then
        txtSource = "[" & txtSource & "]"
    End If

  sSource = "SELECT " & sFields & " FROM (" & 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 " & txtFilter
        sSource = sSource & sFilter           'or sSql?
      
    End If
    Set db = CurrentDb
    Set rsTmp = db.OpenRecordset( _
        Name:=sSource, _
        Type:=dbOpenSnapshot, _
        Options:=dbReadOnly)
'   MsgBox rsTmp!LastName
             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
            CurrentDb.QueryDefs("qryOut").SQL = sSource
        End If
    End If
Do you know which line is causing the error? Like, which line is highlighted when you go to debug?

PS. By the way, it is never our intention to make anyone look like an idiot. But since we can't see your screen, we may sometimes ask for clarifications to better understand the issue.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:18
Joined
Oct 29, 2018
Messages
21,357
Do you know which line is causing the error? Like, which line is highlighted when you go to debug?
Oh wait, I do see you're using the SQL to open a recordset. In that case, you could try this generic recordset function.

 

ClaraBarton

Registered User.
Local time
Today, 02:18
Joined
Oct 14, 2019
Messages
423
You have to know I'm just kidding! I very much appreciate your help. It boggles my mind how how you can stare at something all day long and not see what's wrong!
 

ClaraBarton

Registered User.
Local time
Today, 02:18
Joined
Oct 14, 2019
Messages
423
I get the same error with your fDAOGenericRst... The Recordsource is the query behind my form and the WHERE clause is the form itself.
Bad SQL but I don't know how to fix it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:18
Joined
Oct 29, 2018
Messages
21,357
I get the same error with your fDAOGenericRst... The Recordsource is the query behind my form and the WHERE clause is the form itself.
Bad SQL but I don't know how to fix it.
Hi. Just to be safe, can you post how you tried to use the generic function? Thanks.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:18
Joined
May 7, 2009
Messages
19,169
some modification:
Code:
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
   
    'If Right(Trim(txtsource), 1) = ";" Then
    '    txtsource = Mid(txtsource, 1, Len(Trim(txtsource)) - 1)
    'ElseIf InStr(txtsource, " ") <> 0 And Left(txtsource, 1) <> "[" Then
    '    txtsource = "[" & txtsource & "]"
    'End If

  sSource = "SELECT " & sFields & " FROM " & 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           'or sSql?
     
    End If
    Set db = CurrentDb
    Set rsTmp = db.OpenRecordset( _
        Name:=sSource, _
        Type:=dbOpenSnapshot, _
        Options:=dbReadOnly)
'   MsgBox rsTmp!LastName
             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
            db.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
put on separate Module:
Code:
'arnelgp
Public Function IsTableOrQuery(ByVal strObject As String)
    Dim bolFound As Boolean
    Dim intX As Integer
    Dim db As DAO.Database
    Set db = CurrentDb
    For intX = 0 To db.TableDefs.Count - 1
        bolFound = (db.TableDefs(intX).Name = strObject)
        If bolFound Then Exit For
    Next
    If Not bolFound Then
        For intX = 0 To db.QueryDefs.Count - 1
            bolFound = (db.QueryDefs(intX).Name = strObject)
            If bolFound Then Exit For
        Next
    End If
    IsTableOrQuery = bolFound
End Function

'arnelgp
Public Function RemoveFormRef(ByVal pText As String) As String
    With CreateObject("VBScript.RegExp")
   
        .Pattern = "([\[a-z0-9\]])*\."
        .Global = True
        .IgnoreCase = True
       
        RemoveFormRef = .Replace(pText, "")
       
    End With
End Function
 

ClaraBarton

Registered User.
Local time
Today, 02:18
Joined
Oct 14, 2019
Messages
423
AANNNDD... this is why I keep coming back! Thank you, arnelgp. It works perfectly. I haven't a clue what "RemoveFormRef" is doing and I don't care. It just works!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:18
Joined
May 7, 2009
Messages
19,169
you're welcome :)

the function is just removing and transforming the:

[formRef].[fieldName]

to:

[fieldName]
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:18
Joined
Oct 29, 2018
Messages
21,357
AANNNDD... this is why I keep coming back! Thank you, arnelgp. It works perfectly. I haven't a clue what "RemoveFormRef" is doing and I don't care. It just works!
Hi. Glad to hear you got it sorted out. Good luck with your project.

@arnelgp Thanks for the assist!
 

Users who are viewing this thread

Top Bottom