check to see if a current date exists before creating a record

[Steve]

I'm designing an Access Database with multiple forms. a [Log On] form allows
users to choose options such as Create a new log and Open an existing log
among other choices. This Log keeps track of all the days processes for each
day. Currently, I can open a new log and it creates a new page with the
current date(Which is the primary key). I can save all the info and
everything works fine. However, If I go back to the Log on form and choose
Create a new log again, it starts another log with the same date instead of
opening the already existing log with that date. This creates a Duplicate
that I have been trying for days to write the proper code or query or macro
that would check if the date already exists before opening a new page.

I'm using Access 2003

 

[Tom van Stiphout]

On Sun, 18 Jan 2009 16:06:12 -0800, Steve

Since you mentioned that the data is the PK, you should have said it
TRIES to create a duplicate. Fortunately the unique index stops this
from happening.
I am assuming that "Create new log" always creates a log for today's
date. You can simply make that button unavailable if today's log has
already been created. In the Form_Load write:
if dcount("myDateField", "myTable", "myDateField=#" & Date & "#") > 0
then
Me.myNewLogBuoon.Enabled = False
end if
(of course you need to substitute your object names)
Note how I am wrapping the date value in #-signs, like you should for
date parameters.
The DCount function is in the help file. It counts how many records
exist with the given date.

-Tom.
Microsoft Access MVP

 

[Steve]

Ok, I tried that code in the Form_Load and it's still not working. Maybe I'm
not putting in the right field names or not doing something right. This is
what I have.

Table: Daily_Ops_Schedule (This is the Main Table that stores all the data)
OpsDate (Primary Key of Daily_Ops_Schedule - Can only have 1 Date for each
day)

Form: LogOn (The 1st Form that appears with option buttons to choose from.)

Option_Frame (The named of the option box with the following options)
Create_New_Log (1st option button, value=1) - creates a new log with
today's date
Update_Log (2nd option button, value=2) - opens the last log in the record
which should be today's date if one exists, if not, it opens the previous days
search_logs (3rd option button, value=3) - still working on this
create_special_grocery_log (4th option button, value=4) duplicate date not
important
create_special_fresh_log (5th option button, value=5) duplicate date not
important
create_special_gm_log (6th option button, value=6) duplicate date not
important

Continue_Button (Command Button - Runs which ever option was selected)
If [Option_Frame].Value = 1 Then DoCmd.OpenForm ("daily_ops_schedule"):
DoCmd.GoToRecord , , acNewRec: Form_Daily_Ops_Schedule.Ops_Date =
Form_LogOn.LogOn_Date

If [Option_Frame].Value = 2 Then DoCmd.OpenForm ("daily_ops_schedule"):
DoCmd.GoToRecord , , acLast

The Continue Button works just fine, as it opens form: Daily_Ops_Schedule
and creates a New Record with the current date (Option button 1) or goes to
the last record (option button 2), or opens the other special forms which
works no problem

It's just not disabling "create_new_log" option button if the date already
exists in "Daily_Ops_Schedule" table

Here is how I coded it from your example:
If DCount("opsdate", "daily_ops_schedule", "opsdate=#" & Date & "#") > 0
Then Me.create_new_log.Enabled = False

On Sun, 18 Jan 2009 16:06:12 -0800, Steve

Since you mentioned that the data is the PK, you should have said it
TRIES to create a duplicate. Fortunately the unique index stops this
from happening.
I am assuming that "Create new log" always creates a log for today's
date. You can simply make that button unavailable if today's log has
already been created. In the Form_Load write:
if dcount("myDateField", "myTable", "myDateField=#" & Date & "#") > 0
then
Me.myNewLogBuoon.Enabled = False
end if
(of course you need to substitute your object names)
Note how I am wrapping the date value in #-signs, like you should for
date parameters.
The DCount function is in the help file. It counts how many records
exist with the given date.

-Tom.
Microsoft Access MVP

I'm designing an Access Database with multiple forms. a [Log On] form allows
users to choose options such as Create a new log and Open an existing log
among other choices. This Log keeps track of all the days processes for each
day. Currently, I can open a new log and it creates a new page with the
current date(Which is the primary key). I can save all the info and
everything works fine. However, If I go back to the Log on form and choose
Create a new log again, it starts another log with the same date instead of
opening the already existing log with that date. This creates a Duplicate
that I have been trying for days to write the proper code or query or macro
that would check if the date already exists before opening a new page.

I'm using Access 2003