If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Repeat fields from last record
Access 2003
I have a form with many fields most of which are based on a table. My client would like these fields to be defaulted from the last entered record. Should I use Defaultvalue property for each field? If yes, how to get last record without disturbing current (new) record ? TIA -- AP |
#2
|
|||
|
|||
Repeat fields from last record
You can set the Default Value property of each control you want to carry over
the value for by using the Form Before Update event. You make the default value the current value: With Me .txtSomeControl.DefaultValue = .txtSomeControl .txtAnotherControl.DefaultValue = .txtAnotherControl etc.... End With -- Dave Hargis, Microsoft Access MVP "Ardus Petus" wrote: Access 2003 I have a form with many fields most of which are based on a table. My client would like these fields to be defaulted from the last entered record. Should I use Defaultvalue property for each field? If yes, how to get last record without disturbing current (new) record ? TIA -- AP |
#3
|
|||
|
|||
Repeat fields from last record
See:
Assign default values from the last record at: http://allenbrowne.com/ser-24.html The article explains how to use the form's BeforeInsert event procedure to populate the fields of the current record with the values from the last one. The code recognises the autonumber and any calculated controls, so doesn't try to assign them. You can also specify other controls that should not be copied over. If you have only one or two fields you want to duplicate, you could use the DefaultValue of those controls, as explained he Carry current value of a control to new records at: http://www.mvps.org/access/forms/frm0012.htm -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ardus Petus" wrote in message ... Access 2003 I have a form with many fields most of which are based on a table. My client would like these fields to be defaulted from the last entered record. Should I use Defaultvalue property for each field? If yes, how to get last record without disturbing current (new) record ? |
#4
|
|||
|
|||
Repeat fields from last record
You'll hopefully have noticed that the code at the second link Allen set you
wraps the value in quotes characters. That's important. The DefaultValue property is a string expression regardless of the data type in question. In most cases it won't matter if you don't wrap the value in quotes, but in some cases its crucial. Its particularly so with dates in short date format. Say you have entered today's date in the format 07/01/2008 in a control and you want it carried forward to the next record entered. If you use the following in a form's AfterUpdate event procedu Me.txtMyDate.DefaultValue = Me.txtMyDate then 07/01/2008 would be interpreted as an arithmetic expression evaluating to 0.00348605577689243. In Access's date/time implementation that value in fact represents 30 December 1899 00:05:01, which is not what you'd want inserted. By using: Me.txtMyDate.DefaultValue = """" & Me.txtMyDate & """" its is interpreted correctly as a string expression and the correct value will be assigned to the DefaultValue property. The value will only be retained while the form is open of course, so if its closed and reopened the value won't be carried forward. You can achieve that if each record includes a unique value in a DateTimeStamp column, however, whose DefaultValue property can be set to Now(), you can look up the last entered record in the form's Current event procedure, and assign the values from it to the DefaultValue properties of controls on the form: Dim dbs As DAO.Database, rst As DAO.Recordset Dim LastDateTime As Variant Dim strSQL as String If Me.NewRecord Then LastDateTime = DMax("DateTimeStamp", "MyTable") If Not IsNull(LastDateTime) Then strSQL = "SELECT * FROM MyTable " & _ "WHERE DateTimeStamp = #" & _ FORMAT(LastDateTime,"mm/dd/yyyy hh:nn:ss") & "#" Set dbs = CurrentDb Set rst = dbs.OpenRecordset(strSQL) With rst Me.txtMyDate.DefaultValue = _ """" & .Fields("MyDate") & """" Me.txtSomeOtherField.DefaultValue = _ """" & .Fields("SomeOtherField") & """" ' and so on End With End If End If One thing to be aware of when using the DefaultValue property is that this does not initiate a new record and Dirty the form. That only happens when the user begins to insert data, or values are assigned by code. This should not be a problem as if no other values are inserted into other fields, or none of the default values are changed, this would suggest that there is something wrong with the design of the underlying table. BTW don't assume that if the table includes an incrementing autonumber column this can be used in place of a TimeDateStamp column to determine the last entered record. Mostly it would, but you can't absolutely guarantee it as an autonumber is only designed to ensure unique values not necessarily sequential ones. Ken Sheridan Stafford, England "Ardus Petus" wrote: Access 2003 I have a form with many fields most of which are based on a table. My client would like these fields to be defaulted from the last entered record. Should I use Defaultvalue property for each field? If yes, how to get last record without disturbing current (new) record ? TIA -- AP |
#5
|
|||
|
|||
Repeat fields from last record
Thanks to all. I finally used the DefaultValue property.
Cheers -- AP "Ardus Petus" a écrit dans le message de news: ... Access 2003 I have a form with many fields most of which are based on a table. My client would like these fields to be defaulted from the last entered record. Should I use Defaultvalue property for each field? If yes, how to get last record without disturbing current (new) record ? TIA -- AP |
Thread Tools | |
Display Modes | |
|
|