By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,682 Members | 1,980 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,682 IT Pros & Developers. It's quick & easy.

error setting a subforms recordsource from another form

P: n/a
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_Click

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.FirstName) 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.RecordSource = strSQL
End If
Exit_cmdFirst_Click:
Exit Sub

Err_cmdFirst_Click:
MsgBox Err.Description
Resume Exit_cmdFirst_Click

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

Dec 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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*******@yahoo.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.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_Click

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.FirstName) 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.RecordSource = strSQL
End If
Exit_cmdFirst_Click:
Exit Sub

Err_cmdFirst_Click:
MsgBox Err.Description
Resume Exit_cmdFirst_Click

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

Dec 14 '05 #2

P: n/a
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! :)

Dec 14 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.