Opening a specific record on a sub-form.

[Ant_Snell]

I would like to open a sub form on a specific record but can’t seam to get the syntax right.



The main form is: frm_Index_Employee_HR

The sub-form is: frm_Emp_Details_Current

The App_ID field is the record I want to open where it equals the App_ID field of the form I am on when running the code.

DoCmd.OpenForm "frm_Index_Employee_HR", , , "App_ID=" & App_ID
Successfully opens the main form but does not go to the correct record on the sub-form

DoCmd.OpenForm "frm_Emp_Details_Current", , , "App_ID=" & App_ID
Successfully opens the correct record but not as a sub-form within frm_Index_Employee

I have tried various combinations of syntax Like: forms!frm_Index_Employee_HR!frm_Emp_Details_Curren t
To say sub-form within the main form but unsucsessfully.

Can anyone help with how this should look.
Thanks

[Welshgasman]

I would have thought your subform would be linked by App_ID ?, so if you get to that main record, you would see the linked records?
So how is the subform linked.

You could pass in the App_ID as OpenArgs, and if present the set focus to subform and find first App_ID

[Ant_Snell]

Hi Welshgasman


The bigger picture is that the App_ID is the Key field in the applicant table. I am trying to append all the applicant details of a successful applicant to the employee table, then open the employee record on the new employee that’s just been appended. The App_ID is also appended in order to link back things like reference tables but App_ID is not the key field in the employee table, Emp_ID is.


All the append bit works fine, I am just struggling to get the newly appended recorded in the frm_Emp_Details_Current sub-form to open on the correct record.


I have not used OpenArgs before so if this what I should be using could you so syntax please?

[Welshgasman]

https://docs.microsoft.com/en-us/off....form.openargs

I am suggesting OpenArgs, so you can open the form without it normally? If a value exists in OpenArgs, then you carry out the process you are attempting here.

[Micron]

Would it not make more sense to have all the same fields/details in one table, and if the HireDate is Null, they're not an employee?

[Ant_Snell]

Micron, Thanks, Yes I did consider that and posted question asking for an opinion on 1 or 2 tables before I started the design. I opted for 2 tables in the end because I didn't want to clog up the employees table with potentially 100s of records that will never be viewed as they never become employees. I now have an applicant table that can be regular purged for GDPR reasons and an employee table with just employees, I do however filter current and historic employees.

[Gicu]

Please post a screen shot of your main form\subform with the child\master fields details.

Cheers,