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

Error during On Load recordset code

100+
P: 176
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub OpenfrmCustomersByCity_Click()
  2. Dim strPageToOpen As String
  3. Dim stLinkCriteria As String
  4.     strPageToOpen = "gotorecord" 
  5.     DoCmd.OpenForm "frmCustomersByCity", , , stLinkCriteria, , , strPageToOpen
  6. End Sub
the code I'm using in the form to be opened is:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.       Select Case OpenArgs
  3.       Case "gotorecord"
  4.           Dim rs As DAO.Recordset
  5.           Dim frm As Form
  6.           Set frm = Forms!frmCustomersByCity!subfrmCustomers.Form
  7.           Set rs = frm.RecordsetClone
  8.  
  9.           'assign and activate filter city value      
  10.           Me.subfrmCity.Form.CityList.Value = _
  11.               Forms!frmDistribution!CityList.Value
  12.           Me.subfrmCustomerst.Form.FilterOn = True
  13.  
  14.           'assign and goto customer record
  15.           Me.subfrmCustomerst.Form.Filter = "subfrmCustomerst.ID_City = " _
  16.               & "Forms!frmCustomersByCity!subfrmCity.Form!CityList.Value"
  17.  
  18.  
  19.  
  20. 'here I get RunTime Error 3420: Object Invalid or no longer set
  21.           rs.FindFirst "[Customer_Id] = " & Forms!frmDistribution!cmoCustomer_Id  
  22.  
  23.  
  24.           If rs.NoMatch Then
  25.               MsgBox "can't find record"
  26.           Else
  27.               'Display the found record in the form.
  28.               frm.Bookmark = rs.Bookmark
  29.           End If
  30.           Set rs = Nothing
  31.           Set frm = Nothing
  32.       End Select
  33. End Sub
Does anyone can tell why it is happening?
Thanks a bunch, Michael.
Feb 21 '07 #1
Share this Question
Share on Google+
18 Replies


ADezii
Expert 5K+
P: 8,597
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub OpenfrmCustomersByCity_Click()
  2. Dim strPageToOpen As String
  3. Dim stLinkCriteria As String
  4.     strPageToOpen = "gotorecord" 
  5.     DoCmd.OpenForm "frmCustomersByCity", , , stLinkCriteria, , , strPageToOpen
  6. End Sub
the code I'm using in the form to be opened is:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.       Select Case OpenArgs
  3.       Case "gotorecord"
  4.           Dim rs As DAO.Recordset
  5.           Dim frm As Form
  6.           Set frm = Forms!frmCustomersByCity!subfrmCustomers.Form
  7.           Set rs = frm.RecordsetClone
  8.  
  9.           'assign and activate filter city value      
  10.           Me.subfrmCity.Form.CityList.Value = _
  11.               Forms!filmdistribution!CityList.Value
  12.           Me.subfrmCustomerst.Form.FilterOn = True
  13.  
  14.           'assign and goto customer record
  15.           Me.subfrmCustomerst.Form.Filter = "subfrmCustomerst.ID_City = " _
  16.               & "Forms!frmCustomersByCity!subfrmCity.Form!CityList.Value"
  17.  
  18.  
  19.  
  20. 'here I get RunTime Error 3420: Object Invalid or no longer set
  21.           rs.FindFirst "[Customer_Id] = " & Forms!filmdistribution!cmpcustomer_Id  
  22.  
  23.  
  24.           If rs.NoMatch Then
  25.               MsgBox "can't find record"
  26.           Else
  27.               'Display the found record in the form.
  28.               frm.Bookmark = rs.Bookmark
  29.           End If
  30.           Set rs = Nothing
  31.           Set frm = Nothing
  32.       End Select
  33. 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:
Expand|Select|Wrap|Line Numbers
  1. Dim rs As DAO.Recordset
  2. Dim frm As Form
'The Object Variable rs is pointing to a copy of the Recordset for subfrmCustomers (RecordsetClone)
Expand|Select|Wrap|Line Numbers
  1. Set frm = Forms!frmCustomersByCity!subfrmCustomers.Form
  2. Set rs = frm.RecordsetClone
Once you apply a Filter to it as in:
Expand|Select|Wrap|Line Numbers
  1. Me.subfrmCustomerst.Form.FilterOn = True    'typo?
  2.  
rs will no longer be valid. The Object Variable (rs) pointed to the underlying Recordset of subfrmCustomers 'before' the Filter was applied.!
Expand|Select|Wrap|Line Numbers
  1. Me.subfrmCustomerst.Form.FilterOn = True
When you now try to initiate a FindFirst, what does rs point to?
Expand|Select|Wrap|Line Numbers
  1. 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!
Feb 22 '07 #2

100+
P: 176
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:
Expand|Select|Wrap|Line Numbers
  1. Dim rs As DAO.Recordset
  2. Dim frm As Form
'The Object Variable rs is pointing to a copy of the Recordset for subfrmCustomers (RecordsetClone)
Expand|Select|Wrap|Line Numbers
  1. Set frm = Forms!frmCustomersByCity!subfrmCustomers.Form
  2. Set rs = frm.RecordsetClone
Once you apply a Filter to it as in:
Expand|Select|Wrap|Line Numbers
  1. Me.subfrmCustomerst.Form.FilterOn = True    'typo?
  2.  
rs will no longer be valid. The Object Variable (rs) pointed to the underlying Recordset of subfrmCustomers 'before' the Filter was applied.!
Expand|Select|Wrap|Line Numbers
  1. 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?
Feb 22 '07 #3

ADezii
Expert 5K+
P: 8,597
(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.
Feb 22 '07 #4

100+
P: 176
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdView_Click()
  2.         dim strFilter as String
  3.  
  4.         strFilter = Me.Form.Filter
  5.  
  6.         Me.Form.RecordSource = "Distribution2"
  7.         Me.Form.Requery
  8.         Me.Form.Filter = strFilter
  9.         Me.Form.FilterOn = True
  10. 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.
Feb 22 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdView_Click()
  2.         dim strFilter as String
  3.  
  4.         strFilter = Me.Form.Filter
  5.  
  6.         Me.Form.RecordSource = "Distribution2"
  7.         Me.Form.Requery
  8.         Me.Form.Filter = strFilter
  9.         Me.Form.FilterOn = True
  10. 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.  
  3.         Me.RecordSource = Forms!Form1Name!ComboBoxName
  4.         Me.Requery
  5.  
  6. End Sub
  7.  
Feb 22 '07 #6

100+
P: 176
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...
Feb 22 '07 #7

ADezii
Expert 5K+
P: 8,597
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdView_Click()
  2.         dim strFilter as String
  3.  
  4.         strFilter = Me.Form.Filter
  5.  
  6.         Me.Form.RecordSource = "Distribution2"
  7.         Me.Form.Requery
  8.         Me.Form.Filter = strFilter
  9.         Me.Form.FilterOn = True
  10. 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:
Expand|Select|Wrap|Line Numbers
  1. strFilter = "[LastName]='" & "Dezii" & "'"
Feb 22 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 22 '07 #9

P: 2
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
Feb 22 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 22 '07 #11

100+
P: 176
If you explicitly assign the Filter, it will work:
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 22 '07 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1. [LastName]="ADezii"
Feb 23 '07 #13

100+
P: 176
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.
Feb 23 '07 #14

100+
P: 176
Expand|Select|Wrap|Line Numbers
  1. Me.Form.Filter = "[LastName]='" & "Dezii" & "'"
  2. Me.Form.FilterOn = True
I added this to the code, and now at least it works, and not hides all the records
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 23 '07 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
Expand|Select|Wrap|Line Numbers
  1. Me.Form.Filter = "[LastName]='" & "Dezii" & "'"
  2. Me.Form.FilterOn = True
I added this to the code, and now at least it works, and not hides all the records
Expand|Select|Wrap|Line Numbers
  1. 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
Feb 23 '07 #16

100+
P: 176
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:
Expand|Select|Wrap|Line Numbers
  1.         strFilter = Me.Form.Filter
  2.  
  3.         Me.Form.RecordSource = "Distribution2"
  4.         Me.Form.Requery
  5.         Me.Form.Filter = strFilter
  6.         Me.Form.FilterOn = True
Michael.
Feb 26 '07 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 26 '07 #18

100+
P: 176
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.
Feb 26 '07 #19

Post your reply

Sign in to post your reply or Sign up for a free account.