Controlling Subforms with a combo box
- Thread starter jsanders
- Start date
- Local time
- Today, 07:17
- Joined
- Jul 9, 2003
- Messages
- 17,503
More information required please!
I am doing this at the moment. However while it accomplishes what I originally wanted it occured to me that I needed more functionality and now I wish to be able to add a new record to the table that is the control for the subform.
I have a combo box in my main form (an incident form) that stores an ID for a field agent.
The combo box has 2 columns with the first(ID field) hidden leaving the name of the agent shown. When an agent is selected, the subform refreshes with the agents details. (using an [Event Procedure] with the After Update event)
Private Sub cmb_CPMAgentID_AfterUpdate()
Form.Refresh
[tbl_CPM Agents subform].Requery
End Sub
the record source for the subform is a query.
SELECT [tbl_CPM Agents].[Telephone Number], [tbl_CPM Agents].[CPM AgentID], [tbl_CPM Agents].[Agent Name], [tbl_CPM Agents].[Mobile Number], [tbl_CPM Agents].Territory FROM [tbl_CPM Agents];
The Master and child link is the AgentID from the form combo box and the AgentID from the subform (not displayed)
The subform displays: Phone number, Mobile Number and territory
These details can be edited and updated with no problem.
A one to many relationship has been set up for the incident table and the agents table.
all is working perfectly. However, it doesnt accomodate new agents as I cannot create a new record with the subform in the agent table. I think I need to somehow create a new record using the 'On not in List' event...??
Am not sure if I given enough detail to receive guidance, but I hoped I've helped the original question out.
I have a combo box in my main form (an incident form) that stores an ID for a field agent.
The combo box has 2 columns with the first(ID field) hidden leaving the name of the agent shown. When an agent is selected, the subform refreshes with the agents details. (using an [Event Procedure] with the After Update event)
Private Sub cmb_CPMAgentID_AfterUpdate()
Form.Refresh
[tbl_CPM Agents subform].Requery
End Sub
the record source for the subform is a query.
SELECT [tbl_CPM Agents].[Telephone Number], [tbl_CPM Agents].[CPM AgentID], [tbl_CPM Agents].[Agent Name], [tbl_CPM Agents].[Mobile Number], [tbl_CPM Agents].Territory FROM [tbl_CPM Agents];
The Master and child link is the AgentID from the form combo box and the AgentID from the subform (not displayed)
The subform displays: Phone number, Mobile Number and territory
These details can be edited and updated with no problem.
A one to many relationship has been set up for the incident table and the agents table.
all is working perfectly. However, it doesnt accomodate new agents as I cannot create a new record with the subform in the agent table. I think I need to somehow create a new record using the 'On not in List' event...??
Am not sure if I given enough detail to receive guidance, but I hoped I've helped the original question out.
