Create Form Based on Pass Through Query

[Rubie]

I tried to create a Form based on a Pass Through Query but the select data
source dialog box pops up and it doesn't close when I click the ok button.
Any advice on how I can create the Form or if it can be done would be greatly
appreciated.

Thanks in advance.

 

[tkelley]

I have a function that I use to dynamically set the SQL for one of my
PassThrough queries. It will open the connection for you, and set that
passthrough's sql:

---------------------------------------
Public Sub PassThroughSetup( _
strQdfName As String, _
strSQL As String, _
Optional fRetRecords As Boolean = True)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = currentdb
Set qdf = db.QueryDefs(strQdfName)

If Len(strSQL) > 0 Then
qdf.SQL = strSQL
End If

qdf.Connect = fnGetConnectionString 'This is a function that returns
your connection string
qdf.ReturnsRecords = fRetRecords
qdf.Close

Set qdf = Nothing

End Sub
---------------------------------------

Then if I call that function, then set the recordsource at runtime ... it
works like a dream:

---------------------------------------
Private Sub Form_Open(Cancel As Integer)

Dim strSQL As String
strSQL = "SELECT * FROM dbo.stsEmployee"

Call PassThroughSetup("qryPassThrough_Dynamic", strSQL)

Me.RecordSource = "qryPassThrough_Dynamic"

End Sub

 

[Rubie]

Hi tkelley,

Thanks for your response. I don't do VB coding very well. Is there a way
in access to create the form based on the query? When I run the query the
data source box pops up and I choose the source and it runs when I click ok.
I just can't get the form to cooperate like that.

Best Regards.