Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB Run time error 2447 (Access 2003)

    I have 'inherited' a database which contains extensive amounts of very complex visual basic which (now?) has an error in it.

    I am told that the underlying feature using this VB worked in Access 97 and since the DB was converted to Access 2000, but I have no proof of that.

    The Visual Basic contains these declarations.

    Dim frm as form
    Dim strPageLetter as String

    The offending code is :

    Set frm = Screen.ActiveForm
    strPageLetter = frm!CurrentPage

    VB run-time errors (error 2447) on the assignment to strPageLetter with the error :

    "There is an invalid use of the . (dot) or ! operator or invalid parentheses."

    As far as I can deduce, frm!CurrentPage will return a Long so I don't see how this assignment can ever have worked, although it's the syntax of the frm!CurrentPage which seems to cause the error before the attempt to complete the assignment.

    Can anyone think how this might work/have worked, state it can never have worked etc please? Can it be anything to do with converting from Access 97 to 2000?

    Thanks
    Silverback
    Silverback

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB Run time error 2447 (Access 2003)

    CurrentPage is not a built-in property in Access, so it probably refers to a control on the form. Is there a control named CurrentPage, or a field in the record source of the form named CurrentPage?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Run time error 2447 (Access 2003)

    Hans
    I've found CurrentPage (thank you for the lead) but my confusion is only deepening. It's a hidden control (a text box) on the form and it contains the following as its control source :

    =[MainTabControl].[Pages]([MainTabControl].[Value]).[Name]

    Clearly you can't know what all these objects are (and I can't find them in the DB) but can you help with what this is doing? What is the concept of Control Source for a text box?
    Thanks
    Silverback
    Silverback

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB Run time error 2447 (Access 2003)

    The Control Source of a text box specifies how the text box is populated. It can be:
    1. <LI> Empty - this means that the text box is unbound. The user is free to enter text in it; this text can be used by other controls, or by VBA code, but it will not be stored in the table(s) the form is based on.
      <LI> The name of a field in the table(s) the form is based on. We say that the text box is bound to the field. The text entered by the user will be stored in the field in the table.
      <LI> An expression, i.e. a formula beginning with =. This makes the text box a calculated control. The user cannot type anything in the text box, since the contents are defined by the formula.
    Your text box is of the third kind - a calculated control.

    MainTabControl presumably is the name of a tab control, aka a multipage control on your form. Pages is the collection of tab pages on this tab control. Value is the index number of the currently active tab page (starting at 0, i.e. the first tab is 0, the second one is 1 etc.).

    MainTabControl.Pages(MainTabControl.Value).Name is the name of the currently active tab page. See screenshot below. The upper part shows a tab control with two pages, of which the second is currently active. The text box below shows the name of this tab page.

    Which brings us to the error message you got. Which procedure (sub) or function contains the line that causes the problem?
    Attached Images Attached Images
    • File Type: png x.png (7.4 KB, 3 views)

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Run time error 2447 (Access 2003)

    Hans
    I have attached a Word doc (zipped) containing the offending VB. The procedure which errors is rf5cFillPickList and I have highlighted this in green. The offending line is highlighted in red.
    There is another procedure which resets (clears) all the fields on the tab on the form; this contains the same VB and, when we forced it to execute this procedure, the same syntax (frm!CurrentPage) failed there, also. I have highlighted this in red, too.
    I have received confirmation that this all did work and very recently (end Feb '07) but then someone'tidied up the PC' of the user and it stopped working. Of course, the user does not know exactly what this support engineer did, (I can't find out because it's an external company contracted), but I am therefore beginning to suspect that the user's version of Microsoft Office has changed.
    This seems to be a syntax error - could a change say from Office 2002 to 2003 be the cause?
    (I'm trying to get a trace on what software the user used to have on her old PC and what she has now.)
    Thanks
    Silverback
    Attached Files Attached Files
    Silverback

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB Run time error 2447 (Access 2003)

    Frankly, I have no idea why it fails, but see if it works if you replace

    frm!CurrentPage

    with

    frm.Controls("CurrentPage")

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Run time error 2447 (Access 2003)

    Solved!!!
    It turns out that the VB is freeware produced by a gentleman called Steve Nyberg. Searching for him on Google found a website which listed an error exactly as described above. It apparently appeared in Access2003 and Steve has produced a fix.
    The fix page can be seen here and it is the Oct 7th, 2004 entry.
    There is a lot of confusion at the hospice about how the user could have used this feature at end Feb but not be on Access2003 at that date! Whatever, it is now fixed (fix applied and tested and working).
    As a VB expert yourself, Hans, can you understand why his original VB stopped working in 2003?
    Many thanks again for your time and efforts, Hans. Many beers to you when I find out how to send 'em over the internet! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Silverback
    Silverback

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB Run time error 2447 (Access 2003)

    I have no idea what causes the error. It might have to do with the enhanced security feature named Sandbox Mode which disallows the use of some properties and functions in expressions such as the control source of a control, but neither Pages nor Name is listed as blocked in How to configure Jet 4.0 to prevent unsafe functions from running in Access 2003.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Run time error 2447 (Access 2003)

    Hans
    Thanks for the link in your last posting; this led us to the offending area in Office 2003.
    The original control source was =[MainTabControl].[Pages]([MainTabControl].[Value]).[Name]------------------------------(MainTabControl was what he called the tab control on the form.)
    After following the link in your last posting, we looked at Macro | Security in Access 2003.
    Running some tests, we found that If this is set to Medium, the code fails. If Macro Security is set to Low then, regardless of whether we select block/don't block unsafe expressions, the code works.

    The solution we have adopted in conjunction with the user is to leave Macro Security at Medium and change the VB code to Chr(MainTabControl.Value + 65) which is the author's fix on his website. (The tabs on the form are called "A", "B", "C" and "D")
    Thanks again for your help.
    Silverback
    Silverback

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •