referencing a field in a sub-form record

[Sprocket]

I have a main form with a sub-form

The main form (frmStudentDetails) is bound to the "Students" table
The subform (subfrmVisits) is bound to the "Visits" table e.g. each student can make many visits.

I also have a button on frmStudentDetails, which opens a linked form (SMSdata) that is used to send SMS text messages. The event on the send button on the SMSdata form sends the text message and returns control to the main form. As it does this it also copies the content of the SMS message into a field (GeneralNotes) on frmStudentDetails. The drawback here is that the message is copied into the "Students" table rather than into the "Visits" table, which is where I want it.

What I need to know is how to reference the "Notes" field in the last record of the "Visits" table for the current student record.

I can pass the focus back to the subform with:

Forms!frmStudentDetails!subfrmVisits.setfocus

but I cannot set the focus into the Notes field because it doesn't know which record on the subform I am trying to refer to. I suspect that I need to have reference to some kind or recordset property here but I'm lost as to how to construct this code or where to place it.

I imagine it will go in the on-click event of the send SMS message button but thats where I get stuck.

this is the code I currently have on the send SMS message button:

Private Sub cmdSendTestText_Click()
Dim strPath As String
Dim strXML As String
Dim myNumber As String
Dim gotit As String
Dim myMessage As String


myNumber = Me.SMS_Number

If IsNull(Me.Text19) Then  ‘ No message so don’t send
MsgBox "Please enter a message and try again"
Exit Sub
End If
myMessage = Me.Text19


strPath = " ***** SORRY THIS LINE IS PRIVATE"
strXML = "<?xml version='1.0' encoding='utf-8'?>" _
                & "<Dmp xmlns='http://localhost/XmlHttp/MessageMTSend.xsd' version='1.0'>" _
                & "<Account name='StudyAssistance' password='*********'/>" _
                & "<MessageMTSend test='false' client_type='SMSPRESS'>" _
                & "<MessageMT type='Text.GSM0338' class='1'>" _
                & "<Originator>Study Assistance</Originator>" _
                & "<Content>" & myMessage & "</Content>" _
                & "<Recipient userRef='1' o=''><Number>" & myNumber & "</Number></Recipient>" _
                & "</MessageMT>" _
                & "</MessageMTSend>" _
                & "</Dmp>"


Debug.Print strXML
WinHTTPReq.Open "POST", strPath, False
WinHTTPReq.SetRequestHeader "Content-Type", "text/xml"
WinHTTPReq.Send strXML
Me.txtStatus3.Value = WinHTTPReq.Status & " " & WinHTTPReq.StatusText
Me.txtResult3.Value = WinHTTPReq.ResponseText

THIS NEXT BIT IS THE CODE THAT WRITES THE MESSAGE BACK INTO THE “STUDENTS” TABLE THAT I WANT TO REPLACE

Forms!special_needs.NOTES.SetFocus
Forms!special_needs.NOTES.Value = Forms!special_needs.NOTES.Value & "( ) SMS message: " & myMessage
Me.BtnExitForm.SetFocus

End Sub

Any help on this greatly appreciated

 

[MarkK]

You can do something like...

'reference the recordset exposed by the subform
With Forms!YourForm.YourSubformControl.Form.Recordset
  'go to the last record
  .MoveLast
  'and add some text
  .Edit
  !Notes = !Notes & vbcrlf & "Some new note"
  .Update
End with

But if the message you want to save is actually associated with a visit, it seems more intuitive to me to have the message creation scheme (Button & Code) occur on the visit form itself, rather than on the parent. This would also simplify the assignment of text to a field, since the focus would definitely be on the appropriate record.

 

[Sprocket]

Thanks Lagbolt - that is exactly what I needed. I take on board your point about putting the message creation scheme on the visit form and now that I am able to write back to the appropriate record I will certainly be moving the button over.

Many Thanks... Sprocket