Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2002
    Posts
    25

    Unhappy Unanswered: "Set FunctionName = Recordset" error

    Before posting I searched the extensive information with the Search at the top of the forums. After limiting the wealth of info down in my searches, I found info on passing recordsets as arguments, but nothing about setting the function name to the recordset (to return the recordset to the calling statement). I believe that I know where the error occurs, but not how to fix the problem.

    I pass 2 arguments to a very simple function (below). If I comment out the last line [Set ApplyFilterToRecordset = rstTemp.OpenRecordset] I receive no error. With that line in the function I receive the "Microsoft Access" error window noting "Too few paramaters. Expected 3." I believe that this error message may be caused by the last line either calling the function again instead of simply setting the function to the recordset -or- causing the function to expect an "ApplyFilterToRecordset" argument for that last line. It may be a combination of the two to expect 3 arguments because the function only requires 2 arguments.

    Private Function ApplyFilterToRecordset(ByVal rstTemp As DAO.Recordset, ByVal strFilter As String) As DAO.Recordset

    ' Set a filter on the supplied Recordset object argument, then open a
    ' new Recordset object to enable the effects of the filter

    rstTemp.Filter = strFilter
    Set ApplyFilterToRecordset = rstTemp.OpenRecordset

    End Function

    I've seen some seriously insightful answers here and am hopeful that someone can perhaps shed some light on this. Thanks in advance for any help at all.

    Thanks,
    Dennis

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Try the following:

    Private Sub Form_Load()
    Dim dbsNorthwind As Database
    Dim rstOrders As DAO.Recordset
    Dim intOrders As Integer
    Dim strCountry As String
    Dim rstOrdersCountry As DAO.Recordset
    Dim strMessage As String

    Set dbsNorthwind = OpenDatabase("c:\...\Nwind2002.mdb")
    Set rstOrders = dbsNorthwind.OpenRecordset("Orders", dbOpenSnapshot)

    ' Populate th
    rstOrders.MoveLast
    intOrders = rstOrders.RecordCount

    ' Get user input.
    strCountry = Trim(InputBox( _
    "Enter a country to filter on:"))

    If strCountry <> "" Then
    ' Open a filtered Recordset object.
    Set rstOrdersCountry = _
    FilterField(rstOrders, "ShipCountry", strCountry)

    With rstOrdersCountry
    ' Check RecordCount before populating Recordset;
    ' otherwise, error may result.
    If .RecordCount <> 0 Then .MoveLast
    ' Print number of records for the original
    ' Recordset object and the filtered Recordset
    ' object.
    strMessage = "Orders in original recordset: " & _
    vbCr & intOrders & vbCr & _
    "Orders in filtered recordset (Country = '" & _
    strCountry & "'): " & vbCr & .RecordCount
    MsgBox strMessage
    .Close
    End With

    End If

    rstOrders.Close

    dbsNorthwind.Close

    End Sub
    Function FilterField(rstTemp As DAO.Recordset, strField As String, strFilter As String) As DAO.Recordset

    ' Set a filter on the specified Recordset object and then
    ' open a new Recordset object.
    rstTemp.Filter = strField & " = '" & strFilter & "'"
    Set FilterField = rstTemp.OpenRecordset

    End Function

  3. #3
    Join Date
    Jul 2002
    Posts
    25

    Smile Great code feedback, but I'm still in the woods

    Actually, the code snippet that you show is the code that I found on my MSDN disks that gave me the idea for the code that I used. I created the string in this function just as the string is created as you show. It's a good idea because I didn't show how I created the string so I appreciate the check. It would be an easy mistake to make. I need to try the MSDN code exactly as is and see if it works. I suspect that it wouldn't work any differently than my code since they are both very similar. Still, I'll try it to make sure.

    I still think that there is an error in assigning the function the value of a recordset. I'll continue to work on it, but I'd love some more ideas. Again, I do appreciate the feedback. You hit on the example that inspired my function in the first place. The MSDN CD's are a great source of ideas.

    Has anyone else ever assigned a recordset as a return value for a function? You have to use the Set keyword, yet perhaps this causes the problem with the function recognizing that this is its return value. I'm open to any suggestions.

    Thanks,
    Dennis

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    This code does work - the code from the msdn did not work so I had to make a few modifications. But assigning the function using set does not cause a problem. The problem is with your strFilter string - the value of this string is not appropriate for the filter property. What is the value you are passing as strFilter ?

  5. #5
    Join Date
    Jul 2002
    Posts
    25

    I appreciate the follow-up !!

    First, I appreciate the follow-up VERY much - Thank you !!!

    It's hard to tell if there is a difference with the quotation marks so if it's not an abuse of space, I'm going to copy the MSDN code in here and notate the quotation marks used (for others to have as well because it's good code and hard to see in this format). I've probably been looking at code too long this week and I'm missing the change. I apologize for my double-vision. Perhaps you could spell out the difference if it's the quotation marks (which could very well be the problem). I do see where you've declared the recordsets as DAO recordsets, but I've done that too.

    I'll head into work this morning and pull my exact code, but I'm pretty sure I set it up like this example. Still I must check it to make absolutely certain. Again, your response is GREATLY appreciated !!!!

    MSDN:

    Filter Property Example

    This example uses the Filter property to create a new Recordset from an existing Recordset based on a specified condition. The FilterField function is required for this procedure to run.

    Sub FilterX()

    Dim dbsNorthwind As Database
    Dim rstOrders As Recordset
    Dim intOrders As Integer
    Dim strCountry As String
    Dim rstOrdersCountry As Recordset
    Dim strMessage As String

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstOrders = dbsNorthwind.OpenRecordset("Orders", _
    dbOpenSnapshot)

    ' Populate the Recordset.
    rstOrders.MoveLast
    intOrders = rstOrders.RecordCount

    ' Get user input.
    strCountry = Trim(InputBox( _
    "Enter a country to filter on:"))

    If strCountry <> "" Then
    ' Open a filtered Recordset object.
    Set rstOrdersCountry = _
    FilterField(rstOrders, "ShipCountry", strCountry)

    With rstOrdersCountry
    ' Check RecordCount before populating Recordset;
    ' otherwise, error may result.
    If .RecordCount <> 0 Then .MoveLast
    ' Print number of records for the original
    ' Recordset object and the filtered Recordset
    ' object.
    strMessage = "Orders in original recordset: " & _
    vbCr & intOrders & vbCr & _
    "Orders in filtered recordset (Country = '" & _
    strCountry & "'): " & vbCr & .RecordCount
    MsgBox strMessage
    .Close
    End With

    End If

    rstOrders.Close

    dbsNorthwind.Close

    End Sub

    Function FilterField(rstTemp As Recordset, _
    strField As String, strFilter As String) As Recordset

    ' Set a filter on the specified Recordset object and then
    ' open a new Recordset object.
    '*** Below is " = (single quote)(double quote) & strFilter &
    '*** (double quote)(single quote)(double quote)
    rstTemp.Filter = strField & " = '" & strFilter & "'"
    Set FilterField = rstTemp.OpenRecordset

    End Function

    Note To see the effects of filtering rstOrders, you must set its Filter property, and then open a second Recordset object based on rstOrders.

    I'm thinking that you're using a different combination of the quotation marks. I apologize if you've made another change that I'm missing. I'll get my exact filter code out this morning. (It's 8am my time). I can't say thanks enough !!!!! This one really has me stumped.

    Thanks,
    Dennis

  6. #6
    Join Date
    Jul 2002
    Posts
    25

    Smile It has to be my filter code

    Hey,

    I've been running around all day and just popped your code into VBA and tried it out in Access 2000. I notice that you referenced the Northwind 2002 database so I wanted to make sure there wasn't an issue with different versions. As you stated, it works great. Hmmmm, the code issue must be in my filter statement. I'll double check it today and let ya know the results. Again, I am very appreciative of your replies and thoughtful help. So often the problem is not where we think that it is. This is where I appear to be guilty. Again, thank you very much !!!!

    Thanks,
    Dennis

  7. #7
    Join Date
    Jul 2002
    Posts
    25

    Talking The filter was the problem :) !!!!!!

    Thank You - Thank You - Thank You

    I was looking in the wrong place for the answer. I have a neat bit of code that lets the user put in a firstname, middlename or lastname and depending on what textbox has a value entered into it will put together a string. Well, I didn't have single quotes around the strName on my multiple field filter statements. You don't need/want the single quote around the fieldname, but you have to have it around each string value that you assign to the corresponding fieldname.

    To help explain that statement above and present the corrected code to benefit others that may want to use it I am including this subroutine below. I do have one question below my code because I ran into another bug. I really need to do my homework first so don't feel compelled to respond unless it's something you know off the top of your head. Anyway, I hope the code helps a fellow developer out.

    ---------------------------------------------------------------------------

    Private Sub cmdStartNameSearch_Click()
    On Error GoTo Err_cmdStartNameSearch_Click

    Dim strFilterByPatient As String
    Dim strSQL As String
    Dim strPatientFirstName As String
    Dim strPatientMiddleName As String
    Dim strPatientLastName As String
    Dim lngPatientFirstNameLength As Long
    Dim lngPatientMiddleNameLength As Long
    Dim lngPatientLastNameLength As Long
    Dim strAccountNum
    Dim strFilterByAccountNum As String
    Dim wsInPatientIncidents As DAO.Workspace
    Dim dbInPatientIncidents As DAO.Database
    Dim rstInPatientIncidents As DAO.Recordset
    Dim wsFilteredInPatientIncidents As DAO.Workspace
    Dim dbFilteredInPatientIncidents As DAO.Database
    Dim rstFilteredInPatientIncidents As DAO.Recordset
    Dim wsPatient As DAO.Workspace
    Dim dbPatient As DAO.Database
    Dim rstPatient As DAO.Recordset
    Dim wsPatientFiltered As DAO.Workspace
    Dim dbPatientFiltered As DAO.Database
    Dim rstPatientFiltered As DAO.Recordset
    Dim lstSearchResultsListbox As ListBox
    Dim lstSearchResults2Listbox As ListBox

    ' Set the mouse pointer to an hourglass
    Screen.MousePointer = 11

    ' The text property of a textbox will return an empty string if there is
    ' a Null value assigned to the textbox or if the textbox is uninitialized.
    ' The value property of a textbox will return a Null value if there is a Null
    ' value assigned to the textbox or if the textbox is uninitialized
    ' The default property for a textbox is the value property
    ' The text property returns a formatted string representing the current contents
    ' of the control. To use the text property you must set the focus to the control.
    ' The textbox does not have to have focus to use the value property.
    ' Check for spaces in all 3 Patient Name textboxes

    If IsNull(Me.txtPatientFirstNameSearchInput) Then
    strPatientFirstName = ""
    Else
    strPatientFirstName = Me.txtPatientFirstNameSearchInput
    End If

    If IsNull(Me.txtPatientMiddleNameSearchInput) Then
    strPatientMiddleName = ""
    Else
    strPatientMiddleName = Me.txtPatientMiddleNameSearchInput
    End If

    If IsNull(Me.txtPatientLastNameSearchInput) Then
    strPatientLastName = ""
    Else
    strPatientLastName = Me.txtPatientLastNameSearchInput
    End If

    lngPatientFirstNameLength = Len(strPatientFirstName)
    lngPatientMiddleNameLength = Len(strPatientMiddleName)
    lngPatientLastNameLength = Len(strPatientLastName)

    If lngPatientFirstNameLength <> 0 Then ' If the textbox is not empty
    If InStr(strPatientFirstName, " ") <> 0 Then
    ' PatientFirstName textbox contains a space
    MsgBox "No spaces are allowed in the First Name", vbOKOnly, _
    "No Spaces Allowed"
    Exit Sub
    End If
    End If

    If lngPatientMiddleNameLength <> 0 Then ' If textbox is not empty
    If InStr(strPatientMiddleName, " ") <> 0 Then
    ' PatientMiddleName textbox contains a space
    MsgBox "No spaces are allowed in the Middle Name", vbOKOnly, _
    "No Spaces Allowed"
    Exit Sub
    End If
    End If

    If lngPatientLastNameLength <> 0 Then ' If the textbox is not empty
    If InStr(strPatientLastName, " ") <> 0 Then
    ' PatientLastName textbox contains a space
    MsgBox "No spaces are allowed in the Last Name", vbOKOnly, _
    "No Spaces Allowed"
    Exit Sub
    End If
    End If

    ' Add the value of each Patient Name textbox that contains a value to the filter
    ' criteria and filter the recordset accordingly. Note: Put a single quote around
    ' each strPatientName variable. You would also want to put a single quote around
    ' each Me.txtName this were used instead of strPatientName to construct a filter
    If lngPatientFirstNameLength <> 0 Then ' If textbox is not empty
    If lngPatientMiddleNameLength <> 0 Then ' If textbox is not empty
    If lngPatientLastNameLength <> 0 Then ' If textbox is not empty
    strFilterByPatient = "PatientFirstName = '" & strPatientFirstName _
    & "' AND PatientMiddleName = '" & strPatientMiddleName _
    & "' AND PatientLastName = '" & strPatientLastName & "'"
    Else
    strFilterByPatient = "PatientFirstName = '" & strPatientFirstName _
    & "' AND PatientMiddleName = '" & strPatientMiddleName & "'"
    End If
    Else
    If lngPatientLastNameLength <> 0 Then
    strFilterByPatient = "PatientFirstName = '" & strPatientFirstName _
    & "' AND PatientLastName = '" & strPatientLastName & "'"
    Else
    strFilterByPatient = "PatientFirstName = '" & strPatientFirstName & "'"
    End If
    End If
    Else
    If lngPatientMiddleName <> 0 Then
    If lngPatientLastName <> 0 Then
    strFilterByPatient = "PatientMiddleName = '" & strPatientMiddleName _
    & "' AND PatientLastName = '" & strPatientLastName & "'"
    Else
    strFilterByPatient = "PatientMiddleName = '" & strPatientMiddleName & "'"
    End If
    Else
    strFilterByPatient = "PatientLastName = '" & strPatientLastName & "'"
    End If
    End If

    ' Apply filter to rstPatient and use as RowSource for lstSearchResults
    ' listbox. Set Bound Column property of lstSearchResults to AccountNum
    ' This fills lstSearchResults with all records matching the name(s).
    ' Then, the selection event in lstSearchResults is used to create a filter
    ' on AccountNum (the bound column of lstSearchResults listbox)
    ' This filter is applied to rstFilteredInPatientIncidents (Me.RecordsetClone)
    ' and used to populate lstSearchResults2. Set bound column of lstSearchResults2
    ' to InPatientIncidentID.

    strSQL = "SELECT AccountNum, MedicalRecordNum, PatientFirstName, " & _
    "PatientMiddleName, PatientLastName, PatientAge FROM tblPatients;"

    ' Create the DAO workspace
    Set wsPatient = DBEngine.Workspaces(0)
    ' Set a reference to the current database
    Set dbPatient = CurrentDb()
    ' Open the recordset
    Set rstPatient = dbPatient.OpenRecordset(strSQL)

    'Use ApplyFilterToRecordset instead of rstPatient.Filter = strFilterByPatient
    ' Set a reference to the DAO workspace
    'Set wsPatientFiltered = DBEngine.Workspaces(0)
    ' Set a reference to the current database
    'Set dbPatientFiltered = CurrentDb()
    ' Supply the recordset and filter to the ApplyFilterToRecordset function
    ' which returns a recordset with the effects of the filter applied
    Set rstPatientFiltered = ApplyFilterToRecordset(rstPatient, strFilterByPatient)

    ' Test to see if only 1 record is in the filtered rstPatientFiltered recordset
    ' If only 1 record matches, then no need to populate Me.lstSearchResults
    If rstPatientFiltered.RecordCount <> 0 Then
    If rstPatientFiltered.RecordCount < 2 Then
    ' RecordCount of rstPatientFiltered = 1
    MsgBox "Only one matching record in the Patients table was " & _
    "found. Click 'OK' to find matching incidents for this patient name." _
    , vbOKOnly, "Direct Patient Name Match"

    ' The steps below in this If/Then section are the equivalent of executing
    ' the code in the Dbl_Click event of Me.lstSearchResults, bypassing the
    ' need for the user to make a selection in this first listbox

    ' Since only 1 record just select AccountNum here instead of filling
    ' Me.lstSearchResults and selecting a value from this listbox
    With rstPatientFiltered
    ' Move to the first and only record
    .MoveFirst
    ' Grab the first column value = Account Number
    If Not IsNull(.Fields(0)) Then
    strAccountNum = .Fields(0)
    Else
    strAccountNum = ""
    End If
    End With

    ' Create a recordset based on Me.RecordsetClone
    Set rstInPatientIncidents = Me.RecordsetClone
    ' Supply the recordset and filter field to the CreateFilterOnRecordset
    ' function which creates a filter, applies it, then returns a recordset
    ' with the effects of the filter applied
    Set rstFilteredInPatientIncidents = CreateFilterOnRecordset(rstInPatientIncidents, _
    "AccountNum", strAccountNum)

    ... Message too long --> Continued in next reply

  8. #8
    Join Date
    Jul 2002
    Posts
    25

    Talking Problem was the filter (Continued)

    ' Test to see if only 1 record is in filtered rstFilteredInPatientIncidents
    ' If only 1 record matches, then no need to populate Me.lstSearchResults2
    If rstFilteredInPatientIncidents.RecordCount <> 0 Then
    If rstFilteredInPatientIncidents.RecordCount < 2 Then
    'rstFilteredInPatientIncidents RecordCount = 1
    With rstFilteredInPatientIncidents
    ' Move to first and only record
    .MoveFirst
    ' Since rstFilteredInPatientIncidents is based on Me.RecordsetClone
    ' and since MoveFirst action leaves cursor on first and only record
    ' Set Form's bookmark to bookmark of the record in the recordset

    ' ERROR: Not a valid bookmark
    Me.Bookmark = .Bookmark

    ' Perhaps have to apply same filter to Me.Recordset so that both
    ' recordsets match -or- once a filter is applied, the recordsets
    ' are never able to be matched via bookmark


    Me.tabSearchResults.Visible = False
    Me.txtAccountNum.SetFocus
    ' Set the mouse pointer back to normal
    Screen.MousePointer = 0
    End With
    Exit Sub
    Else
    lstSearchResults2Listbox = Me.lstSearchResults2
    'rstFilteredInPatientIncidents RecordCount > 1
    'Populate Me.lstSearchResults2 with matching incidents (the recordset)
    If FillListBox(lstSearchResults2Listbox, rstFilteredInPatientIncidents) Then
    'Listbox was filled successfully
    Else
    'An error occurred in filling the listbox
    MsgBox "An error occurred in filling the 'Second-Level Search " & _
    "Results' listbox.", vbOKOnly, "Error Filling Search Results Listbox"
    End If
    ' Make the tabSearchResults Tab visible
    Me.tabSearchResults.Visible = True
    Me.lstSearchResults2.SetFocus
    ' Set the mouse pointer back to normal
    Screen.MousePointer = 0
    Exit Sub
    End If
    Else
    ' RecordCount of rstFilteredInPatientIncidents = 0
    ' This is where you have a name in the Patient Table that does not
    ' have a matching record in the InPatientIncidents Table, i.e. you
    ' either just added a patient without an incident -or more likely - you
    ' deleted the incident, but left the patient name in the patient table
    MsgBox "No incident records were found matching the name search " & _
    "criteria entered", vbOKOnly, "No Matches Found"
    Me.tabSearchResults.Visible = False
    Me.lstSearchResults.SetFocus
    Exit Sub
    End If
    Else
    ' RecordCount of rstPatient > 1
    ' Populate Me.lstSearchResults with matching patients (the recordset)
    lstSearchResultsListbox = Me.lstSearchResults
    If FillListBox(lstSearchResultsListbox, rstPatientFiltered) Then
    ' Listbox was filled successfully
    Else
    ' An error occurred in filling the listbox
    MsgBox "An error occurred in filling the Search Results listbox.", _
    vbOKOnly, "Error Filling Search Results Listbox"
    End If
    End If
    Else
    'rstPatientFiltered RecordCount = 0
    MsgBox "No records were found matching the search criteria entered" _
    , vbOKOnly, "No Matches Found"
    End If

    ' Create second recordset in lstSearchResults' Dbl_Click event and name it
    ' rstFilteredInPatientIncidents. Set this equal to Me.RecordsetClone. This
    ' recordset cannot include the initial filter by Patient Name because Patient
    ' Name is not part of this InPatientIncident recordset (Me.RecordsetClone is
    ' a recordset based on the tblInPatientIncidentID table). This should not
    ' reduce the level of filtering, however, because the AccountNum should always
    ' match a specific Patient.

    ' Create a second filter based on the AccountNum matching the selected record
    ' in the lstSearchResult listbox. Name it strFilterByAccountNum and Apply it
    ' to rstFilteredInPatientIncidents (i.e. apply it to Me.RecordsetClone)
    ' This creates a recordset of InPatient Incidents matching the AccountNum value
    ' from either the search criteria (if 1 record matches) or from user selection
    ' (selecting value from the lstSearchResult listbox)

    ' Use rstFilteredInPatientIncidents to supply the RowSource for lstSearchResult2
    ' unless the filtered recordset only contains 1 record.

    ' Set the mouse pointer back to normal
    Screen.MousePointer = 0

    ' Free up resources
    Set wsInPatientIncidents = Nothing
    Set dbInPatientIncidents = Nothing
    Set rstInPatientIncidents = Nothing
    Set wsFilteredInPatientIncidents = Nothing
    Set dbFilteredInPatientIncidents = Nothing
    Set rstFilteredInPatientIncidents = Nothing
    Set wsPatient = Nothing
    Set dbPatient = Nothing
    Set rstPatient = Nothing
    'Set wsPatientFiltered = Nothing
    'Set dbPatientFiltered = Nothing
    Set rstPatientFiltered = Nothing
    Set lstSearchResultsListbox = Nothing
    Set lstSearchResults2Listbox = Nothing

    Exit_cmdStartNameSearch_Click:
    Exit Sub

    Err_cmdStartNameSearch_Click:
    MsgBox Err.Description
    Resume Exit_cmdStartNameSearch_Click

    End Sub

    ------------------------------------------------------------------------------

    The question was actually in the code (I just copied things as I have them). I have a recordset based on Me.RecordsetClone. Do you have to apply the same filter to Me.Recordset that was applied to Me.RecordsetClone so that both recordsets match in order to bookmark them -or- once a filter is applied, the recordsets are never able to be matched via bookmark. Again, I need to take a look at this before I ask the question. I can't say Thank You enough. Perhaps I'll get a chance to help someone else out in the future as I make this my first development forum home.

    Thanks so much,
    Dennis

  9. #9
    Join Date
    Feb 2002
    Posts
    2,232
    What is the bug/problem you are having with the recordsetclone ? I have worked with clone but not with recordsetclone(from what I have read they seem to be the same) - but I know with clone you will be ok.

    Here is an excerpt from online documentation:

    "If you use the Clone method to create a copy of a Recordset object, the Bookmark property settings for the original and the duplicate Recordset objects are identical and can be used interchangeably. However, you can't use bookmarks from different Recordset objects interchangeably, even if they were created by using the same object or the same SQL statement."

  10. #10
    Join Date
    Jul 2002
    Posts
    25

    Wink Not a valid bookmark??

    rnealejr - You've really hung in there with me on this one ...

    Actually, the error message is "Not a valid bookmark," which tells me that there is something else in my code that I need to double check. Probably just an oversight. In a form, using Me.RecordsetClone is the only way to create a recordset copy of the form's recordset that can be synchronized with it using bookmarks. Your excerpt is absolutely correct. You can create a second bookmark from the same table, query, etc. (not using RecordsetClone) and you will not be able to use bookmarks to synchronize the two recordsets. I believe that it is because the bookmark is a byte array that Access creates for all records in any given form recordset. Me.RecordsetClone doesn't actually create a new recordset per se. Yes, you can move within records in the RecordsetClone copy all you want and it doesn't move the records within the form. The MS Access background application code, however, creates a pointer to the original recordset (or to the byte array I think) so that the bookmark "byte value" is the same between the two recordsets because they both reference the same "Access background indexing byte number" assigned to each record.

    I don't know what happens though if you apply a filter. I guess since it is really pointing to the same array of byte values that you could still sync the two recordsets together - Just a logical guess. I think my error though is telling me that I've made some oversight somewhere (I hope). If you spot it before me, let me know ... Again, I appreciate your patience and tenacity through all of this. You've been a real trooper. I do feel obligated to run this one down at this point, but kibitzing is certainly allowed and welcome ... Thanks for everything!!

    Thanks Yet Again,
    Dennis

  11. #11
    Join Date
    Jul 2002
    Posts
    25

    Cool Found the problem !!

    Here's the problem:

    (Code in subroutine

    Set rstPatientFiltered = ApplyFilterToRecordset(rstPatient, strFilterByPatient)

    (Calls this function

    -----------------------------------------------------------------------------------
    Private Function ApplyFilterToRecordset(ByVal rstTemp As DAO.Recordset, _
    ByVal strFilter As String) As DAO.Recordset

    ' Set a filter on the supplied Recordset object argument, then open a
    ' new Recordset object to enable the effects of the filter

    rstTemp.Filter = strFilter
    Set ApplyFilterToRecordset = rstTemp.OpenRecordset

    End Function
    -----------------------------------------------------------------------------------

    This opens up another recordset based on the recordsetclone. Bookmarking goes
    out the window because it's an entirely new recordset. I'll have to find
    another way to sync up the records. .FindFirst should work here when there
    is a single matching record. Since I was relying on bookmarking in my other
    code, it looks like I have some work ahead. Well, at least I know where my
    problems are now. I truly appreciate all of the help in getting pointed in
    the right direction. Sometimes you get too close to the problem to see where
    the trouble really is hidden. Have a great night and thanks for everything !!!

    Dennis

  12. #12
    Join Date
    Feb 2002
    Posts
    2,232
    I have not been able to see where in your code how rstPatient is based on RecordsetClone - no matter, have you thought about using rstPatient.Clone to create rstPatientFiltered - then set the filter property of rstPatientFiltered in your main code - since the Bookmark properties are identical for both ? Also, to clear up any issues with the filter property, here is another excerpt:

    "The Filter property of the original Recordset, if any, will not be applied to the clone. Set the Filter property of the new Recordset in order to filter the results. The simplest way to copy any existing Filter value is to assign it directly, like this: rsNew.Filter = rsOriginal.Filter"

    I hope I have not oversimplified your dilemma, but this should work.

  13. #13
    Join Date
    Jul 2002
    Posts
    25

    Lightbulb Interesting thought you have there ...

    Hmmmm, you have me thinking about what happens to the recordset when it is passed to the ApplyFilterToRecordset function.

    To clarify some things going on in the code:
    There are 2 tables involved in all of this. One is tblPatients, the other is tblIncidents. The tblPatients table represent patient visits, so you could have multiple entries for any given patient. Your observations are keen in that rstPatients does not open a recordsetclone, but rather uses an SQL statement to create the recordset based on the tblPatients table. Depending on what the user inputs into 3 name search textboxes (firstname, middlename, lastname) the code creates a filter that is applied to the rstPatients recordset. Since multiple records could be returned, I pop the values into a listbox if the rstPatient.RecordCount is greater than 1 using a formula that translates the recordset into a semi-colon delimited string (and sets the listbox's column count, etc to accomodate the string). If only 1 record is in the filtered rstPatient recordset (or if a user makes a selection in the listbox - code not shown here as it's in the listbox dbl_click event), I grab the AccountNum field and use it to create a filter for a recordset that I create based on Me.RecordsetClone called rstInPatientIncidents. That's a little tricky to follow, but if you're still with me ... Then, I check to see how many records exists in the filtered rstInPatientIncidents.RecordCount. If only 1, then I need to set Me.Recordset to the single record. If more than 1, then I pop the matching values into a second listbox using the same FillListBox function. Whether 1 record is returned or after the user makes a selection from that second listbox of values I still have to set the form's recordset (Me.Recordset) to the single and/or selected record.

    Here is where you have me thinking now. I thought that I couldn't use .bookmark to sync the records because I opened up a new recordset in the ApplyFilterToRecordset function (or the CreateFilterOnRecordset function - both similar code).
    Now I wonder -> the Me.RecordsetClone based rstInPatientIncidents recordset is passed to this function. It adds (or creates and adds) a filter to the passed in recordset, "opens" this recordset with the filter, then passes this filtered, opened recordset back - assigning it to rstFilteredInPatientIncidents. Wouldn't the recordset already be open and wouldn't it still be the same recordset based on Me.RecordsetClone? I need to ponder this one ... You certainly have not oversimplified the issue, but rather clued me in to a better way to sync up those recordsets with Me.Recordset. I just have to figure out why I received an "invalid bookmark" error when I tried doing it that way first (before I thought the problem was opening up a new recordset, which I now wonder about). I think you have something there ....

    Thanks Yet Again for the (Re-) Direction,
    Dennis

  14. #14
    Join Date
    Jul 2002
    Posts
    25

    Cool My Function Calls

    To both share my code with others that may benefit from it and to fill in any missing gaps for you (your tenacity is remarkable) here are the functions that I'm using:

    -------------------------------------------------------------------------------
    Private Function FillListBox(ByVal lstListBox As ListBox, ByVal rst As DAO.Recordset) _
    As Boolean
    ' If an argument doesn't need to be modified by the function, declare arguments
    ' using the ByVal attribute overriding the VBA default of ByRef. It is faster
    ' to pass arguments by value (ByVal) than to pass them by reference (ByRef)

    Dim intColumnCount As Integer
    Dim I As Integer
    Dim strRowSource As String

    With rst
    If .RecordCount > 0 Then
    intColumnCount = .Fields.Count
    Do Until .EOF
    For I = 0 To (intColumnCount - 1)
    If IsNull(.Fields(I)) Then
    strRowSource = strRowSource & ";" & ""
    Else
    strRowSource = strRowSource & ";" & .Fields(I)
    End If
    Next I
    .MoveNext
    Loop
    'Remove the first semicolon
    strRowSource = Right(strRowSource, Len(strRowSource) - 1)

    With lstListBox
    .RowSourceType = "Value List"
    ' Set Column Count property of Listbox with intColumnCount
    .ColumnCount = intColumnCount
    'Hide the first column/Leave others at default 1" value
    .ColumnWidths = "0;"
    ' Set the bound column to the hidden first column (1-based)
    .BoundColumn = 1
    ' Set the strRowSource string, which now represents the
    ' recordset, as the argument Listbox's RowSource
    .RowSource = strRowSource
    End With
    ' If everything went well, return True to indicate success
    FillListBox = True
    Else
    MsgBox "No records found", vbOKOnly, "No Records Found"
    FillListBox = False
    Exit Function
    End If
    End With

    End Function

    --------------------------------------------------------------------------------

    Private Function CreateFilterOnRecordset(ByVal rstTemp As DAO.Recordset, _
    ByVal strFilterField As String, ByVal strActualFilterValue As String) As DAO.Recordset
    ' This function creates a filter from the supplied Field argument and Filter value argument
    ' Then opens a new Recordset object to enable the effects of the filter

    rstTemp.Filter = strFilterField & " = '" & strActualFilterValue & "'"
    Set CreateFilterOnRecordset = rstTemp.OpenRecordset

    End Function

    --------------------------------------------------------------------------------

    Private Function ApplyFilterToRecordset(ByVal rstTemp As DAO.Recordset, _
    ByVal strFilter As String) As DAO.Recordset

    ' Set a filter on the supplied Recordset object argument, then open a
    ' new Recordset object to enable the effects of the filter

    rstTemp.Filter = strFilter
    Set ApplyFilterToRecordset = rstTemp.OpenRecordset

    End Function

  15. #15
    Join Date
    Jul 2002
    Posts
    25

    Angry These functions are buggy

    When I perform a search that should yield multiple matches in the tblPatient table (i.e. I just do a search on MiddleName) the following call to the questionable ApplyFilterToRecordset function:

    Set rstPatientFiltered = ApplyFilterToRecordset(rstPatient, strFilterByPatient)

    Returns rstPatientFiltered.RecordCount of only 1 (When I can look at my dozen test values and see multiple matching records)

    // Note: I corrected the variable naming error in the code I supplied above, i.e. changed lngPatientMiddleName to lngPatientMiddleNameLength & same with lngPatientLastName - Ooops]

    I seem to keep having my attention drawn back to these functions. I need to understand how passing a recordset, applying a filter and then opening (re-opening?) the recordset works. My current understanding is clearly lacking here.

    Dennis

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •