Opening a subform on a record where it is equal to a number in a table

[Ant_Snell]

I am trying to right some VB code that on the face of it is basic however I have spent over a day trying to get it to work but unsuccessfully.

The overall objective is: I have an applicant table containing details of applicants for a job vacancy. At some point an applicant is offered the post so on the applicant form I have a “Convert To Employee” button that runs a number of append queries, taking the applicants title, name, contact numbers, (and applicant ID) etc. and creates a new employee record in the employee table (that’s all working fine). I then want the code to open the employee form (that’s a sub form) on the newly created employee record so that any additional data can be added.

The applicant form has a number of sub forms with contact numbers, references etc. but the main form is based on a table “tbl_app_details” and the key field is “App_ID”

The employee form is a sub form inside “frm_Index_Employee_HR” subform name: “frm_Emp_Details_Current” based on a table “tbl_Emp_Details” The table includes an Emp_ID & App_ID

So the it of code I am struggling with is after the append queries have run, adding the app_ID and name details to the “tbl_Emp_Details” is to then open the “frm_Index_Employee_HR” form and go to the subforms "frm_Emp_Details_Current” record that is equal to the App_ID in the “tbl_Emp_Details” table.

Any help appreciated

[orange]

I think readers need to see the form/subform etc in context. Can you post a copy of the database involved- at least enough to show a record or 2 and the forms involved?

[Welshgasman]

I think you would need to store the App_ID of the newly created record, then pass into the main form and if present use a FindFirst with that ID.
The subform should be linked to some sort of ID from the main record?

[Ant_Snell]

For clarity I have now included some screen shots of the forms and table structure.

[IMG]file:///C:/Users/tonys/AppData/Local/Temp/msohtmlclip1/01/clip_image002.png[/IMG]
Figure 1
Figure 1 is the applicant form, this is the applicant we have decided to set on therefore we use the “Convert this applicant to an employee” button. The form also includes a hidden “App_ID” field that is posted to the “tbl_Emp_Details” via an append query when the button is clicked.

[IMG]file:///C:/Users/tonys/AppData/Local/Temp/msohtmlclip1/01/clip_image004.png[/IMG]
Figure 2
Figure 2 is a snippet of the “tbl_Emp_ID” table showing the Emp_ID and the App_ID fields.

[IMG]file:///C:/Users/tonys/AppData/Local/Temp/msohtmlclip1/01/clip_image006.png[/IMG]
Figure 3
This is the “frm_Index_Employee_HR” main form with the sub form “frm_Emp_Details_Current” that also includes the hidden App_ID field.
I am trying to open this form on the newly created record where the Emp_ID it is next to the APP_ID in the table and same as the Emp_ID in the open applicant form “frm_New_App”.
My instinct is that I need some kind of Dlookup in the open form line but try as I may I cant get my syntax to work.

[Ant_Snell]

This is the screen shots for the above post

[Micron]

Are the subform "Link Master Fields" and "Link Child Fields" properties set? See property sheet data tab in form design.

[Ant_Snell]

Are the subform "Link Master Fields" and "Link Child Fields" properties set? See property sheet data tab in form design.

Hi Micron,
“Are the subform "Link Master Fields" and "Link Child Fields" properties set? See property sheet data tab in form design.”

No, however that’s because the master form “frm_Index_Employee_HR” has no fields at all, it is purely used to navigate between the various modules in the application. Clicking the Toucan icon in the top left takes you back to the home screen where you can navigate to any of the other modules. The issue is opening the newly created record because the Emp_ID is not on the “frm_New_App” because at that point it doesn’t exist. Its not until you click the “Convert this applicant to an employee” button, that the below code runs a number of append queries and creates the employee placing the App_ID and other info into the “tbl_Emp_Details” I then want to open the employee record (Sub form) on the new record.
The code below works but only opens “form_Index_Employee_HR” (Master Form) on the first default record so you then have to seach for the record you want.


If MsgBox("You are about to add this applicant to the current Employee list, are you sure?", vbOKCancel, "Adding to Employee") = vbOK Then

DoCmd.SetWarnings False
DoCmd.OpenQuery "Qry_App2Emp"
DoCmd.OpenQuery "Qry_App2Emp_Contact1"
DoCmd.OpenQuery "Qry_App2Emp_Contact2"
DoCmd.OpenQuery "Qry_App2Emp_Contact3"
DoCmd.OpenQuery "Qry_App2Emp_Contact4"


DoCmd.OpenForm "frm_Index_Employee_HR"
DoCmd.Close acForm, "frm_New_App"
DoCmd.Close acForm, "frm_Index_Onboarding"




MsgBox " This applicant has been sucsessfully added as an employee. search and open the record then please ensur you add all required employee details "


Else

DoCmd.CancelEvent
End If

[Micron]

These seem contradictory and make no sense to me.

only opens “form_Index_Employee_HR” (Master Form) on the first default record

master form “frm_Index_Employee_HR” has no fields at all

How do you expect to see records on a form that has no controls? If you've messed up your post then it needs correction. Otherwise did you try requerying the form that has the controls? I'm not seeing the point of a subform on a mainform that has no data. Common approach would be to use a form as a switchboard and open whatever form or report you need from there.

Is this about a navigation form design?

EDIT - I suspect you've designed your tables like spreadsheets because you're running separate but seemingly similar queries. You could copy db, compact and zip and post here if you want feedback. See "how to attach files" at the top of the web page if needed.

[Ant_Snell]

These seem contradictory and make no sense to me.


How do you expect to see records on a form that has no controls? If you've messed up your post then it needs correction. Otherwise did you try requerying the form that has the controls? I'm not seeing the point of a subform on a mainform that has no data. Common approach would be to use a form as a switchboard and open whatever form or report you need from there.

Is this about a navigation form design?

EDIT - I suspect you've designed your tables like spreadsheets because you're running separate but seemingly similar queries. You could copy db, compact and zip and post here if you want feedback. See "how to attach files" at the top of the web page if needed.

Hi Micron,

I will try and explain again.

Your comment: “These seem contradictory and make no sense” (only opens “form_Index_Employee_HR” (Master Form) on the first default record)
The form opens in the same way as it would if you were to click on it in the object pane from design view. i.e. the main form opens with the sub form in it on no specific record. That’s not surprising because its this line of code I am struggling with: “DoCmd.OpenForm "frm_Index_Employee_HR" I am trying to open the form then navigate to the new record on the sub form. It needs a where clause that goes to the employee (Emp_ID) where the applicant (App_ID) in tbl_Emp_Details is equal to the app_ID on the open form frm_New_App (see table below)

Tbl_Emp_Details

Emp_ID	App_ID	Name
1	25	Tom
2	26	Bill

Your comment: “These seem contradictory and make no sense ”master form “frm_Index_Employee_HR” has no fields at all.
“How do you expect to see records on a form that has no controls?”

See figure 3, The main form is the top and left in orange with the title Employees. It has no controls because it is only used to navigate the different modules or to different forms within this module e.g current employees or former employees. It opens with a sub form (Labled employee details) with no filter i.e all current employees. It is this sub form where I am trying to navigate to the new record. E.g. in table above if the open form tbl_New_App id on record 25 then open employee 1.

“If you've messed up your post then it needs correction.” I don’t believe I have. I have tried to explain the issue as clearly as possible. The issue is nothing to do with my design, navigation or switchboards. This information was included purely to give context and because I was asked to by “Orange”

“I'm not seeing the point of a subform on a mainform that has no data.” See figure 3 however this is irrelevant to the question and without going into the whys & wherefores, works well for the purpose of the application rather than a conventional switchboard as you suggest.

“Is this about a navigation form design?” Not at all, please see my previous comments / posts in this thread.

“I suspect you've designed your tables like spreadsheets because you're running separate but seemingly similar queries” irrelevant to my question however your assumptions are wrong. The design is fully relational hence the need for similar queries.

The reason for:

DoCmd.OpenQuery "Qry_App2Emp_Contact1"
DoCmd.OpenQuery "Qry_App2Emp_Contact2"
DoCmd.OpenQuery "Qry_App2Emp_Contact3"
DoCmd.OpenQuery "Qry_App2Emp_Contact4"

Is because the contact table includes a related table contact type, so Qry_App2Emp_Contact1 appends to the contact table with type 1 (Mobile) Qry_App2Emp_Contact2 appends to the contact table with type 2 (email) etc. this is all working fine.
It is not related on the “frm_New_App” because this data is pulled from an existing website that is a flatbed design. It is then converted to relational if an applicant becomes an employee.

[Micron]

Not sure if you want to go to a particular related record and still be able to navigate through other records

go to the subforms "frm_Emp_Details_Current” record that is equal to the App_ID in the “tbl_Emp_Details” table.

or if you want to load the form with the single record and that is all. For the former, there is a gotorecord method
https://learn.microsoft.com/en-us/of...cmd.gotorecord

Otherwise I think you need to either
a) apply a filter to the subform in a main form event; perhaps Current or Activate, or
b) in the subform Load event, set the recordsource of the subform to a sql statement that uses the ID as filter criteria

This might help you decide which main form event(s) you would need
https://support.microsoft.com/en-us/...7-ce86553682f9

[Ant_Snell]

Micron, Thanks I will check out your links, only just got due to other family commitments.