Solved Link between forms broken (1 Viewer)

[nashaz]

Hi all

I have a training database where on the employee form, I have a command button which takes me to another form i.e., Training Records. I have a junction table with EmployeeID and CourseID (PK for this junction table is simply called ID). Issue I am having is that any new data I try and add to Training Record form, it does not get registered against that employee. I can use the form to add a record, but when I open the junction table, the EmployeeID field is empty. How do I solve this issue?

Thanks!

 

[MajP]

The simplest way is to do your pop up as a main form, sub form. The main form is employee and can simply show the employee name and make it locked (uneditable). The subform is you junction table linked to the main form by EmployeeID. The subform then has a combobox to select the course and save the courseID. Now you simply open the pop up using the docmd criteria.
docm.openform "frmCourseEmployee",,,,, "EmployeeID = " & me.EmployeeID

Now the main form is on Smith (ID 1) and the subform is linked to 1.

 

[nashaz]

The simplest way is to do your pop up as a main form, sub form. The main form is employee and can simply show the employee name and make it locked (uneditable). The subform is you junction table linked to the main form by EmployeeID. The subform then has a combobox to select the course and save the courseID. Now you simply open the pop up using the docmd criteria.
docm.openform "frmCourseEmployee",,,,, "EmployeeID = " & me.EmployeeID

Now the main form is on Smith (ID 1) and the subform is linked to 1.

This is exactly I had it setup. And that's how I had entered previous data but now for some reason, that link is gone. I can now only save new courses but access does not save the employeeID connection.

 

[Josef P.]

Hi,
how is the subform linked to the main form?
Can you upload a sample file showing the behavior?

 

[MajP]

This is exactly I had it setup. And that's how I had entered previous data but now for some reason, that link is gone. I can now only save new courses but access does not save the employeeID connection

Can you tell us or show a screen shot of what is in your Subform control properties for?
Link Master Fields:
Link Child Fields:

 

[nashaz]

Hi both
It is not a subform. Its a separate form which using a command button (using docmd.openform). The separate form is based on a junction table, which links EmployeeID to CourseID). Reason I have not put it as a subform is because I have other forms linking to the main form, all of which I need to programme in the same way.

I am attaching few images to portray what really is happening:

1. image1 is the main form showing EmployeeID and the Generic Training command button for the separate form
2. image2 shows the Generic Training Record form open showing old training record, and employeeID on the top right
3. image3 is the screenshot of when I click in the new record. You can see the employeeID disappers
4. image4 is when I have entered a new training in the Generic Training Record and saved it
5. image5 shows John Doe's records. the old entry has the employee name but the new entry i.e., ID 81, only shows course name and no employee details

Thanks once again for your helping efforts

 

[Gasman]

You set the ID if a new record.?

 

[Josef P.]

It is not a subform.

Then you have to make sure yourself that the value for the foreign key is set. A table relationship and the filtered opening of a form does not take care of this entry.
For example, you could set the default value of a textbox (bound to the foreign key field).
Or you can still open a form (bound to main table with EmployeeID) with a subform ... the simplest variant that doesn't require any additional code.

 

[nashaz]

You set the ID if a new record.?

Yep. the junction table's own PK is called ID, whereas EmployeeID and CourseID are FKs

 

[Gasman]

Yep. the junction table's own PK is called ID, whereas EmployeeID and CourseID are FKs

So you set the FK if a new record.

 

[nashaz]

Then you have to make sure yourself that the value for the foreign key is set. A table relationship and the filtered opening of a form does not take care of this entry.
For example, you could set the default value of a textbox (bound to the foreign key field).
Or you can still open a form (bound to main table with EmployeeID) with a subform ... the simplest variant that doesn't require any additional code.

I might just go with subform then. Just for my own curiosity, how would you bound a text box to the EmployeeID of the current record on the main form?

 

[nashaz]

So you set the FK if a new record.

I'm sorry I don't follow. I thought that's what should happen, where every new record on a junction table has its own new ID?

 

[MajP]

This is exactly I had it setup. And that's how I had entered previous data but now for some reason, that link is gone. I can now only save new courses but access does not save the employeeID connection.

That does not appear to be what you are doing. Read how I said to do it with the pop up that has a main form Employees and subform bound to the junction table.
If you do it the way you show then pass the Employee ID in Open Args.
In the forms before update set the EmployeeID_FK to open args.

 

[Josef P.]

I might just go with subform then. Just for my own curiosity, how would you bound a text box to the EmployeeID of the current record on the main form?

Insert a hidden Textbox (bound to EmployeeID_FK) and set the property "DefaultValue" .. maybe after Form.Load or ApplyFilter.
The ID can be read from Filter property or transfered with OpenArgs.

It is easier with a subform.

 

[nashaz]

That does not appear to be what you are doing. Read how I said to do it with the pop up that has a main form Employees and subform bound to the junction table.
If you do it the way you show then pass the Employee ID in Open Args.
In the forms before update set the EmployeeID_FK to open args.

My bad, apologies. I assigned the EmployeeID in OpenArgs, however, now when I click on new record, the textbox field says #Error

Note: As Josef P said, it is easier with the subform so I am doing that way, however, extra knowledge can never harm, so I appreciate your response very much.

 

[Josef P.]

Simple example:

docmd.openform "frmCourseEmployee",,,,, "EmployeeID = " & me.EmployeeID, me.EmployeeID

Code in frmCourseEmployee:

Private Sub Form_Load()
   if len(me.OpenArgs)>0 then
      Me.Textbox_bound_to_EmployeeID_FK.DefaultValue = Me.OpenArgs
   end if
End Sub

But be careful:: Form.Load runs only on 1st opening, if Docmd.OpenForm is run on already opened form, the new filter will be applied, but the default value will not be changed via Form_Load procedure (also OpenArgs property will not be changed).

 

[nashaz]

Insert a hidden Textbox (bound to EmployeeID_FK) and set the property "DefaultValue" .. maybe after Form.Load or ApplyFilter.
The ID can be read from Filter property or transfered with OpenArgs.

It is easier with a subform.

I am sorry to bother you again, but issue I am facing with subform now is that I cannot add new record within the subform. When I open the subform individually, I can add records. Please see attached images. Capture1 is standalone form, Capture2 is embedded in the main form.

 

[MajP]

My guess is that your subform is based on a query that includes the employee table. Only include the junction table.
If not show the query for the subform
Also why is there a 50 showing? Is that bound to anything. What are you link Master and link child properties?
If you want post the db. We can answer far faster. You can give us a dummy with only 1 or 2 fake employee names.

 

[Pat Hartman]

Here's a link to a working sample. It shows two ways to work with the junction table. From one direction, the sample uses a subform. From the other direction, it uses a popup. Both methods are correct. One will make more sense than the other in any given application.

Master/Child links ONLY work for subforms. For the popup, YOU need code to populate the FK for the "other" side of the relationship.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

 

[nashaz]

My guess is that your subform is based on a query that includes the employee table. Only include the junction table.
If not show the query for the subform
Also why is there a 50 showing? Is that bound to anything. What are you link Master and link child properties?
If you want post the db. We can answer far faster. You can give us a dummy with only 1 or 2 fake employee names.

Please find the db attached. I was reluctant more because it may be a mess. But at the moment, I just need the subforms to work.
Thanks for your time, really.