using Pass through queries in subform

[David]

Hello all,
I am using access 2003, All data can be read-only.

I have a main unbound form with one combo box, and 4 bound subforms. The
record source for the subforms are pass through queries.

I can't follow normal procedure and link the subforms to the combo box, I
get an error that says I can't use a pass through query as a record source
for a subform.

So I use filtering like this

Private Sub cbo_WG_AfterUpdate()

Me.sfrm_WG.Form.Filter = "[WorkGroup] = '" & Me.cbo_WG & "'"
Me.sfrm_WG.Form.FilterOn = True
Me.sfrm_WG.Visible = True


End Sub

With this technique, the subform load and query when the main form is
loaded, PROBLEM is 5 seconds or so after the data is displayed, I get the
following error
ODBC - Call Failed
[Microsoft][ODBC Driver Manager] Function sequence error (#0)

This message always pops up one less time then the number of subforms I
have. Turns values to #Name?, except the last subform.

After the error popup messages, I can select a value from the combo box, and
it filters and populates the subforms fine...

How can I either re-structure my approach or prevent the error popup message?

Any assistance you can provide is greatly appreciated.

Thank you,
Dave

 

[Armen Stein]

Hello all,
I am using access 2003, All data can be read-only.

I have a main unbound form with one combo box, and 4 bound subforms. The
record source for the subforms are pass through queries.

I can't follow normal procedure and link the subforms to the combo box, I
get an error that says I can't use a pass through query as a record source
for a subform.

So I use filtering like this

Private Sub cbo_WG_AfterUpdate()

Me.sfrm_WG.Form.Filter = "[WorkGroup] = '" & Me.cbo_WG & "'"
Me.sfrm_WG.Form.FilterOn = True
Me.sfrm_WG.Visible = True


End Sub

With this technique, the subform load and query when the main form is
loaded, PROBLEM is 5 seconds or so after the data is displayed, I get the
following error
ODBC - Call Failed
[Microsoft][ODBC Driver Manager] Function sequence error (#0)

This message always pops up one less time then the number of subforms I
have. Turns values to #Name?, except the last subform.

After the error popup messages, I can select a value from the combo box, and
it filters and populates the subforms fine...

How can I either re-structure my approach or prevent the error popup message?

Any assistance you can provide is greatly appreciated.

Thank you,
Dave

I wouldn't use the Filter property. That is an Access-side filter,
but your records are coming from the passthrough query. This may be
causing confusion when they are refreshed, I'm not sure.

Why not add your selection criteria to the passthrough? If you define
your passthrough query in VBA as a query definition object, you can
set its SQL property to the statement you need, including Where
clause.

When you refresh your subforms, they'll use the selection.

Also, it might be faster, since the passthrough is doing the selection
work.

Note that this technique (of changing the passthrough query itself)
requires that each front-end is strictly single user, since you are
changing the one persistent copy of the query. But this is a best
practice anyway.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[David]

Armen,
Thank you for the quick response. I found this bit of info on the forum...
something like this what you have in mind?


Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim BaseSQL As String
Dim strSQL As String
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("YourOriginalQuery"­)
BaseSQL = qdf.SQL
strSQL = Left(BaseSQL, Len(BaseSQL)-3) & " WHERE blabla ... your
criteria"
qdf.SQL = strSQL
DoCmd.TransferSpreadsheet ....
qdf.SQL = BaseSQL
Set qdf = Nothing
Set dbs = Nothing

--
Steve Schapel, Microsoft Access MVP

Hello all,
I am using access 2003, All data can be read-only.

I have a main unbound form with one combo box, and 4 bound subforms. The
record source for the subforms are pass through queries.

I can't follow normal procedure and link the subforms to the combo box, I
get an error that says I can't use a pass through query as a record source
for a subform.

So I use filtering like this

Private Sub cbo_WG_AfterUpdate()

Me.sfrm_WG.Form.Filter = "[WorkGroup] = '" & Me.cbo_WG & "'"
Me.sfrm_WG.Form.FilterOn = True
Me.sfrm_WG.Visible = True


End Sub

With this technique, the subform load and query when the main form is
loaded, PROBLEM is 5 seconds or so after the data is displayed, I get the
following error
ODBC - Call Failed
[Microsoft][ODBC Driver Manager] Function sequence error (#0)

This message always pops up one less time then the number of subforms I
have. Turns values to #Name?, except the last subform.

After the error popup messages, I can select a value from the combo box, and
it filters and populates the subforms fine...

How can I either re-structure my approach or prevent the error popup message?

Any assistance you can provide is greatly appreciated.

Thank you,
Dave

I wouldn't use the Filter property. That is an Access-side filter,
but your records are coming from the passthrough query. This may be
causing confusion when they are refreshed, I'm not sure.

Why not add your selection criteria to the passthrough? If you define
your passthrough query in VBA as a query definition object, you can
set its SQL property to the statement you need, including Where
clause.

When you refresh your subforms, they'll use the selection.

Also, it might be faster, since the passthrough is doing the selection
work.

Note that this technique (of changing the passthrough query itself)
requires that each front-end is strictly single user, since you are
changing the one persistent copy of the query. But this is a best
practice anyway.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Armen Stein]

Armen,
Thank you for the quick response. I found this bit of info on the forum...
something like this what you have in mind?


Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim BaseSQL As String
Dim strSQL As String
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("YourOriginalQuery"­)
BaseSQL = qdf.SQL
strSQL = Left(BaseSQL, Len(BaseSQL)-3) & " WHERE blabla ... your
criteria"
qdf.SQL = strSQL
DoCmd.TransferSpreadsheet ....
qdf.SQL = BaseSQL
Set qdf = Nothing
Set dbs = Nothing

Yes, this is the basic syntax to modify a query.

But this one sets the SQL statement back to its original text after
it's done. That would be trickier for you, since you need the query
to keep its Where clause while the subforms are being used. And it
depends on code completing normally, otherwise the query would not
revert back to its original value and would fail the next time this
code runs.

I included some code in the book "Access 2003 VBA Programmer's
Reference" that you might find helpful. The download URL is
http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764559036,descCd-download_code.html.

Look at the Chamber Application and Database files. There is a module
called basSQLTools. Copy the module into your application. It
contains the ReplaceWhereClause function, which will remove an
existing Where clause in a SQL statement and replace it with another
one that you provide. This approach eliminates the need to preserve
the original query statement as shown above.

So now the untested aircode would look more like this:

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("MyQuery")
qdf.SQL = ReplaceWhereClause(qdf.SQL, "Where MyField = 'blah'")
Set qdf = Nothing
Set dbs = Nothing

Hope this gets you started,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com