Results 1 to 6 of 6
  1. #1
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    328

    Module & dcount problem

    I am trying to use a function module to get a data count of a certain field (Idhull) in a specific query after updating a combo box on any one of several forms. (continuous and single)
    I have tried several things to no avail. The code below is stripped of other statements to get rid of non essentials. I get a syntax error message whenever activating.

    With Screen.ActiveForm
    MsgBox DCount("[idHull]", "[Q_Jib_PriceCalc]", "idhull=.idhull")
    End with

    I also sent the idhull value the module ( called it "ih") and tried this:
    DCount("[idHull]", "[Q_Jib_PriceCalc]", """ idhull=& ih & """)
    This doesn't give an error message, but gives the maximum count of the whole query matter what idhull it receives.



    I am using A2007 with W7

    Any help much much appreciated. Would also appreciate knowing where to find basic instructions on how to do this.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You'd want to get the reference to the value outside the quotes:

    DLookup Usage Samples
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    328
    Thanks for quick reply. I have seen that site before, but it doesn't cover what I am trying to do, which is using the dcount expression in a module that will be accessed by more than one form. Is there something special I have to do when using Screen.activeform in a module? Or, why does my 2nd expression just give me a total count rather than a count that matches what I thought was the criteria?

  4. #4
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    328
    I will never understand access 2007. The expression with screen.activeForm is now working. Is it normal for this kind of thing to happen?

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The link demonstrated concatenating a value outside the quotes, like:

    "Criteria = " & forms!FormName!ControlName

    which is what you needed to do with your variable.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    328
    I think that I am too much a novice as I don't understand how this applies to the problem I submitted.
    In any case, it started working with one of the other formats that didn't work before. ( Hate it when happens) I will play with this as get time. Thanks again.

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

Similar Threads

  1. Module Help
    By gumbi17 in forum Modules
    Replies: 3
    Last Post: 10-20-2011, 03:38 PM
  2. Run a Module
    By mchadwick in forum Modules
    Replies: 7
    Last Post: 09-02-2011, 09:24 AM
  3. Replies: 4
    Last Post: 05-16-2011, 04:58 PM
  4. DCount function problem
    By 10 Gauge in forum Forms
    Replies: 5
    Last Post: 02-28-2011, 02:08 PM
  5. Help with Dcount
    By tozey in forum Programming
    Replies: 1
    Last Post: 08-10-2010, 10:53 AM

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