Error during On Load recordset code | Familiar Sight | | Join Date: Nov 2006
Posts: 157
| |
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.
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: Error during On Load recordset code
Quote:
Originally Posted by 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 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!
| | Familiar Sight | | Join Date: Nov 2006
Posts: 157
| | | re: Error during On Load recordset code
Quote:
Originally Posted by ADezii 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.) Quote:
Originally Posted by ADezii __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?
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: Error during On Load recordset code
Quote:
Originally Posted by Michael R (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.
| | Familiar Sight | | Join Date: Nov 2006
Posts: 157
| | | re: Error during On Load recordset code
Quote:
Originally Posted by ADezii 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.
|  | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: Error during On Load recordset code
Quote:
Originally Posted by Michael R 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
-
| | Familiar Sight | | Join Date: Nov 2006
Posts: 157
| | | re: Error during On Load recordset code
Quote:
Originally Posted by mmccarthy 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...
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: Error during On Load recordset code
Quote:
Originally Posted by Michael R 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" & "'"
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: Error during On Load recordset code
Quote:
Originally Posted by Michael R 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
| | Newbie | | Join Date: Feb 2007
Posts: 2
| | | re: Error during On Load recordset code
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
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: Error during On Load recordset code
Quote:
Originally Posted by 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 Please do not hijack another posters thread. Start a discussion to open your own question.
ADMIN
| | Familiar Sight | | Join Date: Nov 2006
Posts: 157
| | | re: Error during On Load recordset code
Quote:
Originally Posted by ADezii 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. Quote:
Originally Posted by mmccarthy 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.
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: Error during On Load recordset code
Quote:
Originally Posted by Michael R 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. | | Familiar Sight | | Join Date: Nov 2006
Posts: 157
| | | re: Error during On Load recordset code
Quote:
Originally Posted by mmccarthy 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.
| | Familiar Sight | | Join Date: Nov 2006
Posts: 157
| | | re: Error during On Load recordset code
- 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
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.
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: Error during On Load recordset code
Quote:
Originally Posted by 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
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
| | Familiar Sight | | Join Date: Nov 2006
Posts: 157
| | | re: Error during On Load recordset code
Quote:
Originally Posted by mmccarthy 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.
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: Error during On Load recordset code
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
| | Familiar Sight | | Join Date: Nov 2006
Posts: 157
| | | re: Error during On Load recordset code
Quote:
Originally Posted by 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 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.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,471 network members.
|
