Re: ValidationRule DAO extra character problem

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Christine (ctyrrell_at_stny.rr.com)
Date: 08/06/04 

Date: 5 Aug 2004 18:04:44 -0700

Dear Allen,
Thanks so very much for sticking with me on this.

Your SetProperty function is actually very similar to the one I used
and since the ValidationRule property always does exist, since it is
builtin, I think it would always go through the first branch of your
If statement, so it seems as if we are doing the same thing. The
question is, are we really getting different results?....

It has occured to me that my initial post was not very clear and
precise and that I may have ommitted the most important piece of
information which is that the extra character I get is actually
invisible. If I open the table in design view and look at the property
I do not see a 3rd character. However, I know it is there. Perhaps the
following Debug Window items will help clarify more exactly:

1. Set the property directly, through code:
currentdb.TableDefs("tblATest").Fields("SumID").Properties("ValidationRule")
= ">0"
-----------------------------------------------------------------------------
2. Print out the contents of the property surrounded by single quotes
to show that there is an extra character:
?"'" & currentdb.TableDefs("tblATest").Fields("SumID").Properties("ValidationRule")
& "'"
'>0 ' 'Note what looks like a space before the terminating
quote
----------------------------------------------------------------------------
3. Get the length of the property value, confirming that it is 3
characters and not two:
?len(currentdb.TableDefs("tblATest").Fields("SumID").Properties("ValidationRule"))
 3
--------------------------------------------------------------------------
4. This is the thing that boggles my mind. I am asking if the property
value is equal to what I wanted to set it to and it is saying that it
is, and yet, as we have just seen, it is not really because one is a 2
character string and the other is a 3 character string:
?currentdb.TableDefs("tblATest").Fields("SumID").Properties("ValidationRule")
= ">0"
True 'This response really shocks me!!!!!!!!
'---------------------------------------------------------------------
5. Looking at the ascii values of each of the characters and trying to
understand. It seems as if those other posts that talked about Chr(0)
at the end were the same exact issue.
?asc(mid(currentdb.TableDefs("tblATest").Fields("SumID").Properties("ValidationRule"),1,1))
 62 'First character (>)
?asc(mid(currentdb.TableDefs("tblATest").Fields("SumID").Properties("ValidationRule"),2,1))
 48 'Second character (0)
?asc(mid(currentdb.TableDefs("tblATest").Fields("SumID").Properties("ValidationRule"),3,1))
 0 'Third character - my invisible character
?asc(chr(0)) .See how chr(0) gets the same asc result as my
invisible char
 0
?asc(0)
 48
?asc("0")
 48
-------------------------------------------------------------------
If I then go to another field and set the property manually through
the user interface of the design view of the table and then go back to
the Debug window and run through the same steps, I see that this time
I do only have a 2 character string. In other words I cannot reproduce
via code the same result as setting the property manually.

Sorry for the inordinate amount of detail. But since you were kind
enough to take the time and interest to respond I thought it was only
fair to give you all the clues and maybe together we can get to the
bottom of it. If you could try to reproduce my Debug Window steps, I'd
be most interested to see if you get the same results or not.

Once again, thanks so very much for your help.
Regards,
Christine

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message news:<#QJAcBjeEHA.2044@TK2MSFTNGP10.phx.gbl>...
> Hmm. Okay, here is the full code I used, which actually creates the property
> only if it does not already exist:
>
> Function SetPropertyDAO(obj As Object, _
> strPropertyName As String, _
> intType As Integer, _
> varValue As Variant, _
> Optional strErrMsg As String) As Boolean
> On Error GoTo ErrHandler
> 'Purpose: Set a property for an object, creating if necessary.
> 'Arguments: obj = the object whose property should be set.
> ' strPropertyName = the name of the property to set.
> ' intType = the type of property (needed for creating)
> ' varValue = the value to set this property to.
> ' strErrMsg = string to append any error message to.
>
> If HasProperty(obj, strPropertyName) Then
> obj.Properties(strPropertyName) = varValue
> Else
> obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
> varValue)
> End If
> SetPropertyDAO = True
>
> ExitHandler:
> Exit Function
>
> ErrHandler:
> strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & _
> " not set to " & varValue & ". Error " & Err.Number & " - " &
> Err.Description & vbCrLf
> Resume ExitHandler
> End Function
>
> Public Function HasProperty(obj As Object, strPropName As String) As Boolean
> 'Purpose: Return true if the object has the property.
> Dim varDummy As Variant
>
> On Error Resume Next
> varDummy = obj.Properties(strPropName)
> HasProperty = (Err.Number = 0)
> End Function
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Christine" <ctyrrell@stny.rr.com> wrote in message
> news:2474292.0408040223.54bc0a70@posting.google.com...
> > Hi Allen,
> > Thank you so much for taking the time to address my problem.
> > For some reason, we are not getting the same results.
> >
> > When I try to do as you suggest, I get the Error:
> > Error: 3367 Cannot append. An object with that name already exists
> > in the collection.
> >
> > However, if I try to delete the property so that I can add it, I get:
> > Error: 3384 Cannot delete a built in property.
> >
> > I wonder if there is a setting someplace that can control whether
> > ValidationRule is a built in property. In browsing the news groups
> > about this problem, I did see others having my same problem (Cannot
> > append) also.
> >
> > Once again, thanks so much and I am hoping you will stick with me on
> > this and perhaps have some more ideas.
> >
> > G'day, Christine
> >
> > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:<O#oGd8WeEHA.732@tk2msftngp13.phx.gbl>...
> > > Hi Christine.
> > >
> > > I am unable to reproduce this problem in Access 2000 (nor in Access
> 2003).
> > >
> > > I used:
> > > fld.Properties.Append fld.CreateProperty("ValidationRule", dbText,
> > > ">0")
> > > and received a 2-character result.
> > >
> > >
> > > "Christine" <ctyrrell@stny.rr.com> wrote in message
> > > news:2474292.0408021814.21e0553f@posting.google.com...
> > > > I am trying to set the ValidationRule for a table field using DAO but
> > > > get an extra character at the end, that I cannot get rid of. For
> > > > example:
> > > > fld.Properties("ValidationRule") = ">0" results in 3 characters being
> > > > in the property. The first two are correct (">0") but the extra
> > > > character is an ascii 48, which shows up as a box when I hover over
> > > > the value in debug mode.
> > > >
> > > > In searching the newsgroups I have seen complaints of an extra chr(0)
> > > > at the end of the ValidationRule. I don't believe that is the same as
> > > > the ascii 48, but maybe it is. In any case, there does not seem to be
> > > > any way for me to correct this. But it is causing programmatic
> > > > problems, and driving me absolutely crazy. It seems like it must be an
> > > > Access bug.
> > > >
> > > > Does anyone know of a solution for this? None of the chr(0) posts I
> > > > saw had a solution. I can't believe how ridiculous this problem is and
> > > > would really appreciate help. Please help me if you can.
> > > >
> > > > I am using Access 2000. Is there any other way to set the
> > > > ValidationRule programatically? And/Or, if this is a bug, is it fixed
> > > > in Access 2002? or 2003?
> > > > Thanks in advance.
> > > > Christine

Relevant Pages

  • Re: Dealing with I/O problems
    ... I can isolate it by putting the line into a CHARACTER of appropriate length ... set up to read lines of characters using the A format to a ... to another character string (always preset to ... blanks) a until a blank is met (pass all multiple blanks) or maximum ...
    (comp.lang.fortran)
  • Re: get wide character and multibyte character value
    ... a character string of czech. ... I use L on the character string and watch memory to get the wide ... converting an application to unicode for weeks, ... To get the UTF-8 version of the string, ...
    (microsoft.public.vc.language)
  • Re: Special characters and variables in search and replace expressions of regexps
    ... $s contains a three character string, the 'a' character, the TAB character and the 'b' character. ... If you had used a regular expression with capturing parentheses before this point then $r would contain whatever was captured by them. ...
    (perl.beginners)
  • Re: Fortran Guru requested
    ... > '05:00' is a character string, ... It's always a descriptor, and the difference is ... We have strong evidence that the '05:00' is being passed by reference. ... Yes, I agree that in my experience, character string literals are ...
    (comp.os.vms)