[thewebbmasterone]

I have a database that i'm creating that has 3 different tables. Its for safety violations at work. One table is the innitial one that has all the pasic information on it. I set it up so that it automatically assigns a number to each complaint filed. The other two are for follow up. One is for how the problem is going to be fixed and who is supposed to do it, And the other one is for verification to make sure that the problem has been fixed. My problem is that i want the two follow up tables to automatically import the complaint number when a new complaint is filed. I tried to set everything up myself, but had no luck. Can someone please help? Thanks.

[OBP]

Hello, the trick to passing essential data between Tables is first to Set Up the Tables Relationships.
Once they have been set up you then use a Mainform for your Violations and subforms for your "Fixes" and "Follow-up", whcih are Linked to the Main form via "Master and Child" links which are your Related Fields in your Relationships.
In this way when you create or go to a main form record any data entered in to the othe 2 forms will automatically be given the correct Main form ID number.
If you want some ideas on database design I can point you to a couple of other Threads by other Posters who have learnt how to do it from simple to more advanced designs.

[slurpee55]

OBP, other posts are made here regarding this same database - I would suggest we work on this posting
http://forums.techguy.org/business-a...able-list.html

[slurpee55]

OBP, other posts are made here regarding this same database - I would suggest we work on this posting
http://forums.techguy.org/business-a...able-list.html

[thewebbmasterone]

here is my database that was requested. i'm sure that it will look a mess to anyone that is experienced at access. Any pointers and tips and tricks would be greatly appreciated.

[thewebbmasterone]

In my other post about my forms it was asked what i was using for a record source. My record source for my form is a bunch of support tables that list places and things like that. I do not actually have a record source that is my actual database. what i want to do is pull the information i want off of the smaller tables and enter it into the main table along with some new information that is not stored on any of the tables. I guess it seems like i'm going at this all wrong.

[OBP]

I like most of what you have done so far, although I don't personally like Lookup fields.
The problem with your Form is that it does not use the Safety Observations Table as it's Record Source, therefore it has no where to "Save" the data to. You will notice that when viewed in Design mode all of your Fields say "Unbound" which means they aren't linked to any Table Fields.
To overcome this I have made your Safety Table the Form's Record Source and each Field "Bound" to a Field in the Table. I have added a Category field to the table to "hold" the category selected in the first Combo.
This also means that your "Save" button is no longer necessary as the data is automatically saved when you move from the record or close the form.
I do have some suggestions to make about the design of the database, you do need to set up the correct "Relationships" between the Tables.
Also I am not sure that you need a seperate "Observation Closure" table. As you can only have one observation per record and it can only be closed once I think it should be part of the Safety Observations Table.
The Observation Fixes is correct though because you could need more than one fix to allow "closure".

I will post another database with what I think the design should be for optimum use later.

[OBP]

Here is the version of how I think it should all go together.
But I do think that you should also have an "Individuals" Table to select the individuals responsible for "Fixes".

[thewebbmasterone]

yes i had thought of making an indeviduals table, but the problems that i saw was that what if someone is designated the responsible person and they are not on the list? At work there are two main people, head of maintenance and head of manufacturing. usually they will assign a person under them to take care of the problem. but there are dozens of people that that could be. I guess i could make a table and then connect it to that field but not limit entries to the list.

[thewebbmasterone]

How do i specify a record source for fields in a form? is that done by going to properties and then control source?

[OBP]

Yes, that is how you do it, you select the Field from the drop down when you click on the Control Source.
With regard to a Combo for Individuals you can have a "Double Click" VBA routine that opens an Individuals Form to enter a new name which when closed automatically Updates both the Form that you are on and it's Combo box as well. You can see this in action on quite a few of the databases that I have collaborated on and posted on this forum.

[thewebbmasterone]

I tried to set a record source on my original database. I went into the form new observation and selected the area dropdown box and then clicked properties. in the data tab i selected the control source box. The dropdown list has nothing in it so i clicked the three dots instead. and i went through the tables list and found my main table and selected the column that i wanted the information to go in. But when i went back to the form and tried to use the are dropdown box it said that i couldnt edit the value because it was tied to the statement that i had just selected. What did i do wrong? what am i missing?

[thewebbmasterone]

I am trying to make a form that lists all the different collumns in my database and makes it so you can select any different number of combinations and sort the data accordingly and then view a report or a chart. Does anyone know how to do this?

[OBP]

Did you get the Combo working?
What you are asking for in this question is very difficult to achieve without very advanced VBA code. Selecting the fields is not too difficult, the report is simple (the graph a bit harder) but sorting the data on any number of selected fields is difficult.

[thewebbmasterone]

here is the updated version of my database. I added a responsible indeviduals table and made it so it would open a new form to allow me to add to it if i double clicked on the dropdown. (Thanks to you guys for the suggestion). I'm pretty happy with what i have for the data entering process. Now i'm trying to figure out a way to look up information an analize it. I still haven't figured out how to attach some of the fields to record sources. I've tried but i'm missing something somewhere. it would be greatly appreciated if someone could point me in the right direction. And as usual, any pointers or suggestions would be much appreciated.

Thanks