Display a recordset using Visual Basic

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Back in Access 2.0 I used to use 'AddToWhere' to build SQL criteria from
disconnected input boxes on a form and set the recordsource property of a
subform to the returned recordset. This gave me Web like querying before
there was a web.

Now that I have Acces2003 how do I get similar functionality? The Subform
SourceObject cannot accept the returned recordset, and I do not see a data
grid as an option anymore. I don't see any better choices using Data Access
Pages either. Please help, I need to give querying/form reporting on ODBC
Oracle data to my division at work.
 
Desperate Dan said:
Back in Access 2.0 I used to use 'AddToWhere' to build SQL criteria
from disconnected input boxes on a form and set the recordsource
property of a subform to the returned recordset. This gave me Web
like querying before there was a web.

Now that I have Acces2003 how do I get similar functionality? The
Subform SourceObject cannot accept the returned recordset, and I do
not see a data grid as an option anymore. I don't see any better
choices using Data Access Pages either. Please help, I need to give
querying/form reporting on ODBC Oracle data to my division at work.
Click to expand...

Although I'm not altogether clear on what it is you used to do with
Access 2.0 -- perhaps you'd like to post the old code -- it seems to me
you should be able to do something very similar with Access 2003. But
you wouldn't use the subform control's SourceObject and you wouldn't
build a recordset and try to assign it -- at least not as I am
envisioning it.

What you would do (as I see it) is build a SQL query string using the
criteria from your various unbound controls on the main form. That SQL
string would be created from the base string "SELECT * FROM SomeTable",
with the appropriate WHERE clause appended to it as constructed from the
values specified. Then you would assign this string to the RecordSource
property of the Form object returned by the Form property of the subform
control, like this:

Dim strSQLBase As String
Dim strSQLWhere As String

strSQLBase = "SELECT * FROM SomeTable"

' build the criteria for the where clause ...
strSQLWhere = ...

If Len(strSQLWhere) > 0 Then
strSQLWhere = " WHERE " & strSQLWhere
End If

Me!sfSubformControlName.Form.RecordSource = _
strSQLBase & strSQLWhere
 
Back
Top