Getting run time error 2455 using Form Current event

[brian11670]

All,
I have a Current event set on a subform A that is trying to change the source and requery several other subforms when the selected record changes in subform A.
I am getting RUN TIME ERROR 2455 "You entered an expression that has an invalid reference to the property form/report".

Private Sub Form_Current()
Dim qdf As DAO.QueryDef


gVarMbrId = Me.MEMBER_ID
strMbr = GetMember()


Set qdf = CurrentDb.QueryDefs("PassThrough_MBR_ENRLMNT")
qdf.SQL = "EXEC [dbo].[WF_FETCH_MBR_ENRLMNT] '" & strMbr & "'"
  
  
'Enrollment
Forms!Frm_Task_List_Detail!PassThrough_MBR_ENRLMNT_subform.Form.RecordSource = "SELECT * FROM PassThrough_MBR_ENRLMNT"
Forms!Frm_Task_List_Detail!PassThrough_MBR_ENRLMNT_subform.Form.Requery


'RevOpt
Forms!Frm_Task_List_Detail!dbo_WF_REVOPT_HCCs_subform.Form.RecordSource = "SELECT * FROM Qry_RevOpt_Member_HCCs WHERE MBRID = '" & strMbr & "' ORDER BY CLM_SRVC_DT desc"
Forms!Frm_Task_List_Detail!dbo_WF_REVOPT_HCCs_subform.Form.Requery
  
'Edward
Forms!Frm_Task_List_Detail!PassThroughMbrHccQuery_subform.Form.RecordSource = "SELECT * FROM Qry_Mbr_Hcc_Query WHERE member_id = '" & strMbr & "' ORDER BY ENSTRTDT desc"
Forms!Frm_Task_List_Detail!PassThroughMbrHccQuery_subform.Form.Requery






End Sub

What is strange is this code above works fine if I use it on an individual text box OnClick event in the same subform A.
Any idea what I am doing wrong r missing? Any help is greatly appreciated.

[Welshgasman]

No ideas as to why, but if you change a recordsource, there is no need to requery?

[Gicu]

Just curious why not have a textbox on the main form populated with the strMbr string (in the current event) and then simply use master\child linking for the subforms?

Cheers,

[Micron]

Always state which line is causing the error. You have 3 somewhat similar references (6 if you want to count them all). We're left guessing.
Maybe you left one subform control with the default name of "Child#" where # is some number. In other words, the syntax may be correct but the references are not. Often it's just an invalid spelling/reference?

EDIT - even if it works on another event, the above could still apply - if you retyped it and it is correct in the working procedure. If it errs on the first reference, rem it out and see what happens on subsequent lines. I agree that if a recordsource is altered, it doesn't seem to need a requery.

[brian11670]

Always state which line is causing the error. You have 3 somewhat similar references (6 if you want to count them all). We're left guessing.
Maybe you left one subform control with the default name of "Child#" where # is some number. In other words, the syntax may be correct but the references are not. Often it's just an invalid spelling/reference?

EDIT - even if it works on another event, the above could still apply - if you retyped it and it is correct in the working procedure. If it errs on the first reference, rem it out and see what happens on subsequent lines. I agree that if a recordsource is altered, it doesn't seem to need a requery.

It errors on this line:

'Enrollment
Forms!Frm_Task_List_Detail!PassThrough_MBR_ENRLMNT_subform.Form.RecordSource = "SELECT * FROM PassThrough_MBR_ENRLMNT"

[Micron]

If it errs on the first reference, rem it out and see what happens on subsequent lines.

What is the answer to that question/suggestion.

Also, I've read some confusing posts about form controls (including subform controls) not being 'loaded' if the form has no records but could not make sense out of whether that applied to the main form or subform. Suggest you ensure that those recordsources actually produce records. Also, perhaps worth keeping in mind that
- subforms load first, so you cannot usually reference main form fields or properties from subform Open, Load or Current events
- AFAIK, Current event is the last form event to fire when you open a form. It doesn't just run when you change records, so your code is running as a result of form opening.

If I may, I'd also suggest some notes in your code so we don't have to make too many assumptions about what's going on. I'm beginning to think that you're trying to modify a property for the main form (not just some other form that is already open) and the first time that code is called, the main form hasn't loaded yet. If that is the cause, you might want to move the recordsource code to a main form event.

What is strange is this code above works fine if I use it on an individual text box OnClick event in the same subform A.

Now that I've thought more about it, that makes perfect sense - the main form definitely is loaded at that point.

[brian11670]

What is the answer to that question/suggestion.

Also, I've read some confusing posts about form controls (including subform controls) not being 'loaded' if the form has no records but could not make sense out of whether that applied to the main form or subform. Suggest you ensure that those recordsources actually produce records. Also, perhaps worth keeping in mind that
- subforms load first, so you cannot usually reference main form fields or properties from subform Open, Load or Current events
- AFAIK, Current event is the last form event to fire when you open a form. It doesn't just run when you change records, so your code is running as a result of form opening.

If I may, I'd also suggest some notes in your code so we don't have to make too many assumptions about what's going on. I'm beginning to think that you're trying to modify a property for the main form (not just some other form that is already open) and the first time that code is called, the main form hasn't loaded yet. If that is the cause, you might want to move the recordsource code to a main form event.

Now that I've thought more about it, that makes perfect sense - the main form definitely is loaded at that point.

Thank you for your thoughts on this and I think you are right in saying: "Suggest you ensure that those recordsources actually produce records. Also, perhaps worth keeping in mind that
- subforms load first, so you cannot usually reference main form fields or properties from subform Open, Load or Current events"

Initially I have the main form and subforms open blank (no records). I have combo boxes for the user to select from to populate the subforms. Once they make their selections to populate the subforms then I was going to use the CURRENT trigger to refresh some of the subforms based on the value of a field in the current active record.

[brian11670]

I tried this but the problem is the dataset is large and it was taking a long time (4-5 minutes) to populate the subform because it has to drag the table across the network then perform the master\child linking.
I forgot to mention this MS Access application is a front end app with a backend SQL Server.

[Micron]

If the combos are on the main form, use their AfterUpdate event? The current event fires as I noted, but also every time you select a different record on a form. I don't think you should have code that sets properties such as RecordSource in the current event. Perhaps you'd be better off if you make use of the Master and Child field properties of the main/sub form and allowed the subform to populate based on the main form records that are loaded (or not) after the combo AfterUpdate event.

so you cannot usually reference main form fields or properties from subform Open, Load or Current events

I think I made that sound a little bit too rigid. Should have said sometimes, not "usually" and it depends on what you're trying to do.