Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    39

    Unanswered: Run update query, show total of updated records in msg box

    I have a database where we assign employees to properties that need inspected. This is done by form, a user selects an employee from a list box and then enters in a range of parcel numbers in a text box, then they run an update query that enters the username from the list into the "assigned" field in a table where we store our properties. I would like a message box showing the amount of records that employee was just assigned after running the update query.

    I'm thinking I could use Dcount() for this but I'm not sure how to implement it.

    Heres some vba code used when assigning a parcel range

    Private Sub Assign_Range_Click()
    DoCmd.SetWarnings (False)
    If Me.FSBOX > 0 Then
    DoCmd.OpenQuery "QRYRangeAssign", acViewNormal, acEdit

    Else
    MsgBox "You need to select an employee from the employee list above"

    End If
    End Sub

    Note-QRYRangeAssign is an update query

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. What's "Me.FSBOX" and what king of information does it contain?
    2. What's the SQL of the query "QRYRangeAssign"
    3. What's the structure of the table(s) involved?
    Have a nice day!

  3. #3
    Join Date
    Jul 2009
    Posts
    39
    1.The FSBOX is the field staff list box, which contains employees that a user can choose and assign parcels to. Me.FSBOX > 0 is to make sure the user has selected an employee before the update query is ran.

    2.UPDATE [Reval] SET [Reval].Field_Person = NZ([field_person],[forms]![Assign_Menu]![fsbox]), [Reval].Assign_Date = nz([Assign_Date],Format(Now(),"mm/dd/yyyy"))
    WHERE ((([Reval].[Parcel ID]) Between [Forms]![Assign_Menu]![range1txt] And [Forms]![Assign_Menu]![range2txt]));

    I used NZ to ensure that a property wont be assigned twice if a user makes a mistake when assigning properties, if there is already an employee assigned to it, it can't be updated.

    3.There are many fields in the Reval data table but the main ones are Parcel_ID (text data type), Field_Person (text), Assign_Date (Date/Time)

    If you need anything else let me know.
    Thanks.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can obtain the number of row(s) that will be updated using a SELECT query (let's name it "QRYRangeCount") such as:
    Code:
    SELECT [Reval].*
    FROM [Reval] 
    WHERE ((([Reval].[Parcel ID]) Between [Forms]![Assign_Menu]![range1txt] And [Forms]![Assign_Menu]![range2txt]));
    just before running the update query "QRYRangeAssign".

    So:
    Code:
    Private Sub Assign_Range_Click()
    
        Dim lngRowCount as Long
       
        If Me.FSBOX > 0 Then
            lngRowCount = DCount("*", "QRYRangeCount")
            CurrentDb.Execute "QRYRangeAssign", dbFailOnError
            MsgBox lngRowCount & "rows were updated.", vbInformation, "Assign Range"
        Else
            MsgBox "You need to select an employee from the employee list above"
        End If
    
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Jul 2009
    Posts
    39
    Thank You Sinndho! It worked perfectly! You're awesome

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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