Check Date in PayCode.mde

  • Thread starter Thread starter Tim
  • Start date Start date
T

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.
 
Tim said:
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.
Click to expand...


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.
 
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-----
Tim said:
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.
Click to expand...


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.
Click to expand...
 
Tim said:
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.
Click to expand...

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 said:
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.
Click to expand...
Click to expand...
Marshall said:
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.
Click to expand...
Click to expand...
 
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-----
Tim said:
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.
Click to expand...

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,
Click to expand...
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
Click to expand...
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.
Click to expand...
Marshall said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Tim said:
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?
Click to expand...


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.
 
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.
 
Tim said:
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.
Click to expand...


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,

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-----
Tim said:
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.
Click to expand...


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.
Click to expand...
 
Tim said:
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?
Click to expand...

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
Click to expand...


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 said:
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.
Click to expand...


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.
Click to expand...
Click to expand...
 
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-----
Tim said:
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?
Click to expand...

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
Click to expand...
("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.
Click to expand...
Click to expand...

.
Click to expand...
 
Tim said:
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?
Click to expand...

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


-----Original Message-----
Tim said:
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?
Click to expand...

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
Click to expand...
("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.
Click to expand...

.
Click to expand...
Click to expand...
 
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-----
Tim said:
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?
Click to expand...

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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
can
not
suggest anything better.
--
Marsh
MVP [MS Access]
.


.
Click to expand...
Click to expand...

.
Click to expand...
 
Tim said:
Could you show me how to use "error trapping to check if
the recordset can not be opened" ?
Click to expand...


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
 
Back
Top