Event actions upon a sub-form serviced within the sub-form code-sheet

[GraeagleBill]

If there is a Public Function coded in a sub-form's code sheet, is there a way for that function to get control when that function name is coded in a custom popup menu bar via a right-click action? As it is, I've only been successful in getting function control when the function is Public within the parent's code sheet.

Thanks,
Bill

[rpeare]

can you provide a sample db?

[GraeagleBill]

Not sure what you're looking for here. Maybe I wasn't clear enough. I have a custom right-click popup. The "Action" defined in that popup is like "=MyFunction()". The ONLY time I can successfully use that function from a sub-form right-click action is if the function is coded Public in the code-sheet of the parent form. What I want is to be able to have that function executing from the code-sheet of the sub-form itself. The reason being that the function needs to be making changes to fields returned with the RecordSource query that underlies the sub-form. The parent can't be making changes to one of its sub-form's RecordSource.

[June7]

Don't know about rpeare, but I've never dealt with anything like this. Perhaps the function must be in a general module.

[GraeagleBill]

Function code runs fine when coded Public in the code-sheet of the parent. The problem is in updating fields in the RecordSource of the child.

[June7]

I'm with rpeare, need code to analyse. Post it or the db so we can educate ourselves on this behavior and maybe figure out if what you want can be done.

[GraeagleBill]

I'm not quite sure how to handle this. The backend is comprised of tables containing confidential personnel data. I am bound to preserve that confidentiality, so I don't know how to satisfy rpeare's request. The frontend mdb wouldn't be of much help if you couldn't run the troublesome situation.

[GraeagleBill]

Here is the function code as copied out of the parent's code-sheet. It is this code modified appropriately that I want to run in the child's module.

Public Function BedRemove()
Dim CtlName As String 'Name of text-box just right-clicked (Text box is unbounded)
Dim IndividualsID As Integer 'Roster RosID of individual currently assigned to the group, as stored in IDx.
Dim FName As String 'First name of individual either being moved or marked as not attending
Dim Status As Boolean 'Yes/No regarding individuals planning to attend
Dim I As Integer 'Essentially the index value identifying which of the 6 ID's in Groupings was detected.
Dim GrpNo As Integer 'What's the group number


CtlName = Screen.ActiveControl.name 'Name of text box right-clicked
I = Right(CtlName, 1) 'Which ID in Groupings

IndividualsID = Me.Child58.Form("ID" & I) 'Focus is on the record where the right-click occurred.
GrpNo = Me.Child58.Form("GrpID")

'Update Appendages first
CurrentDb.Execute "UPDATE APPENDAGES SET GroupID = Null WHERE AppID = " & IndividualsID & " AND RetYear = '" & gblRetreatYear & "';", dbFailOnError

'Now Update Groupings
CurrentDb.Execute "UPDATE GROUPINGS SET ID" & I & " = 0 WHERE GrpID = " & GrpNo & " AND RetYear = '" & gblRetreatYear & "';", dbFailOnError

Me.Child58.Form.Repaint

FName = DLookup("FirstName", "Roster", "RosID = " & IndividualsID)
Status = DLookup("ATTENDING", "APPENDAGES", "AppID = " & IndividualsID & " AND RetYear = " & """" & gblRetreatYear & """")

SelectID = IndividualsID 'Poised to put individual into a different group

If Status Then
If MsgBox("Do you also want " & FName & " marked as not attending?" & vbNewLine & _
"If NO, then you can click another group to MOVE.", vbYesNo, "Removal Disposition") = vbYes Then
CurrentDb.Execute "UPDATE APPENDAGES SET ATTENDING = FALSE WHERE AppID = " & IndividualsID & " AND RetYear = '" & gblRetreatYear & "';", dbFailOnError
SelectID = 0
End If
End If

End Function

[GraeagleBill]

Note that in the code just posted that gblRetreatYear and SelectID are global (Public) variables. These are not germane to our discussion.........just in case you were wondering.

[June7]

You could make a copy of front end and import relevant tables (some dummy records might be helpful) or make copy of backend and fix records there.


Still not understanding why having code behind the main form is an issue if it works when right clicking on subform control.

Seems I had similar discussion with a poster here few months back about right click on subform. Wish I could find it.

[GraeagleBill]

I'll have to think about the effort involved in setting up meaningful tables in the front end.

The issue is the need/wanting to make changes to the current record in the continuous sub-form. I don't even think Access will allow assignments to child text boxes FROM THE PARENT.

[June7]

Don't see why not. Code from anywhere can set value of any data control of the current record of any open form, even if it is a subform (if the control does not have an expression in ControlSource). I just did it with a command in the VBA Immediate Window.

Just don't think I've ever had or encountered requirement for this.

[GraeagleBill]

"if the control does not have an expression in ControlSource" Not sure I know exactly what you mean. I want to change table values that are bound to text box controls in the child. E.g., tbxMyTextBox bound to a table field returned by the form's ControlSource query........Me.tbxMyTextBox = 0 where the table field bound to tbxMyTextBox gets set to zero in the DB. Access complained when I coded things like Me.Child58.Form("ID" & I) = 0 in parent code.

[June7]

If the control is bound to a field or does not have expression, can set its value. If the ControlSource has an expression like =[qty]*[price] then cannot set value with code.

Use Controls collection when constructing field name with variables. Try: Me.Child58.Form.Controls("ID" & I) = 0

[GraeagleBill]

Ah! Now I know what you mean. No, there's no expressions in the query. All query references are table fields. I'll try references via the controls collection to see if Access is happy with that. I'll post back when I can report further.