adding a 2nd look up field to a form or table

K

KristineO

I am using Access 2003:(
I am creating a database for multiple users.
I have a table for case status that is used to indicate different types of
cases. There are 4 common status types that are already listed in a drop
down list, and only a handful of "other" types that are only used a couple
times throughout the table. In order to limit the "other" types to be
entered and to limit the data that might be entered in this field I want to
create a table of "others" that would be used if "Other" is chosen from the
1st drop down list of status types.
Is this possible?
I have created a separate table of "Other" types.
 
K

Ken Sheridan

In the table include fields Status and OtherStatus and add combo boxes bound
to each on the form, cboStatus and cboOtherStatus say. You can then
enable/disable the second combo box depending on what's selected in the
first. You need to do this in a couple of places.

1. In the form's Current event procedure, so that it caters for existing
records:

If Not Me.NewRecord Then
Me.cboOtherStatus.Enabled = (Me.cboStatus = "Other")
Else
Me.cboOtherStatus.Enabled = False
End If

2. In the AfterUpdate event procedure of the cboStatus control, to cater
for when a user selects an item:

If Me.cboStatus = "Other")
Me.cboOtherStatus.Enabled = True
Me.cboOtherStatus.SetFocus
Me.cboOtherStatus.DropDown
Else
Me.cboStatus = Null
Me.cboOtherStatus.Enabled = False
End If

You might also want to enforce a value for OtherStatus if 'Other' is the
value of Status. You can do this in the form's BeforeUpdate event procedure,
which has a Cancel argument whose return value you can set to True:

Const conMESSAGE = "Please select a value for Other Status."

If Me.cboStatus = "Other" Then
If IsNull(Me.cboOtherStatus) Then
MsgBox conMESSAGE, vbExclamation, "Incomplete Data"
Cancel = True
End If
End If

Be aware that controlling the data integrity at form level like this does
not prevent invalid data being entered in other ways, so be sure that users
can only enter/edit data via the form.

One thing you'll need to be careful of is querying the table on these
columns. Taking a banking analogy, say for instance you wanted to return
rows where the status was 'Cleared' or ANY of the 'other' status values then
it’s a simple operation on the Status column using the IN operator:

WHERE Status IN("Cleared", "Other")

If however, you wanted to return rows where Status is 'Cleared' or the
'other' status is 'Referred to drawer' then you'd need a Boolean OR operation
on both columns:

WHERE Status = "Cleared" OR OtherStatus = "Referred to drawer"

Ken Sheridan
Stafford, England
 
R

Ron2006

Another aspect to consider is the following:

A person selects Other then selects an item in the Otherstatus
dropdown that is fine and correct.

Now if the person goes back and selects something else in the first
combo box you will NOW have to clear out the other status selection
besides disabeling the otherstatus combo box.

Ron
 
R

Ron2006

I just reread Ken's reply.

He tried to do that but had one field name incorrect.

2. In the AfterUpdate event procedure of the cboStatus control, to
cater
for when a user selects an item:


If Me.cboStatus = "Other")
Me.cboOtherStatus.Enabled = True
Me.cboOtherStatus.SetFocus
Me.cboOtherStatus.DropDown
Else
Me.cboStatus = Null
Me.cboOtherStatus.Enabled = False
End If




It should be

2. In the AfterUpdate event procedure of the cboStatus control, to
cater
for when a user selects an item:


If Me.cboStatus = "Other")
Me.cboOtherStatus.Enabled = True
Me.cboOtherStatus.SetFocus
Me.cboOtherStatus.DropDown
Else
Me.cboOtherStatus = Null
Me.cboOtherStatus.Enabled = False
End If



Ron
 
R

Ron2006

You are welcome.

It is amazing how a different set of eyes can see things like that. It
is amazing how many times I have found the problem in a program be the
very act of trying to explain to someone else what was the original
intent of the code And have had someone look at the code and say why
are you doing such and such and have it be the problem.

Have a great day.

Ron
 
Top