Bind a Subform to a Query

[Smartin]

Using Access 97

I am trying to create a form that allows for user entry of search terms
and displays a table of results. I thought I could accomplish this using
a subform but it isn't quite doing what I want it to do.

What I have so far:

Fields to enter search terms
Subform bound to a query
Command button fires VBA code that...
....builds SQL SELECT statement using the search terms
....modifies the query object (to which the subform is bound) to take on
the new SELECT statement
....does subform.Requery

What happens is the subform displays data when the form is launched,
based on whatever the SELECT query happened to be at the time. However,
when the command button is clicked, the subform is not updated to
display the new record set, even though I have verified the query object
is successfully updated.

I would like to update the subform with the revised query when the
button is clicked.

I'm probably going at this all wrong... any suggestions are welcome.

[Rich P]

Ideally, a subform displays data in datasheet view. You have your
parameter fields in the main form along with your command button. For
the query which underlies your subform you can submit parameters using
custom functions.

Say your query (Query1) has 5 fields: FirstName, LastName, Phone,
Address, Zip

So your subform (frmSub), which is based on Query1 will also have 5
fields and will be in datasheet view (select "Datasheet" in the Default
View property of the subform's property sheet in design view - must
close main form first to access design view of subform).

In the query you will use a custom function which you will call in the
criteria field for one of the 5 fields. Lets use the FirstName field.
I will explain how you call the custom function after we create it.

We create a simple custom function in a standard code module (code
module section of Access). First, lets create a public var like this -
at the top of the code module:

Public fName As String

Then we create a simple function like this:

Function GetFname() As String
GetFname = fName
End Function

Save the code module. Drag the subform into the main form (Form1). Add
a text box to Form1 and a button. Text1 is where you enter the
FirstName value of a firstname you want to look up. Say your data table
contains 10 rows will firstName "Bill", 7 rows with firstName "Sue", and
so on. If you type Bill in Text1 the subform will display only rows
that have the FirstName of Bill. In your command button on the main
form (Form1) add this code:

Private Sub Command1_Click()
str1 = Text1
Me.Requery
Me.Refresh
End Sub

When you type Bill and click on the button you get all rows with
FirstName of Bill. If you type Sue in Text1, you get all rows with
FirstName of Sue...

To add the custom function to Query1 you simply type this in the
criteria field for the Firstname field of Query1:

= GetFname()

Save the query. Now open up form1 and type Bill in Text1 and click on
the button. The subform will display the desired records.

HTH

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Smartin]

Rich P wrote:

Ideally, a subform displays data in datasheet view. You have your
parameter fields in the main form along with your command button. For
the query which underlies your subform you can submit parameters using
custom functions.

Say your query (Query1) has 5 fields: FirstName, LastName, Phone,
Address, Zip

So your subform (frmSub), which is based on Query1 will also have 5
fields and will be in datasheet view (select "Datasheet" in the Default
View property of the subform's property sheet in design view - must
close main form first to access design view of subform).

In the query you will use a custom function which you will call in the
criteria field for one of the 5 fields. Lets use the FirstName field.
I will explain how you call the custom function after we create it.

We create a simple custom function in a standard code module (code
module section of Access). First, lets create a public var like this -
at the top of the code module:

Public fName As String

Then we create a simple function like this:

Function GetFname() As String
GetFname = fName
End Function

Save the code module. Drag the subform into the main form (Form1). Add
a text box to Form1 and a button. Text1 is where you enter the
FirstName value of a firstname you want to look up. Say your data table
contains 10 rows will firstName "Bill", 7 rows with firstName "Sue", and
so on. If you type Bill in Text1 the subform will display only rows
that have the FirstName of Bill. In your command button on the main
form (Form1) add this code:

Private Sub Command1_Click()
str1 = Text1
Me.Requery
Me.Refresh
End Sub

When you type Bill and click on the button you get all rows with
FirstName of Bill. If you type Sue in Text1, you get all rows with
FirstName of Sue...

To add the custom function to Query1 you simply type this in the
criteria field for the Firstname field of Query1:

= GetFname()

Save the query. Now open up form1 and type Bill in Text1 and click on
the button. The subform will display the desired records.

HTH

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Thanks for the tip. It didn't help me with this application but I'm
going to save your message for future reference.

For the record I was only one short step away from the solution to my
problem. Rather than having the subform bound to a query object I set
the RecordSource property of the subform at runtime to be the SELECT
statement built up from the text fields. This way I can use multiple
fields to create the query.

Again, thanks for your assistance.

Regards,
--
Smartin