Results 1 to 9 of 9

Thread: Count Records

  1. #1
    Join Date
    Feb 2010
    Posts
    20

    Unanswered: Count Records

    I have 2 related tables that each have a form for input.
    Table Operator
    Table Run
    an operator will have many runs

    on the operator form i have a text box that i want to show how many runs that the particular operator has made by matching the Run_operatorId (from run table) to the Operator_Id (from the operator form that is displayed)

    can't seem to get the SQL statements to work.

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by algiersnola View Post
    I have 2 related tables that each have a form for input.
    Table Operator
    Table Run
    an operator will have many runs

    on the operator form i have a text box that i want to show how many runs that the particular operator has made by matching the Run_operatorId (from run table) to the Operator_Id (from the operator form that is displayed)

    can't seem to get the SQL statements to work.
    How are you attempting this?

    Are you a control source with: =DCount() or something else?

    You could also place a sub form on the operator form that show all the runs with the total count.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Feb 2010
    Posts
    20
    I found some info on the web and tried this,

    Dim sqlstr As String

    sqlstr = "SELECT COUNT (Run_Operator) FROM Run WHERE Run_Operator = " & Me.Operator_ID

    the first thing is that I'm not sure this is even right and how do I get the answer in the text box if it is. You think maybe a sub query on the main form would be easier?

  4. #4
    Join Date
    May 2010
    Posts
    601
    If it were me, I would keep it were simple and just use the DCount() like in my previous post. It requires NO VBA code. A sub query on the main form will probably render the form's recordset as not updatable.

    To use the SQL string, you will basically need to write VBA code to duplicate what the DCount() function alreafy does. If you want to learn how to code the use of a recordset then this would be something to try.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Feb 2010
    Posts
    20
    if using the Dcount function do I use the expression builder in the control source property? or is it better to use vba?

  6. #6
    Join Date
    Feb 2010
    Posts
    20
    in the expression builder I used:
    =DCount([Run_Operator],[Run],[Operator_ID])
    I get an this in the text box: #name?

  7. #7
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by algiersnola View Post
    in the expression builder I used:
    =DCount([Run_Operator],[Run],[Operator_ID])
    I get an this in the text box: #name?
    You are close.

    Try this:
    Code:
    =DCount("*","[Run]","Run_Operator = " & Me.Operator_ID)
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  8. #8
    Join Date
    Feb 2010
    Posts
    20
    Got it, Thanks for the help. A missing or wrong character can really cause confussion

  9. #9
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by algiersnola View Post
    Got it, Thanks for the help. A missing or wrong character can really cause confussion
    You're welcome!

    Glad we could assist.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

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