variable to field in table

[Fev]

Hi Experts
I have very little programming knowledge and I would appreciate help
with the following:
I have a form that lists a group of names and cell numbers based on a
combo box selection. I then have code on a button that concatenates
all the cell numbers into a string with each number seperated by a
semicolon. I need to write this string to: table:
tblNewSMS field: receiver
I keep getting Error 424 Object required. My code is as follows:

Private Sub cmdSendSMS_Click()
On Error GoTo ProcError


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strUsers As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT CellNumber FROM tblSMSTemp " _
& "WHERE CellNumber Is Not Null")


With rs
Do Until rs.EOF
strUsers = strUsers & "+27" & Right(rs![CellNumber], 9) & ";"
rs.MoveNext
Loop

Table!tblNewSMS!receiver = strUsers

more code ....
I would appreciate any help
Thanks
Heather

 

[Maarkr]

this may not help you, but why does it need to get written into the table?
typically, the new value is displayed in a calculated field in a report or on
a form, which is easier and more db friendly.

 

[Fev]

this may not help you, but why does it need to get written into the table?  
typically, the new value is displayed in a calculated field in a report or on
a form, which is easier and more db friendly.

Hi Experts
I have very little programming knowledge and I would appreciate help
with the following:
I have a form that lists a group of names and cell numbers based on a
combo box selection.  I then have code on a button that concatenates
all the cell numbers into a string with each number seperated by a
semicolon.  I need to write this string to:          table:
tblNewSMS    field: receiver
I keep getting Error 424 Object required.  My code is as follows:

Private Sub cmdSendSMS_Click()
On Error GoTo ProcError

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strUsers As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT CellNumber FROM tblSMSTemp " _
                        & "WHERE CellNumber Is Not Null")

   With rs
      Do Until rs.EOF
         strUsers = strUsers & "+27" & Right(rs![CellNumber], 9) & ";"
         rs.MoveNext
      Loop

    Table!tblNewSMS!receiver = strUsers

more code ....
I would appreciate any help
Thanks
Heather- Hide quoted text -

- Show quoted text -

Hi
Thanks for your prompt response - the cell numbers will be used in
conjunction with an SMS package to send an SMS to the selected group.
Multiple cell numbers can be placed in the field seperatd by
semicolons, hence the loop.
Thanks
Heather

 

[Danny Lesandrini]

Fev:

Your recordset code looks good. Can you tell us where you get the "Object Required"
error happens ... on which line?

Also, what does this do ...

Table!tblNewSMS!receiver = strUsers

Perhaps this is where the error is happening. I'm not sure what the Table object is in
this call. Maybe it's just a syntax I'm not accustomed to using, but you can't set a
table's field value with a line of code like this.

 

[Fev]

Fev:

Your recordset code looks good.  Can you tell us where you get the "Object Required"
error happens ... on which line?

Also, what does this do ...

    Table!tblNewSMS!receiver = strUsers

Perhaps this is where the error is happening.  I'm not sure what the Table object is in
this call.  Maybe it's just a syntax I'm not accustomed to using, but you can't set a
table's field value with a line of code like this.
--
Danny J Lesandrini
(e-mail address removed)

Hi Experts
I have very little programming knowledge and I would appreciate help
with the following:
I have a form that lists a group of names and cell numbers based on a
combo box selection.  I then have code on a button that concatenates
all the cell numbers into a string with each number seperated by a
semicolon.  I need to write this string to:          table:
tblNewSMS    field: receiver
I keep getting Error 424 Object required.  My code is as follows:

Private Sub cmdSendSMS_Click()
On Error GoTo ProcError

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strUsers As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT CellNumber FROM tblSMSTemp " _
                       & "WHERE CellNumber Is Not Null")

  With rs
     Do Until rs.EOF
        strUsers = strUsers & "+27" & Right(rs![CellNumber], 9) & ";"
        rs.MoveNext
     Loop

   Table!tblNewSMS!receiver = strUsers

more code ....
I would appreciate any help
Thanks
Heather- Hide quoted text -

- Show quoted text -

Hi
The Object required error appears for the code:
Table!tblNewSMS!receiver = strUsers

This is the table and field that should contain the cell phone number
string - str Users. The table will also contain the text of the SMS
message, who is sending the message etc, but I have no problem with
populating those fields as they are bound fields in the form.
Thanks
Heather

 

[Danny Lesandrini]

Fev, I don't know where you got that syntax, but I've never see such a thing.

If you want to read the field using a single line of code, use DLookup()
strUsers = DLookup("receiver","tblNewSMS","[PersonID]=2345")

If you want to write a value, you'll need to do something else. Either update
the value in the recordset, or perform an UPDATE SQL execution.

CurrentDB.Execute "UPDATE tblNewSMS SET [receiver]='" & strUsers & "' WHERE [PersonID]=2345"

If you have that in your recordset, it becomes something like this ...

rst.Edit
rst!receiver = strUsers
rst.Update

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

Fev:

Your recordset code looks good. Can you tell us where you get the "Object Required"
error happens ... on which line?

Also, what does this do ...

Table!tblNewSMS!receiver = strUsers

Perhaps this is where the error is happening. I'm not sure what the Table object is in
this call. Maybe it's just a syntax I'm not accustomed to using, but you can't set a
table's field value with a line of code like this.

Hi
The Object required error appears for the code:
Table!tblNewSMS!receiver = strUsers

This is the table and field that should contain the cell phone number
string - str Users. The table will also contain the text of the SMS
message, who is sending the message etc, but I have no problem with
populating those fields as they are bound fields in the form.
Thanks
Heather

 

[Fev]

Fev, I don't know where you got that syntax, but I've never see such a thing.

If you want to read the field using a single line of code, use DLookup()
   strUsers = DLookup("receiver","tblNewSMS","[PersonID]=2345")

If you want to write a value, you'll need to do something else.  Eitherupdate
the value in the recordset, or perform an UPDATE SQL execution.

   CurrentDB.Execute "UPDATE tblNewSMS SET [receiver]='" & strUsers& "' WHERE [PersonID]=2345"

If you have that in your recordset, it becomes something like this ...

   rst.Edit
   rst!receiver = strUsers
   rst.Update

--
Danny J Lesandrini
(e-mail address removed)

Fev:

Your recordset code looks good. Can you tell us where you get the "Object Required"
error happens ... on which line?

Also, what does this do ...

Table!tblNewSMS!receiver = strUsers

Perhaps this is where the error is happening. I'm not sure what the Table object is in
this call. Maybe it's just a syntax I'm not accustomed to using, but you can't set a
table's field value with a line of code like this.

Hi
The Object required error appears for the code:
Table!tblNewSMS!receiver = strUsers

This is the table and field that should contain the cell phone number
string - str Users.  The table will also contain the text of the SMS
message, who is sending the message etc, but I have no problem with
populating those fields  as they are bound fields in the form.
Thanks
Heather

Thanks soooo much Danny - it work perfectly.