history table

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • mseo
    New Member
    • Oct 2009
    • 183

    #1

    history table

    hi,
    I have two tables:
    1- customers that contains field named
    Code:
    customerid; autonumber; PK
    Companyname;string
    dateentered; date/time
    inactive; yes/no
    datemodified ;date/time
    2- customerhistory :
    Code:
    customer_ID; number
    Inactive; yes/no
    datefrom; date/time
    dateto; date/time
    I have form for edit customers and its controls bound to table customers
    I need when check the inactive checkbox to insert record in table customerhistory

    any help would be appreciated
    thank you in advance for any help
  • beacon
    Contributor
    • Aug 2007
    • 579

    #2
    Hi mseo,

    I would put code in the BeforeUpdate event for the checkbox and then use the INSERT SQL statement to put specific values in a table.

    Since I don't know what values you want to insert into the table, I'm going to show you the structure and you can fill it in with the values you want:
    Code:
    Private Sub Check0_BeforeUpdate()
    
        Dim strSQL as String
    
        strSQL = "INSERT INTO YourTable/Query(YourTable/QueryField1, YourTable/QueryField2, YourTable/QueryField3) " & _
                 "VALUES(" & Me.Control1 & ", " & Me.Control2 & ", " & Me.Control3 & ");" 'This assumes that all values are numeric. If you have a string or date, you will need to put a single quote or pound sign inside the quotes that surround the Me.Controls
    
        If Me.Check0.Value = True Then
            DoCmd.RunSQL strSQL
        End If
    
    End Sub
    One thing that I probably should point out...the code I've provided doesn't check to see if the customer is already inactive and it doesn't do anything to reactivate the customer should the user accidentally check the check box. Hopefully this gets you started though and you can figure that other stuff out.

    Comment

    • mseo
      New Member
      • Oct 2009
      • 183

      #3
      Originally posted by beacon
      Hi mseo,

      I would put code in the BeforeUpdate event for the checkbox and then use the INSERT SQL statement to put specific values in a table.

      Since I don't know what values you want to insert into the table, I'm going to show you the structure and you can fill it in with the values you want:
      Code:
      Private Sub Check0_BeforeUpdate()
      
          Dim strSQL as String
      
          strSQL = "INSERT INTO YourTable/Query(YourTable/QueryField1, YourTable/QueryField2, YourTable/QueryField3) " & _
                   "VALUES(" & Me.Control1 & ", " & Me.Control2 & ", " & Me.Control3 & ");" 'This assumes that all values are numeric. If you have a string or date, you will need to put a single quote or pound sign inside the quotes that surround the Me.Controls
      
          If Me.Check0.Value = True Then
              DoCmd.RunSQL strSQL
          End If
      
      End Sub
      One thing that I probably should point out...the code I've provided doesn't check to see if the customer is already inactive and it doesn't do anything to reactivate the customer should the user accidentally check the check box. Hopefully this gets you started though and you can figure that other stuff out.
      thank you beacon for your reply
      In addition to you posted
      I need to compare the last inserted record into table customerhistory for specific customerID if it is the same not to insert new record if the inactive control changed insert new record with new values

      thank you very much

      Comment

      • beacon
        Contributor
        • Aug 2007
        • 579

        #4
        Originally posted by mseo
        thank you beacon for your reply
        In addition to you posted
        I need to compare the last inserted record into table customerhistory for specific customerID if it is the same not to insert new record if the inactive control changed insert new record with new values

        thank you very much
        You can use the DLookup function if you want to see if the specific customerID has been entered in the table/query. If you only want to check on the last record, you'll either need to use a SQL query using the LAST statement, DAO utilitizing the FindFirst - FindLast methods, or you can use DLookup in this case too in combination with a new query that looks up the latest ID in the table/query.

        Once you've done the comparison, you can use the code I provided in my first post to insert the new record.

        Comment

        • mseo
          New Member
          • Oct 2009
          • 183

          #5
          Originally posted by beacon
          You can use the DLookup function if you want to see if the specific customerID has been entered in the table/query. If you only want to check on the last record, you'll either need to use a SQL query using the LAST statement, DAO utilitizing the FindFirst - FindLast methods, or you can use DLookup in this case too in combination with a new query that looks up the latest ID in the table/query.

          Once you've done the comparison, you can use the code I provided in my first post to insert the new record.
          thank you very much

          Comment

          Working...