Thanks very much for this suggestion Toby - it works great. Sharon --- In [email protected], "Toby Bierly" <[EMAIL PROTECTED]> wrote: > 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=12h3kg36g/M=362131.6882499.7825260.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1123085844/A=2889191/R=0/SIG=10r90krvo/*http://www.thebeehive.org ">Get Bzzzy! (real tools to help you find a job) Welcome to the Sweet Life - brought to you by One Economy</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/
