using list or combo boxes

[Guest]

Hello,

I have a form with several fields: ID Number, LName, Fname, Employer,
Employer1 Depts and Employer2 Depts.

I want to enter data in first 3 fields (text) which will be inserted to
table 1.
I want to select a value from a lookup table for employer. Based on the
Employer chosen, either Emp1 depts OR Emp2 depts drop down will become
active.
Finally, select a dept from the active dropdown.

Am I thinking correctly in the following:

For the OnChange event when the employer is selected from the dropdown, I
want to do this:
IF employer = EMP1 then
1.1 enable cboEMP1
1.2 select deptname from LkTblDepts where Employee = EMP1
Else If employer = EMP2 then
1.1 enable cboEMP2
1.2 select deptname from LkTblDepts where Employee = EMP2
End IF

After all data is entered and values are selected, they need to be inserted
into the tables.

If this is not correct, can anyone advise on how I can code this
functionality?

TIA,
Rich

 

[Bob Quintal]

Hello,

I have a form with several fields: ID Number, LName, Fname,
Employer, Employer1 Depts and Employer2 Depts.

I want to enter data in first 3 fields (text) which will be
inserted to table 1.
I want to select a value from a lookup table for employer.
Based on the Employer chosen, either Emp1 depts OR Emp2 depts
drop down will become active.
Finally, select a dept from the active dropdown.

Am I thinking correctly in the following:

No! the OnChange event is wrong. Use the Employer_AfterUpdate
event if you need an event at all.

For the OnChange event when the employer is selected from the
dropdown, I want to do this:
IF employer = EMP1 then
1.1 enable cboEMP1
1.2 select deptname from LkTblDepts where Employee
= EMP1
Else If employer = EMP2 then
1.1 enable cboEMP2
1.2 select deptname from LkTblDepts where Employee
= EMP2
End IF

Why not use a single cboDEPT with a query as the rowsource,
which filters the departments based on which employer is chosen.
Imagine if you expand to 50 employers, there would be no room
for 50 combo boxes.

e.g "SELECT DEPT from Departments where Employer = Forms!myForm!
cboEmployer;".

The only line of code needed in the employer combo AfterUdate
event is
me!cboDEPT.Requery.

After all data is entered and values are selected, they need
to be inserted into the tables.

Just bind the combobox to the relevant field in hte table.

If this is not correct, can anyone advise on how I can code
this functionality?

TIA,
Rich

 

[Guest]

Hi Bob,

Thanks for turning me in the right direction.

I haved removed the 2nd drop down as you suggest and when the company is
selected, the dropdown becomes enabled as it should. However, I am no sure
how to create the query as the recordsource. I created a query and set that
query as the recordsource for the dept dropdown. I get a MessageBox-type
prompt and when I enter a value, noting get populated to the dropdown. CAn
you explain?

Thanks,
rich