Data entry form that opens to today's date

C

ChuckW

Hi,

I have a table called SalesLog that has records for every business day of
the week. Including the field BusinessDate, there are about 10 other fields
such as NumberCalls, NumberAppointments etc. I then have a form based a
query called SalesLogQuery that is a data entry screen with a row of text
boxes which represent a day. By using the navigation buttons a sales person
can view every business day of the year and enter in the appropriate data.
Right now, the form opens to 1/3/05 which was the first business day of the
year. I want it to open to today's date but I dont want to restrict it to
only showing today's date. Is there a way for the form to open to today's
date but still have all of the other records available in this data entry
form?

Thanks,
 
A

Al Camp

Try sorting on the Date ascending, and assuming that you have no other
code that might interfere...
DoCmd.OpenForm "frmYourForm"
DoCmd.GoToRecord , , acLast
would send you to the last record created.

If you haven't created a record for today, this will send you to
yesterday's record. Once you have today's record, each time you open the
form, you'll go right to the that Last (latest Date) record.
If you were able to browse all records in the past... you still can with
this code.
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
 
C

ChuckW

Al,

Thanks for your help. Does the code you are suggesting need to go into the
criteria section of my query that runs the form or does it go in the vba
editor for my form?

Thanks,
 
A

Al Camp

Chuck,
Do the Date sorting in the query behind your form.
The code would go behind a button you might use to open the form... like
from a MainMenu form.
If you open the report by just dbl-clicking the form, then I think...
DoCmd.GoToRecord , , acLast
in the OnOpen event of the form should do it.
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
 
C

ChuckW

Al,

I added the code you suggested to a command button that opens the form and I
sorted the query in acsending order. However, when I open the form, it
always opens to 12/31/05 which is the last record in the table. I have tried
editting the form and adding data to a certain date such as 6/1/05 but when I
open the form again it still opens to 12/31/05. One other factor is that
there is some additional information in this table. For instance, I built
this table in Excel and added fields such as DateOfWeek and WeekNumber. I
then imported this into access. So this data appears in all records of the
table. I don't know if that matters. Any ideas as to why this won't open to
the last day that I entered information?

Thanks,
 
A

Al Camp

Chuck,
Pretty hard to tell without seeing the data... but as long as your not
sorting on those DateOfWeek and WeekNo fields, it shouldn't matter...

Did you remove all other sorting in the query... except your date field?
In your query, drag your date field to the very left on the design grid.
Is there any sorting code in the code behind the form? Check all you
form code.

I just went to my Address table that is usually sorted by Last/FirstName.
I changed the sort to the DOC (DateOfCreation) field to ascending, and
added...
Docmd.GoToRecord , , acLast
to the OnOpen event of the form. Whenever the form is opened, the record
with the last DOC is diplayed.

Drop the GoToRecord code from your button, and try it in the OnOpen
event of the form.

The "process" we discussed is correct... it seems as though there may be
something interfering with our sort.

Still stuck? Send me the mdb with just the tables and form that are
needed to run, via my web site "Contact". I'll try to find the problem...
no charge of course. All data will be considered confidential...
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
 
D

Dan

If you're opening to 12/31/05 then you have records beyond today in the
table. In the OnOpen event I would set focus to the date field, find today,
then set focus to the first field in the tab order. You may want to put some
code in to handle the case where today doesn't exist.
 
C

ChuckW

Dan,

Thanks for your help. I actually created the table in Excel with records
for every business day of 2005. Sales reps will use this application, and
either every day or once a week, they will fill out how many phone calls they
made, how many appointments that obtained etc. It isn't practical for them
to create the records since they might mess up the date - especially if they
enter information in once a week.

I am a novice access user. Can you give me more details on what you mean?
On the form properties, I clicked the down arrow next to On Open and a list
of macros appeared. I then used the expression builder to find my date field
and selected that to appear in the On Open line but I receive an error
message when I try to open the form. This form is based on a query that is
based on a table which has about 250 records (one for every business day). I
want the form to open to today's date but I dont want it restricted to this
date. Can you help?

Thanks,
 
D

Dan

Chuck,
Next to OnOpen you want the [Event Procedure] selection. Then click on the
button with three dots. This should bring you to a Visual Basic screen where
you'll see:

Private Sub Form_Open(Cancel As Integer)

End Sub

Between the Private Sub and the End Sub add these lines

Me!BusinessDay.SetFocus
DoCmd.FindRecord Date
Me!FirstFieldInTabOrder.SetFocus

replace BusinessDay with the field name for Your date field,
replace FirstFieldInTabOrder with the actual name of the first field.

it should leave you in today's record.
 
Top