update record via form

[abernut]

First let me start by thanking everyone that has helped me with this every grown task I have been assigned.

I can almost see the light at the end of the tunnel.

I had a meeting today to go over it, since I thought we were finished and guess what I heard.... Can we add this, can you make it do that, etc...

This data base keeps tracks of our Engineering Molds (The request to use them and the maintenance done on them)

Here is a sample of my current DB:

Molds
Mold_no (PK)
Part_Name
Part_No

MoldReq
Work_Ord (PK)
Mold_No (FK)
Req_Comments

MoldLog
Log_ID(PK)
Work_Ord (FK)
Log_Comments


They now want to keep track of the status of the mold. Which can be:
Ready for Production (Mold sitting on the shelf somewhere)
Needs Maintenance


Being Repaired
Being Revised
In Process/Production (Being used somewhere)

I was thinking instead of creating another table called MoldStatus I would just add a new column to the Molds Table called status.
When the user fills out the Mold Req Form they can set the status to "In Process/Production".

After that mold is used it goes to our tooling Department for maintenance and which time they would change the status to one of the other options on their Mold Log Form.

All of this also need to be reportable, ie...Which molds are in Prodcuction, etc...

[pbaldy]

Sounds like you have the right idea; a field in the mold table to store its current status, and a table for the status choices.

By the way, this:

Can we add this, can you make it do that, etc...

Is what we call "job security".

[abernut]

Sounds like you have the right idea; a field in the mold table to store its current status, and a table for the status choices.

By the way, this:

Can we add this, can you make it do that, etc...

Is what we call "job security".

You data base people call it job security, I call it frustration

So back on topic.
So I create a new table.(did I mention this started off as a two table db and now I am up to 9 )

MoldStatus
Status_ID (PK)
Status_Type


I would then add a foreign key "Status_Type" to the MoldReq table and the the MoldLog table.

[pbaldy]

First, I would store the ID value in other tables, not the text. Second, the only table I'd expect to see the status in would be the table that holds the information about the mold. Status is an attribute of the mold. That is unless one of those other tables is like a history of what was done with the mold, and you wanted the status at each step as part of the history.

[abernut]

We need to track it at each stage. For example if the molding department tries to pull a mold that is in a Repair status they will get some sort of error.

[abernut]

Had another meeting today. Here is what was discussed.

The possible statuses are:
1. In Process/Production (It is being used by the plastics department)
2. Being Repaired
3. Being revised
4. Needs Maintenance
5. Ready for Production (Sitting on a shelf waiting to be used)

So now when they plastics department fills out the MoldReq form there should be a spot that lets them select “In Process/Production”. If something happens and they need to stop they can go in and change it to “Needs Maintenance”.

Once the Tooling department receives the Mold they open their MoldLog form and change it to “Being Repaired” or “Being Revised”. Once they are done they can then set the status to “Ready for Production”.

The plastics department should also not be able to select any mold to use unless it is in a “Ready for Production” state.

[pbaldy]

Maybe an app mine is similar. It's for vehicle maintenance, and includes a repair order table. That table has a status field, which they can change as appropriate. I also have an audit table which keeps track of any changes in the status. I add a record for each change with a function:

Public Function InsertAudit(RONum As String, ROStatus As String, UserName As String) As Boolean

  CurrentDb.Execute "INSERT INTO tblROAudit(RONum, ROStatus, ActionDateTime, UserName) " _
                  & "VALUES('" & RONum & "', '" & ROStatus & "', Now(), '" & UserName & "')"

End Function

In your case I see the status field in the molds table. You can certainly add a test so users can only select a mold if it has a certain status.

[abernut]

Maybe an app mine is similar. It's for vehicle maintenance, and includes a repair order table. That table has a status field, which they can change as appropriate. I also have an audit table which keeps track of any changes in the status. I add a record for each change with a function:

Public Function InsertAudit(RONum As String, ROStatus As String, UserName As String) As Boolean

  CurrentDb.Execute "INSERT INTO tblROAudit(RONum, ROStatus, ActionDateTime, UserName) " _
                  & "VALUES('" & RONum & "', '" & ROStatus & "', Now(), '" & UserName & "')"

End Function

In your case I see the status field in the molds table. You can certainly add a test so users can only select a mold if it has a certain status.

Only stuck the status field there because I wasn't sure where else to put it, so if it is easier to accomplish this I am up for ideas. Functions might be a little out of my grasp right now