Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Last Updated for new customer (Access 2000)

    I have a control LastUpdated which is a Date/Time type.I want to use it when i make a new customer.In this case,in the form when making a new customer, i write LastUpdated = Now(). The purpose is to have information for the number of the new clients each month.
    However,it turns out, that i get a false information for a new customer whenever i make some changes in some old customer.
    I want to avoid having last updated when editing an old customer and to use it only when i make a new customer.
    I imagine i could put in the Default Value property set to False, and again to True, or can i receive some advice as to how can i handle this problem?

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

    Re: Last Updated for new customer (Access 2000)

    The name of your control/field is misleading - LastUpdated suggests that the date is modified each time the record is edited. If you want to know the date the record was created, the name DateCreated or something like that would be better.

    If you have a Date/Time field in your table, and if you set its Default Value property to =Now(), the value will be set the moment you create a new record, and it won't be changed by Access afterwards, even if you edit the record. No code is needed. It's best to set the Locked property of the text box bound to the field to Yes, so that the user cannot accidentally change the value.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last Updated for new customer (Access 2000)

    i appreciate vey much your answer and will follow your advices.
    Thank you once again

  4. #4
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last Updated for new customer (Access 2000)

    Dear Hans,

    Would you advise me how can i change the default value property from Now() to 0 ?
    Below i am sending to you the function with which i have created this value.Now i must change
    it to o, to conform with my requirements for a new client.But how can i change it ?
    Or, how can i make the folloiwng :
    fld.Properties("DefaultValue") = "= Now()"
    I do not know how to amend this property,and Access forbids me to delete the field because of the
    index created.And i find myself in an impasse.
    I must send this function to our 5 points,but i am afraid i cannot do it.
    ANy help ?

    Public Function OnCustomers()
    Dim StrPassword As String
    StrPassword = "secret"
    Dim wsp As DAO.Workspace
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    Dim idx As DAO.Index
    Set wsp = DAO.DBEngine.Workspaces(0)
    Set dbs = wsp.OpenDatabase("C:BEstoreBE.mdb", False, False, ";PWD=" & StrPassword)

    Set tdf = dbs.TableDefs("Customers")
    Set fld = tdf.CreateField("LastUpdated", dbDate)
    tdf.Fields.Append fld
    tdf.Fields.Refresh

    ' set additional field properties
    Set fld = tdf.Fields("LastUpdated")
    fld.Properties("DefaultValue") = "= Now()"
    Set prp = fld.CreateProperty("Format", dbText, "Short Date")
    fld.Properties.Append prp

    ' create and append index
    Set idx = tdf.CreateIndex("LastUpdatedIdx")
    idx.Fields.Append idx.CreateField("LastUpdated")
    idx.Unique = False
    tdf.Indexes.Append idx

    dbs.Close
    Set prp = Nothing
    Set idx = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    Set wsp = Nothing
    End Function

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

    Re: Last Updated for new customer (Access 2000)

    It doesn't make much sense to set the default value for a date field to 0. This corresponds to December 30, 1899. Why would you want to do that - can you explain?

  6. #6
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last Updated for new customer (Access 2000)

    I didnt know that. The point is that for the time being my default value is =Now() and i want to delete this value. I do not want to
    have any date in this field except when i make a new customer. But for the other cases, when i merely edit, i want to remove the default t value = Now()
    I have already shown how i did make my code to create this field.How can i remove the default = Now(), and what shall i write instead?

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

    Re: Last Updated for new customer (Access 2000)

    Your statement "But for the other cases, when i merely edit, i want to remove the default t value = Now()" doesn't make sense. The default value ONLY applies to new records: it means that the value of the field will be set to the default value when you create a new record. It has no effect at all on existing records.
    If you mean that you want to clear the LastUpdated field in existing records, you need to run an update query that sets LastUpdated to Null.

  8. #8
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last Updated for new customer (Access 2000)

    Dear Hans,

    Thank you very much for your advice to use Null. I encounter enormous problems and therefore i turn again to you.
    My problem is that i have to change the default property of LastUpdated to Null, instead of Now.
    I have to change it to computetrs in 10 points.I have to do all that by code,otherwise it is very simple
    ,but i have to send it.

    i wanted at first to delete the field and then to set the property again.

    When i tried to delete the field,with the function DeleteField, written below,
    i get the message " Cannot delete a field that is a part of an index "


    Public Function DeleteField()
    Dim wsp As DAO.Workspace
    Dim StrPassword As String
    StrPassword = "secret"
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Set wsp = DAO.DBEngine.Workspaces(0)
    Dim dbs As DAO.Database
    Set dbs = wsp.OpenDatabase("C:BEstoreBE.mdb", False, False, ";PWD=" & StrPassword)
    Set tdf = dbs.TableDefs("customers")
    Set fld = tdf.Fields("lastupdated")
    tdf.Fields.Delete fld.Name
    tdf.Fields.Refresh
    dbs.Close
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    Set wsp = Nothing
    End Function

    Is it possible to remove the Indexed property at first? It is set to Index Yes Duplicated OK.
    Or, is there any way to amend only the Default value by code? That would solve me problems right away

    Kind regards

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

    Re: Last Updated for new customer (Access 2000)

    The Null value was meant to be used in an update query, not in the Default Value property; this is a string value.
    You don't have to delete the field, you can change its Default Value property to an empty string "".
    <code>
    ...
    Set fld = tdf.Fields("lastupdated")
    fld.Properties("DefaultValue") = ""
    dbs.Close
    ...</code>

  10. #10
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last Updated for new customer (Access 2000)

    Thank you so much !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    You lines above solved my problem right awat indeed !!!

Posting Permissions

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