Function call causing Reserved Error 13

[alexsmith91]

Hello,
Below is the use case of my access forms.

I have 2 forms

Form 1: I pull few fields from SQL and store them in a recordset.
Below is the query

Private Sub btn_EDIT_EMPLOYEE_INFO_Click()


Dim strSQL As String
Dim r As New ADODB.Recordset
Dim DC As New DataConnection


strSQL = "SELECT P.REP_ID, P.FIRST_NAME, P.LAST_NAME, P.CURRENT_MEMBER, P.DATE_ADDED, " & _
         "P.DATE_REMOVED, P.PREFERRED_FULL_NAME, P.EMAIL_ADDRESS, A.REASON_ADDED, R.REASON_REMOVED " & _
         "FROM (TableA AS P LEFT JOIN TableX AS A ON P.REASON_ADDED = A.ID) LEFT JOIN " & _
         "TableY AS R ON P.REASON_REMOVED = R.ID WHERE P.REP_ID = '" & Me.REP_ID & "' ;"
         
r.Open strSQL, DC.CPS_DATA, adOpenKeyset, adLockOptimistic


DoCmd.OpenForm "frm_CPS_EDIT"


Set Forms("frm_CPS_EDIT").Recordset = r


Forms("frm_CPS_EDIT").Requery


Call Form_frm_CPS_EDIT.ACTIVATE_DEACTIVATE_STATUS


DoCmd.Close acForm, "DASHBOARD", acSaveNo
End Sub

So I store the values coming from strsql into 'r' recordset and then set those values for the form 'frm_CPS_EDIT'.


Now since this is a button click function, when I click that button, it will open the DoCmd.OpenForm "frm_CPS_EDIT"
But there seems to be an error coming on 'Call Form_frm_CPS_EDIT.ACTIVATE_DEACTIVATE_STATUS'.

Error: Runtime error '13'
Reserved Error.


Few additional Information::



the Form 2 is the == frm_CPS_EDIT

I check the function call for the ACTIVATE_DEACTIVATE_STATUS

Public Sub ACTIVATE_DEACTIVATE_STATUS()
Dim STR_ACTIVATE_DEACTIVATE As String


If Me.ACTIVE_MEMBER_TXT = "YES" Then
STR_ACTIVATE_DEACTIVATE = "DEACTIVATE"
Else: STR_ACTIVATE_DEACTIVATE = "ACTIVATE"
End If


Me.btn_ACTIVATE_DEACTIVATE.Caption = STR_ACTIVATE_DEACTIVATE & " THIS EMPLOYEE"


End Sub

Code for the logic of ACTIVE_MEMBER_TXT

=IIf([CURRENT_MEMBER]=1,"YES","NO")

CURRENT_MEMBER is a checkbox which I believe is coming from the P.CURRENT_MEMBER column from strsql.


MY questions is :
How do i start debugging this issue?
What could be the cause of this issue?

Thanks!

[alexsmith91]

I tried adding debug.print between the code to check if the recordset is populating data or not.
It works fine as expected.

Set Forms("frm_CPS_EDIT").Recordset = r


Debug.Print r.Fields(0).Name
Debug.Print r.Fields(1).Name
Debug.Print r.Fields(2).Name
Debug.Print r.Fields(0).Value
Debug.Print r.Fields(1).Value
Debug.Print r.Fields(2).Value


Forms("frm_CPS_EDIT").Requery

I also added a debug.print during the start of the function call.
It prints the datetime.

Public Sub ACTIVATE_DEACTIVATE_STATUS()Dim STR_ACTIVATE_DEACTIVATE As String


Debug.Print "ACTIVATE_DEACTIVATE_STATUS st" & Now

[Welshgasman]

Try referring to the form in the same way you open it and set it's recordset?

[alexsmith91]

I didnt exactly understand your solution.
Would you mind pointing where do i do that?
Like which part of the code?

Try referring to the form in the same way you open it and set it's recordset?

[Welshgasman]

I just tested this and it works?

Private Sub cmdCheck_Click()
DoCmd.OpenForm "Form2"
Call Forms("Form2").TestCall
   
End Sub

[Minty]

@Gasman has it nailed.

Although you can refer to a form using this syntax
Form_frm_CPS_EDIT.ACTIVATE_DEACTIVATE_STATUS
It's not recommended as it can instantiate another hidden instance of it depending on how it is called.
There is a long and technical discussion about it here
https://www.access-programmers.co.uk.../#post-1747913

[Welshgasman]

What I do not understand, is that the O/P used that syntax for two form calls for that form, then used something completely different for the code call?

[alexsmith91]

Not working, getting the same error on that line.
Is there any other code that I can provide to help me diagnose the issue?

Here is another thing that I tried.

I commented the function call, just so I can atleast get the recordset (r) populate the data on the form 2 = frm_CPS_EDIT

strSQL = "SELECT P.REP_ID, P.FIRST_NAME, P.LAST_NAME, P.CURRENT_MEMBER, P.DATE_ADDED, " & _
         "P.DATE_REMOVED, P.PREFERRED_FULL_NAME, P.EMAIL_ADDRESS, A.REASON_ADDED, R.REASON_REMOVED " & _
         "FROM (TableA AS P LEFT JOIN TableX AS A ON P.REASON_ADDED = A.ID) LEFT JOIN " & _
         "TableY AS R ON P.REASON_REMOVED = R.ID WHERE P.REP_ID = '" & Me.REP_ID & "' ;"
         
r.Open strSQL, DC.CPS_DATA, adOpenKeyset, adLockOptimistic


DoCmd.OpenForm "frm_CPS_EDIT"


Set Forms("frm_CPS_EDIT").Recordset = r


Forms("frm_CPS_EDIT").Requery


'Call Form_frm_CPS_EDIT.ACTIVATE_DEACTIVATE_STATUS


DoCmd.Close acForm, "DASHBOARD", acSaveNo

So, the code runs without any error, it opens the frm_CPS_EDIT, but then all the values in that form are #Name

I just tested this and it works?

Private Sub cmdCheck_Click()
DoCmd.OpenForm "Form2"
Call Forms("Form2").TestCall
   
End Sub

[alexsmith91]

What do you mean by "used something completely different for the code call"

I'm just calling the function in the frm_CPS_EDIT to call the function ACTIVATE_DEACTIVATE_STATUS as soon as the form opens.

Would you mind explaining more about your statement. Happy to elaborate more on that.

Thanks!

What I do not understand, is that the O/P used that syntax for two form calls for that form, then used something completely different for the code call?

[Micron]

I cannot recall ever seeing code that attempted to set a form recordsource property by using forms("frmNameHere").Recordset =
It is customary to use the form recordsource property when you have a sql statement as the source anyway? I don't see evidence of code doing anything with a recordset that can't be done with or to a form so why use this method? Perhaps this will help
https://learn.microsoft.com/en-us/of...form.recordset

As for the error number, it usually means "data type mismatch" and not "reserved error".

[Welshgasman]

What do you mean by "used something completely different for the code call"

I'm just calling the function in the frm_CPS_EDIT to call the function ACTIVATE_DEACTIVATE_STATUS as soon as the form opens.

Would you mind explaining more about your statement. Happy to elaborate more on that.


Thanks!

You used my syntax for two lines of code for your recordset and requery, but did not use that syntax for calling the code?
I have no idea as to why that syntax does not work for you. As I mentioned, I tested it before posting.

[alexsmith91]

If you are talking about this code : Call Forms("Form2").TestCall
then I did use that as well. But getting the error as well.
Let me diagnose the issue from a data type mismatch perspective to what @micron had mentioned and get back to you.

Thanks

You used my syntax for two lines of code for your recordset and requery, but did not use that syntax for calling the code?
I have no idea as to why that syntax does not work for you. As I mentioned, I tested it before posting.

[alexsmith91]

Thanks for your reply.
I checked a few things
1: While debugging, I checked if the value is populating in the intermediate window or not. And , it is.
2: the text formate is marked as Plain text
3: control source is correct as well.
4: the data type in my actual sql table is nvarchar(4)

What else can be done to check the data type mismatch?

I cannot recall ever seeing code that attempted to set a form recordsource property by using forms("frmNameHere").Recordset =
It is customary to use the form recordsource property when you have a sql statement as the source anyway? I don't see evidence of code doing anything with a recordset that can't be done with or to a form so why use this method? Perhaps this will help
https://learn.microsoft.com/en-us/of...form.recordset

As for the error number, it usually means "data type mismatch" and not "reserved error".

[Welshgasman]

If you want ACTIVATE_DEACTIVATE_STATUS as soon as the form opens, then put it in that Form open event, or perhaps the Load event (depending on what is available at that time?)
If you do not want it every time the form opens, pass something into the form with OpenArgs and check for that.

All this advice above is just because what works for me, does not work for you?

[alexsmith91]

Got it. let me try with that approach

If you want ACTIVATE_DEACTIVATE_STATUS as soon as the form opens, then put it in that Form open event, or perhaps the Load event (depending on what is available at that time?)
If you do not want it every time the form opens, pass something into the form with OpenArgs and check for that.

All this advice above is just because what works for me, does not work for you?