Update Query - Automatically Store Todays Date

[Mr-Re Man]

I have currently manipulated Allen Brownes search2000 form, and once the
records have been filtered, a button then allows me to to goto a report to
print these filtered records.

What I would like to do is when I goto the report, it will update todays
date in the blank date field called [date_letter_printed].

Firstly, how do I get this to work in the buttons code on the form but also
to have a pop up box to inform them that they wil not be able to undo this
action after it has been pressed.

Finally, the user should only be searching for records to print with a null
[date_letter_printed] field, but the search however will allow them to return
records with a date (As the search form is not just for printing reports).

If they did select criteria that returned a mix of dated and null records
and then clicked the button to print the report, it will ask them if they are
sure they want to print and update the records due to some being identified
as already having a date in them. If they then click yes, its becasue they
want to reprint the report and update the date.

Hope that makes sense for you guys.
regards

 

[Klatuu]

To update the [date_letter_printed], you will need an update query that has
the same filtering criteria as your form.

To present a message that the process can't be reversed and get
confirmation, use a Message Box. You can test to see whether you have a
mixed set of data, check your form filtering.

Allen Browne's Client Search form uses the form FilterOn property to
determine whether the form is currently filtered. You can use this to
determine what you want to do.

This is untested "air code", but it should give you the idea of how you can
accomplish your goal:

Sub UpdateAndPring()
Dim strMsg As String
Dim strSQL As String

strMsg = ("You Are About to Update " & Me.Recordset.RecordCount & _
"Records" & vbNewLine & "Continue"

strSQL = "UPDATE _tblClient SET [ReportDate] = Date()"

If Me.FilterOn Then
strSQL = strSQL & " WHERE " & Me.Filter
Else
strMsg = Replace(strMsg, "Continue", & _
"Some Records Have Dates that will be Updated" & vbNewLine &
"Continue"
End If
strSQL = strSQL & ";"
If MsgBox(strMsg, vbYesNo+vbQuestion) = vbYes Then
If Me.FilterOn Then
Docmd.OpenReport acViewPreview, "MyReport", Me.Filter
Else
Docmd.OpenReport acViewPreview, "MyReport"
End If
Currentdb.Execute(strSQL, dbFailOnError)
Else
Msg Box "UpDate Canceled", vbInformation
End If

End Sub

 

[Mr-Re Man]

Do I place the code and tweak it in the onClick event button in the search
criteria form that opens the report?

And do I copy the standard query witrh the filtering and save it as an
update query.

I'm getting there with Access...slowly, but need extra shoves in the right
direction now and then.

thanks again

To update the [date_letter_printed], you will need an update query that has
the same filtering criteria as your form.

To present a message that the process can't be reversed and get
confirmation, use a Message Box. You can test to see whether you have a
mixed set of data, check your form filtering.

Allen Browne's Client Search form uses the form FilterOn property to
determine whether the form is currently filtered. You can use this to
determine what you want to do.

This is untested "air code", but it should give you the idea of how you can
accomplish your goal:

Sub UpdateAndPring()
Dim strMsg As String
Dim strSQL As String

strMsg = ("You Are About to Update " & Me.Recordset.RecordCount & _
"Records" & vbNewLine & "Continue"

strSQL = "UPDATE _tblClient SET [ReportDate] = Date()"

If Me.FilterOn Then
strSQL = strSQL & " WHERE " & Me.Filter
Else
strMsg = Replace(strMsg, "Continue", & _
"Some Records Have Dates that will be Updated" & vbNewLine &
"Continue"
End If
strSQL = strSQL & ";"
If MsgBox(strMsg, vbYesNo+vbQuestion) = vbYes Then
If Me.FilterOn Then
Docmd.OpenReport acViewPreview, "MyReport", Me.Filter
Else
Docmd.OpenReport acViewPreview, "MyReport"
End If
Currentdb.Execute(strSQL, dbFailOnError)
Else
Msg Box "UpDate Canceled", vbInformation
End If

End Sub
--
Dave Hargis, Microsoft Access MVP

I have currently manipulated Allen Brownes search2000 form, and once the
records have been filtered, a button then allows me to to goto a report to
print these filtered records.

What I would like to do is when I goto the report, it will update todays
date in the blank date field called [date_letter_printed].

Firstly, how do I get this to work in the buttons code on the form but also
to have a pop up box to inform them that they wil not be able to undo this
action after it has been pressed.

Finally, the user should only be searching for records to print with a null
[date_letter_printed] field, but the search however will allow them to return
records with a date (As the search form is not just for printing reports).

If they did select criteria that returned a mix of dated and null records
and then clicked the button to print the report, it will ask them if they are
sure they want to print and update the records due to some being identified
as already having a date in them. If they then click yes, its becasue they
want to reprint the report and update the date.

Hope that makes sense for you guys.
regards

 

[Klatuu]

Yes, you could use the After Update event of the button where you print the
report.
You do not need to modify any stored queries. Notice the Currentdb.Execute
method. It does the update to the table. The record source for the report
should not contain any filtering regarding the date. Using the Where
argument of the OpenReport method takes care of that. You would only need to
put in filtering that you would want regardless of whether it should be
filtered on the date.
--
Dave Hargis, Microsoft Access MVP

Do I place the code and tweak it in the onClick event button in the search
criteria form that opens the report?

And do I copy the standard query witrh the filtering and save it as an
update query.

I'm getting there with Access...slowly, but need extra shoves in the right
direction now and then.

thanks again

To update the [date_letter_printed], you will need an update query that has
the same filtering criteria as your form.

To present a message that the process can't be reversed and get
confirmation, use a Message Box. You can test to see whether you have a
mixed set of data, check your form filtering.

Allen Browne's Client Search form uses the form FilterOn property to
determine whether the form is currently filtered. You can use this to
determine what you want to do.

This is untested "air code", but it should give you the idea of how you can
accomplish your goal:

Sub UpdateAndPring()
Dim strMsg As String
Dim strSQL As String

strMsg = ("You Are About to Update " & Me.Recordset.RecordCount & _
"Records" & vbNewLine & "Continue"

strSQL = "UPDATE _tblClient SET [ReportDate] = Date()"

If Me.FilterOn Then
strSQL = strSQL & " WHERE " & Me.Filter
Else
strMsg = Replace(strMsg, "Continue", & _
"Some Records Have Dates that will be Updated" & vbNewLine &
"Continue"
End If
strSQL = strSQL & ";"
If MsgBox(strMsg, vbYesNo+vbQuestion) = vbYes Then
If Me.FilterOn Then
Docmd.OpenReport acViewPreview, "MyReport", Me.Filter
Else
Docmd.OpenReport acViewPreview, "MyReport"
End If
Currentdb.Execute(strSQL, dbFailOnError)
Else
Msg Box "UpDate Canceled", vbInformation
End If

End Sub
--
Dave Hargis, Microsoft Access MVP

I have currently manipulated Allen Brownes search2000 form, and once the
records have been filtered, a button then allows me to to goto a report to
print these filtered records.

What I would like to do is when I goto the report, it will update todays
date in the blank date field called [date_letter_printed].

Firstly, how do I get this to work in the buttons code on the form but also
to have a pop up box to inform them that they wil not be able to undo this
action after it has been pressed.

Finally, the user should only be searching for records to print with a null
[date_letter_printed] field, but the search however will allow them to return
records with a date (As the search form is not just for printing reports).

If they did select criteria that returned a mix of dated and null records
and then clicked the button to print the report, it will ask them if they are
sure they want to print and update the records due to some being identified
as already having a date in them. If they then click yes, its becasue they
want to reprint the report and update the date.

Hope that makes sense for you guys.
regards

 

[Mr-Re Man]

Thanks for this, I couldn't find an AfterUpdate on the button that is within
the search form, so I added the code to the onClick event instead. Here is
the code I have used

Private Sub cmdUpdateandPrint_Click()
Dim strMsg As String
Dim strSQL As String
Dim stDocName As String

strMsg = ("You Are About to Update " & Me.Recordset.RecordCount & "
Records" & vbNewLine & "Continue?")

strSQL = "UPDATE repConveyanceofPupils_Letter SET [date_of_request_date]
= Date()"

stDocName = "rep_conveyance_letter_parent"

If Me.FilterOn Then
strSQL = strSQL & " WHERE " & Me.Filter
Else
strMsg = Replace(strMsg, "Continue", "Some Records Have Dates that
will be updated" & vbNewLine & "Continue")
End If
strSQL = strSQL & ";"
If MsgBox(strMsg, vbYesNo + vbQuestion) = vbYes Then
If Me.FilterOn Then
DoCmd.OpenReport stDocName, acPreview, Me.Filter
Else
DoCmd.OpenReport stDocName, acPreview
End If
Currentdb.Execute(strSQL, dbFailOnError)
Else
MsgBox "UpDate Canceled", vbInformation
End If

End Sub

I am getting an error message on this line:
Currentdb.Execute(strSQL, dbFailOnError)
It indicates a = is required.

I tried tweaking the code to:
CurrentDb.Execute "StrSQL", dbFailOnError
but I had different error messages.

The pop up message boxes work fine, the messgaes appeared as the report
opened.

I feel we are close to solving this one.

Yes, you could use the After Update event of the button where you print the
report.
You do not need to modify any stored queries. Notice the Currentdb.Execute
method. It does the update to the table. The record source for the report
should not contain any filtering regarding the date. Using the Where
argument of the OpenReport method takes care of that. You would only need to
put in filtering that you would want regardless of whether it should be
filtered on the date.
--
Dave Hargis, Microsoft Access MVP

Do I place the code and tweak it in the onClick event button in the search
criteria form that opens the report?

And do I copy the standard query witrh the filtering and save it as an
update query.

I'm getting there with Access...slowly, but need extra shoves in the right
direction now and then.

thanks again

To update the [date_letter_printed], you will need an update query that has
the same filtering criteria as your form.

To present a message that the process can't be reversed and get
confirmation, use a Message Box. You can test to see whether you have a
mixed set of data, check your form filtering.

Allen Browne's Client Search form uses the form FilterOn property to
determine whether the form is currently filtered. You can use this to
determine what you want to do.

This is untested "air code", but it should give you the idea of how you can
accomplish your goal:

Sub UpdateAndPring()
Dim strMsg As String
Dim strSQL As String

strMsg = ("You Are About to Update " & Me.Recordset.RecordCount & _
"Records" & vbNewLine & "Continue"

strSQL = "UPDATE _tblClient SET [ReportDate] = Date()"

If Me.FilterOn Then
strSQL = strSQL & " WHERE " & Me.Filter
Else
strMsg = Replace(strMsg, "Continue", & _
"Some Records Have Dates that will be Updated" & vbNewLine &
"Continue"
End If
strSQL = strSQL & ";"
If MsgBox(strMsg, vbYesNo+vbQuestion) = vbYes Then
If Me.FilterOn Then
Docmd.OpenReport acViewPreview, "MyReport", Me.Filter
Else
Docmd.OpenReport acViewPreview, "MyReport"
End If
Currentdb.Execute(strSQL, dbFailOnError)
Else
Msg Box "UpDate Canceled", vbInformation
End If

End Sub
--
Dave Hargis, Microsoft Access MVP


:

I have currently manipulated Allen Brownes search2000 form, and once the
records have been filtered, a button then allows me to to goto a report to
print these filtered records.

What I would like to do is when I goto the report, it will update todays
date in the blank date field called [date_letter_printed].

Firstly, how do I get this to work in the buttons code on the form but also
to have a pop up box to inform them that they wil not be able to undo this
action after it has been pressed.

Finally, the user should only be searching for records to print with a null
[date_letter_printed] field, but the search however will allow them to return
records with a date (As the search form is not just for printing reports).

If they did select criteria that returned a mix of dated and null records
and then clicked the button to print the report, it will ask them if they are
sure they want to print and update the records due to some being identified
as already having a date in them. If they then click yes, its becasue they
want to reprint the report and update the date.

Hope that makes sense for you guys.
regards

 

[Mr-Re Man]

After a lot of tweaking, I have solved my question to you.

thanks for you help

NEW CODE if anyone needs to see what I did

Private Sub cmdUpdateandPrint_Click()
Dim strMsg As String
Dim strSQL As String
Dim stDocName As String

strMsg = ("You are about to print & update " & Me.Recordset.RecordCount
& " Record(s)" & vbNewLine & "Continue?")

//changed// strSQL = "UPDATE your_table SET your_field = Date()"

stDocName = "your_report"

If Me.FilterOn Then
strSQL = strSQL & " WHERE " & Me.Filter
Else
strMsg = Replace(strMsg, "Continue", "Some records have dates that
will be updated" & vbNewLine & "Continue")
End If
strSQL = strSQL & ";"
If MsgBox(strMsg, vbYesNo + vbQuestion) = vbYes Then
If Me.FilterOn Then
//changed// DoCmd.OpenReport stDocName, acPreview, , Me.Filter
Else
DoCmd.OpenReport stDocName, acPreview
End If
//changed// CurrentDb.Execute strSQL, dbFailOnError
Else
MsgBox "Request Cancelled", vbInformation
End If

End Sub

 

[Mr-Re Man]

Please can somebody help, I'm going nuts here, my previously correct code has
stopped working and coming up with the error "Run Time Error 3061 - Too few
Parameters. Expected 1"

What is wrong with this code?

It highlights:
CurrentDb.Execute strSQL, dbFailOnError
as the problem but I presume it is really to do with this line:
strSQL = "UPDATE tbl_pupil_permits SET date_permit_sent_date] = Date()"

---------------------------------------------------------------------------------------

Private Sub cmdUpdateandPrint_Click()
'This code allows you to print records slected from the search criteria box
and update a date in the table
Dim strMsg As String
Dim strSQL As String
Dim stDocName As String
Dim strFilter As String

strMsg = ("You are about to print & update " & Me.Recordset.RecordCount
& " Record(s)" & vbNewLine & "Continue?")

strSQL = "UPDATE tbl_pupil_permits SET date_permit_sent_date] = Date()"

stDocName = "rep_conveyance_letter_parent"

strFilter = "Where " & Me.Filter

If Me.FilterOn Then
strSQL = strSQL & strFilter
Debug.Print strSQL
Else
strMsg = Replace(strMsg, "Continue", "Some records have dates that
will be updated" & vbNewLine & "Continue")
End If
strSQL = strSQL & ";"
If MsgBox(strMsg, vbYesNo + vbQuestion) = vbYes Then

If Me.FilterOn Then
DoCmd.OpenReport stDocName, acPreview, , Me.Filter

Else
DoCmd.OpenReport stDocName, acPreview

End If
CurrentDb.Execute strSQL, dbFailOnError

Else
MsgBox "Request Cancelled", vbInformation
End If

End Sub

 

[Bob Barrows]

Please can somebody help, I'm going nuts here, my previously correct
code has stopped working and coming up with the error "Run Time Error
3061 - Too few Parameters. Expected 1"

What is wrong with this code?

It highlights:
CurrentDb.Execute strSQL, dbFailOnError
as the problem but I presume it is really to do with this line:
strSQL = "UPDATE tbl_pupil_permits SET date_permit_sent_date] =
Date()"

You're missing an opening bracket.

.... [date_permit_sent_date] = ...

 

[John Spencer]

That is a malformed query you have a square bracket at the end of the field
name, but NOT at the beginning of the field name.

This
strSQL = "UPDATE tbl_pupil_permits SET date_permit_sent_date] = Date()"
should be this
strSQL = "UPDATE tbl_pupil_permits SET [date_permit_sent_date] = Date()"

The next thing you need to worry aout is adding the WHERE clause since you
don't have a space in front of the where, the string is going to end up
reading like
"UPDATE tbl_pupil_permits SET date_permit_sent_date] = Date()WHERE ..."

So change the line to
strFilter = " Where " & Me.Filter

If you get error messages when the query tries to execute, then use
Debug.Print strSQL
to print the SQL to the immediate window. You can examine the query string
for problems and/or copy the string and paste it into a blank query (SQL view)
to attempt to run it and see what errors you need to fix.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Mr-Re Man]

Thank you, I have updted the changes and the same error and location is still
appearing when the code runs.

Run Time Error 3061 - Too few Parameters. Expected 1
CurrentDb.Execute strSQL, dbFailOnError

I didn't know where to put Debug.Print strSQL

I'm so grateful for your assistance

That is a malformed query you have a square bracket at the end of the field
name, but NOT at the beginning of the field name.

This
strSQL = "UPDATE tbl_pupil_permits SET date_permit_sent_date] = Date()"
should be this
strSQL = "UPDATE tbl_pupil_permits SET [date_permit_sent_date] = Date()"

The next thing you need to worry aout is adding the WHERE clause since you
don't have a space in front of the where, the string is going to end up
reading like
"UPDATE tbl_pupil_permits SET date_permit_sent_date] = Date()WHERE ..."

So change the line to
strFilter = " Where " & Me.Filter

If you get error messages when the query tries to execute, then use
Debug.Print strSQL
to print the SQL to the immediate window. You can examine the query string
for problems and/or copy the string and paste it into a blank query (SQL view)
to attempt to run it and see what errors you need to fix.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Please can somebody help, I'm going nuts here, my previously correct code has
stopped working and coming up with the error "Run Time Error 3061 - Too few
Parameters. Expected 1"

What is wrong with this code?

It highlights:
CurrentDb.Execute strSQL, dbFailOnError
as the problem but I presume it is really to do with this line:
strSQL = "UPDATE tbl_pupil_permits SET date_permit_sent_date] = Date()"

---------------------------------------------------------------------------------------

Private Sub cmdUpdateandPrint_Click()
'This code allows you to print records slected from the search criteria box
and update a date in the table
Dim strMsg As String
Dim strSQL As String
Dim stDocName As String
Dim strFilter As String

strMsg = ("You are about to print & update " & Me.Recordset.RecordCount
& " Record(s)" & vbNewLine & "Continue?")

strSQL = "UPDATE tbl_pupil_permits SET date_permit_sent_date] = Date()"

stDocName = "rep_conveyance_letter_parent"

strFilter = "Where " & Me.Filter

If Me.FilterOn Then
strSQL = strSQL & strFilter
Debug.Print strSQL
Else
strMsg = Replace(strMsg, "Continue", "Some records have dates that
will be updated" & vbNewLine & "Continue")
End If
strSQL = strSQL & ";"
If MsgBox(strMsg, vbYesNo + vbQuestion) = vbYes Then

If Me.FilterOn Then
DoCmd.OpenReport stDocName, acPreview, , Me.Filter

Else
DoCmd.OpenReport stDocName, acPreview

End If
CurrentDb.Execute strSQL, dbFailOnError

Else
MsgBox "Request Cancelled", vbInformation
End If

End Sub

 

[John Spencer]

Just before you execute the query, add the line

Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

The message indicates that you have something in the query that looks like a
field, but the query cannot resolve that. I suspect that the filter string is
the culprit, but it is possible that you have misspelled the
Date_Permit_Sent_Date field name.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thank you, I have updted the changes and the same error and location is still
appearing when the code runs.

Run Time Error 3061 - Too few Parameters. Expected 1
CurrentDb.Execute strSQL, dbFailOnError

I didn't know where to put Debug.Print strSQL

I'm so grateful for your assistance

That is a malformed query you have a square bracket at the end of the field
name, but NOT at the beginning of the field name.

This
strSQL = "UPDATE tbl_pupil_permits SET date_permit_sent_date] = Date()"
should be this
strSQL = "UPDATE tbl_pupil_permits SET [date_permit_sent_date] = Date()"

The next thing you need to worry aout is adding the WHERE clause since you
don't have a space in front of the where, the string is going to end up
reading like
"UPDATE tbl_pupil_permits SET date_permit_sent_date] = Date()WHERE ..."

So change the line to
strFilter = " Where " & Me.Filter

If you get error messages when the query tries to execute, then use
Debug.Print strSQL
to print the SQL to the immediate window. You can examine the query string
for problems and/or copy the string and paste it into a blank query (SQL view)
to attempt to run it and see what errors you need to fix.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Please can somebody help, I'm going nuts here, my previously correct code has
stopped working and coming up with the error "Run Time Error 3061 - Too few
Parameters. Expected 1"

What is wrong with this code?

It highlights:
CurrentDb.Execute strSQL, dbFailOnError
as the problem but I presume it is really to do with this line:
strSQL = "UPDATE tbl_pupil_permits SET date_permit_sent_date] = Date()"

---------------------------------------------------------------------------------------

Private Sub cmdUpdateandPrint_Click()
'This code allows you to print records slected from the search criteria box
and update a date in the table
Dim strMsg As String
Dim strSQL As String
Dim stDocName As String
Dim strFilter As String

strMsg = ("You are about to print & update " & Me.Recordset.RecordCount
& " Record(s)" & vbNewLine & "Continue?")

strSQL = "UPDATE tbl_pupil_permits SET date_permit_sent_date] = Date()"

stDocName = "rep_conveyance_letter_parent"

strFilter = "Where " & Me.Filter

If Me.FilterOn Then
strSQL = strSQL & strFilter
Debug.Print strSQL
Else
strMsg = Replace(strMsg, "Continue", "Some records have dates that
will be updated" & vbNewLine & "Continue")
End If
strSQL = strSQL & ";"
If MsgBox(strMsg, vbYesNo + vbQuestion) = vbYes Then

If Me.FilterOn Then
DoCmd.OpenReport stDocName, acPreview, , Me.Filter

Else
DoCmd.OpenReport stDocName, acPreview

End If
CurrentDb.Execute strSQL, dbFailOnError

Else
MsgBox "Request Cancelled", vbInformation
End If

End Sub

 

[Mr-Re Man]

OK, Have added in the line as suggested and have copy and pasted the field
name from the table, but am still receiving the same error.

Could it be anything to do with the report that opens having a Data
RecordSource? I use a Query to pull in the information for the letter (its
stored in three different tables), but am asking access to update an
alternative table with a date for this piece of code.

When I put "UPDATE tbl_pupil_permits SET [date_permit_sent_date] = Date()"
in the line of code CurrentDb.Execute strSQL, dbFailOnError yesterday it was
updating all the records with the date instead of the filtered ones. I know
it is incorrect, but it worked then.

So frustrating when you know the solution is going to be something simple.

Just before you execute the query, add the line

Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

The message indicates that you have something in the query that looks like a
field, but the query cannot resolve that. I suspect that the filter string is
the culprit, but it is possible that you have misspelled the
Date_Permit_Sent_Date field name.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thank you, I have updted the changes and the same error and location is still
appearing when the code runs.

Run Time Error 3061 - Too few Parameters. Expected 1
CurrentDb.Execute strSQL, dbFailOnError

I didn't know where to put Debug.Print strSQL

I'm so grateful for your assistance

That is a malformed query you have a square bracket at the end of the field
name, but NOT at the beginning of the field name.

This
strSQL = "UPDATE tbl_pupil_permits SET date_permit_sent_date] = Date()"
should be this
strSQL = "UPDATE tbl_pupil_permits SET [date_permit_sent_date] = Date()"

The next thing you need to worry aout is adding the WHERE clause since you
don't have a space in front of the where, the string is going to end up
reading like
"UPDATE tbl_pupil_permits SET date_permit_sent_date] = Date()WHERE ..."

So change the line to
strFilter = " Where " & Me.Filter

If you get error messages when the query tries to execute, then use
Debug.Print strSQL
to print the SQL to the immediate window. You can examine the query string
for problems and/or copy the string and paste it into a blank query (SQL view)
to attempt to run it and see what errors you need to fix.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Mr-Re Man wrote:
Please can somebody help, I'm going nuts here, my previously correct code has
stopped working and coming up with the error "Run Time Error 3061 - Too few
Parameters. Expected 1"

What is wrong with this code?

It highlights:
CurrentDb.Execute strSQL, dbFailOnError
as the problem but I presume it is really to do with this line:
strSQL = "UPDATE tbl_pupil_permits SET date_permit_sent_date] = Date()"

---------------------------------------------------------------------------------------

Private Sub cmdUpdateandPrint_Click()
'This code allows you to print records slected from the search criteria box
and update a date in the table
Dim strMsg As String
Dim strSQL As String
Dim stDocName As String
Dim strFilter As String

strMsg = ("You are about to print & update " & Me.Recordset.RecordCount
& " Record(s)" & vbNewLine & "Continue?")

strSQL = "UPDATE tbl_pupil_permits SET date_permit_sent_date] = Date()"

stDocName = "rep_conveyance_letter_parent"

strFilter = "Where " & Me.Filter

If Me.FilterOn Then
strSQL = strSQL & strFilter
Debug.Print strSQL
Else
strMsg = Replace(strMsg, "Continue", "Some records have dates that
will be updated" & vbNewLine & "Continue")
End If
strSQL = strSQL & ";"
If MsgBox(strMsg, vbYesNo + vbQuestion) = vbYes Then

If Me.FilterOn Then
DoCmd.OpenReport stDocName, acPreview, , Me.Filter

Else
DoCmd.OpenReport stDocName, acPreview

End If
CurrentDb.Execute strSQL, dbFailOnError

Else
MsgBox "Request Cancelled", vbInformation
End If

End Sub

 

[John Spencer]

Your problem has to be in the filter string. Since you have never shown that
to us, we don't know what the string is doing.

Could you copy and post the SQL string that you are trying to execute? The
string should be what is being printed in the immediate window. Your problem
(at a guess) is that one (or more) fields you reference in the filter DO NOT
EXIST in the table tbl_pupil_permits

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

OK, Have added in the line as suggested and have copy and pasted the field
name from the table, but am still receiving the same error.

Could it be anything to do with the report that opens having a Data
RecordSource? I use a Query to pull in the information for the letter (its
stored in three different tables), but am asking access to update an
alternative table with a date for this piece of code.

When I put "UPDATE tbl_pupil_permits SET [date_permit_sent_date] = Date()"
in the line of code CurrentDb.Execute strSQL, dbFailOnError yesterday it was
updating all the records with the date instead of the filtered ones. I know
it is incorrect, but it worked then.

So frustrating when you know the solution is going to be something simple.

"John Spencer" wrote:

SNIP

 

[Mr-Re Man]

You are spot on, I noticed this before I left work last night.
tbl_pupil_permits only has certain fields available, the query that is used
to pull in all the information for the letter does have the necessary fields.

I did attempt to use this query instead, but it said something about an
update query, which I also created, but still didn't work. Then I read
something about if you have too many 1 to many relationships and lots of
tables in an update query, it will not work. I have 7 tables and 7 1 to many
relationships in the query to bring in the informataion required.

So I am stumped unless you can think of something.

Your problem has to be in the filter string. Since you have never shown that
to us, we don't know what the string is doing.

Could you copy and post the SQL string that you are trying to execute? The
string should be what is being printed in the immediate window. Your problem
(at a guess) is that one (or more) fields you reference in the filter DO NOT
EXIST in the table tbl_pupil_permits

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

OK, Have added in the line as suggested and have copy and pasted the field
name from the table, but am still receiving the same error.

Could it be anything to do with the report that opens having a Data
RecordSource? I use a Query to pull in the information for the letter (its
stored in three different tables), but am asking access to update an
alternative table with a date for this piece of code.

When I put "UPDATE tbl_pupil_permits SET [date_permit_sent_date] = Date()"
in the line of code CurrentDb.Execute strSQL, dbFailOnError yesterday it was
updating all the records with the date instead of the filtered ones. I know
it is incorrect, but it worked then.

So frustrating when you know the solution is going to be something simple.

"John Spencer" wrote:

SNIP