Looking to have one field based on two other fields.

[David]

Hi,

This is probably really simple but kinda has me stumped. I have taken over a
data base and am looking to modify it to make it more user friendly.
Currently, I have a Combo box with 4 separate selections. I have four
individuals that make credits based on 4 separate criteria. Currently, it
looks something like this.

[Prov_Code]
Ontario - Reg.
Ontario - Not reg.
Other - Reg.
Other - Not reg.

This box has two coloumns and the Row source is as follows "Ontario -
Reg.";"John Doe";:"Ontario - Not reg.";"Jane Doe" etc.

What I would like to do is to separate this into two combo boxs, one for
Registered or not Registered, (two selections) and another having the
provinces. The criteria I would need is as follows for each of these
individuals. I am not sure if I should be using a Dlookup, IIf, or a 3
coloumn setup or have another field set up based on the these two fields.
Any help would be much appreciated.

[Type] Registered
[Province] ON

[Type] Registered
[Province] <>ON

[Type] Not Registered
[Province] ON

[Type] Not Registered
[Province] <>ON

Thank you
David

[Bob Quintal]

"David" <so*******@somewhere.ca> wrote in
news:aa*********************@news20.bellglobal.com :

Hi,

This is probably really simple but kinda has me stumped. I
have taken over a data base and am looking to modify it to
make it more user friendly. Currently, I have a Combo box with
4 separate selections. I have four individuals that make
credits based on 4 separate criteria. Currently, it looks
something like this.

[Prov_Code]
Ontario - Reg.
Ontario - Not reg.
Other - Reg.
Other - Not reg.

This box has two coloumns and the Row source is as follows
"Ontario - Reg.";"John Doe";:"Ontario - Not reg.";"Jane Doe"
etc.

What I would like to do is to separate this into two combo
boxs, one for Registered or not Registered, (two selections)
and another having the provinces. The criteria I would need is
as follows for each of these individuals. I am not sure if I
should be using a Dlookup, IIf, or a 3 coloumn setup or have
another field set up based on the these two fields. Any help
would be much appreciated.

[Type] Registered
[Province] ON

[Type] Registered
[Province] <>ON

[Type] Not Registered
[Province] ON

[Type] Not Registered
[Province] <>ON

Thank you
David

The data should be stored in a table, if it can ever change.
Employees come and go. It should have just the three columns,
Province, Registered and Administrator. I'll call it Assignments
for the purpose of this example.

The province combo should use a rowsource "SELECT DISTINCT
Province FROM Assignments;"

The Registered combo needs a rowsource
"Select Administrator, Registered FROM Assignments " _
& "WHERE Province = '" & me!cboProvince & "'"

Bind the Administrator to your main table. You can make it's
column width 0" to hide the name

Put a me.cboRegistered.requery in cboProvince's afterupdate
event

Having writ all that, you may be able to simplify the form if
each of your options can only be two states, using checkboxes.

--
Bob Quintal

PA is y I've altered my email address.

[David]

Hi Bob,

Thank you very much for your reply. I am not that fluent in access and have
to do this on Access 97 at the moment. We will be upgrading to Access 2000
in the near future at work. I did not quite understand your programming but
was able to take an example of the 2 combo box synchronize form and modify
it to my needs. I am really amazed at what this product can do and the level
of support that you people provide through this newsgroup.

Thanks again
David


"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...

"David" <so*******@somewhere.ca> wrote in
news:aa*********************@news20.bellglobal.com :

Hi,

This is probably really simple but kinda has me stumped. I
have taken over a data base and am looking to modify it to
make it more user friendly. Currently, I have a Combo box with
4 separate selections. I have four individuals that make
credits based on 4 separate criteria. Currently, it looks
something like this.

[Prov_Code]
Ontario - Reg.
Ontario - Not reg.
Other - Reg.
Other - Not reg.

This box has two coloumns and the Row source is as follows
"Ontario - Reg.";"John Doe";:"Ontario - Not reg.";"Jane Doe"
etc.

What I would like to do is to separate this into two combo
boxs, one for Registered or not Registered, (two selections)
and another having the provinces. The criteria I would need is
as follows for each of these individuals. I am not sure if I
should be using a Dlookup, IIf, or a 3 coloumn setup or have
another field set up based on the these two fields. Any help
would be much appreciated.

[Type] Registered
[Province] ON

[Type] Registered
[Province] <>ON

[Type] Not Registered
[Province] ON

[Type] Not Registered
[Province] <>ON

Thank you
David

The data should be stored in a table, if it can ever change.
Employees come and go. It should have just the three columns,
Province, Registered and Administrator. I'll call it Assignments
for the purpose of this example.

The province combo should use a rowsource "SELECT DISTINCT
Province FROM Assignments;"

The Registered combo needs a rowsource
"Select Administrator, Registered FROM Assignments " _
& "WHERE Province = '" & me!cboProvince & "'"

Bind the Administrator to your main table. You can make it's
column width 0" to hide the name

Put a me.cboRegistered.requery in cboProvince's afterupdate
event

Having writ all that, you may be able to simplify the form if
each of your options can only be two states, using checkboxes.

--
Bob Quintal

PA is y I've altered my email address.