VBA How Do I?

[Duncan Edment]

When a user enters a date, I want to then search the database for all records
that fall within the week the record relates to, and if another field in the
database is set to 'Yes', display a message informing them that they can't add
the record.

For example, if I add a record with a date of 08/10/03, I want to--within
VBA--search for all records that have been entered for that week--namely those
between 06/10 & 12/10--and if the "saved" field is yes on anyone of those
records, display a message advising that the record cannot be saved.

Hope that makes sense!

I have used the following code in the After Update event:

Dim datCRecDate As Date
Dim datWkStart As Date
Dim datWkEnd As Date
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String

' code assumes that a week runs from Monday through Sunday
datCRecDate = Me.fldDateWorked
datWkStart = DateAdd("d", 1 - DatePart("w", datCRecDate, vbMonday),
datCRecDate)
datWkEnd = DateAdd("d", 6, datWkStart)

strSQL = "SELECT * from tblTimeSheet WHERE fldDateWorked >= #'datWkstart'#
and fldDateWorked <= #'datWkEnd'#;"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

And I have used several variations of the strSQL line. However, everytime it
fails...usually giving me an error on the Ser rst line.

Anyone help?

Duncan

 

[Dirk Goldgar]

When a user enters a date, I want to then search the database for all
records that fall within the week the record relates to, and if
another field in the database is set to 'Yes', display a message
informing them that they can't add the record.

For example, if I add a record with a date of 08/10/03, I want
to--within VBA--search for all records that have been entered for
that week--namely those between 06/10 & 12/10--and if the "saved"
field is yes on anyone of those records, display a message advising
that the record cannot be saved.

Hope that makes sense!

I have used the following code in the After Update event:

Dim datCRecDate As Date
Dim datWkStart As Date
Dim datWkEnd As Date
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String

' code assumes that a week runs from Monday through Sunday
datCRecDate = Me.fldDateWorked
datWkStart = DateAdd("d", 1 - DatePart("w", datCRecDate,
vbMonday), datCRecDate)
datWkEnd = DateAdd("d", 6, datWkStart)

strSQL = "SELECT * from tblTimeSheet WHERE fldDateWorked >=
#'datWkstart'# and fldDateWorked <= #'datWkEnd'#;"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

And I have used several variations of the strSQL line. However,
everytime it fails...usually giving me an error on the Ser rst line.

Anyone help?

I haven't checked your logic for coming up with datWkStart and datWkEnd,
but I can see the problem with your SQL statement. As it stands, you
could correct it to this:

strSQL = _
"SELECT * from tblTimeSheet " & _
"WHERE fldDateWorked >= #" & _
Format(datWkStart, "mm/dd/yyyy") & _
"# And fldDateWorked <= #" & _
Format(datWkEnd, "mm/dd/yyyy") & "#;"

It seems to me that, as you explained it, you are really only interested
in those records that fall between the dates and whose [Saved] fields
are set to Yes/True. If that is so, you could further qualify your SQL
statement like this:

strSQL = _
"SELECT * from tblTimeSheet " & _
"WHERE fldDateWorked >= #" & _
Format(datWkStart, "mm/dd/yyyy") & _
"# And fldDateWorked <= #" & _
Format(datWkEnd, "mm/dd/yyyy") & _
"# And [Saved] = True;"

That's assuming that the field is named "Saved" and that it's a Yes/No
(boolean) field.

 

[Duncan Edment]

Dirk, apologies for the delay in replying. Thank you for that, it works
perfectly.

Duncan

--
"Most people are bothered by those passages of Scripture they do not understand,
but the passages that bother me are those I do understand."
~ Mark Twain

When a user enters a date, I want to then search the database for all
records that fall within the week the record relates to, and if
another field in the database is set to 'Yes', display a message
informing them that they can't add the record.

For example, if I add a record with a date of 08/10/03, I want
to--within VBA--search for all records that have been entered for
that week--namely those between 06/10 & 12/10--and if the "saved"
field is yes on anyone of those records, display a message advising
that the record cannot be saved.

Hope that makes sense!

I have used the following code in the After Update event:

Dim datCRecDate As Date
Dim datWkStart As Date
Dim datWkEnd As Date
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String

' code assumes that a week runs from Monday through Sunday
datCRecDate = Me.fldDateWorked
datWkStart = DateAdd("d", 1 - DatePart("w", datCRecDate,
vbMonday), datCRecDate)
datWkEnd = DateAdd("d", 6, datWkStart)

strSQL = "SELECT * from tblTimeSheet WHERE fldDateWorked >=
#'datWkstart'# and fldDateWorked <= #'datWkEnd'#;"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

And I have used several variations of the strSQL line. However,
everytime it fails...usually giving me an error on the Ser rst line.

Anyone help?

I haven't checked your logic for coming up with datWkStart and datWkEnd,
but I can see the problem with your SQL statement. As it stands, you
could correct it to this:

strSQL = _
"SELECT * from tblTimeSheet " & _
"WHERE fldDateWorked >= #" & _
Format(datWkStart, "mm/dd/yyyy") & _
"# And fldDateWorked <= #" & _
Format(datWkEnd, "mm/dd/yyyy") & "#;"

It seems to me that, as you explained it, you are really only interested
in those records that fall between the dates and whose [Saved] fields
are set to Yes/True. If that is so, you could further qualify your SQL
statement like this:

strSQL = _
"SELECT * from tblTimeSheet " & _
"WHERE fldDateWorked >= #" & _
Format(datWkStart, "mm/dd/yyyy") & _
"# And fldDateWorked <= #" & _
Format(datWkEnd, "mm/dd/yyyy") & _
"# And [Saved] = True;"

That's assuming that the field is named "Saved" and that it's a Yes/No
(boolean) field.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)