PDA

View Full Version : SOLVED: Access subforms and data



jrenee
07-30-2004, 04:49 PM
Hi, :hi:

Well, they have me off onto a new project. I'm a user of Access that has me somewhere between novice and intermediate. Still don't know VBA, but I've created enough databases in Access to at least have the basics down.

Here's my issue:

I have a main data entry form that has three subforms (all have separate tables). I have a primary key in the main table, which is the employee ID number. I have created a relationship (one-to-many) using the main table and it's primary key to link to the subtables.

In the main data form, I have three buttons, each that goes to a separate subform/subtable.

Here's the dilemma: When I open a subform to enter a new record under the employee's ID no., I have to enter the employee number again. Is there a way to encode the Employee ID number on the subform so it automatically shows up in the subform every time I add a new record? I'm sure it's something I need to do in the properties, but I'm not sure what it is.

Any help is greatly appreciated!

JRenee

Ziggy1
07-30-2004, 05:56 PM
The table that the subform is bound to should have an Employee ID field as well except it will just be a number type.

I set that up first and then with the main form in design view (starting new), position the DB window and the form so you can see both and drag the subform or table object onto the main form. This will trigger the Subform wizard (if activated) and allow you to connect the 2 fields to make the subform work.

Oh if you wanted to do it for all 3 then place the "Tab" control 1st and then what Isaid above for each sub

GP George
07-31-2004, 12:43 PM
First, as Ziggy points out, you need to use a main form-sub form design to handle this type of data entry. I'll get back to that in a second.

A more important point that you need to address first is the Primary-Foreign key you have used. Your Primary Key (in the employee table) should NOT be the actual Employee ID you assign to employees. It should be a unique key that your users never see. The best choice for that is the Autonumber. Your actual Employee ID should then be a separate field in your table so your users can work with it.

The Foreign Keys in the other tables will be numbers (long integer data type), as Ziggy also pointed out.

There are a number of reasons for not using natural keys like Employee ID's (or Social Security Numbers) as the primary key of a database table, not the least of which is that, while they are unique within a given time period and context, there is no guarantee that your company won't decide to change the way it designates Employee IDs at some point, creating the possibility that you'll have to revise your tables--all of your tables--to match the new format.

So, if you have used your company's actual Employee IDs as primary keys, I would recommend you go back now and add a real primary key field to that table and convert the foreign key fields in the related tables to match.

Now, for the main form-sub form design. By definition, a subform is embedded into a main form and does not need to be opened as a separate form. There are other uses for this design, but the most common is for data input in situations exactly like this one. The record source for the main form is the table on the "One" side of a one-to-many relationship. The record source for the sub form is the table on the "Many" side of that relationship.

Because Access knows about the relationships defined between these tables, it will automatically try to set up the links between them when you insert the subform into the main form. And it also takes care of inserting the foreign key into the "many" side table.

HTH

George

jrenee
08-04-2004, 05:02 PM
Thank you for your responses. It's been a while since I last posted, but I've had time to go back and implement what has been suggested, and it's still not quite what I'm looking for.

I found that when I do the drag and drop as suggested, I get the whole form or table dropped into the main form;however, I prefer to use the command button to open a form in which to enter data. (I also went ahead at GP George's suggestion and assigned an autonumber field as the primary key, not using the Employee ID number)

Here is what I have:

I have a main table/form in which data is entered for each employee, which would include employee ID number, name, and position. Then I have created another table/form to store data for each employee's certifications (types of certifications and their renewal dates) called Certification Info. I have linked these two tables by the employee ID numbers. Then I have created another table/form to store data for each employee's training (date of training, name of class/seminar, location, credit hours) called Training Info. I have linked this table to the main table/form by the employee ID numbers. I have another table/form to enter performance evaluation data (type of evaluation and evaluation date) called Perf Eval Info. I have linked this table to the main table/form by the employee ID numbers.

The idea is to open the main table/form, and go to the record for John Smith. On the form, click the command button labeled Training Info (there are also command buttons for Certification Info and Perf Eval Info on this same form), which opens the Training Info form. I can enter the training that John Smith went to on July 1, 2004, so that record can be added to the Training Info table. (I do have filters turned on) I can then click the command button on the Training Info form that says "Add Record" and enter another record for John Smith for the training he attended on July 31, 2004, so that record can be added to the Training Info table. I can then click the command button on the Training Info form that says "Close Form" and the form will close and go back to the main form for John Smith.

Here is the problem that I am trying to resolve: When I open the Training Info form from the command button on the main form, I have to rekey in the Training Info form John Smith's ID number. It's like it has to have it entered in order for it to associate itself to the John Smith in the main form. What I want to do is encode that field in the Training Info form so that I don't have to rekey that information. The association will already be there.

Eventually, I will want to continue to build on this database, but this is my main hurdle at the moment. I'm trying to eliminate having to enter the same information with every new record for each employee.

Maybe it's in my relationships or something. Hopefully someone can help me with this problem. I know it can be done...I've seen it...I just don't know how to do it.

Thank you.

Ziggy1
08-04-2004, 06:57 PM
I still don't think your quite getting what we're saying to do with the EmployeeID. Lets start by calling the EmployeeID the number that you will type freeform linked to the real person.

Now The autonumber Field on the Main form Lets call that EmployeeRef, include that field in all your other table/forms but set them to Long integer as George pointed out. The EmployeeID does not need to be repeated on the other forms it will be related through the EmployeeRef.

When you do the Drag and drop I suggested you should get a dialogue box from where you can select the employeeRef form the Main and the EmployeeRef from the sub.

Note for above: Use the Subform wizard if it doesn't pop up autmatically


I know this is not what your asking but I'm not clear on whether you were able to get it working. When you call up John smith, and proceed to enter training info the employeeRef number should be the same in both. You won't need to enter the employeeRef into the subform it should appear automatically.

Each of the Sub table/forms should also have their own ID also as they will have details.

*Comboboxes can be added to the form to assist in selecting records

If you do have no problem making the form work then, then I'll look at offering other solutions. I have a feeling George is stirring something up.:rolleyes:

jrenee
08-05-2004, 08:50 AM
Yes. I'm still having problems. I went via Ziggy's and George's instructions, and it keeps dropping the subform into my main form, which is not what I'm looking to do. I've created the primary key as EmployeeRef in my main table at Ziggy's instruction, and then I went through the other (sub)tables and created a field called EmployeeRef and made them numbers with long integers...just like Ziggy suggested. I went to Subform wizard, created my own links...which was the Employee Ref, and when I did, it dropped the subform right into my main form. I really want a command button that will take me to that subform.

Any other suggestions?

SJ McAbney
08-06-2004, 02:21 AM
Nice to see you join us here Pat. :)

jrenee
08-10-2004, 05:16 PM
Okay. I finally got it to work using the subform. I appreciate everyone's help in this endeavor. I look forward to tapping into your resources again in the future.

Thanks, again.

jrenee