Change Variables in Query using VBA

[ijswalker]

Hi,

I have a report that has an underlying passthrough query. I would like to be able to use a form to input the date and then pass the date entered into the form to the query and then run the query. I have seen this done before but unfortunately I didn't take notes.

Can someone help?

Thanks

Ian​

 

[pbaldy]

Search on QueryDef. You can change the passthrough query's SQL with that.

 

[Guus2005]

Here's what i use

Public Sub ReplaceInQueryDefs(strSearch As String, strReplace As String)
'Run this command in your immediate window (control-g)
'Replace a string in all query definitions
'Example: ReplaceInQueryDefs "U_ParentID", "ParentID"

    Dim qdf         As QueryDef
    Dim qdfs        As QueryDefs
    Dim strSql      As String
       
    Set qdfs = CurrentDb.QueryDefs
    
    For Each qdf In qdfs
        strSql = qdf.SQL
        If InStr(1, strSql, strSearch) > 0 Then
            qdf.SQL = Replace(strSql, strSearch, strReplace)
            Debug.Print "Replaced in query : " & qdf.Name
            If vbNo = MsgBox("Found!" & vbCrLf & vbCrLf & "String replaced in " & qdf.Name & _
                                           vbCrLf & vbCrLf & qdf.SQL & "" & _
                                           vbCrLf & vbCrLf & "Click 'Yes' to continue search, 'No' to stop", _
                                           vbExclamation + vbYesNo, "ReplaceInQueryDefs") Then
                Exit Sub
            End If
        End If
    Next qdf
    
    MsgBox "Done searching.", vbInformation

End Sub

Share and enjoy!

 

[pbaldy]

That's nice code for a global find and replace, but I'm not sure it's an appropriate solution for this particular problem, unless I've misunderstood the question.

 

[Guus2005]

Offcourse this code needs to be modified somewhat.
It gives the TS a Hint or a little bit more on how to proceed.

If it is not clear please let me know.

Share and enjoy!

 

[ijswalker]

Thanks for your help. The QueryDefs is helpful. I have taken a slighly different approach by using a template for the query that works very nicely. I now have a form that I can enter the date in and am now able to change the query based on the date I have chosen.

Thanks again. Very helpful indeed.

Ian