Ask Me Help Desk - Forms in MS Access

I setup a table with All of the different fields we need to keep track of maintenance in access, and setup various forms depending on what piece of machinery the maintenance is being done on. The problem I'm having is getting the form to load or keep track of the data. I'm not very familiar with Access, so it could be a simple mistake. I made the ID field in the database Date/Time... I figured since I'm keeping track by date, this would be OK. When I select the first form and check what we've done maintenance on, everything works fine. If I open another form, select the same date in the ID field, it does not load the same record though and does not track the data I entered.

Anyone know what I'm doing wrong?

There could be several things wrong here.

Quote:

If I open another form, select the same date in the ID field, it does not load the same record

First, you do not have fields on a form. You have Controls that may or may not be bound to a field in a table. This is a subtle but important distinction.

You say you "select" the date. Is the control where you select the date a search combo? Did you create it using the combobox wizard? If not, then all you are doing is adding a new record for the date and that may not work if the date is considered the primary key.

Does the form open to a blank record? If not, then all you may be doing is changing the date on that record. If the form opens to a blank record, then you may have the Data Entry property set to Yes. This is a poorly named property, setting it to yes means the form can only be used to enter new record.

Quote:

I made the ID field in the database Date/Time... I figured since I'm keeping track by date, this would be OK

This might be OK if the date would be unique for each record. This means you could only enter one maintenance per day. Or that you don't have a primary key for your table.

Quote:

I setup a table with All of the different fields we need to keep track of maintenance

You setup a single table? If so, then your database is not properly designed. You need to do some research on the concept of Normalization a key principle in the design of relational databases. I would guess that you probably need at least FOUR, probably more, tables.
tblMachinery: info about each machine
tluMaintenanceTasks: The specific maintenance tasks performed
tblMaintenance: The date and machine being maintained
tblMaintTasks: A list of all the tasks performed as part of a single maintenance event

If you want some more help with this, it would help if you could zip and upload a copy of your database with just some sample data, so we can take a look.

Thanks for your help Scott. (You always seem to be the one to answer my questions) :) A lot of your questions I'm not really sure how to answer... The ones that I do know are, I do not have the form creating a new record each time and I did not create a combo box for selecting the date. When I click the field, it gives me an option to pop up a mini calendar and I select it there... I'm guessing that is wrong... I've attached my database, any tips/help is greatly appreciated!

Thanks!

Also, I'm not sure if it effects how I should create the database... but once I get this portion working, I'm going to create reports to show when things were done, and when things are due...

Also, I'm not sure if it changes how I should set it all up, but once this part is working correctly, I'm going to make reports to show when things have been done, and when they are due...

Ok, As I suspected you are committing a frequent mistake that newbies make. While it is easier from a user interface standpoint to create a table with a bunch of yes/no fields and then have a form where you just check off the items you want, this is not good database design. As I said you should have one table that lists all your machines. The topics table seems to come close but it appears to include general tasks like Clean & Regrease. If you want to use the Topics table to categorize the Checklist items, that's fine.

the way I would do this, it have a table like so:

tblDailyMaintenance
DailyMaintenanceID (Primary Key) Autonumber
MaintDate
MaintTopicID (Foreign Key)

You may need other fields in this table like who performed the maintenance or when completed.

You then need a table like:
tluCheckList
ChecklistID (PK Autonumber)
ChecklistItem

This table would have a value in the ChecklistItem for each field you have in your Maint table (except date)

next you have a table like this:
tblMaintChecklist
MaintChecklistID (PK Autonumber)
DailyMaintenanceID (FK)
ChecklistID (FK)

Again you may need other fields about each item. You can set up cascading comboboxes to filter the Checklist items by the topic for the selected maintenance.

As I said, proper table design is the foundation of an efficient database. Once you properly design the tables, then you can start on forms and reports. I can help you as you go along.