Sharon,
There are several ways to do this:
1. Query the table for the desired Value, dump the results into a RecordSet,
and then check whether the RecordSet is Null or not
2. Use the DCount function to count whether there are 0 or 1 instances of
the desired Value in the table
3. Use the DLookup function to check for the desired Value (function returns
Null if Value is not found)
4. Open the table into a recordset and iterate through it checking for the
Value (clunky way to do it)
5. Use the SQL function COUNT in the query, dump the results into a
RecordSet, and then see if the result is 0 or 1
And there are probably more variations. I personally would use #3. Replace
your inside If Statement with the following, except change "AreaFieldName"
in the DLookup statement to whatever the field name is in your
tblAreaFieldName table.
*************
If Not IsNull(DLookup("AreaFieldName", "tblAreaFieldName", "AreaFieldName =
'" & area & "'")) Then
Exit Sub
Else
MsgBox "This field name does not match existing field name", vbOKOnly
Me.DifferentAreaName.Value = True
End If
*************
HTH,
Toby
----- Original Message -----
From: "sharon4855" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Tuesday, August 02, 2005 4:03 AM
Subject: [AccessDevelopers] Comparing value in a field with values in a
table
> Hi
>
> I have a probably quite basic query which I have been able to handle
> in a crude way, but the user has requested a more refined way of
> handling the problem.
>
> In a subform I have two fields, one textbox called 'attributename'
> and one checkbox called attributetype. If the text entered in the
> attributename field matches the data in a table (tblAreaFieldName)
> and the attributetype is ticked, nothing needs to be done. However,
> if the attributetype is ticked and the attributename text does not
> match the data in the tblAreaFieldName, a message has to be
> displayed to the user and another checkbox (areafield) ticked
> automatically.
>
> The way I initially handled this was as follows, on the AfterUpdate
> event of the attributename:
>
> ******************************************************************
> Dim area As String
> Dim areatrue As String
>
> area = Me.attribute.Value
> areatrue = Me.areafield.Value
>
> If areatrue = True Then
> If area = "AREA" Or area = "AREA0" Or area = "AREA_HA" Or
> area = "AREA_HA0" Or area = "HECTARES" Or area = "MEASURE" Or area
> = "SITEAREA" Or area = "STAT_AREA" Or area = "TOT_AREA" Then
> Exit Sub
> Else
> msgbox "This field name does not match existing field
> name", vbOKOnly
> Me.DifferentAreaName.Value = True
> End If
> End If
> ******************************************************************
>
> This does the job, but as I said it is very crude and relies on
> someone amending the code every time a new area name is added to the
> list. A neater way of doing this is to compare the value in the
> attributename field with the contents of the table. Unfortunately
> my VBA skills are so rusty I can't remember how to do this: I have a
> vague idea about opening a recordset but would be extremely grateful
> for some help with this.
>
> Thanks very much,
> Sharon
>
>
>
>
>
>
> Please zip all files prior to uploading to Files section.
> Yahoo! Groups Links
>
>
>
>
>
>
------------------------ Yahoo! Groups Sponsor --------------------~-->
<font face=arial size=-1><a
href="http://us.ard.yahoo.com/SIG=12hf3r93i/M=362329.6886308.7839368.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1123008131/A=2894321/R=0/SIG=11dvsfulr/*http://youthnoise.com/page.php?page_id=1992
">Fair play? Video games influencing politics. Click and talk back!</a>.</font>
--------------------------------------------------------------------~->
Please zip all files prior to uploading to Files section.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AccessDevelopers/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
