Referencing a control from a module (1 Viewer)

[Groundrush]

Hi all,

Can anyone help me please?

I'm trying to reference a mobile no from a subform but can't get it to work

Basically a mobile phone no is displayed when a user sends an SMS message which should allow the recipient to reply to it.

I have a table with all the windows login user names along with their mobile no's & it's this information that is referenced when the user sends an SMS message. I have one subform that picks up the windows login name & the other displayes the mobile no linked to the name

I know this method works as I can reference the Username in the SMS message footer

strFooter = Replace("-" & [Forms]![frmSMSMessage]![frmUserNameSubform]![txtUserName], " ", "+") '''''''this works if using a subform instead of a listbox

I've then tried but failed to reference the mobile no

Global Const strClickatell_From = "&from=[Forms]![frmSMSMessage]![frmUserMobileNoSubform]![txtUserMobileNo]"

Here is the rest of the module code that is being used

Option Compare Database
'Used for Text Messaging interface
Global Const strClickatellURL = "http://api.clickatell.com/http/sendmsg?"
Global Const strAPI_ID = "api_id=*****"
Global Const strClickatell_User = "&user=*****"
Global Const strClickatell_Pass = "&password=*****"
'Global Const strClickatell_From = "&from=447712345678" ''''''Works if only using this generic no
'Global Const strClickatell_From = "&from= Forms!frmSMSMessage!lstUserMobileNo.Column(1)" ''''''tried using a refence to a list box
'Global Const strClickatell_From = "&from= & chr(34)& Forms!frmSMSMessage!lstUserMobileNo.Column(1) & chr(34)"'''''' No compile error but does not work
'Global Const strClickatell_From = & chr(34) & Forms!frmSMSMessage!lstUserMobileNo.Column(1) & chr(34)''''''Compile Error
Global Const strClickatell_From = "&from=[Forms]![frmSMSMessage]![frmUserMobileNoSubform]![txtUserMobileNo]" ' tried the same as the method for the footer below but still does not work


Public Sub SendText2(strMessage As String, strNumber As String)
   
	
	Dim strBody As String, strFooter As String
   
  strFooter = Replace("-" & [Forms]![frmSMSMessage]![frmUserNameSubform]![txtUserName], " ", "+") '''''''this works if using a subform instead of a listbox
 
	
	strBody = strClickatellURL & strAPI_ID & strClickatell_User & strClickatell_Pass & strClickatell_From
	
	'Modify the format of the message and check the number is in the right format...
	strMessage = Replace(strMessage, " ", "+")
	strMessage = Replace(strMessage, "&", "and")
	strNumber = Replace(strNumber, " ", "")
	If Left(strNumber, 1) = "0" Then strNumber = "44" & Mid(strNumber, 2, 99)
   
	
	strBody = strBody & "&to=" & strNumber & "&text=" & strMessage & strFooter
	
	DoEvents
	
	If Not OpenURL(strBody) Then
		MsgBox "Sorry, there was a problem when attempting to send the message", vbOKOnly + vbInformation, "Could not connect to internet"
   End If
   'Resume
End Sub

Any help would be greatly appreciated... thanks

 

[JANR]

To refrence a controls value in a subform from the parent form, you must include .Form to the subformconteiner name.

.."&from=[Forms]![frmSMSMessage]![frmUserMobileNoSubform].Form![txtUserMobileNo]

JR

 

[Groundrush]

To refrence a controls value in a subform from the parent form, you must include .Form to the subformconteiner name.

.."&from=[Forms]![frmSMSMessage]![frmUserMobileNoSubform].Form![txtUserMobileNo]

JR

Hi, thanks for your reply

I have tried your suggestion but it still does not work, the message still goes through but the sender mobile no is not included

Global Const strClickatell_From = "&from=[Forms]![frmSMSMessage]![frmUserMobileNoSubform].Form![txtUserMobileNo]"

The footer works with or without adding the the extra Form! but I changed it anyway.

 strFooter = Replace("-" & [Forms]![frmSMSMessage]![frmUserNameSubform].Form![txtUserName], " ", "+") '''''''this works if using a subform instead of a listbox

 

[Galaxiom]

The footer works with or without adding the the extra Form! but I changed it anyway.

Forms!formname!subformcontrolname!fieldname

refers to a field in the recordset of the SourceObject of the subformcontrol.

ie

 Forms!formname.subformcontrolname.Form.Recordset!fieldname

This is why the Form property could be ommitted in your footer textbox control source. Remember though you are referring to a field not a control.

However a reference to a control on a subform must include the Form property of the subformcontol.

 Forms!formname.subformcontrolname.Form.controlname

When you add the Form property you are referring to the control. Most of the time it makes no difference becaause the field and its bound control have the same name when created by the form Wizard. However it makes a huge difference in some circumstances.

 

[Groundrush]

Thanks guys

I was able to get some help & it's now sorted

we simply replaced the strBody code


my code now reads

Option Compare Database
'Used for Text Messaging interface
Global Const strClickatellURL = "http://api.clickatell.com/http/sendmsg?"
Global Const strAPI_ID = "api_id=*****"
Global Const strClickatell_User = "&user=*****"
Global Const strClickatell_Pass = "&password=*****"
'Global Const strClickatell_From = "&from=447712135118" ''''''Works if only using this generic no


Public Sub SendText2(strMessage As String, strNumber As String)
	
	
   Dim strBody As String, strFooter As String
   
  strFooter = Replace("-" & [Forms]![frmSMSMessage]![frmUserNameSubform].Form![txtUserName], " ", "+") '''''''this works if using a subform instead of a listbox
 
	
	'strBody = strClickatellURL & strAPI_ID & strClickatell_User & strClickatell_Pass & strClickatell_From ' original code without the option to include senders Mobile No
	strBody = strClickatellURL & strAPI_ID & strClickatell_User & strClickatell_Pass & _
  "&from=" & [Forms]![frmSMSMessage]![frmUserMobileNoSubform].Form![txtUserMobileNo] ' now includes the senders mobile no

	
	'Modify the format of the message and check the number is in the right format...
	strMessage = Replace(strMessage, " ", "+")
	strMessage = Replace(strMessage, "&", "and")
	strNumber = Replace(strNumber, " ", "")
	If Left(strNumber, 1) = "0" Then strNumber = "44" & Mid(strNumber, 2, 99)
   
	
	strBody = strBody & "&to=" & strNumber & "&text=" & strMessage & strFooter
	
	DoEvents
	
	If Not OpenURL(strBody) Then
		MsgBox "Sorry, there was a problem when attempting to send the message", vbOKOnly + vbInformation, "Could not connect to internet"
   End If
   'Resume
End Sub