Check Date in PayCode.mde

[Tim]

Hi everyone,

I built two database called PayCode.mde and Payment.mdb.
The payment.mdb call the codes from PayCode.mde.

In PayCode.mde have the following objects:

tblPayDate:fldDate
PayCode1
PayCode2
PayCode3


Public Sub PayCode1()

Const csdate = #12/1/03#

Dim PayDate As Date

Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String


Set dbCurrent = CurrentDb

Set rst = dbCurrent.OpenRecordset("SELECT max(fldDate)AS
MaxOffldDate FROM [tblPayDate]")

PayDate = Date

Select Case PayDate

Case Is < csdate, Is < rst.Fields("MaxOffldDate").Value
MsgBox "Hi"

Case Else
DoCmd.Quit

End Select

End Sub


Public Sub PayCode2()

Const csdate = #12/1/03#

Dim PayDate As Date

Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String


Set dbCurrent = CurrentDb

Set rst = dbCurrent.OpenRecordset("SELECT max(fldDate)AS
MaxOffldDate FROM [tblPayDate]")

PayDate = Date

Select Case PayDate

Case Is < csdate, Is < rst.Fields("MaxOffldDate").Value
MsgBox "Done"

Case Else
DoCmd.Quit

End Select

End Sub

When I called PayCode1 or PayCode2 from payment.mdb, I got
an error message said:"tblPayDate does not exist"

When I removed the code referred to tblPayDate, it worked
if PayDate < csdate. If PayDate > csdate, it exited both
payment.mdb and PayCode.mde database. It was not what I
expected.

I want to have a check date function/sub that can be
called by any function/sub in PayCode.mde to check the
date of csdate and the date in tblPayDate. If it is true,
then continue the function/sub. If not, exit that
function/sub instead of exit both databases.

Does anyone have any idea? I really appreciat any help.

Thanks in advance.

Tim.

 

[Marshall Barton]

I built two database called PayCode.mde and Payment.mdb.
The payment.mdb call the codes from PayCode.mde.

In PayCode.mde have the following objects:

tblPayDate:fldDate
PayCode1
PayCode2
PayCode3


Public Sub PayCode1()

Const csdate = #12/1/03#

Dim PayDate As Date

Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String


Set dbCurrent = CurrentDb

Set rst = dbCurrent.OpenRecordset("SELECT max(fldDate)AS
MaxOffldDate FROM [tblPayDate]")

PayDate = Date

Select Case PayDate

Case Is < csdate, Is < rst.Fields("MaxOffldDate").Value
MsgBox "Hi"

Case Else
DoCmd.Quit

End Select

End Sub


Public Sub PayCode2()

Const csdate = #12/1/03#

Dim PayDate As Date

Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String


Set dbCurrent = CurrentDb

Set rst = dbCurrent.OpenRecordset("SELECT max(fldDate)AS
MaxOffldDate FROM [tblPayDate]")

PayDate = Date

Select Case PayDate

Case Is < csdate, Is < rst.Fields("MaxOffldDate").Value
MsgBox "Done"

Case Else
DoCmd.Quit

End Select

End Sub

When I called PayCode1 or PayCode2 from payment.mdb, I got
an error message said:"tblPayDate does not exist"

When I removed the code referred to tblPayDate, it worked
if PayDate < csdate. If PayDate > csdate, it exited both
payment.mdb and PayCode.mde database. It was not what I
expected.

I want to have a check date function/sub that can be
called by any function/sub in PayCode.mde to check the
date of csdate and the date in tblPayDate. If it is true,
then continue the function/sub. If not, exit that
function/sub instead of exit both databases.

Quit exits the application. I think you want to use:

Exit Sub

or, since you have no code following the End Select
statement, leave out that line altogether.

 

[Tim]

Marsh,

Thanks for your respone. Is there a way to create a
check date function/sub that can be called from any
function/sub? If the date is true, contiue the
function/sub. If not, exit function/sub.

Thanks a lot.

Tim.

-----Original Message-----

I built two database called PayCode.mde and Payment.mdb.
The payment.mdb call the codes from PayCode.mde.

In PayCode.mde have the following objects:

tblPayDate:fldDate
PayCode1
PayCode2
PayCode3


Public Sub PayCode1()

Const csdate = #12/1/03#

Dim PayDate As Date

Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String


Set dbCurrent = CurrentDb

Set rst = dbCurrent.OpenRecordset("SELECT max(fldDate) AS
MaxOffldDate FROM [tblPayDate]")

PayDate = Date

Select Case PayDate

Case Is < csdate, Is < rst.Fields("MaxOffldDate").Value
MsgBox "Hi"

Case Else
DoCmd.Quit

End Select

End Sub


Public Sub PayCode2()

Const csdate = #12/1/03#

Dim PayDate As Date

Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String


Set dbCurrent = CurrentDb

Set rst = dbCurrent.OpenRecordset("SELECT max(fldDate) AS
MaxOffldDate FROM [tblPayDate]")

PayDate = Date

Select Case PayDate

Case Is < csdate, Is < rst.Fields("MaxOffldDate").Value
MsgBox "Done"

Case Else
DoCmd.Quit

End Select

End Sub

When I called PayCode1 or PayCode2 from payment.mdb, I got
an error message said:"tblPayDate does not exist"

When I removed the code referred to tblPayDate, it worked
if PayDate < csdate. If PayDate > csdate, it exited both
payment.mdb and PayCode.mde database. It was not what I
expected.

I want to have a check date function/sub that can be
called by any function/sub in PayCode.mde to check the
date of csdate and the date in tblPayDate. If it is true,
then continue the function/sub. If not, exit that
function/sub instead of exit both databases.

Quit exits the application. I think you want to use:

Exit Sub

or, since you have no code following the End Select
statement, leave out that line altogether.

 

[Marshall Barton]

Marsh,

Thanks for your respone. Is there a way to create a
check date function/sub that can be called from any
function/sub? If the date is true, contiue the
function/sub. If not, exit function/sub.

Probably yes, you can do that, but without knowing more
about what you're trying to accomplish, I can't really say
for sure. It is not unusual to use code something like:

. . .
Select Case PayDate
Case Is < csdate, Is < rst!MaxOffldDate
MsgBox "Hi"
Case Else
Exit Sub
End Select
. . .

but that may or may not integrate well with the rest of your
program.

Tim.

-----Original Message-----

I built two database called PayCode.mde and Payment.mdb.
The payment.mdb call the codes from PayCode.mde.

In PayCode.mde have the following objects:

tblPayDate:fldDate
PayCode1
PayCode2
PayCode3


Public Sub PayCode1()

Const csdate = #12/1/03#

Dim PayDate As Date

Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String


Set dbCurrent = CurrentDb

Set rst = dbCurrent.OpenRecordset("SELECT max(fldDate) AS
MaxOffldDate FROM [tblPayDate]")

PayDate = Date

Select Case PayDate

Case Is < csdate, Is < rst.Fields("MaxOffldDate").Value
MsgBox "Hi"

Case Else
DoCmd.Quit

End Select

End Sub


Public Sub PayCode2()

Const csdate = #12/1/03#

Dim PayDate As Date

Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String


Set dbCurrent = CurrentDb

Set rst = dbCurrent.OpenRecordset("SELECT max(fldDate) AS
MaxOffldDate FROM [tblPayDate]")

PayDate = Date

Select Case PayDate

Case Is < csdate, Is < rst.Fields("MaxOffldDate").Value
MsgBox "Done"

Case Else
DoCmd.Quit

End Select

End Sub

When I called PayCode1 or PayCode2 from payment.mdb, I got
an error message said:"tblPayDate does not exist"

When I removed the code referred to tblPayDate, it worked
if PayDate < csdate. If PayDate > csdate, it exited both
payment.mdb and PayCode.mde database. It was not what I
expected.

I want to have a check date function/sub that can be
called by any function/sub in PayCode.mde to check the
date of csdate and the date in tblPayDate. If it is true,
then continue the function/sub. If not, exit that
function/sub instead of exit both databases.

Quit exits the application. I think you want to use:

Exit Sub

or, since you have no code following the End Select
statement, leave out that line altogether.

 

[Tim]

Marsh,

I am looking for something like this:

sub PayCode1()

call PayDate 'Function to check PayDate
MsgBox "Hi"

end sub

sub PayCode2()

call PayDate 'Function to check PayDate
MsgBox "Done"

end sub

I have a difficult to create the PayDate sub. Can you
show me how to do it?

Thanks.

Tim.

-----Original Message-----

Marsh,

Thanks for your respone. Is there a way to create a
check date function/sub that can be called from any
function/sub? If the date is true, contiue the
function/sub. If not, exit function/sub.

Probably yes, you can do that, but without knowing more
about what you're trying to accomplish, I can't really say
for sure. It is not unusual to use code something like:

. . .
Select Case PayDate
Case Is < csdate, Is < rst!MaxOffldDate
MsgBox "Hi"
Case Else
Exit Sub
End Select
. . .

but that may or may not integrate well with the rest of your

program.

Tim.
-----Original Message-----
Tim wrote:
I built two database called PayCode.mde and Payment.mdb.
The payment.mdb call the codes from PayCode.mde.

In PayCode.mde have the following objects:

tblPayDate:fldDate
PayCode1
PayCode2
PayCode3


Public Sub PayCode1()

Const csdate = #12/1/03#

Dim PayDate As Date

Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String


Set dbCurrent = CurrentDb

Set rst = dbCurrent.OpenRecordset("SELECT max(fldDate) AS
MaxOffldDate FROM [tblPayDate]")

PayDate = Date

Select Case PayDate

Case Is < csdate, Is < rst.Fields ("MaxOffldDate").Value
MsgBox "Hi"

Case Else
DoCmd.Quit

End Select

End Sub


Public Sub PayCode2()

Const csdate = #12/1/03#

Dim PayDate As Date

Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String


Set dbCurrent = CurrentDb

Set rst = dbCurrent.OpenRecordset("SELECT max(fldDate) AS
MaxOffldDate FROM [tblPayDate]")

PayDate = Date

Select Case PayDate

Case Is < csdate, Is < rst.Fields ("MaxOffldDate").Value
MsgBox "Done"

Case Else
DoCmd.Quit

End Select

End Sub

When I called PayCode1 or PayCode2 from payment.mdb,

I
got

an error message said:"tblPayDate does not exist"

When I removed the code referred to tblPayDate, it worked
if PayDate < csdate. If PayDate > csdate, it exited both
payment.mdb and PayCode.mde database. It was not

what
I

expected.

I want to have a check date function/sub that can be
called by any function/sub in PayCode.mde to check the
date of csdate and the date in tblPayDate. If it is true,
then continue the function/sub. If not, exit that
function/sub instead of exit both databases.

Quit exits the application. I think you want to use:

Exit Sub

or, since you have no code following the End Select
statement, leave out that line altogether.

 

[Marshall Barton]

Marsh,

I am looking for something like this:

sub PayCode1()

call PayDate 'Function to check PayDate
MsgBox "Hi"

end sub

sub PayCode2()

call PayDate 'Function to check PayDate
MsgBox "Done"

end sub

I have a difficult to create the PayDate sub. Can you
show me how to do it?

Maybe. What's the PayDate sub supposed to do? The code you
posted earlier doesn't do any thing except display a message
box. Generally, if a procedure is supposed to "check"
something, then it should either be a function so it can
return a value for the calling procedure to use in making a
decision. Or it could be a Sub that makes some kind of
change to some data in a table or somewhere.

 

[Tim]

Marsh,

I want to make the following code into a sub called
PayDate.

Const csdate = #12/1/03#

Dim PayDate As Date

Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String


Set dbCurrent = CurrentDb

Set rst = dbCurrent.OpenRecordset("SELECT max(fldDate)AS
MaxOffldDate FROM [tblPayDate]")

PayDate = Date

Select Case PayDate

Case Is < csdate, Is < rst.Fields("MaxOffldDate").Value

case else
exit sub

end select

Right now, I put the about code into all my paycode sub
to check sysdate. I don't think it is the right way to
do it. So, I want to create a sub or function that can
be called by any call to check the paydate is valid or
not.

Please help me out.

Thanks.

Tim.

 

[Marshall Barton]

I want to make the following code into a sub called
PayDate.

Const csdate = #12/1/03#

Dim PayDate As Date

Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String


Set dbCurrent = CurrentDb

Set rst = dbCurrent.OpenRecordset("SELECT max(fldDate)AS
MaxOffldDate FROM [tblPayDate]")

PayDate = Date

Select Case PayDate

Case Is < csdate, Is < rst.Fields("MaxOffldDate").Value

case else
exit sub

end select

Right now, I put the about code into all my paycode sub
to check sysdate. I don't think it is the right way to
do it. So, I want to create a sub or function that can
be called by any call to check the paydate is valid or
not.

I think what you are asking for is a function?

Const csdate = #12/1/03#
Public Function CheckPayDate()
Dim PayDate As Date
Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String

Set dbCurrent = CurrentDb
strSQL = "SELECT max(fldDate) AS MaxOffldDate " _
& " FROM [tblPayDate]"
Set rst = dbCurrent.OpenRecordset(strSQL)

PayDate = Date
Select Case PayDate
Case Is < csdate, Is < rst!MaxOffldDate
CheckPayDate = True
Case else
CheckPayDate = False
End Select
rst .Close : Set rst = Nothing
Set dbCurrent = Nothing
End Function

Then that function can be used in another procedure
something like this:

. . .
If CheckPayDate() Then Exit Sub
. . .

Note: I think your use of a recordset like this is
inefficient, but without a lot more information I can not
suggest anything better.

 

[Tim]

Marsh,

I tried the code but I got an error message: "Jet engine
cannot find table 'tblPayDate'. I know this happen
because I made this db as .mde and I called it from other
db to refer to it. Is it possible the code can refer to
tblPayDate that is in .mde?

By the way, how to change the following line to "and"
intead of "or"?

Case Is < csdate, Is < rst.Fields("MaxOffldDate").Value

Case Is < csdate AND Is < rst.Fields("MaxOffldDate").Value

Thanks a lot.

Tim.

-----Original Message-----

I want to make the following code into a sub called
PayDate.

Const csdate = #12/1/03#

Dim PayDate As Date

Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String


Set dbCurrent = CurrentDb

Set rst = dbCurrent.OpenRecordset("SELECT max(fldDate) AS
MaxOffldDate FROM [tblPayDate]")

PayDate = Date

Select Case PayDate

Case Is < csdate, Is < rst.Fields("MaxOffldDate").Value

case else
exit sub

end select

Right now, I put the about code into all my paycode sub
to check sysdate. I don't think it is the right way to
do it. So, I want to create a sub or function that can
be called by any call to check the paydate is valid or
not.

I think what you are asking for is a function?

Const csdate = #12/1/03#
Public Function CheckPayDate()
Dim PayDate As Date
Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String

Set dbCurrent = CurrentDb
strSQL = "SELECT max(fldDate) AS MaxOffldDate " _
& " FROM [tblPayDate]"
Set rst = dbCurrent.OpenRecordset(strSQL)

PayDate = Date
Select Case PayDate
Case Is < csdate, Is < rst!MaxOffldDate
CheckPayDate = True
Case else
CheckPayDate = False
End Select
rst .Close : Set rst = Nothing
Set dbCurrent = Nothing
End Function

Then that function can be used in another procedure
something like this:

. . .
If CheckPayDate() Then Exit Sub
. . .

Note: I think your use of a recordset like this is
inefficient, but without a lot more information I can not
suggest anything better.

 

[Marshall Barton]

I tried the code but I got an error message: "Jet engine
cannot find table 'tblPayDate'. I know this happen
because I made this db as .mde and I called it from other
db to refer to it. Is it possible the code can refer to
tblPayDate that is in .mde?

So the PayDate table is in a different file? Then change
your SQL to tell it what file it is in:

trSQL = "SELECT max(fldDate) AS MaxOffldDate " _
& " FROM [tblPayDate] IN ""C:\path\file"""

By the way, how to change the following line to "and"
intead of "or"?

Case Is < csdate, Is < rst.Fields("MaxOffldDate").Value

Case Is < csdate AND Is < rst.Fields("MaxOffldDate").Value

Since the select case only has two conditions, let's get rid
of it and shorten the code. Instead of all this:

Select Case PayDate
Case Is < csdate, Is < rst!MaxOffldDate
CheckPayDate = True
Case else
CheckPayDate = False
End Select

just use this:

CheckPayDate = PayDate < csdate _
And PayDate < rst.Fields("MaxOffldDate")
--
Marsh
MVP [MS Access]

-----Original Message-----

I want to make the following code into a sub called
PayDate.

Const csdate = #12/1/03#

Dim PayDate As Date

Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String


Set dbCurrent = CurrentDb

Set rst = dbCurrent.OpenRecordset("SELECT max(fldDate) AS
MaxOffldDate FROM [tblPayDate]")

PayDate = Date

Select Case PayDate

Case Is < csdate, Is < rst.Fields("MaxOffldDate").Value

case else
exit sub

end select

Right now, I put the about code into all my paycode sub
to check sysdate. I don't think it is the right way to
do it. So, I want to create a sub or function that can
be called by any call to check the paydate is valid or
not.

I think what you are asking for is a function?

Const csdate = #12/1/03#
Public Function CheckPayDate()
Dim PayDate As Date
Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String

Set dbCurrent = CurrentDb
strSQL = "SELECT max(fldDate) AS MaxOffldDate " _
& " FROM [tblPayDate]"
Set rst = dbCurrent.OpenRecordset(strSQL)

PayDate = Date
Select Case PayDate
Case Is < csdate, Is < rst!MaxOffldDate
CheckPayDate = True
Case else
CheckPayDate = False
End Select
rst .Close : Set rst = Nothing
Set dbCurrent = Nothing
End Function

Then that function can be used in another procedure
something like this:

. . .
If CheckPayDate() Then Exit Sub
. . .

Note: I think your use of a recordset like this is
inefficient, but without a lot more information I can not
suggest anything better.

 

[Tim]

Marsh,

Thanks for your help. I have another concerned. If
the "tblPayDate" table was deleted, then I would get an
error message that was not what I wanted. I want if the
table was deleted, then the code stil check csdate and
give me the result either 'TRUE' or 'FALSE'.

Could you tell me how to implement it?

Thank you very much.

Tim.

-----Original Message-----

I tried the code but I got an error message: "Jet engine
cannot find table 'tblPayDate'. I know this happen
because I made this db as .mde and I called it from other
db to refer to it. Is it possible the code can refer to
tblPayDate that is in .mde?

So the PayDate table is in a different file? Then change
your SQL to tell it what file it is in:

trSQL = "SELECT max(fldDate) AS MaxOffldDate " _
& " FROM [tblPayDate] IN ""C:\path\file"""

By the way, how to change the following line to "and"
intead of "or"?

Case Is < csdate, Is < rst.Fields("MaxOffldDate").Value

Case Is < csdate AND Is < rst.Fields

("MaxOffldDate").Value


Since the select case only has two conditions, let's get rid
of it and shorten the code. Instead of all this:

Select Case PayDate
Case Is < csdate, Is < rst!MaxOffldDate
CheckPayDate = True
Case else
CheckPayDate = False
End Select

just use this:

CheckPayDate = PayDate < csdate _
And PayDate < rst.Fields ("MaxOffldDate")
--
Marsh
MVP [MS Access]

-----Original Message-----
Tim wrote:
I want to make the following code into a sub called
PayDate.

Const csdate = #12/1/03#

Dim PayDate As Date

Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String


Set dbCurrent = CurrentDb

Set rst = dbCurrent.OpenRecordset("SELECT max(fldDate) AS
MaxOffldDate FROM [tblPayDate]")

PayDate = Date

Select Case PayDate

Case Is < csdate, Is < rst.Fields ("MaxOffldDate").Value

case else
exit sub

end select

Right now, I put the about code into all my paycode sub
to check sysdate. I don't think it is the right way to
do it. So, I want to create a sub or function that can
be called by any call to check the paydate is valid or
not.


I think what you are asking for is a function?

Const csdate = #12/1/03#
Public Function CheckPayDate()
Dim PayDate As Date
Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String

Set dbCurrent = CurrentDb
strSQL = "SELECT max(fldDate) AS MaxOffldDate " _
& " FROM [tblPayDate]"
Set rst = dbCurrent.OpenRecordset(strSQL)

PayDate = Date
Select Case PayDate
Case Is < csdate, Is < rst!MaxOffldDate
CheckPayDate = True
Case else
CheckPayDate = False
End Select
rst .Close : Set rst = Nothing
Set dbCurrent = Nothing
End Function

Then that function can be used in another procedure
something like this:

. . .
If CheckPayDate() Then Exit Sub
. . .

Note: I think your use of a recordset like this is
inefficient, but without a lot more information I can not
suggest anything better.

.

 

[Marshall Barton]

Marsh,

Thanks for your help. I have another concerned. If
the "tblPayDate" table was deleted, then I would get an
error message that was not what I wanted. I want if the
table was deleted, then the code stil check csdate and
give me the result either 'TRUE' or 'FALSE'.

Could you tell me how to implement it?

Use error trapping to check if the recordset can not be
opened.
--
Marsh
MVP [MS Access]

-----Original Message-----

I tried the code but I got an error message: "Jet engine
cannot find table 'tblPayDate'. I know this happen
because I made this db as .mde and I called it from other
db to refer to it. Is it possible the code can refer to
tblPayDate that is in .mde?

So the PayDate table is in a different file? Then change
your SQL to tell it what file it is in:

trSQL = "SELECT max(fldDate) AS MaxOffldDate " _
& " FROM [tblPayDate] IN ""C:\path\file"""

By the way, how to change the following line to "and"
intead of "or"?

Case Is < csdate, Is < rst.Fields("MaxOffldDate").Value

Case Is < csdate AND Is < rst.Fields

("MaxOffldDate").Value


Since the select case only has two conditions, let's get rid
of it and shorten the code. Instead of all this:

Select Case PayDate
Case Is < csdate, Is < rst!MaxOffldDate
CheckPayDate = True
Case else
CheckPayDate = False
End Select

just use this:

CheckPayDate = PayDate < csdate _
And PayDate < rst.Fields ("MaxOffldDate")
--
Marsh
MVP [MS Access]

-----Original Message-----
Tim wrote:
I want to make the following code into a sub called
PayDate.

Const csdate = #12/1/03#

Dim PayDate As Date

Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String


Set dbCurrent = CurrentDb

Set rst = dbCurrent.OpenRecordset("SELECT max(fldDate)
AS
MaxOffldDate FROM [tblPayDate]")

PayDate = Date

Select Case PayDate

Case Is < csdate, Is < rst.Fields ("MaxOffldDate").Value

case else
exit sub

end select

Right now, I put the about code into all my paycode sub
to check sysdate. I don't think it is the right way to
do it. So, I want to create a sub or function that can
be called by any call to check the paydate is valid or
not.


I think what you are asking for is a function?

Const csdate = #12/1/03#
Public Function CheckPayDate()
Dim PayDate As Date
Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String

Set dbCurrent = CurrentDb
strSQL = "SELECT max(fldDate) AS MaxOffldDate " _
& " FROM [tblPayDate]"
Set rst = dbCurrent.OpenRecordset(strSQL)

PayDate = Date
Select Case PayDate
Case Is < csdate, Is < rst!MaxOffldDate
CheckPayDate = True
Case else
CheckPayDate = False
End Select
rst .Close : Set rst = Nothing
Set dbCurrent = Nothing
End Function

Then that function can be used in another procedure
something like this:

. . .
If CheckPayDate() Then Exit Sub
. . .

Note: I think your use of a recordset like this is
inefficient, but without a lot more information I can not
suggest anything better.

.

 

[Tim]

Marsh,

Could you show me how to use "error trapping to check if
the recordset can not be opened" ?

Thanks a lot.

Tim.

-----Original Message-----

Marsh,

Thanks for your help. I have another concerned. If
the "tblPayDate" table was deleted, then I would get an
error message that was not what I wanted. I want if the
table was deleted, then the code stil check csdate and
give me the result either 'TRUE' or 'FALSE'.

Could you tell me how to implement it?

Use error trapping to check if the recordset can not be
opened.
--
Marsh
MVP [MS Access]

-----Original Message-----
Tim wrote:
I tried the code but I got an error message: "Jet engine
cannot find table 'tblPayDate'. I know this happen
because I made this db as .mde and I called it from other
db to refer to it. Is it possible the code can refer to
tblPayDate that is in .mde?

So the PayDate table is in a different file? Then change
your SQL to tell it what file it is in:

trSQL = "SELECT max(fldDate) AS MaxOffldDate " _
& " FROM [tblPayDate] IN ""C:\path\file"""


By the way, how to change the following line to "and"
intead of "or"?

Case Is < csdate, Is < rst.Fields ("MaxOffldDate").Value

Case Is < csdate AND Is < rst.Fields ("MaxOffldDate").Value


Since the select case only has two conditions, let's

get
rid

of it and shorten the code. Instead of all this:

Select Case PayDate
Case Is < csdate, Is < rst!MaxOffldDate
CheckPayDate = True
Case else
CheckPayDate = False
End Select

just use this:

CheckPayDate = PayDate < csdate _
And PayDate < rst.Fields ("MaxOffldDate")
--
Marsh
MVP [MS Access]



-----Original Message-----
Tim wrote:
I want to make the following code into a sub called
PayDate.

Const csdate = #12/1/03#

Dim PayDate As Date

Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String


Set dbCurrent = CurrentDb

Set rst = dbCurrent.OpenRecordset("SELECT max (fldDate)
AS
MaxOffldDate FROM [tblPayDate]")

PayDate = Date

Select Case PayDate

Case Is < csdate, Is < rst.Fields ("MaxOffldDate").Value

case else
exit sub

end select

Right now, I put the about code into all my paycode sub
to check sysdate. I don't think it is the right

way
to

do it. So, I want to create a sub or function that can
be called by any call to check the paydate is

valid
or

not.


I think what you are asking for is a function?

Const csdate = #12/1/03#
Public Function CheckPayDate()
Dim PayDate As Date
Dim dbCurrent As Database
Dim rst As Recordset
Dim strSQL As String

Set dbCurrent = CurrentDb
strSQL = "SELECT max(fldDate) AS MaxOffldDate " _
& " FROM [tblPayDate]"
Set rst = dbCurrent.OpenRecordset(strSQL)

PayDate = Date
Select Case PayDate
Case Is < csdate, Is < rst!MaxOffldDate
CheckPayDate = True
Case else
CheckPayDate = False
End Select
rst .Close : Set rst = Nothing
Set dbCurrent = Nothing
End Function

Then that function can be used in another procedure
something like this:

. . .
If CheckPayDate() Then Exit Sub
. . .

Note: I think your use of a recordset like this is
inefficient, but without a lot more information I

can
not

suggest anything better.
--
Marsh
MVP [MS Access]
.


.

.

 

[Marshall Barton]

Could you show me how to use "error trapping to check if
the recordset can not be opened" ?

Here's a general outline of catching an OpenrRecordset on a
non-existent table.

On Error GoTo ErrorHandler
. . .
Set rs = db.OpenRecordset( . . .
. . .

ExitHere:
On Error Resume Next
rs.Close: Set rs = Nothing
Set db = Nothing
Exit Sub

ErrorHandler:
Select Case Err.number
Case 3078 'Table doesn't exist
whatever you want to do here
Resume ExitHere
Case Else
MsgBox Err.number & " - " & Err.Description
Resume ExitHere
End Select
End Sub