Filtering, RecordsetClone, Main Form - Subform Issues

[WittyGirl]

My client has an Access database that was created in an earlier version of
Access and they are now trying to use it with Access 2007. The main Customers
form is not working right since changing versions. I can't figure out what's
wrong with it. Can you please help me?

The form has (5) tabs on it; Tab 1 ("Search by Customer Info") has unbound
textboxes for the user to enter search criteria (such as "Dave" in the First
Name textbox), then click a command button to display matching records in a
subform (continuous form) on this tab. Then, when they find the customer they
want in the list on the subform, they could click the name and the data
related to this customer (such as orders and payments) would populate the
other tabs of the main form. This doesn't work anymore. Relevant code below.

============

MAIN FORM: Customers
Record Source: SELECT tblCustomer.* FROM tblCustomer;

Private Sub cmdFindItNow_Click()

Dim qdef As QueryDef, SrchFrm As Form
Dim lstrSQL As String
Dim lblnOK As Boolean, lMsg As String

'*-- Build the first half of the query needed to perform the search
lstrSQL = "SELECT DISTINCTROW "
lstrSQL = lstrSQL & "tblCustomer.* "
lstrSQL = lstrSQL & "FROM tblCustomer "
lstrSQL = lstrSQL & "Where ("

'*-- If there is something in the Name field, include it in the search
If Not IsNull(Me!txtSrchFirstName) And Len(Me!txtSrchFirstName) > 0 Then
If Right(Me!txtSrchFirstName, 1) = "*" Then
lstrSQL = lstrSQL & "(cuFirstName LIKE '" & Me!txtSrchFirstName
& "')"
Else
lstrSQL = lstrSQL & "(cuFirstName LIKE '" & Me!txtSrchFirstName
& "*')"
End If

End If

'*-- If there is something in the Last Name field, include it in the
search
If Not IsNull(Me!txtSrchLastName) And Len(Me!txtSrchLastName) > 0 Then
If Right(lstrSQL, 1) <> "(" Then
lstrSQL = lstrSQL & " AND "
End If
If Right(Me!txtSrchLastName, 1) = "*" Then
lstrSQL = lstrSQL & "(cuLastName LIKE '" & Me!txtSrchLastName &
"')"
Else
lstrSQL = lstrSQL & "(cuLastName LIKE '" & Me!txtSrchLastName &
"*')"
End If
End If

'... continues on for the other fields

'*-- Tack on the end of the SQL string
lstrSQL = lstrSQL & ") Order By cuLastName, cuFirstName;"

Set qdef = CurrentDb.QueryDefs("CaseSearch")
qdef.SQL = lstrSQL
qdef.Close

'*-- Changing the record source forces the form to pick up the new
definition of CaseSearch
Me.frmStudentSubSearchResults.Visible = True
Me.frmStudentSubSearchResults.Form.Requery
Me.frmStudentSubSearchResults.Form.RecordSource = lstrSQL ' This is
where I think it's not working???

If Me.frmStudentSubSearchResults.Form.RecordsetClone.RecordCount = 0 Then
MsgBox "No Customers were found matching your selection criteria.",
vbInformation, "Company Name"
End If

End Sub

=============

SUBFORM: SearchResults
Record Source: SELECT DISTINCTROW tblCustomer.* FROM tblCustomer;

Private Sub Form_Current()

Me!ctlCurrentRecord = Me.SelTop

On Error Resume Next

Dim lrecTempRC As Recordset
Set lrecTempRC = Me.Parent.RecordsetClone

Dim strCriteria As String
strCriteria = "cuID = " & Me!txtcuid

lrecTempRC.FindFirst (strCriteria)

If lrecTempRC.NoMatch Then
MsgBox "Record not found"
Else
Me.Parent.Bookmark = lrecTempRC.Bookmark
End If

lrecTempRC.Close

End Sub

Private Sub Form_Click()
Me!ctlCurrentRecord = Me.SelTop
End Sub

==========

There is an existing query named "CaseSearch", and if I go open it after
trying the search form, it has been changed to use the criteria I entered.
But the subform doesn't pull up any records at all. I'm really baffled and
would appreciate any help you could offer.

Thanks!

 

[roger]

'*-- Changing the record source forces the form to pick up the new
definition of CaseSearch
True, but so would opening the qry:
Docmd.openquery "casesearch", achidden

Me.frmStudentSubSearchResults.Visible = True
Me.frmStudentSubSearchResults.Form.Requery
Me.frmStudentSubSearchResults.Form.RecordSource = lstrSQL
< AND you're setting the RecordSource to the STRING of the sql and not the
query name

AND shouldn't you requery the subform AFTER you reset the recordsource?
(yes, you should )


I think just opening the new query and requerying the form (in that order)
is enough
hth roger

 

[WittyGirl]

As was said before:
Me.frmStudentSubSearchResults.Form.RecordSource = lstrSQL 'AND THEN
Me.frmStudentSubSearchResults.Visible = True
Me.frmStudentSubSearchResults.Form.Requery

you don't even need to store the string in a Query.... You can create the
query SQL on the fly and then assign it. No QueryDef required.

Thank you all so much for your replies.
I commented out the section relating to the QueryDef and used the section of
code PieterLinden references above. Unfortunately, the problem remains. The
subform, which is populated with all customer records upon initial opening of
the main form, goes blank when I click the Search button (when I enter a
first name which appears in the list so should return records). Do you have
any other ideas about what might be wrong?

Thank you!

 

[WittyGirl]

I went in to the Immediate Window and found that:
"? Forms!frmCustomer!frmSubSearchResults.Form.RecordSource"
gives the correct SQL ("SELECT DISTINCTROW tblCustomer.* FROM tblCustomer
Where ((cuFirstName LIKE 'Kent*')) Order By cuLastName, cuFirstName;") but
"? Forms!frmCustomer!frmSubSearchResults.Form.Recordset.RecordCount" gives
zero records.

I have solved the problem. In doing some searching, I ran across another
forum post
(http://www.accessmonster.com/Uwe/Fo...coding/51907/Access-2007-Recordsource-Subform)
where a user named Bruce ("Datasmith") said the following:

What I found was that when programmatically assigning the subform
recordsource it was also assigning values to the LinkMasterFields and
LinkChildFields properties, even though none existed to start. Now every
time I need to reassign the recordsource I also add two statements to clear
the relational linkage:

Forms!frmMain!fsubTemp.Form.Recordsource = "MyQuery"
Forms!frmMain!fsubTemp.LinkChildFields = ""
Forms!frmMain!fsubTemp.LinkMasterFields = ""

I used the Immediate Window to verify that the form indeed had values for
master and child linked fields. Then I added the lines to my code, and it was
fixed.

 

[WittyGirl]

When I enter criteria into the main form and click the Search button, the
subform goes blank and pops up a msgbox saying zero records were found.

I went in to the Immediate Window and found that:
"? Forms!frmCustomer!frmSubSearchResults.Form.RecordSource"
gives the correct SQL ("SELECT DISTINCTROW tblCustomer.* FROM tblCustomer
Where ((cuFirstName LIKE 'Kent*')) Order By cuLastName, cuFirstName;") but
"? Forms!frmCustomer!frmSubSearchResults.Form.Recordset.RecordCount" gives
zero records.

Why aren't the records there?