Results 1 to 12 of 12
  1. #1
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194

    Error Message Please Help


    I'm getting the following error from another computer trying to access my application. They can open the database just fine, but they get the below error when they try to open a form:

    "The expression on click you entered as the event property setting produced the following error: Function not available in expression in query expression: 'Left([RawMaterial],6'"

    I'm stumped because I am using the same .accde file and can access the form just fine. Any help would be appreciated.

    Sorry if this has been posted before, but I couldn't find an article with my expression at the end.

    I really feel like there is a missing reference on the machine in question. I did a reference check and the only reference that my machine has that the problem machine doesn't is:
    Name: MSComCtl2
    FullPath: C:\Windows\SysWOW64\mscomct2.ocx
    Version: 2.0
    Last edited by lccrews; 04-26-2018 at 04:13 PM. Reason: New information added

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Sounds like you are missing a library on the machine which produces the error. perhaps they are running a different version of access?

    otherwise really need to see your code. And can you confirm you are using a .accde - I didn't think they produced error messages of this sort.

  3. #3
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    well you are missing a ) symbol....though maybe that is just a typo in your post....

    Left([RawMaterial],6

    Left([RawMaterial]),6


  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I think
    Left([RawMaterial]),6

    should be this

    Left([RawMaterial],6)

    but agree you might be showing a typo, and the underlying issue is a missing reference.

  5. #5
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    Thank you for the responses. First, Left([Raw Material],6) is how the formula looks in the query. Second, here is the code that I'm using:
    Code:
    Private Sub Command2_Click()On Err GoTo errHandler
    
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qry_CurrentFormula"
    DoCmd.OpenQuery "qry_CurrentFormula_Pt4"
    DoCmd.SetWarnings True
    DoCmd.OpenForm "frm_CurrentFormula"
    
    
    Exit Sub
    
    
    errHandler:
        MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
        VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"
    
    
    End Sub
    Third, the computer in question is indeed using an .accde file. I'm at a loss now. The reference listed in OP is the only reference that my machine has that the problematic machine doesn't.

  6. #6
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    The portion DoCmd.OpenQuery "qry_CurrentFormula_Pt4" has a field that relies on qry_CurrentFormula_Pt3 where the formula Left([Raw Material],6) resides.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Name: MSComCtl2
    FullPath: C:\Windows\SysWOW64\mscomct2.ocx
    Version: 2.0
    I believe the above would be for a PC with 32-bit Access in 64-bit Windows
    However, if I remember correctly MSComCtl2 doesn't work in 64-bit Access

    See this link for confirmation: https://www.access-programmers.co.uk...25&postcount=1
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    Hi Ridders,

    Could that affect the listed function Left( ? How do I add this to his computer? He is also running 64-bit windows but doesn't have this reference. I took a look at your link, but that didn't help. I've done a bit more troubleshooting and found that the .accdb file works on the other computers just fine. It's something to do with the .accde file.

    So does that mean it isn't the reference that is the issue?
    Last edited by lccrews; 04-27-2018 at 01:27 PM.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by lccrews View Post
    Hi Ridders,

    Could that affect the listed function Left( ? How do I add this to his computer? He is also running 64-bit windows but doesn't have this reference. I took a look at your link, but that didn't help. I've done a bit more troubleshooting and found that the .accdb file works on the other computers just fine. It's something to do with the .accde file.

    So does that mean it isn't the reference that is the issue?
    The function Left([Raw Material],6) depends on one of the standard VBA libraries so that shouldn't be an issue

    To reiterate, MSComctrl2.ocx does not work in 64-bit Access and is not supplied with 64-bit Windows
    You could copy the file to a 64-bit machine but it still won't work
    Instead you need to work around this
    a) Make a copy of your db on your PC. Remove that reference and see if the VBA code compiles.
    b) If it does compile you can remove the reference permanently & it should then work on 64-bit Access

    c) If it doesn't compile, you will need to alter the code changing any parts that currently rely on that library
    Once you have it working & compiling correctly try again in 64-bit Access

    You also need to ensure you have good error checking as without that any program errors will cause ACCDE files to crash.

    Also if you use Windows API calls anywhere in your database, these must be updated to work in 64-bit Access
    However as you said the ACCDB version works fine in 64-bit, that sounds unlikely to be an issue for you

    Hope that helps

    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by lccrews View Post
    The portion DoCmd.OpenQuery "qry_CurrentFormula_Pt4" has a field that relies on qry_CurrentFormula_Pt3 where the formula Left([Raw Material],6) resides.
    Maybe post a screen shot of the query designs and the sql's? I'd agree that the reference should have no bearing on the Left function. I guess those are not Select queries. Exactly which line presents the error?
    Last edited by Micron; 04-27-2018 at 05:34 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    Hi Mircron thanks for responding. I am not exactly sure which reference was the problem, but it was indeed the references. I deleted extra ones that were not necessary and the form started working again without issue. I really wish I knew exactly which reference it was, but nonetheless, it works. Sorry to all the future people who come across this..... Check your references. That was my problem.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well that's interesting because I believe the message was ab out the old calendar control. Makes sense in a way, but then again, can't tell from what you posted that you might have been using that control.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-26-2016, 06:34 AM
  2. Replies: 7
    Last Post: 03-17-2016, 05:53 PM
  3. Replies: 5
    Last Post: 05-15-2015, 10:49 AM
  4. Replies: 15
    Last Post: 11-01-2013, 03:24 PM
  5. Replies: 2
    Last Post: 06-23-2012, 11:59 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums