runtime error 2465 (1 Viewer)

[pbuethe]

I have:

Main form – frmDenialTracking

Combobox on main form – cboProvider

Subform – sfrmDenialTracking (this is the name of the subform AND the subform control)

The combobox selection provides criteria to a query which I am changing the recordsource of the subform to.

When I select from cboProvider, I get run time error 2465 – "application-defined or object-defined error"

The line Me!sfrmDenialTracking.Form.LinkChildFields = "[CaseNbr]" is highlighted in the following code:

Private Sub cboProvider_Change()

Me!sfrmDenialTracking.Form.RecordSource = "qryfrmDenTrackingActionNeeded"
Me!sfrmDenialTracking.Form.LinkChildFields = "[CaseNbr]"
Me!sfrmDenialTracking.Form.LinkMasterFields = "[CaseNbr]" 
Me.Requery
Forms!frmDenialTracking!sfrmDenialTracking.Form.Visible = True
Me.cboCaseID.Value = Null
Me.Refresh
Me.sfrmDenialTracking.Form.FilterOn = False
Me.sfrmDenialTracking.Requery

End Sub

Thanks in advance for your help.

 

[theDBguy]

Hi. Just a wild guess but try setting the focus to the subform first before manipulating it. Just a thought...

 

[Pat Hartman]

There doesn't seem to be anything variable in the code. What is the purpose of changing the query from what is already there to this on and HOW would you go back to the original?

If this is simply a search and the query already includes criteria:

Where ProviderID = Forms!yourformname!cboProvider

Then all you need to do is:

Me.Requery

There is no need to update the RecordSource or the master/child links. If the user uses the filters, you should reset them as you are doing.

 

[arnelgp]

Private Sub cboProvider_Change()

Me!sfrmDenialTracking.Form.RecordSource = "qryfrmDenTrackingActionNeeded"
Me!sfrmDenialTracking.LinkChildFields = "[CaseNbr]"
Me!sfrmDenialTracking.LinkMasterFields = "[CaseNbr]" 
Me!sfrmDenialTracking.Visible = True
Me.cboCaseID.Value = ""

End Sub

 

[pbuethe]

theDBguy: I tried setting the focus to the subform as you suggested, but got run-time error 2110: "can't move the focus to the control sfrmDenialTracking". Attempts to correct that led to other errors.

Pat Hartman:

I made the following changes, based on your comments and on the code from other forms in the database which were working:

Removed the record source of frmDenialTracking. It was qryfrmDenTrackingActionNeededAll, which was the same as the query I was setting the subform record source to, except without the provider criteria. This change left the record source of frmDenialTracking blank.

Changed the record source of sfrmDenialTracking (in the property sheet) to qryfrmDenTrackingActionNeededAll.

Changed the code to:

Private Sub cboProvider_Change()

With Me!sfrmDenialTracking.Form
.Visible = True
.Filter = "[CUID] = '" & cboProvider.Value & "'"
.FilterOn = True
.Requery
End With

Me.cboCaseID.Value = Null
Me.Refresh

End Sub

Then when I clicked the button to open the main form, I got an “Enter Parameter Value” dialog box for “CaseNbr”. When I clicked “Cancel”, the form opened.
When I selected a provider, I got run-time error ‘2467’: "The expression you entered refers to an object that is closed or doesn’t exist."
In Debug, the highlighted line is “With Me!sfrmDenialTracking.Form"

 

[theDBguy]

theDBguy: I tried setting the focus to the subform as you suggested, but got run-time error 2110: "can't move the focus to the control sfrmDenialTracking". Attempts to correct that led to other errors.

Hi. Sorry to hear that. Hope the others have another solution for you. Good luck!

 

[The_Doc_Man]

You indicated that the subform control name and the form itself had the same name. I found an article that suggests that there can be a difference in response based on which one you use (in some rare cases) and that there is a preferred choice of which you should use. However, because your form name and control name match, it is possible that Access, faced with a 50-50 choice of which one you meant, picked the wrong one.

https://www.fmsinc.com/microsoftaccess/Forms/Subform/Master-Link-Fields.asp

First, consider changing the name of the control. Second, use the control name in the update.

I also found this article, which should be pretty definitive.

https://docs.microsoft.com/en-us/office/vba/api/access.subform.linkmasterfields

In your case you are using the same name for the fields to use, which is theoretically acceptable if and only if that name is used on both forms... because according to the second article you have to use a field from the parent for the LinkMasterField and something else from the subform for the LinkChildField. IF you have the same exact field name on parent and sub, I wonder if you need to qualify it? Such as one of them being Me.[CaseNbr] and the other one being (similar to) Me!sfrmDenialTracking.Form.[CaseNbr] - or something like that.

 

[pbuethe]

I got it working!

The code is now:

Private Sub cboProvider_Change()

If (Not IsNull(Trim(Me.cboProvider))) Then
  Me!sfrmDenialTracking.Visible = True
  Me!sfrmDenialTracking.Form.RecordSource =  "qryfrmDenTrackingActionNeeded"
  Me.sfrmDenialTracking.Requery
End If
Me.cboCaseID.Value = ""


End Sub

Also, I removed CaseNbr from the LinkMasterFields and LinkChildFields properties in the property sheet of the subform control.

Thanks to everyone for your help!

 

[theDBguy]

I got it working!

The code is now:

Private Sub cboProvider_Change()

If (Not IsNull(Trim(Me.cboProvider))) Then
  Me!sfrmDenialTracking.Visible = True
  Me!sfrmDenialTracking.Form.RecordSource =  "qryfrmDenTrackingActionNeeded"
  Me.sfrmDenialTracking.Requery
End If
Me.cboCaseID.Value = ""


End Sub

Also, I removed CaseNbr from the LinkMasterFields and LinkChildFields properties in the property sheet of the subform control.

Thanks to everyone for your help!

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

 

[Pat Hartman]

1. NEVER use the change event for something like this. The change event runs multiple times. Once for each character typed. Better to use the AfterUpdate event of the control which only runs once.
2. Back to my original observation. You are always changing the RecordSource to the same query!!! That does nothing. All you should need is:

Me.sfrmDenialTracking.Requery

In the combo's AfterUpdate event.

 

[pbuethe]

Pat,

1. I moved the code to the AfterUpdate event. (Previously, the AfterUpdate event was just calling the Change event!)

2. However, the subform recordsource is originally qryfrmDenTrackingActionNeededAll. It is changed to qryfrmDenTrackingActionNeeded. So I kept the change to the recordsource.

Thanks for your reply!

 

[Pat Hartman]

But your code never changes it back. Changing the RecordSource is a "heavy" operation and shouldn't be done unless there is a real reason.

To have a RecordSource query return different data, add criteria to it.

Where SomeField = Forms!yourform!cboSomeField;

When the form opens, the recordset will be empty because nothing is selected in the combo. In the AfterUpdate event of the combo, you requery the form and the filtering happens because cboSomeField now has a value.

 

[pbuethe]

I changed the original record source to the query with criteria, and removed the code to change the recordsource. Now it works even better!

Thanks again.