Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2009
    Posts
    13

    Unanswered: update Record when opened

    I have a table that I am using a query to find 1 of many specific records that meets the criteria and then opens in a form.

    What I want to do is update the record when I open the record to say it is open or was log into at Date/Time since the query is set up not to pull the record if we have reviewed it in the last 5 days. The table has the log in field and the query runs against the table each time someone looks for the next record.

    I am trying to keep from having more than one person attempt to call a client at the same time or same day even.

    Each Record has an autonumber assigned to it that is unique. I am hoping that I can add to the file the log in date/time when we open it and then add a log out Date/time when we exit the form.

    Any assistance in this would be great. This is my first Database and I am into stuff that I don't understand completely.

    Thank you,
    jayceesdj
    "I'm an idealist. I don't know where I'm going but, I'm on my way." ---carl sandburg

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    So what do you have so far?

    I think you'd have to have some code controlling the "opening" of a record, which sounds like the tricky part. The Form_Close event can handle the closing, that part sounds like it would be easy.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Feb 2009
    Posts
    13
    Quote Originally Posted by StarTrekker
    So what do you have so far?

    I think you'd have to have some code controlling the "opening" of a record, which sounds like the tricky part. The Form_Close event can handle the closing, that part sounds like it would be easy.
    Here is the coding and further details that I can provide. This is the query used to sort through the data on the outbound_call_tbl.

    Code:
    SELECT TOP 1 Reg_in_BC.ID, Reg_in_BC.LogMID, Outbound_Call_tbl.ID, Outbound_Call_tbl.LogMID, Outbound_Call_tbl.MIDaddr1, Outbound_Call_tbl.MIDaddr2, Outbound_Call_tbl.MIDCity, Outbound_Call_tbl.MIDState, Outbound_Call_tbl.MIDZip, Outbound_Call_tbl.MIDPh, Outbound_Call_tbl.MIDContact, Outbound_Call_tbl.inputdate, Outbound_Call_tbl.LogMemo4, Outbound_Call_tbl.Setup_Date, Outbound_Call_tbl.log_in, Outbound_Call_tbl.log_out, Outbound_Call_tbl.SSO
    FROM Outbound_Call_tbl LEFT JOIN Reg_in_BC ON Outbound_Call_tbl.LogMID = Reg_in_BC.LogMID
    WHERE (((Outbound_Call_tbl.Setup_Date) Is Null) AND ((Outbound_Call_tbl.log_in) Is Null Or (Outbound_Call_tbl.log_in)<Date()) AND ((Outbound_Call_tbl.log_out) Is Null Or (Outbound_Call_tbl.log_out)<Date()));
    Here is the filter I am using to open the one record I want to view:

    Code:
    Private Sub Form_Open(Cancel As Integer)
    
    
            Me.FilterOn = True
            Me.Filter = "[id]=" & DLookup("Outbound_Call_tbl.ID", "Outbound_Call_tbl_Query")
            
            
    End Sub
    Then what I am trying to do is update the log_in field on the Outbound_call_tbl when I open the one record in form veiw. The code I have attempted to use is:

    Code:
    Private Sub Form_Current()
    
    Dim cdb, dbSQL
    
     dbSQL = "INSERT into [Outbound_Call_tbl].log_in VALUES('" & Date & "')" _
        & "WHERE [Outbound_Call_tbl]=" & ID & ";"
            
        Set cdb = CurrentDb
        cdb.Execute dbSQL
        Set cdb = Nothing
    
    End Sub
    Everything works fine up to trying to update the log_in field in the Outbound_call_tbl where the ID is equal to the ID from the filter used.

    I either get a Runtime error 3137 missing semicolon at end of SQL statement. I have added the ";" at the end and that didn't seem to help.

    any assistance would be greatly appreciated I would like to complete this so I can go on vacation...

    Thank you,
    jayceesdj
    "I'm an idealist. I don't know where I'm going but, I'm on my way." ---carl sandburg

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    So, since your form is opening only one record, you can control what's going on via the Form_Open procedure.

    Code idea:

    Code:
    Private Sub Form_Open(Cancel As Integer)
       Dim intID as Integer
       intID = DLookup("Outbound_Call_tbl.ID", "Outbound_Call_tbl_Query")
       Me.Filter = "[id]=" & intID
       Me.FilterOn = True        
       
       'now set the log_in recording here...
       CurrentDB.Execute "INSERT into [Outbound_Call_tbl].log_in VALUES('" & Date & "') WHERE [Outbound_Call_tbl] = " & intID & ";"
      
    End Sub
    Watch the spacing in your SQL too, seems there is a space missing before the WHERE.

    Is the log_in field a date/time or text?

    I have to also assume many things here, such as the query data being updatable.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Feb 2009
    Posts
    13

    Talking

    Quote Originally Posted by StarTrekker
    So, since your form is opening only one record, you can control what's going on via the Form_Open procedure.

    Code idea:

    Code:
    Private Sub Form_Open(Cancel As Integer)
       Dim intID as Integer
       intID = DLookup("Outbound_Call_tbl.ID", "Outbound_Call_tbl_Query")
       Me.Filter = "[id]=" & intID
       Me.FilterOn = True        
       
       'now set the log_in recording here...
       CurrentDB.Execute "INSERT into [Outbound_Call_tbl].log_in VALUES('" & Date & "') WHERE [Outbound_Call_tbl] = " & intID & ";"
      
    End Sub
    Watch the spacing in your SQL too, seems there is a space missing before the WHERE.

    Is the log_in field a date/time or text?

    I have to also assume many things here, such as the query data being updatable.
    Thank you for your assistance I did find the error and finally got it working.
    jayceesdj
    "I'm an idealist. I don't know where I'm going but, I'm on my way." ---carl sandburg

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Good job, and you're welcome
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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