Linking two records via form within a form

[atticus1802]

Ok I understand this may not be the best way to do it and might not work as I intend it to but basically I have a form within a form. The first shows and employee, the second shows the employees desk details i.e. number, laptop, serial etc.

At the moment I have to manually add the employees number to the desk number so they are linked. I was thinking if it was possible to have the desk number in a combo box, this combo box then populates the remaining five fields in the same record.

I thought if this happens and I save the record it would save both forms together rather than manually adding the employee number to the table.

As I said I dont know if this will work, or even makes sense. I am fiarly new to this.

 

[Galaxiom]

The subformcontol has properties LinkMasterFields and LinkChildFields.

Enter the common fields into these properties and your subform will track the main form.

 

[atticus1802]

this may sound stupid but where is the subformcontrol?

 

[Galaxiom]

Its is the object you use to put a subform on the main form.

Click on the subform on and Access draws a line around it.
Look at the Properties window. (Right click and select properties if it isn't showing)

Click the Data tab.

 

[atticus1802]

ahh yeah I see, i was looking there but missed the field.

I had already done this what I wanted to know was if I select a record from the subform and save the mainform will this automatically link the two so that if Record 1 is displayed on the main form, and I select record 3 on the subform. Will record 1 always show record 3?

 

[Galaxiom]

I am not sure you understand the relationship beteeen the record in a form and its subforms. Each record in the subform is typically related to the main form by some attribute. eg Payments by a customer. The customer record is the main form record source while the subfrom hold the payment records.

Normally database forms use bound controls (the boxes often wrongly called fields).
Changes save automatically when moving to another record in either part.

When the form loads the the first record in the form is the current record by default. When you change the current record in the main form the first related record in the subform is the current subform record.

Maybe you should explain more about what you are trying to achieve.

 

[atticus1802]

Ok basically what I am trying to do is

Form 1) Employee details
Subform) Desk Details

Everything in the subform stays the same as the desk never changes, as employees come and go new people will be assigned to desks. What I am trying to achieve is to be able to pick a desk number from a combo box so that the employee will be linked with all the information on that desk. So far the only way I have been able to do it is by having the employee number linked via relationships and by adding the employee number to the desk. However when an employee leaves this means finding the desk in the table view and deleting the information manually.

 

[atticus1802]

bumping the post, no ideas?

 

[Galaxiom]

Why not link the desk number by having a Desk field in the Employee record.

Also include a Resignation checkbox field to indicate when an employee resigns. Empty desks can be found in a query by those that are related to employees that don't have Resignation unchecked.

This query becomes the row source of the desk selection combo so that only empty deska are available. This combo is bound to the desk field in the employee record.

The desk subform is related to the main form via the Desk field of the main form recordsource and the DeskID in the desk subform.

Hope this helps.

 

[atticus1802]

This seemed like a good idea until I tried to implement it, the problem I am having is that the desk numbers are not unique. We have a desk 1 in our accounts department, a desk 1 in our HR department etc. Any ideas?

Prefably I would like two linked combo boxes so that you could pick the department then pick the desk in that department, however I do not know how I would link this via a relationship to the desk table.

Sorry if this is not clear.

 

[Galaxiom]

The desk table should also have a DepartmentID foreign key to the Department table.

Presumably the Employee record has a DepartmentID field too. Then you can automatically link the department via the DepartmentID fields in Employee and Desk tables.

 

[atticus1802]

I am trying this but without much luck, so would having a department in table 1 and department in table 2 with a relationship allow only the desks numbers from that department?

I am quite new to this and my database is very complicated and probably very messy in its design.