limit data entry question

[Heather]

Hello!

Simple question (I think):

I have a form that enters all employees under one company
name. Each company can have one primary contact and one
secondary contact. The form is set up for the user to
choose an employee's name from a combo box, then choose
either "primary" or "secondary" from cboStatus.
There can only be one 'primary' employee and
one 'secondary' employee.
How do I create the scenario that limits the user to
choosing only one 'primary' employee and one 'secondary'
employee per company? There may be 10 employees, the
other 8 would not have correlating data in cboStatus.

Thanks!!!

 

[Graham R Seach]

Heather,

I would do this from the Company form. Add two columns to the Company table;
PriContact an SecContact. Assuming the primary key in the Employees table is
an Autonumber, make PriContact and SecContact both Long Integer.

Add two combo's to the Company form; cboPriContact and cboSecContact, and
bind them to the appropriate columns in the Company table. Set the RowSource
property of cboPriContact as follows:
SELECT EmpID, [Name] FROM tblEmployees WHERE EmplID <>
Nz(Forms!frmCompany!cboSecContact, 0)

Then set the RowSource property for cboSecContact as follows:
SELECT EmpID, [Name] FROM tblEmployees WHERE EmplID <>
Nz(Forms!frmCompany!cboPriContact, 0)

The above RowSource queries will display a list of all employees that have
not been selected in the other combo.

Of course, change the names of the columns and tables to reflect what you
actually have.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Heather]

Thanks graham, but I can't change the table as it is a
standard table set up from dozens franchises. Can I
apply this to one combo box with an if then or
something? I'm kind of a novice at code, but learning
fast

Heather

-----Original Message-----
Heather,

I would do this from the Company form. Add two columns to the Company table;
PriContact an SecContact. Assuming the primary key in the Employees table is
an Autonumber, make PriContact and SecContact both Long Integer.

Add two combo's to the Company form; cboPriContact and cboSecContact, and
bind them to the appropriate columns in the Company table. Set the RowSource
property of cboPriContact as follows:
SELECT EmpID, [Name] FROM tblEmployees WHERE EmplID

Nz(Forms!frmCompany!cboSecContact, 0)

Then set the RowSource property for cboSecContact as follows:
SELECT EmpID, [Name] FROM tblEmployees WHERE EmplID

 

[Graham R Seach]

Heather,

If you can't add the appropriate columns to the table, then how do you
intend to store the values?

Can you show me the table structure behind the form you're talking about.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

Thanks graham, but I can't change the table as it is a
standard table set up from dozens franchises. Can I
apply this to one combo box with an if then or
something? I'm kind of a novice at code, but learning
fast

Heather

-----Original Message-----
Heather,

I would do this from the Company form. Add two columns to the Company table;
PriContact an SecContact. Assuming the primary key in the Employees table is
an Autonumber, make PriContact and SecContact both Long Integer.

Add two combo's to the Company form; cboPriContact and cboSecContact, and
bind them to the appropriate columns in the Company table. Set the RowSource
property of cboPriContact as follows:
SELECT EmpID, [Name] FROM tblEmployees WHERE EmplID

Nz(Forms!frmCompany!cboSecContact, 0)

Then set the RowSource property for cboSecContact as follows:
SELECT EmpID, [Name] FROM tblEmployees WHERE EmplID

Nz(Forms!frmCompany!cboPriContact, 0)

The above RowSource queries will display a list of all employees that have
not been selected in the other combo.

Of course, change the names of the columns and tables to reflect what you
actually have.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html





.