Update

[M.Costner]

Hi everyone:

I know this may sound basic, but I need advice from experienced programmers!

I am trying to use the update method to change from false to true a check
box a yes/no field only for the record I'm working with after it sends an
e-mail, but it keeps updating the yes/no fields of all records in the
databse. What am I doing wrong? How do I specify that the update is just
for the record on focus? Help much appreciated! Mark

Here's what I'm using:

Private Sub Command91_Click()
Dim record As String
Dim Update As String
Dim Message As String

record = "SELECT * From Database WHERE Database.sentemail = False"

If Me![sentemail] = False Then

DoCmd.SendObject _
, _
, _
, _
, _
, _
, _
, _
True

Update = "Update Database SET sentemail = True WHERE sentemail = False"
CurrentDb.Execute Update

Else
Message = MsgBox("blablabla")

End If

End Sub

 

[Andrew Backer]

Well... it appears you are updaing all the records, not just one. You
need to get the ID # from the row and just update that one.

dim myId as long
myId = me!IdField
update = " update ... where idfield = myId"

That should be all you need to do. It depends on your circumstances
exactly how you need to get the id, but assuming that the button is on
the same form that contains the id (ie. not on a parent form where you
have it in a subform), something like that should work.

Make sure that the sql you have as a source for you form contains your
table's unique id, then just reference that name.

- Andrew

 

[M.Costner]

Sorry, Andrew, I don't understand what my "ID number" means. The field is a
true/false box called "sentemail". That's my identifier. I will be
updating this field as TRUE (therefore, "sent the e-mail") for each record
in the database as I need to by clicking on a box with a procedure to send
an e-mail. Yes, the button is on the same form and so is the true/false box.

I have an ID (autonumber for each record). Is this what you mean?

Mark

 

[fredg]

Sorry, Andrew, I don't understand what my "ID number" means. The field is a
true/false box called "sentemail". That's my identifier. I will be
updating this field as TRUE (therefore, "sent the e-mail") for each record
in the database as I need to by clicking on a box with a procedure to send
an e-mail. Yes, the button is on the same form and so is the true/false box.

I have an ID (autonumber for each record). Is this what you mean?

Mark

Yes that is what he means.
You update query should read something like this:

Update YourTable Set YourTable.[CheckBoxField] = -1 Where
YourTable.[IDField] = " & Me![IDField] & ";"

Change [YourTable] and [CheckBoxfield] and [IDField] to whatever the
actual table and field names are.

Note regarding your use of "Database" as a table name, and "Update" as
a variable string.

Database and Update are reserved Access/VBA/Jet words and should not
be used as object names.
See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

Change the table name and the variable to something else, i.e. perhaps
tblDatabase and strUpdate

 

[M.Costner]

Hi Fred,

Thanks for your help. I only used "DAtabase" and "Update" to make it clear
to explain what I was trying to do, but I guess I didn't have to. What is
happening now is that the form just closes. It executes the e-mail
procedure, but then it just closes. Here's what I have. Registration is my
form, "ID" is an autonumber field, "sentmail" is my box field. What am I
doing wrong? And please apologize my ignorance.

Private Sub Command91_Click()
Dim myId As Long
Dim Checkbox As String
Dim Message As String

ID = Me![ID]
If Me![sentemail] = False Then

DoCmd.SendObject _
, _
, _
, _
, _
, _
, _
"email message" _
True

Checkbox = "UPDATE Registration SET Registration.[sentemail] = -1 WHERE
Registration.[ID] = myId"
Else
Message = MsgBox("warning message")

End If

End Sub

 

[M.Costner]

I meant "Registration" is my db, not my form.

Mark

 

[Steve Conway]

Hi Fred,

Thanks for your help. I only used "DAtabase" and "Update" to make it
clear
to explain what I was trying to do, but I guess I didn't have to. What is
happening now is that the form just closes. It executes the e-mail
procedure, but then it just closes. Here's what I have. Registration is
my
form, "ID" is an autonumber field, "sentmail" is my box field. What am I
doing wrong? And please apologize my ignorance.

Private Sub Command91_Click()
Dim myId As Long
Dim Checkbox As String
Dim Message As String

ID = Me![ID]
If Me![sentemail] = False Then

DoCmd.SendObject _
, _
, _
, _
, _
, _
, _
"email message" _
True

Checkbox = "UPDATE Registration SET Registration.[sentemail] = -1 WHERE
Registration.[ID] = myId"
Else
Message = MsgBox("warning message")

End If

End Sub
[/QUOTE]

Hi M.Costner

I think this may be your problem:

Dim myId As Long
ID = Me![ID]
WHERE Registration.[ID] = myId

myID is never being set to Me![ID] You will need to change this line:
ID = Me![ID]
to
myID = Me![ID]

I would also change Dim Checkbox and Message to something like Dim
strCheckbox and strMessage

HTH
Steve C