Results 1 to 7 of 7

Thread: Table Monitor

  1. #1
    Join Date
    Oct 2004
    Posts
    86

    Unanswered: Table Monitor

    Hi
    Please find my FE attached

    What i want is for my main form to update tblEmployee each time a record is added for that employee i want it to increment tblemployee/ScreenedMTD +1 each record for that agent

    then what i want is for my subform - to display these fields in tblemployee so the data entry person can see how many records there are for that agent in that particular month

    or is there an easier way to do this?

    thanks

    Attachment
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Posts
    34
    What are you referring to by ScreenedMTD?

  3. #3
    Join Date
    Oct 2004
    Posts
    86
    ScreenedMTD (month to date) i want as a variable in my tblEmployee, this i want updated by the frmScreeningForm each time an entry is made using that employees name

  4. #4
    Join Date
    Nov 2004
    Posts
    34
    Ok, just to make sure I'm trying to solve the right problem, you want the CallsScreenedMTD to increment by 1 each time any Employee listed in the tblEmployee table starts a new record in the frmScreeningForm? And then at the beginning of each month the CallsScreenedMTD is reset to 0?

  5. #5
    Join Date
    Oct 2004
    Posts
    86
    i think so let me clarify

    frmScreeningForm is the data entry form that has drop down boxes the boxes point to tblEmployee

    Scenario

    Data input record #1

    Employeename: Joe Bloggs
    Department: Sales
    Team Manager: Peter Pepper

    once the record is complete i want the field "ScreenedMTD" to increment +1 for agent "Joe Bloggs" and yes reset each month

    Can this be done in a query or is it better to have the field in tblemployee updated? as ive heard having calculated fields in a table is a bad idea.

    i think a query would be better, then i can get a form to point to the query
    so how would i do this?

    thanks

  6. #6
    Join Date
    Nov 2004
    Posts
    34
    Well I'm not sure about the query method, but the way I would do it it is to put the following vba code in the forms after update event.

    Code:
    dim strSQL as string
    
    strSQL = "UPDATE tblEmployee SET tblEmployee.CallsScreenedMTD = [tblEmployee]![CallsScreenedMTD]+1 " _
    & "WHERE (((tblEmployee.EmployeeName)=[Forms]![frmScreeningForm]![cboEmployee]));"
    
    docmd.runsql strSQL
    If you wish you can also stop the warning message appearing telling you that you are about the append records. But judging by what you've done so far with this database I feel you probably know how to do that.

  7. #7
    Join Date
    Nov 2004
    Posts
    34
    To reset the CallsScreenedMTD each month just have an IF statment in the open event of your switchboard form. Like follows:

    Code:
    Private Sub Form_Open(Cancel As Integer)
    dim myDate as date
    
    myDate = date
    mydate = mid(mydate,,2)
    
    if mydate = "01" then
    docmd.runquery The Query that resets CallsScreenedMTD to 0
    end if
    End sub
    For the query, just make an update query based on the tblEmployees table, with the following criteria for the CallsScreenedMTD field: "0". Easy!

Posting Permissions

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