Auto Populate a textbox on an Access form with PhoneContacts from a table field whenever checkboxes are selected (1 Viewer)

emma debs

New member
Local time
Today, 06:13
Joined
Apr 4, 2020
Messages
12
I am trying to build an MS Access database that can be used to send bulksms.
The problem am facing has to do with how the recipient textbox (ttcontact) on a form can be populated with phone numbers (separated with commas) when they are checked in checkboxes.
I researched a little and was able to come up with the vba codes below but they only display the latest contact checked in the checkbox

Code:
Private Sub Select_AfterUpdate()
If Me.Select.Value = True Then
Me.ttContact.Value = Me.MobilePhone.Value
' etc.
Else: Me.ttContact.Value = Null
End If
End Sub

Attached is a picture of how the form looks like.

Please kindly help.
 

Attachments

  • form picture.jpg
    form picture.jpg
    59 KB · Views: 90

Gasman

Enthusiastic Amateur
Local time
Today, 13:13
Joined
Sep 21, 2011
Messages
14,052
I would have thought you would need to move through the recordset for the contacts and for very record that has a checkbox set, append contact number and "," to the relevant control/variable, and remove the last comma.?

Is there a limit to how many can be sent at once.? Would you need to keep track of those sent if that is the case, batches of 50 perhaps.?
 

emma debs

New member
Local time
Today, 06:13
Joined
Apr 4, 2020
Messages
12
I would have thought you would need to move through the recordset for the contacts and for very record that has a checkbox set, append contact number and "," to the relevant control/variable, and remove the last comma.?

Is there a limit to how many can be sent at once.? Would you need to keep track of those sent if that is the case, batches of 50 perhaps.?
Am not good in access vba. Can you please translate your comment into vba codes for me.
There are no limit to how many contacts can be send at once.
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:13
Joined
Sep 21, 2011
Messages
14,052
Something alomg the lines of

Code:
Dim rst As DAO.Recordset
Dim strSMS As String

Set rst = Me.RecordsetClone
If rst.RecordCount = 0 Then Exit Sub

rst.MoveFirst
Do Until rst.EOF
    If rst!Select and rst!Staus = "ACTIVE" Then
        strSMS = strSMS & rst!MobilePhone & ","
    End If
    rst.MoveNext
Loop
strSMS = Left(strSMS, Len(strSMS) - 1)

Set rst = Nothing

my strSMS is what you would assign to your SMS control.

HTH
 

emma debs

New member
Local time
Today, 06:13
Joined
Apr 4, 2020
Messages
12
Something alomg the lines of

Code:
Dim rst As DAO.Recordset
Dim strSMS As String

Set rst = Me.RecordsetClone
If rst.RecordCount = 0 Then Exit Sub

rst.MoveFirst
Do Until rst.EOF
    If rst!Select and rst!Staus = "ACTIVE" Then
        strSMS = strSMS & rst!MobilePhone & ","
    End If
    rst.MoveNext
Loop
strSMS = Left(strSMS, Len(strSMS) - 1)

Set rst = Nothing

my strSMS is what you would assign to your SMS control.

HTH
thanks, will try and give you feedback
 

emma debs

New member
Local time
Today, 06:13
Joined
Apr 4, 2020
Messages
12
thanks, will try and give you feedback
Something alomg the lines of

Code:
Dim rst As DAO.Recordset
Dim strSMS As String

Set rst = Me.RecordsetClone
If rst.RecordCount = 0 Then Exit Sub

rst.MoveFirst
Do Until rst.EOF
    If rst!Select and rst!Staus = "ACTIVE" Then
        strSMS = strSMS & rst!MobilePhone & ","
    End If
    rst.MoveNext
Loop
strSMS = Left(strSMS, Len(strSMS) - 1)

Set rst = Nothing

my strSMS is what you would assign to your SMS control.

HTH
Please where in the code should i place my ttcontact control to display the selected contacts
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:13
Joined
Sep 21, 2011
Messages
14,052
Not sure I understand you.?

I would have a button (call it cmdPopulate) that would run that code in it's click event.
As I mentioned previously, the resulting string is in strSMS, so if your control for the contacts is ttcontact then you would use

Code:
Me.ttcontact = strSMS
Set rst = Nothing
 

emma debs

New member
Local time
Today, 06:13
Joined
Apr 4, 2020
Messages
12
Not sure I understand you.?

I would have a button (call it cmdPopulate) that would run that code in it's click event.
As I mentioned previously, the resulting string is in strSMS, so if your control for the contacts is ttcontact then you would use

Code:
Me.ttcontact = strSMS
Set rst = Nothing
Its working.
But the displayed contacts always ends with comma (,)
Also, if no contacts are selected and you run it, it returns run-time error '5'
How do i correct these two problems
 

isladogs

MVP / VIP
Local time
Today, 13:13
Joined
Jan 14, 2017
Messages
18,186
Update the string to remove the final comma using e.g,
Code:
strSMS=Left(strSMS,Len(strSMS)-1)

Use error handling to manage error 5 or use the Nz function to handle null values for contacts field
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:13
Joined
Sep 21, 2011
Messages
14,052
Have you included this line?

Code:
strSMS = Left(strSMS, Len(strSMS) - 1)
That should remove the last comma

As for runtime error 5 (why do people think all the error codes are known off by heart? :( ) I am presuming it is on that line?

If so, change to
Code:
If Len(strSMS) > 0 then
    strSMS = Left(strSMS, Len(strSMS) - 1)
End if
 

emma debs

New member
Local time
Today, 06:13
Joined
Apr 4, 2020
Messages
12
Thanks to you all especially Gasman and isladogs. Everything is working well
 

emma debs

New member
Local time
Today, 06:13
Joined
Apr 4, 2020
Messages
12
Hello, am back again with another follow up question. Sending the same message to different contacts has been achieved. The new problem is, how can i personalized the message to send to their respective recipients.
For instance, in sending the message, i would like the vba code to auto insert the individual members' names and their amounts into the message body and send to their corresponding numbers.
Something like this; (Dear [NameField], your [AmountField] has been received. Thank you.)
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:13
Joined
Sep 21, 2011
Messages
14,052
You would need to construct and send the message within the loop.

You would also need to obtain the extra data that you want to use. If that is not in the form recordset already, then you would need to construct a query with all that data for required contacts and use that as your recordset.?
 

emma debs

New member
Local time
Today, 06:13
Joined
Apr 4, 2020
Messages
12
You would need to construct and send the message within the loop.

You would also need to obtain the extra data that you want to use. If that is not in the form recordset already, then you would need to construct a query with all that data for required contacts and use that as your recordset.?
Thanks for your prompt response. The recordset is similar to this very one in the vba code you sent. Can you once again help on how i can construct and send the message within the lop using vba codes since am not good in that. Thanks in advance
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:13
Joined
Sep 21, 2011
Messages
14,052
No sorry.
Without seeing your DB or code, I'd just be guessing.
Also you cannot expect members to just write all your code for you. I can help where I can, but it would take me a fair time to do so, even if I had your DB.?

To join variables to static strings you would do something like

Code:
strSMSMessage = "Dear " & rs!FirstName & ", your " & Format(rs!Amount,"Currency") & " has been received. Thank you."

Then send strSMSMessage in your loop.
 

Users who are viewing this thread

Top Bottom