Database for Equipment associated to specific projects.

[lavey]

Hi there,

Before I start I would like to say that I have very basic knowledge in access (touched base on it high school around 6 years ago) and want to get better at using and creating a database. I am using MS Access 2010 edition.

I've tasked myself to make some sort of database so that a user can input data into the database and it will slowly populate it. For now, I've mapped it out very basically but I want to expand it sufficiently as I progress and as my knowledge of access develops.
1. I want the initial thing the user see's is a form where they can select what project they want to access (also if it is a new project they can add it to the database themselves). Project Details - For some reason in my relationship here I can't get my form to display data that i have entered in the corresponding tables that are linked.


2. Once the project is selected I want it to lead to another type of form where user can select what they would like to do next. For example, they can click on a "mechanical list" and it will link the user to an equipment list for mechanical equipment only, and if they select "electrical list" then it would link them to a electrical list only and so forth.
3. Once the user has selected what action/list they want, the user should be able to input data and select data from list/combo boxes. I have imported an example equipment list and started to make relationships for that. The user should be able to add to / edit the data they have previously entered (as more data becomes available as a project progresses)
4. Eventually, I want all the lists to be related to one another. For example, If I select a specific pump from a list (I will select he associated tag number) I want to be able to bring up all corresponding information. So perhaps all the information from its own specific list will be displayed, but there will be an option (list/combo box or button) that will link me to other disciplinary lists (electrical, piping etc) that will display ONLY information specific the that Tag Number. - This is the final product that I want.. however I just want to get the basics down first

I believe that all of this can't be done with just using the functions provided by access, and I will need to set up some macros and write some code. If anyone can give me some help with this as well or provide some example codes or whatever that would be greatly appreciated as well

I know that this isn't that much information to work from, perhaps I'll write up a functional spec when I get some time so it will be easier even for myself to map out exactly how I want to do this.
Thanks,

David

[June7]

Most of that probably can be done with intrinsic Access functionality but may not be as 'user friendly'. The more user friendly (customization) the more code.

Item 4 describes dependent (cascading) combo and list boxes. http://www.datapigtechnologies.com/f...combobox2.html

[orange]

Your field names in table Equipment List contain embedded spaces and special characters. These will cause you frustration with syntax.
Better to adopt a naming convention that uses alphanumeric characters and underscore "_" only, and no embedded spaces.

You will also want a relationship between Project and Equipment, but Equipment List may need to be broken out into additional tables. Totals and Qty seem to be calculations and would not necessarily be stored, but calculated as and when needed.

[lavey]

Thanks for the response.

I have just looked back into this, so here is some more questions etc:

Most of that probably can be done with intrinsic Access functionality but may not be as 'user friendly'. The more user friendly (customization) the more code.
I looked into this and it seems that its indeed not so user-friendly, uses c# from what i saw?

Your field names in table Equipment List contain embedded spaces and special characters
Also went through and changed this, and will also start manipulating the equipment list table once I sort most of the other things out - but for now that's just there as a reference point

For now, before I continue with populating getting everything accurate, I want to play around with the design of the database and making things work here and there, so could someone please shed light on the following questions (and how to do them/example/code) - I have not done code in many years so actual examples would be helpful or if most things can be done by access functions that would be most helpful

- On my Main Menu, I have a "New Project" button where the user is linked to a form where they can input a brand new project. However, I also have a combo box on there which contains the name of projects that have previously been entered. I want the user to be able to select a project from this combo box, and then click on a button which will then take the user to a page that displays all the information for that particular project they selected (editable unless the user clicks on an edit button?)

- How do I lock data. For example, a user has inputted/edited data and it is in final revision for now and should not be changed purposely or even accidentally. How can I create a button or action to lock this data from being edited.

- Kind of branching from the previous point, How do I create permission? ie. Only specific users can lock/unlock/edit certain data?...Is it possible to make a "edit" button become 'grayed out' unless the user has access/it has been unlocked for editing.

- How do I display information that has been entered. Say for the entire equipment list, how would I bring up the entire list and then apply filters to it, and then highlight a row and be capable of pressing an edit button that will link me to the data input form for that specific item.

- Lastly (for now), how do I create a search box, to find a specific item or specific words/letters etc.

Hopefully someone will be able to help me out with some of these issues, I've tried googling for most of them but haven't found an appropriate way to do what I want.

I'll be fiddling around with it until I get a response.

Many thanks

[June7]

You looked into what and why do you think C# is involved? Access use macros and VBA for programming.

Cannot 'lock' data in Access. You can 'flag' record by setting a value in a field and then exclude the record from queries or use Conditional Formatting of textboxes to disable them if this 'flag' field has a certain value.

You 'link' a data input form to existing record by either applying a filter to the form or going to record. Again, there are intrinsic tools on the ribbon and right click shortcut menu to accomplish.

Managing user permissions is a common topic in forum. Probably will need VBA code.

You have asked many broadly worded questions, too much to all be addressed with code samples in this thread. When you encounter a specific issue, post question.

Have you completely worked through a basic tutorial book? Here is a tutorial site http://www.rogersaccesslibrary.com/

[lavey]

Sorry I googled intrinsic functions and came up with c#, I must of clicked on the wrong one.

Thanks for the info, I've managed to do a lot of what I asked for, but I'm somewhat stuck on doing the following:

- 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

[June7]

Not sure, maybe you need a related table for records of equipment associated with a project and subform for entry of those records. Select equipment from a combobox in the subform.