Writing an IF/Then statement in a table

T

T.Banks

How do I make a drop down list that is dependent on the previous field.
Example:
If [Department]="Security" then [Area]=[SecuityLocations] or
If [Department]="Treatment" then [Area]=[TreatmentLocations]
[Department] field is a drop down list with "Security", "Treatment"
[SecurityLocations] field is a drop down list with "Units", "Isolation", etc.
[TreatmentLocations] field is a drop down list with "Counselor's Office,
"Treatment Office", etc
[Area] field is the drop down showing either the [SecurityLocations] list or
the [TreatmentLocations] list depending on what was chosen in the
[Department] list
-- Please help if this makes sense. Thanks/
T.Banks
 
E

Evi

Do you mean that you want some code in your form to put those values into a
field? or do you mean that you want a combo box in your form or do you mean
that you want to write an IIF statement in a query?
Or do you mean that you want one combo box to change the choice of
selections in another one.
I suspect that you may be using Lookup fields in your tables to input data.
Fess up! is this true?

Evi
 
T

T.Banks

Yes, I'm a newbie and I've been using the LookUp Fields in the table. I'm
unsure how to use a query or a form macro to do what I want. I simply want
to create a list that a user can choose from in one field and that will
produce a specific list out of many in the next field. If field one has a
lookup list box containing "Security", "Treatment", etc. I want the next
field to show only the [SecuirtyAreas] if "Security" was chosen .
[SecurityAreas] has a list of security areas the user can choose from. Being
new to Access I'm finding it hard to even pose a question that is easy to
understand. Thanks--
T.Banks


Evi said:
Do you mean that you want some code in your form to put those values into a
field? or do you mean that you want a combo box in your form or do you mean
that you want to write an IIF statement in a query?
Or do you mean that you want one combo box to change the choice of
selections in another one.
I suspect that you may be using Lookup fields in your tables to input data.
Fess up! is this true?

Evi

T.Banks said:
How do I make a drop down list that is dependent on the previous field.
Example:
If [Department]="Security" then [Area]=[SecuityLocations] or
If [Department]="Treatment" then [Area]=[TreatmentLocations]
[Department] field is a drop down list with "Security", "Treatment"
[SecurityLocations] field is a drop down list with "Units", "Isolation", etc.
[TreatmentLocations] field is a drop down list with "Counselor's Office,
"Treatment Office", etc
[Area] field is the drop down showing either the [SecurityLocations] list or
the [TreatmentLocations] list depending on what was chosen in the
[Department] list
-- Please help if this makes sense. Thanks/
T.Banks
Click to expand...
Click to expand...
 
E

Evi

I quite understand that asking the right question is as difficult as getting
the right answer :)
May I suggest that you change the lookup fields in your table so that they
are now text boxes. Use combo boxes in a proper form to achieve what you
need.
I'll make up table and field names to give you an idea of how to do this.
Replace them with the real names. If it's not clear enough, please give me
the table and field names and what you want to call your form and combos
Form is FrmChoice
One combo box named cboCategory will be based on your table containing
Security, Treatment etc TblCategory
Your other table, TblItems should have a foreign key field (CategoryID)
linking it to TblCategory (so that Access will know which are Security
Areas).

Create a query based on TblItems
Under CategoryID, in the Criteria row type
=Forms![FrmChoice]![cboCategory]

Base the other combo, cboItems, on this query.

In Form Design View, click on cboCategory. Click on the Properties button.
On the Events tab, click next to After Update and choose Event Procedure.
Click just right of that to open a code page
Above where it says

End Sub

type

Me.cboItems.Requery

(this ensures that each time you make a choice in cboCategory, cboItems
changes its filter).

If any of this is unclear, please write back.
Evi




T.Banks said:
Yes, I'm a newbie and I've been using the LookUp Fields in the table. I'm
unsure how to use a query or a form macro to do what I want. I simply want
to create a list that a user can choose from in one field and that will
produce a specific list out of many in the next field. If field one has a
lookup list box containing "Security", "Treatment", etc. I want the next
field to show only the [SecuirtyAreas] if "Security" was chosen .
[SecurityAreas] has a list of security areas the user can choose from. Being
new to Access I'm finding it hard to even pose a question that is easy to
understand. Thanks--
T.Banks


Evi said:
Do you mean that you want some code in your form to put those values into a
field? or do you mean that you want a combo box in your form or do you mean
that you want to write an IIF statement in a query?
Or do you mean that you want one combo box to change the choice of
selections in another one.
I suspect that you may be using Lookup fields in your tables to input data.
Fess up! is this true?

Evi

T.Banks said:
How do I make a drop down list that is dependent on the previous field.
Example:
If [Department]="Security" then [Area]=[SecuityLocations] or
If [Department]="Treatment" then [Area]=[TreatmentLocations]
[Department] field is a drop down list with "Security", "Treatment"
[SecurityLocations] field is a drop down list with "Units",
Click to expand...
"Isolation",
etc.
[TreatmentLocations] field is a drop down list with "Counselor's Office,
"Treatment Office", etc
[Area] field is the drop down showing either the [SecurityLocations]
Click to expand...
list
or
the [TreatmentLocations] list depending on what was chosen in the
[Department] list
-- Please help if this makes sense. Thanks/
T.Banks
Click to expand...
Click to expand...
Click to expand...
 
T

T.Banks

Thanks for the help. After several hours I got it to work. The only problem
I'm having now is the requery. While in the form the [items] field will not
reset unless I exit the form and restart it. Example: If I choose "Security"
then my [items] field will list my security areas. If I change "Security" to
"Treatement" the [items] field will still only show security areas. If I
exit the form and restart it and then choose "Treatment" the [items] field
will show the treatment areas. But again, if I change "Treatment" to
"Security" the [items] field will still only show treatment areas. This it
true to when I go to more records. I added the requery like you advised but
does not seem to work.
--
T.Banks


Evi said:
I quite understand that asking the right question is as difficult as getting
the right answer :)
May I suggest that you change the lookup fields in your table so that they
are now text boxes. Use combo boxes in a proper form to achieve what you
need.
I'll make up table and field names to give you an idea of how to do this.
Replace them with the real names. If it's not clear enough, please give me
the table and field names and what you want to call your form and combos
Form is FrmChoice
One combo box named cboCategory will be based on your table containing
Security, Treatment etc TblCategory
Your other table, TblItems should have a foreign key field (CategoryID)
linking it to TblCategory (so that Access will know which are Security
Areas).

Create a query based on TblItems
Under CategoryID, in the Criteria row type
=Forms![FrmChoice]![cboCategory]

Base the other combo, cboItems, on this query.

In Form Design View, click on cboCategory. Click on the Properties button.
On the Events tab, click next to After Update and choose Event Procedure.
Click just right of that to open a code page
Above where it says

End Sub

type

Me.cboItems.Requery

(this ensures that each time you make a choice in cboCategory, cboItems
changes its filter).

If any of this is unclear, please write back.
Evi




T.Banks said:
Yes, I'm a newbie and I've been using the LookUp Fields in the table. I'm
unsure how to use a query or a form macro to do what I want. I simply want
to create a list that a user can choose from in one field and that will
produce a specific list out of many in the next field. If field one has a
lookup list box containing "Security", "Treatment", etc. I want the next
field to show only the [SecuirtyAreas] if "Security" was chosen .
[SecurityAreas] has a list of security areas the user can choose from. Being
new to Access I'm finding it hard to even pose a question that is easy to
understand. Thanks--
T.Banks


Evi said:
Do you mean that you want some code in your form to put those values into a
field? or do you mean that you want a combo box in your form or do you mean
that you want to write an IIF statement in a query?
Or do you mean that you want one combo box to change the choice of
selections in another one.
I suspect that you may be using Lookup fields in your tables to input data.
Fess up! is this true?

Evi

How do I make a drop down list that is dependent on the previous field.
Example:
If [Department]="Security" then [Area]=[SecuityLocations] or
If [Department]="Treatment" then [Area]=[TreatmentLocations]
[Department] field is a drop down list with "Security", "Treatment"
[SecurityLocations] field is a drop down list with "Units", "Isolation",
etc.
[TreatmentLocations] field is a drop down list with "Counselor's Office,
"Treatment Office", etc
[Area] field is the drop down showing either the [SecurityLocations] list
or
the [TreatmentLocations] list depending on what was chosen in the
[Department] list
-- Please help if this makes sense. Thanks/
T.Banks
Click to expand...
Click to expand...
Click to expand...
 
T

T.Banks

My problem was I had to allow it to run. The "Security Warning" was blocking
it.

I placed the following [Event Procedure] on the "On Change" row in Property
Sheet.

Private Sub cboCategory_Change()
Me.cboItem.Requery
End Sub

All is working now and I thank all of you for your help. I've spent several
days and hours trying to figure out how to write this procedure and would not
have been able to do it if I hadn't received your advice. Thanks much!
 
E

Evi

I've never used on Change before for this kind of feature (I'm not really
sure of the difference between On Change and After Update but I bet there is
one!). Didn't it work in the After Update of cboCategory?
Evi
T.Banks said:
My problem was I had to allow it to run. The "Security Warning" was blocking
it.

I placed the following [Event Procedure] on the "On Change" row in Property
Sheet.

Private Sub cboCategory_Change()
Me.cboItem.Requery
End Sub

All is working now and I thank all of you for your help. I've spent several
days and hours trying to figure out how to write this procedure and would not
have been able to do it if I hadn't received your advice. Thanks much!
--
T.Banks


John W. Vinson said:
It does work if done correctly. Please post your code.
Click to expand...
Click to expand...
 
J

John Spencer

The change event runs after every keystroke or after you select an item from
a list box or combo box.

After Update runs once. Usually when you exit the control or after you
select a new item from a list or combo box.

In most cases, you would use the after update event if you wanted to execute
code dependent on the final value in the control. If you wanted to execute
the code for each change of the content (every keystroke for example) then
you would use the Change evetn
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Evi said:
I've never used on Change before for this kind of feature (I'm not really
sure of the difference between On Change and After Update but I bet there
is
one!). Didn't it work in the After Update of cboCategory?
Evi
T.Banks said:
My problem was I had to allow it to run. The "Security Warning" was blocking
it.

I placed the following [Event Procedure] on the "On Change" row in Property
Sheet.

Private Sub cboCategory_Change()
Me.cboItem.Requery
End Sub

All is working now and I thank all of you for your help. I've spent several
days and hours trying to figure out how to write this procedure and would not
have been able to do it if I hadn't received your advice. Thanks much!
--
T.Banks


wrote:

I added the requery like you advised but
does not seem to work.

It does work if done correctly. Please post your code.
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

My problem was I had to allow it to run. The "Security Warning" was blocking
it.

I placed the following [Event Procedure] on the "On Change" row in Property
Sheet.

Private Sub cboCategory_Change()
Me.cboItem.Requery
End Sub

All is working now and I thank all of you for your help. I've spent several
days and hours trying to figure out how to write this procedure and would not
have been able to do it if I hadn't received your advice. Thanks much!
Click to expand...

As Evi and John warn, that's NOT the correct event; use the combo's
AfterUpdate event instead.
 
E

Evi

Thanks John, I can see how that would be useful (though not for the writer
of this question).
Evi
John Spencer said:
The change event runs after every keystroke or after you select an item from
a list box or combo box.

After Update runs once. Usually when you exit the control or after you
select a new item from a list or combo box.

In most cases, you would use the after update event if you wanted to execute
code dependent on the final value in the control. If you wanted to execute
the code for each change of the content (every keystroke for example) then
you would use the Change evetn
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Evi said:
I've never used on Change before for this kind of feature (I'm not really
sure of the difference between On Change and After Update but I bet there
is
one!). Didn't it work in the After Update of cboCategory?
Evi
T.Banks said:
My problem was I had to allow it to run. The "Security Warning" was blocking
it.

I placed the following [Event Procedure] on the "On Change" row in Property
Sheet.

Private Sub cboCategory_Change()
Me.cboItem.Requery
End Sub

All is working now and I thank all of you for your help. I've spent several
days and hours trying to figure out how to write this procedure and
Click to expand...
would
not
have been able to do it if I hadn't received your advice. Thanks much!
--
T.Banks


:
Click to expand...
wrote:

I added the requery like you advised but
does not seem to work.

It does work if done correctly. Please post your code.
Click to expand...
Click to expand...
Click to expand...
 
T

T.Banks

I will change it. Thanks
--
T.Banks


John W. Vinson said:
My problem was I had to allow it to run. The "Security Warning" was blocking
it.

I placed the following [Event Procedure] on the "On Change" row in Property
Sheet.

Private Sub cboCategory_Change()
Me.cboItem.Requery
End Sub

All is working now and I thank all of you for your help. I've spent several
days and hours trying to figure out how to write this procedure and would not
have been able to do it if I hadn't received your advice. Thanks much!
Click to expand...

As Evi and John warn, that's NOT the correct event; use the combo's
AfterUpdate event instead.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I set up a column to populate... 3
if then statements using "or" and "and" 6
Menu Tree Style Entries 3
Field in Form 1
Drop down list -> show different controls? 2
Access 2007 - Table A lookup values based on Table B 1
Auto populate a cell based off a look up table 2
Writing an If...then event/query/thingy! 2

Top