Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    OnChange event (Access 2000)

    I need to build a new table that contains only those rows that have been changed The original table is called Customers and has fields as :
    CustomerId
    CompanyName
    city
    ets
    ets


    I need to make a new table called CustomersTemp containing only those customers that have been changed in some respect. For example, there may be a change in the CompanyName or in the city, or any other field not mentioned. So, the new table must contain only those customers that have been changed. I have a form called frmCustomers.I know that there is an OnChange event and i want to use it in the OnClose event of the form, if possible,or at least to click somewhere. If i succeed with the task, after that the table CustomersTemp will be sent via email to another city and deleted from the database. This is the task i have and i need help, i cannot do it myself. Up to now i have built only the make up table function as follows:

    Public Function OnChange()
    Dim StrCustomers As String
    StrCustomers = " SELECT customers.* INTO CustomersTemp FROM customers"
    CurrentDb.Execute StrCustomers
    CurrentDb.Execute "CREATE INDEX PrimaryKey ON CustomersTemp (CustomerID)

    WITH PRIMARY"
    End Function

    The above function only creates the table CustomersTemp, but of course it is a copy of the first table and does not show only those customers that have been changed. The other task i have is to add on also the new customers.I think a new customer must also be considered as a change in the table.
    Does somebody has any idea to suggest out of your experience? I supose somebody else might have come accross the same task that i have.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OnChange event (Access 2000)

    I fear it would be a daunting task to do it the way you describe. While a form is open, the user can add/edit/delete multiple records, but when the form is closed, only one record is "current", so the On Close event doesn't seem to be ideal for what you want - how to determine which records have been updated?

    I would take another approach: add a Date/Time field LastUpdated to each table for which you want to keep track of updates. Set its Default Value property to =Now(). In the form used to edit the records of this table, use the Before Update event of the form to set the LastUpdated field:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.LastUpdated = Now
    End Sub

    You can create a query that copies all records that have been added/changed since a date to be specified; you could prompt the user for this date:

    strCustomers = "SELECT Customers.* INTO CustomersTemp FROM Customers WHERE Customers.LastUpdated > [Since when]"

    Or you could grab the date from a form.

    Notes:
    - Users should never modify records directly in the table; if they do, the LastUpdated field will not be kept up-to-date.
    - If you also want to keep track of deleted records, it would be a lot more work.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OnChange event (Access 2000)

    Thank you very much indeed for your ressponse.I will work on your recommendations.It is a big help for me. Could you tell me why do i i get the error
    Too few parameters on my function?

    Public Function OnChange()
    Dim StrCustomers As String
    StrCustomers = " SELECT customers.* INTO CustomersTemp FROM customers WHERE Customers.LastUpdated > [#01/01/2003#]"
    CurrentDb.Execute StrCustomers
    CurrentDb.Execute "CREATE INDEX PrimaryKey ON CustomersTemp (CustomerID) WITH PRIMARY"
    End Function

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OnChange event (Access 2000)

    The code I posted contained a parameter [Since when]. If you use it like that, Access will prompt the user for a date. If you want to use a fixed date, you should not include the square brackets [ ]:

    StrCustomers = " SELECT customers.* INTO CustomersTemp FROM customers WHERE Customers.LastUpdated > #01/01/2003#"

    Note: you probably knew this already, but just to make sure: if you include a fixed date in an SQL string, it must be in US format mm/dd/yyyy. In your example, there is no ambiguity, but if you want to check fo records modified after the first of October, you must use #10/01/2003#.

Posting Permissions

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