How do I look though a set of records to find whether a specific value exists?

[Flora]

I want to look through a set of records to determine if the value
"other" exists (like under the COLOUR column below). If "other" exists
in the COLOUR column, then I want to enable the OTHER text box in the
form to allow the user to enter the specific colour. For example:

ITEM_ID COLOUR (combo box) OTHER (text box)
A1 red
A1 blue
A1 other azure

B2 yellow
B2 green

C3 blue
C3 other emerald

Currently I have a form with the primary key ITEM_ID and the OTHER text
box field. I have a subform that shows all the COLOURs for each item.
I want the OTHER text box on the form to be enabled only when "other"
is selected under COLOUR in the subform.

How do I filter through the selected group of records (for example, for
ITEM_ID A1) to determine whether "other" has been selected, so I can
either enable or disable the OTHER text box? What is the best method
to achieve the desired result?? Any options? Please show me any
syntax you'd recommend.

Thanks so much!!

 

[Allen Browne]

If this subform is in Datasheet or Continuous Form view, you cannot
enable/disable the text box on selective rows.

If it is in Form View, you could enable/disable the Other text box in the
AfterUpdate event procedure of the Colour combo:
Private Sub COLOUR_AfterUpdate()
Dim bEnabled As Boolean
bEnabled = Nz(Me.Colour = "Other", False)
If Me.Other.Enabled <> bEnabled Then
Me.Other.Enabled = bEnabled
End If
End Sub
Then call this from the Current event of the form as well:
Private Sub Form_Current()
Call COLOUR_AfterUpdate
End Sub
You will need error handling to move the focus if the disabled control has
focus.

If the form is continuous and you are using Access 2000 or later, you can
simulate the effect with Conditional formatting. Select the Other text box
in form design view, and choose Conditional Formatting on the Format menu.
Use:
Expression... [COLOUR] = "Other"
and choose a grey background that looks like it is disabled. More info:
http://www.lebans.com/conditionalformatting.htm

Now that we have given you lots of choices, if you really wanted to look up
the table to see if the Other value is there you could do that with
DLookup():
Not IsNull(DLookup("ITEM_ID", "Table1", "(ITEM_ID = " & [ITEM_ID] & ")
AND (COLOUR = 'Other')"))
More info on DLookup():
http://allenbrowne.com/casu-07.html

 

[Flora]

Thank you for your quick response. Sorry to bother.. I have another
question about this:

I think I'd like to go with the dlookup solution but I'm not sure about
what context to put it into. MS VBA Help doesn't really give any
information about context either. It appears that I have to make the
dlookup equal to a variable. Should this variable be boolean as you
showed in your first solution above or a variant as below:

Dim varX As Variant
varX = Not IsNull(DLookup...

Then I compare varX to criteria to effect the OTHER text box?
Also, what is the function of the "Not" in the Not IsNull clause?

Could you please translate your solution to english? This would help
me apply the solution to my specific problem. Here is my attempt:
Lookup an ITEM_ID from table1 where the ITEM_ID matches the one we're
specifically looking for AND where the colour is Other. If there is an
item with that ID and with the colour OTHER, then the statement returns
a true. Otherwise the statement returns a false. <-- is this the
correct translation?

Would I then proceed to make the OTHER text box enabled if true and
disabled if false?

Thank you again for your help.

 

[Allen Browne]

DLookup() is explained in the link posed previously.
It takes 3 arguments.
The 3rd one is the criteria of what to match.

In the statement:
DLookup("ITEM_ID", "Table1", "(ITEM_ID = " & [ITEM_ID] & ") AND (COLOUR
= 'Other')"))
the criteria string is built up by concatenating the value of the ITEM_ID in
the form into the string. For example, if the ITEM_ID in the form is 7, the
string becomes:
(ITEM_ID = 7) AND (COLOUR = 'Other')
The DLookup() is therefore saying:
Look up the Item_ID in Table1 where (ITEM_ID = 7) AND (COLOUR =
'Other').

If there is a match, it will return 7.
If there is no match, it will return Null.
We therefore test if we got a Null with IsNull().
That is True if there was no match.
We want to change it so we get False if there was not match.
So, we add Not to reverse it.
The result is a True/False value, you assign it to a boolean value.

You can then set the Enabled property of your control based on the result,
unless this is a continuous form. Don't forget to use the form's Current
event as well.