specific equipment list for different projects

[lavey]

Hi there,
I'm creating an equipment database and have ran into some problems creating it. This is an Engineering oriented Database
MS Access 2010
Here is the basic idea of how I want it to pan out.
Design:

- Users enter a main menu where they have the option of creating a new project, or selecting an existing project and viewing/printing/editing information in that project - I've created the button that leads to a form where I can create a new, all is left (I believe) is just making this work perfectly

- Once the user has selected the project (combo box to select project and press button to go next) A page shall appear that will display ALL Project information for ONLY the specific project that was chosen. On this page I want there to be another button that will lead to an "equipment list" which is specific for this project only. Eventually the user should be able to add/edit/delete data, search for data etc (all the normal functions that a database should have)


Question:

- When the user has selected his project and is now linked to this next form, I want there to be an "Equipment list" button which leads to an equipment list. I have imported an equipment list from excel and created a form etc. How will I be able to make it for that equipment list to be populated specifically for just that one project?
example: I select project number S111 and then click on an equipment list which will lead to a split form where I can populate the equipment list. But then I want to go back to main menu and select project number S888 and then go on to populate the equipment list for that project. However the data in this equipment list is isolated just to this project number.

Will I need to create separate tables per project or is there some simple method?.. sorry hard to explain this one

let me know if you need any more info or overview

Thanks

[Seth Schrock]

It sounds like you might need to look into Database Normalization. No you wouldn't want to have a separate table for each project. You would want a separate table for your equipment list information. This table would have a field (called a foreign key) that would link to your projects table so that you would know which piece of equipment is needed for each project. If you have times where you have the same piece of equipment being used for multiple projects, then you would have to create a join table for this many-to-many relationship. Once you have your tables setup, then you would just provide the filter when you open your equipment list form to be just the currently selected project. If you need to be able to add equipment to the list, then there is a little more involved.

[lavey]

Yeah I would need to be capable of adding equipment to this list as the user needs. Sometimes all the information wouldn't be available immediately and calculations would change etc. How would I go about doing it if that's the case?
Thanks Seth

[Seth Schrock]

How I normally do it would be to use your equipment form's OpenArgs property to pass the value of the project ID from the projects form to the equipment form. I then have a hidden textbox on the equipment form that is bound to the foreign key field and I use the form's OnLoad event to set this textbox's Default Value property to what is in the OpenArgs property. Then, when I add equipment it gets the proper foreign key value.

[lavey]

could you please have a look at the attached picture and try walking me through that process if you're capable of doing so?
I'm unsure on how to progress completely and don't want to mess it up.

Thanks

[Seth Schrock]

Now you just need the link between Project Details and Equipment List. If one piece of equipment can only be attached to one project, then you just need to add a ProjectID field to Equipment List. If the same piece of equipment can be attached to multiple projects, then you need to setup a join table, like you have done with Equipment Location.

[lavey]

I've googled setting up appropriate join tables etc, but i can't quite figure out how to do one specifically for this database?

Also another problem I just randomly noticed. My equipment list form won't let me enter any values to any of the text boxes or use any of the combo boxes. I have the primary keys and all set up like above (where im about 90% sure it was working when i took that screenshot) There are no queries associated to the form that I recall creating, and all permission are set to yes/unlocked etc.

Unless you got some quick suggestion for this un-enterable data, i'll make sure I make a new thread, just didn't want to flood the page with qestions :)

Thanks

[Seth Schrock]

First, are you having the same piece of equipment attached to multiple projects or not?

For my question suggestion, make sure that Allow Edits and Allow Additions in the form property are set to YES. Otherwise, you will need to post a new question.

[lavey]

Yes, same type of equipment for different projects, but not physically the same equipment. so, the tag numbers (m_EquipmentTag ) will vary for the same type of equipment for different projects. For example, two Vessels with the same dimensions can be called 100-VD-001 for one project but on another project it could be 300-VD-001. So that being said, the same 'type' of equipment can be on different projects but with different tagging philosophies.

However it would still be the same type of equipment (m_EquipmentTyp e) ie. Vessel, heat exchanger, pump etc etc.

so:
- Same type of pump with same dimensions etc
- But will physically be two separate pumps
- will have different tag numbers

Thanks

PS. For the form problem, i've checked that and all is in order, so I'll continue searching for an answer and I don't get one I'll make a question. Thanks again

PPS. The problem for the form was that it was a split form? Why can't I enter data when it's a split form? weird.

[Seth Schrock]

Then you just need to add a field to your equipment table of the same data type as your primary key field in your projects table (Auto Number = Number - Long Integer). You then setup the join just like you have on all the other relationships.

[jforbes]

As for your PPS on Post #9, a Split Form view is not programmed as solidly as the other Form Views. They are in practice two separate Forms that are loosely linked by Access and their behavior isn't always what you'd expect. For example, the two Forms have separate copies of Form Variables, so if in code a variable is set in the Datasheet instance, the Form instance of the variable will not change.

I'm not sure exactly what you are experiencing, but I would use a Split Form view with caution.

[lavey]

Thanks for that guys.

One last thing. Seth, how do I go about assigning the OpenArgs property to pass the value of the ProjectID?

[Seth Schrock]

One of the parameters of the DoCmd.OpenForm command is the OpenArgs parameter. When you use this command, just add commas at the end until Intellisense highlights the OpenArgs parameter and then you can enter Me!ProjectID.

[lavey]

Hi Seth I ran into a small problem.

I have the following code as my button to link me to the equipment list form

DoCmd.OpenForm "Equipment List", acNormal, , "[p_ProjectID]= " & p_ProjectID

Now I think I will need to write the OpenArgs expression for the OnLoad event in the equipment list form. I have the following to try fetch the projectID but it is not working, could you edit it appropriately, please?

Me.OpenArgs Me!p_ProjectID

Thank you

[Seth Schrock]

Use the following to open your form:

DoCmd.OpenForm FormName:="Equipment List", _
               View:=acNormal, _
               WhereCondition:="[p_ProjectID]= " & p_ProjectID, _
               OpenArgs:=Me!p_ProjectID

In the form's OnLoad event, you can get the value of the OpenArgs property like this:

Me.txtProjectID.DefaultValue = Me.OpenArgs

Notice that I use the default value property. This makes it so that it doesn't automatically create a new record, but any new record gets this value.