error setting a subforms recordsource from another form

[Bex]

Hi All.

I'm attempting at the moment to set the recordsource of an unbound
subform when i open up it's main form.

My view is to have one intial form - called "Search" which has a number
of unbound textboxes. When a user enters in a value into a field and
hits the search button this opens up a results form called "people3"
and sets a subform in this "people3" form - called "results" to show
certain records matching the entered search criteria. The Subform is
initially unbound.

So far i have this code on the search form (only one search field so
far but will be more eventually):

Private Sub cmdFirst_Click( )
On Error GoTo Err_cmdFirst_Cl ick

Dim stDocName As String
Dim stLinkCriteria As String
Dim message
Dim strSQL, strWHERE As String

stDocName = "People3"
strSQL = "SELECT DISTINCTROW People.* FROM People "

If Not IsNull(Me.First Name) Then
strWHERE = " WHERE People.[FirstName] Like " & "'*" &
Me![FirstName] & "*'"
End If
strSQL = strSQL & strWHERE

If Len(strWHERE) = 0 Then
message = MsgBox("No search values entered")
Else
DoCmd.OpenForm stDocName
Forms![People3]![Results].Form.RecordSou rce = strSQL
End If
Exit_cmdFirst_C lick:
Exit Sub

Err_cmdFirst_Cl ick:
MsgBox Err.Description
Resume Exit_cmdFirst_C lick

End Sub

Which is opening up the form but I keep getting the error of: "the
expression you entered refers to an object that is closed or doesn't
exist"

So what am i doing wrong?!?!
Cheers
Bex

[Allen Browne]

Could you save your subform with a RecordSource that returns no records:
SELECT People.* FROM People WHERE (False);

That should keep Access happy about the bound controls in the subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bex" <be*******@yaho o.com> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.com.. .

Hi All.

I'm attempting at the moment to set the recordsource of an unbound
subform when i open up it's main form.

My view is to have one intial form - called "Search" which has a number
of unbound textboxes. When a user enters in a value into a field and
hits the search button this opens up a results form called "people3"
and sets a subform in this "people3" form - called "results" to show
certain records matching the entered search criteria. The Subform is
initially unbound.

So far i have this code on the search form (only one search field so
far but will be more eventually):

Private Sub cmdFirst_Click( )
On Error GoTo Err_cmdFirst_Cl ick

Dim stDocName As String
Dim stLinkCriteria As String
Dim message
Dim strSQL, strWHERE As String

stDocName = "People3"
strSQL = "SELECT DISTINCTROW People.* FROM People "

If Not IsNull(Me.First Name) Then
strWHERE = " WHERE People.[FirstName] Like " & "'*" &
Me![FirstName] & "*'"
End If
strSQL = strSQL & strWHERE

If Len(strWHERE) = 0 Then
message = MsgBox("No search values entered")
Else
DoCmd.OpenForm stDocName
Forms![People3]![Results].Form.RecordSou rce = strSQL
End If
Exit_cmdFirst_C lick:
Exit Sub

Err_cmdFirst_Cl ick:
MsgBox Err.Description
Resume Exit_cmdFirst_C lick

End Sub

Which is opening up the form but I keep getting the error of: "the
expression you entered refers to an object that is closed or doesn't
exist"

So what am i doing wrong?!?!
Cheers
Bex

[Bex]

Thanks Allen... that seems t have sorted it - or certainly got it to a
point where hopefully i can get the other bits to work! :)