Please help me set up my tables for new database

[jzwp11]

I reworked a couple things I got the subform working the way I wanted. Instead of directly linking the subform to the main form via the asset type, I just changed the recordsource of the form to reference the asset type combo box. The revised database is attached. I saved it in an Access 2003 format so that I could work on it here at work.



BTW, the reason the form crashed on you is because you had the main form's record source as a query that had many tables rather than just the asset table. You will have to replace a few of your controls with combo boxes (those with name#). You cannot have those as fixed values since the users will have to select the appropriate values when they are adding new records.

[10 Gauge]

When setting up the combobox for Models, how do I set up the control so that it only shows a list of models for the MakeID selected, or is that not possible?

[jzwp11]

You need a technique called cascading combo boxes. There are many resourses on-line that discuss the topic. Here is one

[10 Gauge]

Perfect thank you, I got that to work beautifully.

I am still trying to figure out what we have going on for the AssetPMSchedule subform.

Edit: After looking at it some more, the AssetType should automatically be selected based off of the main form, so I don't think that needs to be visible? And then when you change the PMSchedule number it just changes that number on the field with whatever is showing in the subform below it. Rather shouldn't it be a combobox displaying the available PM levels and when a number is chosen it filters the subform below it to show the PMTasks associated with the selected PMSchedule #?

[10 Gauge]

Good morning jzw! I am done with my little side project in the office and I am back to working on the database.

My first question is, for the PM Tasks area, how do I set that up so that PMSchedule queries or filters the data in the subform below it, instead of setting the value for what's being displayed. I have converted it to a combobox but that's not right I don't think, or maybe it is but I need to add further conditions to make it do what I am trying to do.

TIA, look forward to continuing this project with you.

Edit: I've almost got it figured out. I have it filtering properly now as a combobox but now having a problem with the combobox showing only the PMSchedule's associated with the selected AssetTypeID. Currently it's listing all PMSchedule's no matter what AssetTypeID they are.

Edit 2: Nevermind, I thought I had it doing what I wanted it to, but I didn't. Still broke. bleh.

[jzwp11]

As it has been a few days and other things have crowded my brain, can you explain in a little more detail what you want to do?

Also, I had a thought the other day and I don't know if I had mentioned it in a post. It deals with the interval at which the PM is to be done. At present we have the interval associated with the task, but I think that is the wrong place because the task depends on the schedule and the schedule depends on the interval, so I think the interval should be in AssetTypePMScheduleNames rather than PMScheduleTasks.

[10 Gauge]

I'm going to have to let my brain absorb that one for a second and see if I can make sense of that and how it would change by doing that.

In the meantime... What I am trying to do, is in the PM Tasks subforum, be able to choose the PM # from a drop down box and then have it display (in another subform) the PM Tasks that need to be completed for that PM #. The kicker is that the PM #'s need to be inherited based upon AssetTypeID (trucks are going to end up having 5 pm schedules, everything else will have 4). Let me show you what I've got, at the moment it's a bit fubarred but I think I'm on the right track.

[10 Gauge]

Any luck with getting the PM Tasks to populate properly? I worked on it all day yesterday and couldn't get it to work quite right.

[jzwp11]

The attachment only had the ldb file and not the mdb file. Can you repost the attachment?

[10 Gauge]

My bad! Here it is....

[jzwp11]

Regarding the PM form/subform you had on the Asset detail form, I would probably create those separately from the Asset detail form, since the PM schedules will probably not change once you have initially defined them. What I would opt for is the combo box you mention with a list box that displays the tasks for that PM #. Of course the PM# is dependent on the asset type, so you have to update the PM combo box based on the asset type. So in essence you have cascading combo boxes (asset type combo box-->PM combo box--->TaskList box). I have taken care of that in the attached DB.

As to your PM history subform I removed that because I saw that you had the PMNextDueUsage and PMNextDueNumber controls that correspond to fields in the PMEvents table. Since these two values can be determined or calculated (using a query or some functions), the values should not be stored in the table, but rather calculated when you need them. You will need a query that looks at the intervals at which each PM needs to be done and compare to the currrent usage & when the last PM was done. This will get somewhat involved, and is also why the interval must be tied to the PM # not the individual task of a PM#.