hi mike im using 3 different forms to create the record as there are a lot of fields to fill in. Trying to make it simple for my work collegues to enter the info. but i click on the button and the new form opens showing record 1, i would like it to either show blank fields if the info has not yet been entered or show existing info if they have. relative to the record im working on.
Either way you are into filtering or record selection. Your different forms are only giving you a different view of the same record and therefore Form2 has to open on the same record as Form1 but it is displaying different fields of the same record.
When you open a form it first opens and displays all records in the table or query which it is based on. At the bottom of the form it will say 1 of 66 or 1 of 5000 as the case might be.
What you need to do is "filter" the record or records you want out of the whole display.
If you go to your form in Design view and right click on either the buttons, fields, labels or the area of the screen that is outside the body of the form and then click Properties, a box will open. On that box there are tabs, click on the tab for Event. If you have Access 2007 I think the properties box is off to one side. I don't have Access 2007, so if you do and an't find Properties then post and ask.
When you see Event you see a list of things such as On Click, On Lost Focus and so on. These are the events that will trigger VBA or macros.
Your present button will have very basic VBA to simply open a form. But what is missing is a "Where Clause".
A macro or VBA is written that will provide the "where clause" or the VBA or macro will open the second form and change its record source to a query where the criteria of the query is information or data in a field on the form you are opening from.
If you could actually speak to Access then you would say......please open my other form and then find a record in that form where the value of Fieldxyx is the same as the value of "Fieldabc" (Or Fieldxyz) on my first form.
The most common reference will be to a unique ID field. If it was not unique, such as lastname etc. then the seond form might display a few records. Do you habve an autonumber field or some other ID field?
The reason why i have so many fieldss its all information eg name, addy next of kin their addy, wether tests have been taken info from other forms basically info where i have to use memo cus of the size in the end it all merges into 15 pages . I have now got a book called Access VBA programming, but sometimes confirmation of what i think im learning is better provided by people. I do apprieciate ur help
If you have real lots of fields in a table then you need to carefully examine whether some of pieces of data should be in another table.
Look for fields/data that can have multiple instances and especially where the number of instances is an unknown. For example......next of kin
Do a search on Google for Normalisation Access and you will see some easy to understand examples of table structures. If you don't have your table structure at least basically right then it can prove to be a real pain further down the track.
I think im trying to run before i can walk. this book that i have got doesnt even tell me about filters. I know u wanna shoot me ..... heheheheeh ok i will check out some tutorial sites on how to apply filters in vba. Unless of course. u wanna get more stressed with me and guide me.
The tables are like the foundations of the house. Like the house foundations, you don't see the tables in normal use of the data base but if the the foundations are weak, then the real nice house will fall down.
For your tables you start with what you might call your maintable. This will have the basic details of whoever or whatever is to be the main entity.
In general, the fields for the maintable will be for data that applies to all entries. Everyone has a first and last name, a date of birth, a gender etc. No one has two dates of birth or two genders. Everyone is either a smoker ir non smoker. However, next of kin, test results, details of meetings or file notes will vary with people.
The classic example used for Access is Customers and Orders. There is one customer but the could be no orders, one order or lots of orders that relate to that customer.
In your maintable you have a field for ID. The Access autonumber field is designed for this purpose. It will automatically a unique number each time a new record is added. There will be no duplicates of ID number in the maintable. But the orders table or next of kin table or the appointment details table, will have the customers ID number repeated.
Access is based on tables/records having a One to Many relationship. Thus in the main table, the customer or your main entity has one record and never more than one record. That one record relates to Many Records in a related table.
Let's say we tried to include children of the main entity as part of the main entity's record. Then we would need to have Child1Gender, Child1FirstName, Child1DateBirth and then Child2Gender, Child2FirstName, Child2DateBirth etc. But how many fields would we make allowance for. Think of the difficulty with searches. Access also limits you to 255 fields in a table and you might run out of fields. However, if we have children details in another table then it does not matter whether the person has no children, one child or 10 children.
If you don't have yur data base at least half right on a One to Many table setup then you will be going down a road that ends with a swamp and you will need to turn around start again.
Select Queries look like tables and froms can are more often than not based on queries. Queries select records from the table based on a criteria that you supply. Thus a query can display all the records where the gender is male, the date of birth is greater than March 1 1980 etc. Queries also allow for doing calculations or altering how data that you entered is displayed. You can create extra fields in queries that are based on the data in the other fields. Thus such a field might have a 1 when certain conditions exist on the record or a 0 for other conditions.
There are other query types called Append, Delete, Update and MakeTable. These alter data in a table based on a criteria you supply. For example, under the criteria you supply and Append query might add certain records from one table to another table and then under the same criteria a Delete query deletes those same records. Thus you have moved a batch of records from one table to another.
Queries can join tables and the joing of tables is at the heart of Access. Thus a table that has next of kin can be joined to the table that has the main details of the person. This is done with the ID number. Imagine a doctor has one box of cards with each patient's details. He has another box with a card for each visit. Some patients might have 1 car or 21 cards in the "patient visit" box. If was Access then when pulled out a patient card it could relate that card to the cards for that patient in the "patient visit" box
Forms, VB and Macros
If we were to stick with the house analogy then forms, VB and macros would be all the things in the house that are not the bricks and mortar.
Forms are important because they don't just make for a nice readable display. The forms carry the macros or VBA.
Basically, macros or VBA simply automate tasks. If we go back to your original post, then VBA or macos will automatically find the record you want in the other form.
What makes tables and queries critical is that they are at the foundation level of the house and so changing them at a later date can be a big deal. Macros or VBA are a bit like the roof of the house. It is 100 times easier to change 20 roof tiles than it is to change one brick in the foundations.
Basically, the large majority of macros or VBA are referring to fields on a form which of course will trace back to a field in a table. So changes in a table at a later date can mean a lot of stuff will fall over Trust me on this one
There are a few things that are done with macros or VBA, whereby you if can do them then you can have quite a complex data base.
1) Being able to open a form and find a specific record or a selected number of records. This can be done by a "filter system" or changing the record source on the form being opened......to a query that gets its criteria based on data that is on the form from where you are opening the second form.
2) Being able to change the value of a field. This can be inserting data in one field from another field. It can also be a calculation such as the current date plus 30 days etc.
3) Making appearance changes on a form and especially making a field or label visible or invisible. All the various formatting you do when making a form can also be done with macros or VBA. A macro or VBA will even change the dimensions or postion of a field on a form.
4) Setting condtions. These are the the conditions, usually based on data in fields that determine what part of the VBA or macro executes or even which VBA or macro runs. For example, when "clicking" to open another form you might have a warning sign pop up because the date in a field is more than 60 days before the current date.
However, where macros, VBA and their conditions are less critical than tables and queries is they lend themselves to refinement without having to tear the whole data base apart. Also, no matter how nifty and tricky you can get with macros or VBA, you can still be limited if your table structure is junk.
Obvioulsy the navigation between forms needs to be fixed.
Your two tables seem to be one table that is split. Your form is on a query that joins both of them so perhaps you could just have one table. Given that one table is called Sheet1 I assume it was brought across from Excel.
I would prefer field names that are done as one word. If you do make any changes in the table (s) it is better to do it now as it can be a real pain down the track when you have layers of forms, macros/VBA.
Do some of the some of the fields such as Disability require other data if the person answers Yes. Type of disability, degree and duration, more than one disability, workers compensation......physical or mental etc.
If Age Now is meant to be current then it should be calculated. If in fact it is referreing to their age at starting then it should be calculate and called Age at Start. However, I would still have it calculated so that the person entering the data did not need to work at age now, that is, a calculated age would be inserted into the table.
If you wish to use the data base to be able to correlate different things eg. all the people with xyz Career Preferences, who got the name of the program from leaflets etc and etc. then some of your fields will be limiting. Naturally data such as Career Preferences needs to be written put perhaps you could put most combinations into a drop down list or have a number go in another field for a category of Career Preferences.
If you retain the two tables then you might Enforce Referential Integrity and Cascade Delete. That will mean if you delete a record in Sheet1 you will delete the other part of the record in the other table.
It appears that your data base is really a computerised version of a card or manilla folder system. If that is the case then it is basically OK.
Your forms should have the Close button (x) removed.
Some changes I would make for myself, but they are personal rather than functional:
1) Have the forms the same size and opening the same distance from the top of the screen.
2) The data entering screen I would have "less busy" Quite a few of your fields are similar categories of data. For example, starting date, which work centre etc. I would have one of these on the data entry form and then another small form would pop open to allow the other data to be entered. There is no problem with the person having to make a decision as the form can be opened when a field loses focus or gets focus. You will often find that people who are newer to your screens initially prefer less busy screens and pop open forms with an instruction on the top.
3) For people who don't do huge amounts of data entry it can be easier to enter John Andrews in one field. Same deal for address. Left/Right functions producing query fields can break the data up. Although l imagine data entry for you does not amount to huge numbers.
4) Probably more functional than personal, but you need an index of some type.
5) Since the data entry for each person is extensive I imagine that sometimes it will not be completed at one sitting. If so you need a setup that takes you back to non completed records.
6) I find data entry of this type (probably someone on the phone etc) is better if a "notes" screen can be used. This is done with a separate table so each note is a different record and with time/date. If such a note is made then a label on the main form is made visible so that everyone knows there is additional information. There is no end to what you can Like you I have several data types that require 250 character text fields or memo fields. I like to store that data in a related table so as I can use a category word or number. For example, I have lots of file notes made and each note is given a category from a drop down list. But this type of thing is only of use for extensive searches, correlation data etc.
So.... There are lots for me to do. I have been using the database for my self cus i know its hard for other people to follow./ I just hope that I can impliment the changes that u suggest without causing problems. But Mike I hope to dont mind helping me out. it gives you something to do while ur winter draws near. if u can suggest any good books. I know how to build, query and report, but thats just basic stuff. Anyway Monday morning got to get off to work and use the database to help me fill in all them bloody forms. Have a good day