Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2006

    Unanswered: DSum function issue with conditions / criteria expressions

    Hi community experts,

    I am having an isue with Dsum function that is used to count the total number for a particular field (intake) at the textbox afterupdate event with condition/criteria (subject). Below is the scenario in details.

    What I need to do is summing the intake corresponding to what is selected from the dropdown combo box. For instance if the user choose maths in the combo box, the total no of intake should be displayed in the subject textbox as 150.

    May I knw hw can that be achieved and whether I am in the correct direction? I have provided the necessary details that is required for this scenario. Tks for advice, code snippets.

    Form controlSource = Registration Table

    Registration Table design view

    SerialCode autonumber primary key
    subject text
    intake number

    Registration Table view

    SerialCode Subject Intake
    1 maths 100
    2 science 100
    3 maths 50
    4 science 50
    5 english 100

    Form design view

    Subject unbounded combo box
    Subject unbounded textbox
    Intake unbounded textbox


    Private Sub Subject_AfterUpdate(Cancel As Integer)
    Dim sum As Variant
    sum = DSum("Intake", "Registration", "subject = '" & Me.ComboSubject.Column(0) & "'")

    End Sub

  2. #2
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    The DSum looks okay. You realize you don't do anything with the value? Presumably you'd want to set your textbox to the value. Also, I'd use the after update event of the combo, so your value changes whenever the combo does.

  3. #3
    Join Date
    Oct 2002
    Leicester - UK
    I'd either do this with a straight VBA equation or via SQL
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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