Results 1 to 12 of 12
  1. #1
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,722
    Thanks
    146
    Thanked 156 Times in 149 Posts

    Access2003 VBA issue

    I have a problem with an Access 2003 database. It's my list of photographs and has been working for years. I have a main menu with various pages off that for different cameras etc. I'm not aware that I've made any changes but when I just tried to use it I found none of my buttons work. The error message is attached error.PNG.
    The on click value for the button is =Open_Form("frmNegatives").

    The VBA to open the form is

    Code:
    Public Function Open_Form(stDocName)
    On Error GoTo Err_Open_Form
    
     '   Dim stDocName As String
      '  Dim stLinkCriteria As String
    
        DoCmd.OpenForm stDocName  ', , , "[FilmCode] = """ & stLinkCriteria & """"
    
    Exit_Open_Form:
        Exit Function
    
    Err_Open_Form:
        MsgBox Err.Description
        Resume Exit_Open_Form
        
    End Function
    Note that the commented out code in the open form command is a placeholder for other forms called with parameters - none work so I don't think that's the cause of the problem.

    Has anyone any ideas?

    P.S. I have two other databases which are the same sorts of working and they work fine.
    Last edited by access-mdb; 2015-04-26 at 14:19. Reason: Bit more info

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Access,

    Have you set a breakpoint in the code to see what value is being passed in?

    At a minimum I'd try this:
    Code:
    Public Function Open_Form(stDocName as String) 
    
    On Error GoTo Err_Open_Form
    
        DoCmd.OpenForm stDocName  , acNormal, , , acDialog
    
    Exit_Open_Form:
        Exit Function
    
    Err_Open_Form:
        MsgBox Err.Description
        Resume Exit_Open_Form
        
    End Function
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,722
    Thanks
    146
    Thanked 156 Times in 149 Posts
    Thanks RG - I put this code in and it said that an expression in argument 5 has an invalid value. I'm not sure what acDialog is though, or its value as it isn't being passed anything.

    Or have I missed the point and this is just an example for me to follow, rather than just copy it.....?

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Access,

    Here's code that works but may or may not fit your circumstances.
    Code:
    Private Sub cmdTestForm_Click()
    
       Dim zFrmToOpen As String
       
       zFrmToOpen = Me![Text0]  '*** A field on your form with the form to open name
       
    On Error GoTo Err_cmdTestForm
    
        DoCmd.OpenForm zFrmToOpen, acNormal, , , , acDialog
        
    Exit_cmdTestForm:
        Exit Sub
    
    Err_cmdTestForm:
        MsgBox Err.Description
        Resume Exit_cmdTestForm
    
    End Sub
    I should have read the message more carefully. It says you can't use a Function as the argument for an event! Thus, you need to call a standard OnClick Event (as above) then have it grab the necessary arguments from the form.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    I've used a similar function for years and not had any problems. Have you checked your references? A Missing Reference will cause what are seemingly unrelated problems.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by access-mdb View Post
    Thanks RG - I put this code in and it said that an expression in argument 5 has an invalid value. I'm not sure what acDialog is though, or its value as it isn't being passed anything.
    Access,

    Sorry I missed a comma! See post #4 which is an actual test on my machine.

    Let's hope Mark is right will save you a lot of time. If he is please post back as to which reference was missing as I was getting the same message as you when I tried to test a function.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,722
    Thanks
    146
    Thanked 156 Times in 149 Posts
    I'm getting very confused here. I've got an old backup (from 2010) and this works - with the exact same code as the one that's failing (as far as I can see).

    Mark, as I've said, I too have used a function. So which reference are you talking about?

    As an aside, I would have had a backup from file history to go back to, but I managed to delete a lot of the files thinking I was on a different disk - ho hum

    Just adding that I'm now retiring for the night, so will get on any answers or suggestions tomorrow.
    Last edited by access-mdb; 2015-04-26 at 17:28. Reason: Going to bed!

  8. #8
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by access-mdb View Post
    I'm getting very confused here. I've got an old backup (from 2010) and this works - with the exact same code as the one that's failing (as far as I can see).

    Mark, as I've said, I too have used a function. So which reference are you talking about?

    As an aside, I would have had a backup from file history to go back to, but I managed to delete a lot of the files thinking I was on a different disk - ho hum

    Just adding that I'm now retiring for the night, so will get on any answers or suggestions tomorrow.
    In the Visual Basic editor, click on Tools -> References and see if any references in the list are flagged as MISSING. You may have 'lost' a code library somewhere along the way, or something has got renamed, or you may just need to search down the list of references for something that needs to be re-selected.

  9. #9
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,722
    Thanks
    146
    Thanked 156 Times in 149 Posts
    Thanks Jeremy, looked at that and no obviously missing refs. I compared both the failing database and the 5 year old one (which works) and they appear to be identical. Indeed, they appear to be sharing the same file.

    This is what confuses me - the old one is working and the newer one isn't, so I don't think it's a problem with Access per se, but something's not quite right with my new db. I think I'll try repair now to see what happens.

    Nope, that didn't work. I need to tidy the database up (too many old tables which are redundant). I'm also thinking of getting Office 365 so that will force me to tidy up and redo. Perhaps it's just a corruption which redoing will help.
    Last edited by access-mdb; 2015-04-27 at 04:19. Reason: Reapir didn't work

  10. #10
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,722
    Thanks
    146
    Thanked 156 Times in 149 Posts
    Update: I have now created a new database and have imported the relevant tables and forms to it. I have copied and pasted those VBA scripts I need (well the ones I have spotted so far ) and all is working as expected. I suspect the old db has been corrupted in some way. As I said, like Topsy it has just growed and there's a lot of redundant stuff lying around, so this will mean a leaner cleaner db!

    Thanks for your suggestions RG, Mark and Jeremy.

  11. #11
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,722
    Thanks
    146
    Thanked 156 Times in 149 Posts
    I have found the problem.

    Have you ever been asked why a program isn't working, and your first question is 'Have you made any changes?' The answer is often along the lines of 'No. Well only a small one'. Well that was my answer as well.

    I had a number of forms which had some VBA to display one of two fields, whichever wasn't blank. I had recently realised that this wasn't actually necessary as only one of the fields was needed (a memo field, the other was a text field). This was in versions of Access previous to 2003, you couldn't search or sort a memo field and I had some data which was bigger than 255 bytes. So I recently tidied this up to remove the duplicate fields, but I didn't amend the VBA scripts. They were causing the problem - and I found the issue by compiling the scripts (under menu debug/compile VBA module), and it flagged the lines which were causing the problem. It's now working fine!

    Irony is, I used to do this all the time when I was writing scripts at work (especially as some of them were quite complex). I had just got out of the habit....

    Ad of course, it shows that one change can produce error messages which aren't helpful and don't point to the real cause.

    I will still continue to use my 'new' db, as it's 6Mb rather than 36!
    Last edited by access-mdb; 2015-04-27 at 12:16. Reason: typo

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by access-mdb View Post
    ... I'm also thinking of getting Office 365 so that will force me to tidy up and redo. Perhaps it's just a corruption which redoing will help.
    If you decide to adopt Office 365, you will face some choices. Assuming you get the version that includes Acccess, you will end up with what is effectively 2013, though there are hints that a new version will be released not long after Windows 10 and I've seen virtually no info about databases in that new version. You will also have the option of putting the data in the cloud, and that can be done, but there are potential performance issues if you do. Just some food for thought....
    Wendell

Posting Permissions

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