Updating a table

[Guest]

Hi, there. I am trying to combine a bit of code and it seems that it works
sometimes (it updates the table) and sometimes it doesn't.

With help from the group, I've created a form with a command button. The
command button runs a report and updates the table, in theory. I've tested
it, it worked, then I deleted the data from the table and tried again, but it
didn't update the 2nd time. I'm horrible at coding and appreciate your
suggestions.

Private Sub PrintLetter_Click()
"Sends all donors that have not yet been sent letters or
'where the DateSent field is before Oct.1st of that current year.
'Letters marked before that will be sent. Letters after that date will not.

On Error GoTo Err_PrintLetter_Click

DoCmd.OpenReport "Solicitation Letter", acViewNormal, , "[DateSent] Is Null
Or [DateSent] < DateSerial(Year(Date()),10,1)"
Dim strSQL As String
strSQL = "Update tblAuctionDonors Set tblAuctionDonors.DateSent= #" & Date &
"# Where tblAuctionDonors.[AuctionDonorID] = " & [AuctionDonorID] & ";"
Exit_PrintLetter_Click:
Exit Sub

Err_PrintLetter_Click:
MsgBox Err.Description
Resume Exit_PrintLetter_Click

End Sub

 

[Graham Mandeno]

Hi Stephanie

First, you are building a SQL string, but not using it. You need to add the
following line:

CurrentDb.Execute strSQL, dbFailOnError

Second, you can use the Date() function in SQL, which avoids possible issues
with date formatting in VBA:

strSQL = "Update tblAuctionDonors Set tblAuctionDonors.DateSent=Date()
where...

Third, I'm not sure if it's what you intend, but you are using a different
WHERE clause for printing the report from the one you are using to mark the
letters as sent.

 

[Guest]

Graham,
Thanks for the reply- I appreciate it! I think I've fixed the first two
issues, but have a question on the third (using two different WHERE clauses
to print and update). Here's what I have now:

Private Sub PrintLetter_Click()
'Sends all donors that have not yet been sent letters or
'where the DateSent field is before Oct.1st of that current year.
'Letters marked before that will be sent. Letters after that date will not.

On Error GoTo Err_PrintLetter_Click

On Error GoTo Err_PrintLetter_Click

DoCmd.OpenReport "Solicitation Letter", acViewNormal, , "[DateSent] Is Null
Or [DateSent] < DateSerial(Year(Date()),10,1)"
Dim strSQL As String
strSQL = "Update tblAuctionDonors Set tblAuctionDonors.DateSent= Date()
Where tblAuctionDonors.[AuctionDonorID] = " & [AuctionDonorID] & ";"

CurrentDb.Execute strSQL, dbFailOnError

Exit_PrintLetter_Click:
Exit Sub

Err_PrintLetter_Click:
MsgBox Err.Description
Resume Exit_PrintLetter_Click

End Sub
(good so far?)

On the third issue, I am using a different WHERE clause for printing the
report than the one I'm using to mark the letters as sent. I'm actually
having two issues with the report/code. 1- The report is printing but
doesn't have the updated DateSent (until I reprint it, so it's not getting
the update and then printing). 2- The code is not updating all of the records
as it should (records 1 and 3 should both have a DateSent, but only record 1
got updated). Here's the report code:

Dim intPreview As Integer
'---------------------
Private Sub GroupFooter3_Format(Cancel As Integer, FormatCount As Integer)
If intPreview = 0 Then
Dim strSQL As String
strSQL = "Update tblAuctionDonors Set tblAuctionDonors.DateSent= #" & Date &
"# Where tblAuctionDonors.[AuctionDonorID] = " & [AuctionDonorID] & ";"

CurrentDb.Execute strSQL, dbFailOnError
End If

End Sub
'------------------------------
Private Sub Report_Activate()
intPreview = -1
End Sub
'--------------------

As bad as I am a coding, I'm even worse at report coding. I'd appreciate
any suggestions.

--
Thanks for the help!

Hi Stephanie

First, you are building a SQL string, but not using it. You need to add the
following line:

CurrentDb.Execute strSQL, dbFailOnError

Second, you can use the Date() function in SQL, which avoids possible issues
with date formatting in VBA:

strSQL = "Update tblAuctionDonors Set tblAuctionDonors.DateSent=Date()
where...

Third, I'm not sure if it's what you intend, but you are using a different
WHERE clause for printing the report from the one you are using to mark the
letters as sent.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi, there. I am trying to combine a bit of code and it seems that it
works
sometimes (it updates the table) and sometimes it doesn't.

With help from the group, I've created a form with a command button. The
command button runs a report and updates the table, in theory. I've
tested
it, it worked, then I deleted the data from the table and tried again, but
it
didn't update the 2nd time. I'm horrible at coding and appreciate your
suggestions.

Private Sub PrintLetter_Click()
"Sends all donors that have not yet been sent letters or
'where the DateSent field is before Oct.1st of that current year.
'Letters marked before that will be sent. Letters after that date will
not.

On Error GoTo Err_PrintLetter_Click

DoCmd.OpenReport "Solicitation Letter", acViewNormal, , "[DateSent] Is
Null
Or [DateSent] < DateSerial(Year(Date()),10,1)"
Dim strSQL As String
strSQL = "Update tblAuctionDonors Set tblAuctionDonors.DateSent= #" & Date
&
"# Where tblAuctionDonors.[AuctionDonorID] = " & [AuctionDonorID] & ";"
Exit_PrintLetter_Click:
Exit Sub

Err_PrintLetter_Click:
MsgBox Err.Description
Resume Exit_PrintLetter_Click

End Sub

 

[Guest]

Graham,
I think I'm doing better now. I think I've combined the table update with
the report printing. My report now runs with the DateSent already updated.
However, only the first record in the table is getting updated with DateSent.
I can't figure out why all records that match the criteria are not getting
updated with DateSent. I'd appreciate any suggestions.

Private Sub PrintLetter_Click()
'Send to all donors that have not yet been sent letters or
'where the DateSent field is before Jan,1st of that current year.
'Letters marked before that will be sent. Letters after that date will not.

On Error GoTo Err_PrintLetter_Click

Dim strSQL As String

strSQL = "Update tblAuctionDonors Set tblAuctionDonors.DateSent= Date()
Where tblAuctionDonors.[AuctionDonorID] = " & [AuctionDonorID] & ";"

CurrentDb.Execute strSQL, dbFailOnError

DoCmd.OpenReport "Solicitation Letter", acViewNormal, , "[DateSent] Is Null
Or [DateSent] < DateSerial(Year(Date()),1,1)"

Dim strSQL1 As String
strSQL1 = "Update tblAuctionDonors Set tblAuctionDonors.DateSent= #" & Date
& "# Where tblAuctionDonors.[AuctionDonorID] = " & [AuctionDonorID] & ";"

CurrentDb.Execute strSQL1, dbFailOnError

Exit_PrintLetter_Click:
Exit Sub

Err_PrintLetter_Click:
MsgBox Err.Description
Resume Exit_PrintLetter_Click

End Sub

--
Thanks for the help!

Hi Stephanie

First, you are building a SQL string, but not using it. You need to add the
following line:

CurrentDb.Execute strSQL, dbFailOnError

Second, you can use the Date() function in SQL, which avoids possible issues
with date formatting in VBA:

strSQL = "Update tblAuctionDonors Set tblAuctionDonors.DateSent=Date()
where...

Third, I'm not sure if it's what you intend, but you are using a different
WHERE clause for printing the report from the one you are using to mark the
letters as sent.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi, there. I am trying to combine a bit of code and it seems that it
works
sometimes (it updates the table) and sometimes it doesn't.

With help from the group, I've created a form with a command button. The
command button runs a report and updates the table, in theory. I've
tested
it, it worked, then I deleted the data from the table and tried again, but
it
didn't update the 2nd time. I'm horrible at coding and appreciate your
suggestions.

Private Sub PrintLetter_Click()
"Sends all donors that have not yet been sent letters or
'where the DateSent field is before Oct.1st of that current year.
'Letters marked before that will be sent. Letters after that date will
not.

On Error GoTo Err_PrintLetter_Click

DoCmd.OpenReport "Solicitation Letter", acViewNormal, , "[DateSent] Is
Null
Or [DateSent] < DateSerial(Year(Date()),10,1)"
Dim strSQL As String
strSQL = "Update tblAuctionDonors Set tblAuctionDonors.DateSent= #" & Date
&
"# Where tblAuctionDonors.[AuctionDonorID] = " & [AuctionDonorID] & ";"
Exit_PrintLetter_Click:
Exit Sub

Err_PrintLetter_Click:
MsgBox Err.Description
Resume Exit_PrintLetter_Click

End Sub

 

[Graham Mandeno]

Hi Stephanie

Sorry I haven't replied sooner - just got home from opening night of the
opera (performing!) Don't worry - I'm going to stick to my day job <g>

The problem is that your WHERE clauses are different. You are printing
reports for one bunch of records and updating the DateSent for another
bunch.

Specifically, you are printing the reports where...
"[DateSent] Is Null Or [DateSent] < DateSerial(Year(Date()),1,1)"

and you are updating DateSent where...
"tblAuctionDonors.[AuctionDonorID] = " & [AuctionDonorID]

I think you should do the following:

1. Set a variable with your WHERE condition:
Dim strWhere as String
strWhere = "[DateSent] Is Null Or [DateSent] <
DateSerial(Year(Date()),1,1)"

2. Print the reports using this WHERE condition:
DoCmd.OpenReport "Solicitation Letter", acViewNormal, , strWhere

3. Use the same WHERE condition to update the date sent:
strSQL = "Update tblAuctionDonors Set DateSent=Date() where " _
& strWhere

4. Ask the user to confirm the update of DateSent:
If MsgBox( "Do you want to record these letters as sent?", _
mbYesNo) = mbYes Then
CurrentDb.Execute strSQL, dbFailOnError

To get around the problem of the incorrect date being printed on the
reports, you should change the ControlSource of the textbox on the report
which displays the date from DateSent to =Date().
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
I think I'm doing better now. I think I've combined the table update with
the report printing. My report now runs with the DateSent already
updated.
However, only the first record in the table is getting updated with
DateSent.
I can't figure out why all records that match the criteria are not getting
updated with DateSent. I'd appreciate any suggestions.

Private Sub PrintLetter_Click()
'Send to all donors that have not yet been sent letters or
'where the DateSent field is before Jan,1st of that current year.
'Letters marked before that will be sent. Letters after that date will
not.

On Error GoTo Err_PrintLetter_Click

Dim strSQL As String

strSQL = "Update tblAuctionDonors Set tblAuctionDonors.DateSent= Date()
Where tblAuctionDonors.[AuctionDonorID] = " & [AuctionDonorID] & ";"

CurrentDb.Execute strSQL, dbFailOnError

DoCmd.OpenReport "Solicitation Letter", acViewNormal, , "[DateSent] Is
Null
Or [DateSent] < DateSerial(Year(Date()),1,1)"

Dim strSQL1 As String
strSQL1 = "Update tblAuctionDonors Set tblAuctionDonors.DateSent= #" &
Date
& "# Where tblAuctionDonors.[AuctionDonorID] = " & [AuctionDonorID] & ";"

CurrentDb.Execute strSQL1, dbFailOnError

Exit_PrintLetter_Click:
Exit Sub

Err_PrintLetter_Click:
MsgBox Err.Description
Resume Exit_PrintLetter_Click

End Sub

--
Thanks for the help!

Hi Stephanie

First, you are building a SQL string, but not using it. You need to add
the
following line:

CurrentDb.Execute strSQL, dbFailOnError

Second, you can use the Date() function in SQL, which avoids possible
issues
with date formatting in VBA:

strSQL = "Update tblAuctionDonors Set
tblAuctionDonors.DateSent=Date()
where...

Third, I'm not sure if it's what you intend, but you are using a
different
WHERE clause for printing the report from the one you are using to mark
the
letters as sent.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi, there. I am trying to combine a bit of code and it seems that it
works
sometimes (it updates the table) and sometimes it doesn't.

With help from the group, I've created a form with a command button.
The
command button runs a report and updates the table, in theory. I've
tested
it, it worked, then I deleted the data from the table and tried again,
but
it
didn't update the 2nd time. I'm horrible at coding and appreciate your
suggestions.

Private Sub PrintLetter_Click()
"Sends all donors that have not yet been sent letters or
'where the DateSent field is before Oct.1st of that current year.
'Letters marked before that will be sent. Letters after that date will
not.

On Error GoTo Err_PrintLetter_Click

DoCmd.OpenReport "Solicitation Letter", acViewNormal, , "[DateSent] Is
Null
Or [DateSent] < DateSerial(Year(Date()),10,1)"
Dim strSQL As String
strSQL = "Update tblAuctionDonors Set tblAuctionDonors.DateSent= #" &
Date
&
"# Where tblAuctionDonors.[AuctionDonorID] = " & [AuctionDonorID] & ";"
Exit_PrintLetter_Click:
Exit Sub

Err_PrintLetter_Click:
MsgBox Err.Description
Resume Exit_PrintLetter_Click

End Sub

 

[Guest]

Graham- you Rock! Or apparently Opera! Bravissimo!
I appreciate your detailed step-through instructions. Working through them
is a good learning experience.

Here's my results: On my form, a message box poped up and I selected "OK"
(to update the table). I changed the date field on the report and it printed
just right- thanks! However, the table did not update with the DateSent.

So 2 questions: 1) why isn't the table updating? 2) what if the user
decides at the last minute that they don't want to update the table?- they
don't have the choice to say "no"... (I added an End If to the message box
code- maybe I messed something up?!)
And hey, if I can be picky I'd like to allow the user to enter the
DateSerial(Year(Date()),1,1) input (maybe through a "message box") so that
it's not hard-coded. Possible?
Here's what it looks like now:

Private Sub PrintLetter_Click()
'Send to all donors that have not yet been sent letters or
'where the DateSent field is before Jan,1st of that current year.
'Letters marked before that will be sent. Letters after that date will not.

On Error GoTo Err_PrintLetter_Click

Dim strWhere As String
strWhere = "[DateSent] Is Null Or [DateSent] <
DateSerial(Year(Date()),1,1)"

DoCmd.OpenReport "Solicitation Letter", acViewNormal, , strWhere
strSQL = "Update tblAuctionDonors Set DateSent=Date() where " & strWhere

If MsgBox("Do you want to record these letters as sent?", mbYesNo) =
mbYes Then
CurrentDb.Execute strSQL, dbFailOnError
End If
'NOTE: I added the End If since I was getting a compling error- hope I
that's OK

Exit_PrintLetter_Click:
Exit Sub

Err_PrintLetter_Click:
MsgBox Err.Description
Resume Exit_PrintLetter_Click

End Sub

--
Thanks for the help and for sharing your talents!

Hi Stephanie

Sorry I haven't replied sooner - just got home from opening night of the
opera (performing!) Don't worry - I'm going to stick to my day job <g>

The problem is that your WHERE clauses are different. You are printing
reports for one bunch of records and updating the DateSent for another
bunch.

Specifically, you are printing the reports where...
"[DateSent] Is Null Or [DateSent] < DateSerial(Year(Date()),1,1)"

and you are updating DateSent where...
"tblAuctionDonors.[AuctionDonorID] = " & [AuctionDonorID]

I think you should do the following:

1. Set a variable with your WHERE condition:
Dim strWhere as String
strWhere = "[DateSent] Is Null Or [DateSent] <
DateSerial(Year(Date()),1,1)"

2. Print the reports using this WHERE condition:
DoCmd.OpenReport "Solicitation Letter", acViewNormal, , strWhere

3. Use the same WHERE condition to update the date sent:
strSQL = "Update tblAuctionDonors Set DateSent=Date() where " _
& strWhere

4. Ask the user to confirm the update of DateSent:
If MsgBox( "Do you want to record these letters as sent?", _
mbYesNo) = mbYes Then
CurrentDb.Execute strSQL, dbFailOnError

To get around the problem of the incorrect date being printed on the
reports, you should change the ControlSource of the textbox on the report
which displays the date from DateSent to =Date().
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Guest]

Graham,
I figured it out (but only because you did most of the work)! I did post
for help on my strSQL statement which was fine. I thought I might be having
problems with the msgbox and sure enough! So I changed it to:

If MsgBox("Record update: Letters as sent, OK?", vbQuestion + vbYesNo,
"Letters sent") = vbYes Then
DoCmd.OpenReport "Solicitation Letter", acViewNormal, , strWhere
CurrentDb.Execute strSQL, dbFailOnError
End If

which gives me the yes/no capabilities I wanted, doesn't print the report
until the reply to the msgbox is "yes" and actually updates the table.
Yippee!

I appreciate your help and multiple responses.

Hi Stephanie

Sorry I haven't replied sooner - just got home from opening night of the
opera (performing!) Don't worry - I'm going to stick to my day job <g>

The problem is that your WHERE clauses are different. You are printing
reports for one bunch of records and updating the DateSent for another
bunch.

Specifically, you are printing the reports where...
"[DateSent] Is Null Or [DateSent] < DateSerial(Year(Date()),1,1)"

and you are updating DateSent where...
"tblAuctionDonors.[AuctionDonorID] = " & [AuctionDonorID]

I think you should do the following:

1. Set a variable with your WHERE condition:
Dim strWhere as String
strWhere = "[DateSent] Is Null Or [DateSent] <
DateSerial(Year(Date()),1,1)"

2. Print the reports using this WHERE condition:
DoCmd.OpenReport "Solicitation Letter", acViewNormal, , strWhere

3. Use the same WHERE condition to update the date sent:
strSQL = "Update tblAuctionDonors Set DateSent=Date() where " _
& strWhere

4. Ask the user to confirm the update of DateSent:
If MsgBox( "Do you want to record these letters as sent?", _
mbYesNo) = mbYes Then
CurrentDb.Execute strSQL, dbFailOnError

To get around the problem of the incorrect date being printed on the
reports, you should change the ControlSource of the textbox on the report
which displays the date from DateSent to =Date().
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
I think I'm doing better now. I think I've combined the table update with
the report printing. My report now runs with the DateSent already
updated.
However, only the first record in the table is getting updated with
DateSent.
I can't figure out why all records that match the criteria are not getting
updated with DateSent. I'd appreciate any suggestions.

Private Sub PrintLetter_Click()
'Send to all donors that have not yet been sent letters or
'where the DateSent field is before Jan,1st of that current year.
'Letters marked before that will be sent. Letters after that date will
not.

On Error GoTo Err_PrintLetter_Click

Dim strSQL As String

strSQL = "Update tblAuctionDonors Set tblAuctionDonors.DateSent= Date()
Where tblAuctionDonors.[AuctionDonorID] = " & [AuctionDonorID] & ";"

CurrentDb.Execute strSQL, dbFailOnError

DoCmd.OpenReport "Solicitation Letter", acViewNormal, , "[DateSent] Is
Null
Or [DateSent] < DateSerial(Year(Date()),1,1)"

Dim strSQL1 As String
strSQL1 = "Update tblAuctionDonors Set tblAuctionDonors.DateSent= #" &
Date
& "# Where tblAuctionDonors.[AuctionDonorID] = " & [AuctionDonorID] & ";"

CurrentDb.Execute strSQL1, dbFailOnError

Exit_PrintLetter_Click:
Exit Sub

Err_PrintLetter_Click:
MsgBox Err.Description
Resume Exit_PrintLetter_Click

End Sub

--
Thanks for the help!

Hi Stephanie

First, you are building a SQL string, but not using it. You need to add
the
following line:

CurrentDb.Execute strSQL, dbFailOnError

Second, you can use the Date() function in SQL, which avoids possible
issues
with date formatting in VBA:

strSQL = "Update tblAuctionDonors Set
tblAuctionDonors.DateSent=Date()
where...

Third, I'm not sure if it's what you intend, but you are using a
different
WHERE clause for printing the report from the one you are using to mark
the
letters as sent.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi, there. I am trying to combine a bit of code and it seems that it
works
sometimes (it updates the table) and sometimes it doesn't.

With help from the group, I've created a form with a command button.
The
command button runs a report and updates the table, in theory. I've
tested
it, it worked, then I deleted the data from the table and tried again,
but
it
didn't update the 2nd time. I'm horrible at coding and appreciate your
suggestions.

Private Sub PrintLetter_Click()
"Sends all donors that have not yet been sent letters or
'where the DateSent field is before Oct.1st of that current year.
'Letters marked before that will be sent. Letters after that date will
not.

On Error GoTo Err_PrintLetter_Click

DoCmd.OpenReport "Solicitation Letter", acViewNormal, , "[DateSent] Is
Null
Or [DateSent] < DateSerial(Year(Date()),10,1)"
Dim strSQL As String
strSQL = "Update tblAuctionDonors Set tblAuctionDonors.DateSent= #" &
Date
&
"# Where tblAuctionDonors.[AuctionDonorID] = " & [AuctionDonorID] & ";"
Exit_PrintLetter_Click:
Exit Sub

Err_PrintLetter_Click:
MsgBox Err.Description
Resume Exit_PrintLetter_Click

End Sub

 

[Graham Mandeno]

Hi Stephanie

I'm so pleased you got it all working. Thanks for the great feedback!

I think, though, that I would print the report before the "If MsgBox" part.
That way, you are asking the user to confirm that the letters were actually
printed OK before you update the table.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
I figured it out (but only because you did most of the work)! I did post
for help on my strSQL statement which was fine. I thought I might be
having
problems with the msgbox and sure enough! So I changed it to:

If MsgBox("Record update: Letters as sent, OK?", vbQuestion + vbYesNo,
"Letters sent") = vbYes Then
DoCmd.OpenReport "Solicitation Letter", acViewNormal, , strWhere
CurrentDb.Execute strSQL, dbFailOnError
End If

which gives me the yes/no capabilities I wanted, doesn't print the report
until the reply to the msgbox is "yes" and actually updates the table.
Yippee!

I appreciate your help and multiple responses.

Hi Stephanie

Sorry I haven't replied sooner - just got home from opening night of the
opera (performing!) Don't worry - I'm going to stick to my day job <g>

The problem is that your WHERE clauses are different. You are printing
reports for one bunch of records and updating the DateSent for another
bunch.

Specifically, you are printing the reports where...
"[DateSent] Is Null Or [DateSent] < DateSerial(Year(Date()),1,1)"

and you are updating DateSent where...
"tblAuctionDonors.[AuctionDonorID] = " & [AuctionDonorID]

I think you should do the following:

1. Set a variable with your WHERE condition:
Dim strWhere as String
strWhere = "[DateSent] Is Null Or [DateSent] <
DateSerial(Year(Date()),1,1)"

2. Print the reports using this WHERE condition:
DoCmd.OpenReport "Solicitation Letter", acViewNormal, , strWhere

3. Use the same WHERE condition to update the date sent:
strSQL = "Update tblAuctionDonors Set DateSent=Date() where " _
& strWhere

4. Ask the user to confirm the update of DateSent:
If MsgBox( "Do you want to record these letters as sent?", _
mbYesNo) = mbYes Then
CurrentDb.Execute strSQL, dbFailOnError

To get around the problem of the incorrect date being printed on the
reports, you should change the ControlSource of the textbox on the report
which displays the date from DateSent to =Date().
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
I think I'm doing better now. I think I've combined the table update
with
the report printing. My report now runs with the DateSent already
updated.
However, only the first record in the table is getting updated with
DateSent.
I can't figure out why all records that match the criteria are not
getting
updated with DateSent. I'd appreciate any suggestions.

Private Sub PrintLetter_Click()
'Send to all donors that have not yet been sent letters or
'where the DateSent field is before Jan,1st of that current year.
'Letters marked before that will be sent. Letters after that date will
not.

On Error GoTo Err_PrintLetter_Click

Dim strSQL As String

strSQL = "Update tblAuctionDonors Set tblAuctionDonors.DateSent= Date()
Where tblAuctionDonors.[AuctionDonorID] = " & [AuctionDonorID] & ";"

CurrentDb.Execute strSQL, dbFailOnError

DoCmd.OpenReport "Solicitation Letter", acViewNormal, , "[DateSent] Is
Null
Or [DateSent] < DateSerial(Year(Date()),1,1)"

Dim strSQL1 As String
strSQL1 = "Update tblAuctionDonors Set tblAuctionDonors.DateSent= #" &
Date
& "# Where tblAuctionDonors.[AuctionDonorID] = " & [AuctionDonorID] &
";"

CurrentDb.Execute strSQL1, dbFailOnError

Exit_PrintLetter_Click:
Exit Sub

Err_PrintLetter_Click:
MsgBox Err.Description
Resume Exit_PrintLetter_Click

End Sub

--
Thanks for the help!



:

Hi Stephanie

First, you are building a SQL string, but not using it. You need to
add
the
following line:

CurrentDb.Execute strSQL, dbFailOnError

Second, you can use the Date() function in SQL, which avoids possible
issues
with date formatting in VBA:

strSQL = "Update tblAuctionDonors Set
tblAuctionDonors.DateSent=Date()
where...

Third, I'm not sure if it's what you intend, but you are using a
different
WHERE clause for printing the report from the one you are using to
mark
the
letters as sent.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi, there. I am trying to combine a bit of code and it seems that
it
works
sometimes (it updates the table) and sometimes it doesn't.

With help from the group, I've created a form with a command button.
The
command button runs a report and updates the table, in theory. I've
tested
it, it worked, then I deleted the data from the table and tried
again,
but
it
didn't update the 2nd time. I'm horrible at coding and appreciate
your
suggestions.

Private Sub PrintLetter_Click()
"Sends all donors that have not yet been sent letters or
'where the DateSent field is before Oct.1st of that current year.
'Letters marked before that will be sent. Letters after that date
will
not.

On Error GoTo Err_PrintLetter_Click

DoCmd.OpenReport "Solicitation Letter", acViewNormal, , "[DateSent]
Is
Null
Or [DateSent] < DateSerial(Year(Date()),10,1)"
Dim strSQL As String
strSQL = "Update tblAuctionDonors Set tblAuctionDonors.DateSent= #"
&
Date
&
"# Where tblAuctionDonors.[AuctionDonorID] = " & [AuctionDonorID] &
";"
Exit_PrintLetter_Click:
Exit Sub

Err_PrintLetter_Click:
MsgBox Err.Description
Resume Exit_PrintLetter_Click

End Sub