1 Form Feeding 2 Tables

[wilderfan]

This question has come up before, but I have not completely understood the answers given.

QUESTION: Can I use 1 form (avoiding sub-forms, if possible) to enter the following info?

First Name
Last Name
Join Date (date hired basically)

going into one table;



with the Job Description going into another table (so that an employee's job history can be tracked over time)?

The JoinDate entered on the form should automatically be copied into the JobHistory table as the JobStartDate for a new employee.

[I will create another form later to use when a previously hired employee changes jobs.]


Any suggestions would be appreciated.

A zip file is attached.

 

[Alansidman]

What difference does it make if there is a sub form? Is it an optics issue? If so, you can embed the subform in the primary form and no one except you will know that it is a sub form.

Alan

 

[RossWindows]

Also, Assuming you have related the two tables to eachother, create a query that contains all fields from both tables, then base the form's recordsource on that query.
On the form, all the fields that the user types into are unbound. The 'real' fields are on the form but not visible. When the user has finished entering data, they click a button that takes each value from the unbound field and puts it into the bound field (It must do this for the fields of the parent table first if you are enforcing referential integrity between the two tables).
I do this ALL the time, so if you need samples, just let me know

 

[wilderfan]

Alan -

If the parent & sub forms can be made to "look" like they are 1 single form, then I think that might be okay. The only drawback I see is that the list of forms would include a sub form which I would not expect or want the end user to open directly.

Ross -

I will try your method to see if I can fumble my way through. Yes, I have established a 1:M relationship between the tables and ticked the referential integrity option.

If you can post a zip file with some samples, I would appreciate that very much.

Thanks.

 

[RossWindows]

wilderfan,
These samples show 1 form linked to two tables (without a subform);
frmCreateJob can be used to create a job for an existing employee.
frmCreateEmpAndJob can simultaneously create an employee and a job

** Updated the sample frmCreateEmpAndJob so it does not create a new job if the user only enters employee details.

 

[Mike375]

The only drawback I see is that the list of forms would include a sub form which I would not expect or want the end user to open directly.

If you name the form with USys as the prefix, that is, if your form is currently named Form123 then change it to USysForm123, it will be hidden under system objects. Tools>Options>View and you will see a box to tick to hide or unhide system objects.

 

[gemma-the-husky]

do it another way - you dont need a subform (for this purpose anyway)

if its a new guy, there IS no job history

if its an existing guy, and you change the job, then use the fields update event to store the change (needs a bit of sql to insert the new value into a history table, is all) - its like an audit trail - then have a button to popup the history

 

[wilderfan]

RossWindows - Thanks for the re-working of my zip file.

I have a few questions which I hoped you could answer:


Why do we need additional text boxes that are unbound? Wouldn't it be easier to have the textboxes bound to the "real" fields in the tables?

I noticed that the Query for the form (viewed as an SQL statement) uses "AS" to change the EmpID fields of both tables. I was wondering why that was done versus using tablename.fieldname (with a "." between).

Finally, I'm thinking that the form which adds both the employee name and the job description does not need the 2nd date textbox. I was going to take the Join Date from the form and place it into the date field of the Employee table and the date field of the Job History table. I think this should be fairly simple, but I'll find out shortly.

Thanks for your help, Ross Windows.

 

[RossWindows]

Well, the reason for the unbound fields is to prevent bad data from entering the tables. There are other ways of doing this without unbound fields, but this method is the easiest for me. When you have a form with only 'real' text boxes, what happens when the user starts filling it out but decides to close the form halfway through? or the code breaks for some reason?

1) An autonumber (if you have an autonumber field in your table) gets created.
2) Unless you have code to delete or cancel the new record, it will end up in your table half-completed.

As for the 'AS' statements in the form's recordsource.. you can use an 'AS' statement or you can use the decimal seperator. I think I was using a query builder that added the 'AS' automatically.

For the form that adds both an employee and job... good thinking! That's where logical brains come in handy.