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_EquipmentType) 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.

[lavey]

Thanks for that,

I'm getting a "method or data member not found" error for this:

Me.txtProjectID.DefaultValue = Me.OpenArgs

Do I have to state these methods somewhere first before hand?

[Seth Schrock]

Did you put this in the Equipement List form's OnLoad event?

[lavey]

yeah I sure did, not sure why its happening.. Do I need to state the method somewhere before calling it?

the txtProjectID is being highlighted as the error. I change it to p_ProjectID and then the DefaultValue gets highlighted as the error?

[Seth Schrock]

Lets try this.

Dim lngProjectID As Long
lngProjectID = Me.OpenArgs
 
Me.txtProjectID.DefaultValue = lngProjectID

This will help me know which part is failing.

No you don't have state the method before calling it.

[lavey]

highlighted is .txtProjectID under the Private Sub Form_Load() event.

[Seth Schrock]

So it sounds like a control name has been mistyped. Make sure that txtProjectID is the name of the control (not the control source) of the textbox in the Equipment List form.

[lavey]

Same as before, when I fix this issue .DefaultValue = becomes the next error in this situation? :S

my full code for this:

Private Sub Command66_Click()
DoCmd.OpenForm FormName:="NavEquipmentList", _
               View:=acFormAdd, _
               WhereCondition:="[p_ProjectID]= " & p_ProjectID, _
               OpenArgs:=Me!p_ProjectID
End Sub

I changed acNormal to acFormAdd just to try make it that when the form loads that the fields are empty and don't include the first record in the table - this didn't work.
changed it back to acNormal and the current issue still persists
and then:

Private Sub Form_Load()
Dim lngProjectID As Long
lngProjectID = Me.OpenArgs
 
Me.txtProjectID.DefaultValue = lngProjectID
End Sub

I have a text box hidden and the control name is "txtProjectID"

[jforbes]

You might want to try and trap for Null values. Also you can use a Debug.Print to find out what is going on (you should remove or comment them after your code is working well):

Private Sub Form_Load()
   Dim lngProjectID As Long
   Debug.Print "NavEquipmentList.OpenArgs: " & Me.OpenArgs
   lngProjectID = Nz(Me.OpenArgs, 0)
   If lngProjectID <> 0 Then Me.txtProjectID.DefaultValue = lngProjectID
End Sub
 

I would also suggest setting breakpoints and stepping through the code to see what the variables contain and what is actually happening.

I don't usually set the Form view to acFormAdd and supply a Where Clause as these really are two different ways of using a Form.

Additionally, if you provide a Form with a Where Clause that returns no records on a Form with Additions set to False, your Form can act quite strange. You might not be experiencing this, yet. But it looks like you might be getting close.

[Seth Schrock]

If nulls were the problem, then the error would occur when Me.OpenArgs is assigned to the variable. Instead the error is occuring later when the textbox is being referenced. I'll admit that I'm stumped. This is something that I have done many times and I have never had any trouble with it.

[jforbes]

It's possible the Form is open in Design View. When testing this out, you'll want to Save the NavEquipmentList Form and Close it. I think if this is not done, there is a way to get a Null Value in there. Just a thought.

[lavey]

Even with your method jforbes its still .DefaultValue = as the error - this is weird.. I can't find a similar situation while searching the net also. I guess I'll continue playing around with it and maybe find something

[jforbes]

Then, most likely, Me.txtProjectID is not a valid TextBox control on the Form. Which is what Seth was talking about in Post #21, which gives the error "method or data member not found".

Try dropping a brand spanking new TextBox on the Form and see if you can set it's Default Value.