Error during On Load recordset code

[Michael R]

Hello all.

Please help me with this error, I have no clue why it is happening, maybe you do have. I know this is a bit long, I've made it the simpliest and clearest I could.

I have a code that is designed to go to a specific record in Forms!frmCustomersByCity!subfrmCustomers.Form!Cust omer_Id with a specific city filter Forms!frmCustomersByCity!subfrmCity.Form!City_Id that are in main form Forms!frmCustomersByCity, using bound textbox and unbound combo box values Forms!frmDistribution!cboCustomer_Id.Value, Forms!frmDistribution!cboCity_Id.Value of the form Forms!frmDistribution. I can't use DoCmd.GoToRecord method since it's the record to be gone to is on a subform.

Here's is it again:

open from the form:
Forms!frmDistribution,
Values: Forms!frmDistribution!cboCustomer_Id.Value, Forms!frmDistribution!cboCityList.Value

the form to be opened:
Forms!frmCustomersByCity
goto:
Forms!frmCustomersByCity!subfrmCustomers.Form!Cust omer_Id
filter by:
Forms!frmCustomersByCity!subfrmCity.Form!City_Id

the code I'm using from frmDistribution is:

Private Sub OpenfrmCustomersByCity_Click()
Dim strPageToOpen As String
Dim stLinkCriteria As String
    strPageToOpen = "gotorecord" 
    DoCmd.OpenForm "frmCustomersByCity", , , stLinkCriteria, , , strPageToOpen
End Sub

the code I'm using in the form to be opened is:

Private Sub Form_Load()
      Select Case OpenArgs
      Case "gotorecord"
          Dim rs As DAO.Recordset
          Dim frm As Form
          Set frm = Forms!frmCustomersByCity!subfrmCustomers.Form
          Set rs = frm.RecordsetClone
 
          'assign and activate filter city value      
          Me.subfrmCity.Form.CityList.Value = _
              Forms!frmDistribution!CityList.Value
          Me.subfrmCustomerst.Form.FilterOn = True
 
          'assign and goto customer record
          Me.subfrmCustomerst.Form.Filter = "subfrmCustomerst.ID_City = " _
              & "Forms!frmCustomersByCity!subfrmCity.Form!CityList.Value"
 
 
 
 'here I get RunTime Error 3420: Object Invalid or no longer set
          rs.FindFirst "[Customer_Id] = " & Forms!frmDistribution!cmoCustomer_Id  
 
 
          If rs.NoMatch Then
              MsgBox "can't find record"
          Else
              'Display the found record in the form.
              frm.Bookmark = rs.Bookmark
          End If
          Set rs = Nothing
          Set frm = Nothing
      End Select
End Sub

Does anyone can tell why it is happening?
Thanks a bunch, Michael.

[ADezii]

Hello all.

Please help me with this error, I have no clue why it is happening, maybe you do have. I know this is a bit long, I've made it the simplest and clearest I could.

I have a code that is designed to go to a specific record in Forms!frmCustomersByCity!subfrmCustomers.Form!Cust omer_Id with a specific city filter Forms!frmCustomersByCity!subfrmCity.Form!City_Id that are in main form Forms!frmCustomersByCity, using bound textbox and unbound combo box values Forms!filmdistribution!cboCustomer_Id.Value, Forms!filmdistribution!society_Id.Value of the form Forms!filmdistribution. I can't use DoCmd.GoToRecord method since it's the record to be gone to is on a subform.

Here's is it again:

open from the form:
Forms!filmdistribution,
Values: Forms!filmdistribution!cboCustomer_Id.Value, Forms!filmdistribution!cboCityList.Value

the form to be opened:
Forms!frmCustomersByCity
goto:
Forms!frmCustomersByCity!subfrmCustomers.Form!Cust omer_Id
filter by:
Forms!frmCustomersByCity!subfrmCity.Form!City_Id

the code I'm using from filmdistribution is:

Private Sub OpenfrmCustomersByCity_Click()
Dim strPageToOpen As String
Dim stLinkCriteria As String
    strPageToOpen = "gotorecord" 
    DoCmd.OpenForm "frmCustomersByCity", , , stLinkCriteria, , , strPageToOpen
End Sub

the code I'm using in the form to be opened is:

Private Sub Form_Load()
      Select Case OpenArgs
      Case "gotorecord"
          Dim rs As DAO.Recordset
          Dim frm As Form
          Set frm = Forms!frmCustomersByCity!subfrmCustomers.Form
          Set rs = frm.RecordsetClone
 
          'assign and activate filter city value      
          Me.subfrmCity.Form.CityList.Value = _
              Forms!filmdistribution!CityList.Value
          Me.subfrmCustomerst.Form.FilterOn = True
 
          'assign and goto customer record
          Me.subfrmCustomerst.Form.Filter = "subfrmCustomerst.ID_City = " _
              & "Forms!frmCustomersByCity!subfrmCity.Form!CityList.Value"
 
 
 
 'here I get RunTime Error 3420: Object Invalid or no longer set
          rs.FindFirst "[Customer_Id] = " & Forms!filmdistribution!cmpcustomer_Id  
 
 
          If rs.NoMatch Then
              MsgBox "can't find record"
          Else
              'Display the found record in the form.
              frm.Bookmark = rs.Bookmark
          End If
          Set rs = Nothing
          Set frm = Nothing
      End Select
End Sub

Does anyone can tell why it is happening?
Thanks a bunch, Michael.

It looks like 1 of 2 possibilities
__1 Is your Sub-Form subfrmCustomers or subfrmCustomerst? Both are specifically referenced in the code. If this is just a typo then:
__2 Explanation follows:

Dim rs As DAO.Recordset
Dim frm As Form

'The Object Variable rs is pointing to a copy of the Recordset for subfrmCustomers (RecordsetClone)

Set frm = Forms!frmCustomersByCity!subfrmCustomers.Form
 Set rs = frm.RecordsetClone

Once you apply a Filter to it as in:

Me.subfrmCustomerst.Form.FilterOn = True    'typo?
 

rs will no longer be valid. The Object Variable (rs) pointed to the underlying Recordset of subfrmCustomers 'before' the Filter was applied.!

Me.subfrmCustomerst.Form.FilterOn = True

When you now try to initiate a FindFirst, what does rs point to?

rs.FindFirst "[Customer_Id] = " & Forms!filmdistribution!cmpcustomer_Id  

NOTE: I think I'm on the right track with this, if not I apologize for the endless gibbrish!

[Michael R]

It looks like 1 of 2 possibilities
__1 Is your Sub-Form subfrmCustomers or subfrmCustomerst? Both are specifically referenced in the code. If this is just a typo then:

(It was a typo, I simplified names here.)

__2 Explanation follows:

Dim rs As DAO.Recordset
Dim frm As Form

'The Object Variable rs is pointing to a copy of the Recordset for subfrmCustomers (RecordsetClone)

Set frm = Forms!frmCustomersByCity!subfrmCustomers.Form
 Set rs = frm.RecordsetClone

Once you apply a Filter to it as in:

Me.subfrmCustomerst.Form.FilterOn = True    'typo?
 

rs will no longer be valid. The Object Variable (rs) pointed to the underlying Recordset of subfrmCustomers 'before' the Filter was applied.!

Me.subfrmCustomerst.Form.FilterOn = True

First, Thanks for looking into this. How cool, you are absolutely right, that is the problem.

Now I've put all that is to do with form filtering before the recordset code and recordset dimensioning, and it works fine.

Can you explain why filteron makes rs no longer valid?

[ADezii]

(It was a typo, I simplified names here.)


First, Thanks for looking into this. How cool, you are absolutely right, that is the problem.

Now I've put all that is to do with form filtering before the recordset code and recordset dimensioning, and it works fine.

Can you explain why filteron makes rs no longer valid?

Note: If you close a form or if you change the form's RecordSource property, the Recordset object is no longer valid. If you subsequently refer to the Recordset object or to previously saved bookmarks in the form or the Recordset object, an error will occur. By applying a Filter, you are, in essence changing the Form's RecordSource property which in turn would apply to a Recordset Clone.

[Michael R]

Note: If you close a form or if you change the form's RecordSource property, the Recordset object is no longer valid. If you subsequently refer to the Recordset object or to previously saved bookmarks in the form or the Recordset object, an error will occur. By applying a Filter, you are, in essence changing the Form's RecordSource property which in turn would apply to a Recordset Clone.

Much thanks for your reply.

I hope I could ask here why another code doesn't work for me, and how is it related to what have been explained here, as it is something of this subject.

I have a procedure that changes form's RecordSource to query another query, of exactly the same table, fields and metadata, and I want to keep the same filter to be applied as with the previous RecordSource:

Private Sub cmdView_Click()
        dim strFilter as String
 
        strFilter = Me.Form.Filter
 
        Me.Form.RecordSource = "Distribution2"
        Me.Form.Requery
        Me.Form.Filter = strFilter
        Me.Form.FilterOn = True
End Sub

After the procedure runs, the filter isn't applied, and in fact, the form show's no records at all.

The funny thing is the filter does apply if I add it On Load procedure of the form:
Me.Form.Filter = "filter"
Me.Form.FilterOn = True

Why is that, and how can I work around it?

Much thanks.

[MMcCarthy]

Much thanks for your reply.

I hope I could ask here why another code doesn't work for me, and how is it related to what have been explained here, as it is something of this subject.

I have a procedure that changes form's RecordSource to query another query, of exactly the same table, fields and metadata, and I want to keep the same filter to be applied as with the previous RecordSource:

Private Sub cmdView_Click()
        dim strFilter as String
 
        strFilter = Me.Form.Filter
 
        Me.Form.RecordSource = "Distribution2"
        Me.Form.Requery
        Me.Form.Filter = strFilter
        Me.Form.FilterOn = True
End Sub

After the procedure runs, the filter isn't applied, and in fact, the form show's no records at all.

The funny thing is the filter does apply if I add it On Load procedure of the form:
Me.Form.Filter = "filter"
Me.Form.FilterOn = True

Why is that, and how can I work around it?

Much thanks.

At a guess I would say it is because you are trying to change the recordsource and filter of the query while the form is open. You can't do this. However, it will work in the On Load event because the form is not yet open. You will have to close the form and reopen it with the code in the on load event for it to work. You are not changing the filter so I would suggest setting up a small unbound form with a dropdown list of the Record souces and a command button to open the form. As long as the previous form remains open this should work for you.

Private Sub Form_Load()
 
        Me.RecordSource = Forms!Form1Name!ComboBoxName
        Me.Requery
 
End Sub
 

[Michael R]

At a guess I would say it is because you are trying to change the recordsource and filter of the query while the form is open. You can't do this. However, it will work in the On Load event because the form is not yet open. You will have to close the form and reopen it with the code in the on load event for it to work.

But wait, If I change the RecordSource by one command button, and then apply filter by another command button, the filter does work...

[ADezii]

Much thanks for your reply.

I hope I could ask here why another code doesn't work for me, and how is it related to what have been explained here, as it is something of this subject.

I have a procedure that changes form's RecordSource to query another query, of exactly the same table, fields and metadata, and I want to keep the same filter to be applied as with the previous RecordSource:

Private Sub cmdView_Click()
        dim strFilter as String
 
        strFilter = Me.Form.Filter
 
        Me.Form.RecordSource = "Distribution2"
        Me.Form.Requery
        Me.Form.Filter = strFilter
        Me.Form.FilterOn = True
End Sub

After the procedure runs, the filter isn't applied, and in fact, the form show's no records at all.

The funny thing is the filter does apply if I add it On Load procedure of the form:
Me.Form.Filter = "filter"
Me.Form.FilterOn = True

Why is that, and how can I work around it?

Much thanks.

If you explicitly assign the Filter, it will work:

strFilter = "[LastName]='" & "Dezii" & "'"

[MMcCarthy]

But wait, If I change the RecordSource by one command button, and then apply filter by another command button, the filter does work...

Michael you said the filter didn't change so why do you need to apply it using the command button. If you really do for some reason we can add it to the code on the other button but if it's preset on the form it shouldn't change.

Mary

[funmi]

pls could you give me list of books i can read to understand how to develope Visual basic Application codes that could populate oracle database when appended to excel spreadsheet

[MMcCarthy]

pls could you give me list of books i can read to understand how to develope Visual basic Application codes that could populate oracle database when appended to excel spreadsheet

Please do not hijack another posters thread. Start a discussion to open your own question.

ADMIN

[Michael R]

If you explicitly assign the Filter, it will work:

strFilter = "[LastName]='" & "Dezii" & "'"

I've tried even adding it without the variable medium, and it still didn't work.

Michael you said the filter didn't change so why do you need to apply it using the command button. If you really do for some reason we can add it to the code on the other button but if it's preset on the form it shouldn't change.

The command I have runs a procedure that changes forms .RecordSource and then applies a filter, but that results in an empty form. However, if the filter is applied later via another command, it does work properly.

[MMcCarthy]

I've tried even adding it without the variable medium, and it still didn't work.


The command I have runs a procedure that changes forms .RecordSource and then applies a filter, but that results in an empty form. However, if the filter is applied later via another command, it does work properly.

But if the filter is set in the filter property of the form it won't need to be applied in code.

[LastName]="ADezii"

[Michael R]

But if the filter is set in the filter property of the form it won't need to be applied in code.

But how can I determine what filter is it when I change the .RecordSource? It could be more than one filter.

[Michael R]

Me.Form.Filter = "[LastName]='" & "Dezii" & "'"
Me.Form.FilterOn = True

I added this to the code, and now at least it works, and not hides all the records

DoEvents

It works only if the filter is assigned explicitly, as Adezii suggested, but I can't use this solution, because the form has a possibility of changing the filter during the work.

[MMcCarthy]

Me.Form.Filter = "[LastName]='" & "Dezii" & "'"
Me.Form.FilterOn = True

I added this to the code, and now at least it works, and not hides all the records

DoEvents

It works only if the filter is assigned explicitly, as Adezii suggested, but I can't use this solution, because the form has a possibility of changing the filter during the work.

Michael,

This is new information as in your first post you said the filter didn't change.

If the filter is changing you will have to apply it each time. How and when is this change being triggered.

Mary

[Michael R]

Michael,

This is new information as in your first post you said the filter didn't change.

If the filter is changing you will have to apply it each time. How and when is this change being triggered.

Mary

Thanks for answering. I appologize for not making this clear.

The filter can be changed at any time, but when the form switches to another RecordSource, the filter should remain the same, as it was in its latest of the preivous RecordSource, just before the change, that's why I've coded:

        strFilter = Me.Form.Filter
 
        Me.Form.RecordSource = "Distribution2"
        Me.Form.Requery
        Me.Form.Filter = strFilter
        Me.Form.FilterOn = True

Michael.

[MMcCarthy]

Micael

Have a look at NeoPa's tutorial on this and see if there is anything there that can help out.

Example Filtering on a Form

Mary

[Michael R]

Micael

Have a look at NeoPa's tutorial on this and see if there is anything there that can help out.

Example Filtering on a Form

Mary

I haven't found there anything on the problem of applying a filter again after changing the RecordSouce of a form.

I wonder if it is possible to do so at all, and if not, what is the exact cause that prevents it. It seems to me like although maybe not so common but a relevant thing to preform. It is silly that it doesn't work via coding, but does work if I create a special button that sets the filter again, and press on it after changing the RecordSource.

Regards.