By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 1,689 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

MS Access: Referencing Controls

P: 7
have a form and a subform. In the subform, I have a combo box which I would like to only show the records based on the control on the main form. Let say I have a record of employees which I would like to show on the combo box in the subform. These employees are grouped into departments. When I specify the department in the main form, I would like in my combo box in the subform to show only the employees in that department. I tried to make a program in the dirty event of the combo box and use SQL command in the Record Source. My reference to that control in the main form as my criteria is not working. Pls help. Thanks!
Feb 22 '07 #1
Share this Question
Share on Google+
7 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
have a form and a subform. In the subform, I have a combo box which I would like to only show the records based on the control on the main form. Let say I have a record of employees which I would like to show on the combo box in the subform. These employees are grouped into departments. When I specify the department in the main form, I would like in my combo box in the subform to show only the employees in that department. I tried to make a program in the dirty event of the combo box and use SQL command in the Record Source. My reference to that control in the main form as my criteria is not working. Pls help. Thanks!
Post the sql for the combo box in the subform as it is now.

What is the name of the department control on the main form and what is the name of the main form?

Mary
Feb 22 '07 #2

P: 7
Post the sql for the combo box in the subform as it is now.

What is the name of the department control on the main form and what is the name of the main form?

Mary
Main Form is Payroll. Department control is DeptID
Feb 22 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Main Form is Payroll. Department control is DeptID
OK

The Employees table is assumed to contain the following fields...

EmpID (Primary Key)
EmpName
DeptID (Foreign Key)

The name of the combobox on the subform will be EmpID.

Open the properties of the combobox and set the following...

Under Format tab
Column Count = 2
Column Widths = 0cm;3cm (This will display the EmpName)

Under Data tab
Bound Column = 1 (This will store the EmpID)
Row Source Type = Table/Query
Row Source = SELECT EmpID, EmpName FROM Employees WHERE DeptID=[Forms]![Payroll]![DeptID]

Now on the main Payroll form go to the DeptID Control. In the properties go to the Events tab and set [Event Procedure] in the After Update Event. Then click on the button with dots to the right and the VBA editor will open. You will see the following ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub DeptID_AfterUpdate()
  2.  
  3. End Sub
  4.  
Now add the following so it looks like this.

Expand|Select|Wrap|Line Numbers
  1. Private Sub DeptID_AfterUpdate()
  2.    'Replace SubformOjectName in the lines below with your subform object name
  3.    Me!SubformOjectName.SetFocus
  4.    Me!SubformOjectName.Form!EmpID.Requery
  5. End Sub
  6.  
Now everytime the DeptID is changed the EmpID List will change.

Mary
Feb 22 '07 #4

NeoPa
Expert Mod 15k+
P: 31,186
have a form and a subform. In the subform, I have a combo box which I would like to only show the records based on the control on the main form. Let say I have a record of employees which I would like to show on the combo box in the subform. These employees are grouped into departments. When I specify the department in the main form, I would like in my combo box in the subform to show only the employees in that department. I tried to make a program in the dirty event of the combo box and use SQL command in the Record Source. My reference to that control in the main form as my criteria is not working. Pls help. Thanks!
This link (Example Filtering on a Form.) may help to explain this subject.
Referencing the different forms from each other is discussed in (Referring to Items on a Sub-Form).
Feb 26 '07 #5

P: 7
OK

The Employees table is assumed to contain the following fields...

EmpID (Primary Key)
EmpName
DeptID (Foreign Key)

The name of the combobox on the subform will be EmpID.

Open the properties of the combobox and set the following...

Under Format tab
Column Count = 2
Column Widths = 0cm;3cm (This will display the EmpName)

Under Data tab
Bound Column = 1 (This will store the EmpID)
Row Source Type = Table/Query
Row Source = SELECT EmpID, EmpName FROM Employees WHERE DeptID=[Forms]![Payroll]![DeptID]

Now on the main Payroll form go to the DeptID Control. In the properties go to the Events tab and set [Event Procedure] in the After Update Event. Then click on the button with dots to the right and the VBA editor will open. You will see the following ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub DeptID_AfterUpdate()
  2.  
  3. End Sub
  4.  
Now add the following so it looks like this.

Expand|Select|Wrap|Line Numbers
  1. Private Sub DeptID_AfterUpdate()
  2.    'Replace SubformOjectName in the lines below with your subform object name
  3.    Me!SubformOjectName.SetFocus
  4.    Me!SubformOjectName.Form!EmpID.Requery
  5. End Sub
  6.  
Now everytime the DeptID is changed the EmpID List will change.

Mary
Hi! Mary,
Thanks for your reply. I got it already especialy the requery method. You're a great help!

Dave
Feb 28 '07 #6

P: 7
This link (Example Filtering on a Form.) may help to explain this subject.
Referencing the different forms from each other is discussed in (Referring to Items on a Sub-Form).
Hi!

Thanks! for the link. It is of great assistance to me.

Dave
Mar 1 '07 #7

NeoPa
Expert Mod 15k+
P: 31,186
That's not a problem - I'm pleased you found it helpful :)
Thank you btw, for having the courtesy to reply to all who tried to help. This is appreciated.
Mar 1 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.