Filtering, RecordsetClone, Main Form - Subform Issues
in [Forms]
|
From: WittyGirl on 20 May 2010 23:19 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!
From: roger on 24 May 2010 11:58 >'*-- 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" wrote: > 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!
From: PieterLinden via AccessMonster.com on 24 May 2010 13:16 WittyGirl wrote: >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? > >============ > >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 >' Th > '*-- 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 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1
From: Marshall Barton on 24 May 2010 12:40 roger wrote: > >'*-- 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 FYI, setting the RecordSource (or RowSource) property automatically requeries the form (or list). Adding a Requery too is just a waste of resources. -- Marsh MVP [MS Access]
From: WittyGirl on 25 May 2010 01:24 "PieterLinden via AccessMonster.com" wrote: > 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!
|
Next
|
Last
Pages: 1 2 Prev: Allen Browne’s Name table design Next: Stephen Leban's Conditional Formatting |