One form or the other depnding on certain criteria

[Tara]

I have a command button that runs a query then opens a form. I would like to
have the code first evaluate today's date and then evaluate a field
(crntAvail) in a table (tbl125) for certain criteria, then determine whether
to open this form or another. If the today's date is greater than 03/15 (of
any given year) AND/OR if the value in the field crntAvail is 0, then open
one form, otherwise, open the 2nd form. The current code is:

Private Sub CmdOpnVoucher_Click()

DoCmd.OpenQuery "quptbl125ytd"

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmEmployeeContrib"


DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks,

 

[Marshall Barton]

I have a command button that runs a query then opens a form. I would like to
have the code first evaluate today's date and then evaluate a field
(crntAvail) in a table (tbl125) for certain criteria, then determine whether
to open this form or another. If the today's date is greater than 03/15 (of
any given year) AND/OR if the value in the field crntAvail is 0, then open
one form, otherwise, open the 2nd form. The current code is:

Private Sub CmdOpnVoucher_Click()

DoCmd.OpenQuery "quptbl125ytd"

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmEmployeeContrib"


DoCmd.OpenForm stDocName, , , stLinkCriteria

If Format(Date, "mm dd") > "03 01" _
OR DLookup("crntAvail","tbl125",{certain criteria}) = 0 _
Then
stDocName = "this form"
Else
stDocName = "another form"
End If

 

[Tara]

Thanks Marshall. I tried your suggestion, but when I click the command
button, nothing happens. I realized however that I left out an important
part in my first post and I tried to adapt your suggestion to fit, so I'm
sure that's why it didn't work.

The part I neglected to mention was that on the form with the Command button
is a combo box with EmployeeID's. The field crntAvail needs to be filtered
using the ID the user chooses in the dropdown. So, basically I need to look
at the record in tbl125 that belongs to the Employee chosen in the combo, see
if they have a value greater than 0 in the field crntAvail, evaluate the
date, and then open one form or the other.

Sorry for not giving you the whole picture. I didn't even think of it as I
was writing out my post. Thanks for any other help you can give.

 

[Marshall Barton]

I think(?) that means {certain criteria} should be:

"EmployeeID=" & Me.[the combo box]

 

[Tara]

It worked perfectly! Thanks so much!

I think(?) that means {certain criteria} should be:

"EmployeeID=" & Me.[the combo box]
--
Marsh
MVP [MS Access]

Thanks Marshall. I tried your suggestion, but when I click the command
button, nothing happens. I realized however that I left out an important
part in my first post and I tried to adapt your suggestion to fit, so I'm
sure that's why it didn't work.

The part I neglected to mention was that on the form with the Command button
is a combo box with EmployeeID's. The field crntAvail needs to be filtered
using the ID the user chooses in the dropdown. So, basically I need to look
at the record in tbl125 that belongs to the Employee chosen in the combo, see
if they have a value greater than 0 in the field crntAvail, evaluate the
date, and then open one form or the other.

Sorry for not giving you the whole picture. I didn't even think of it as I
was writing out my post. Thanks for any other help you can give.