Carry over data to next entry

[Stuart Grant]

I have a project with an Input Form. The first field (after an AutoNumber
ID) is TranDate. Since there are often quite a number of the same
transactions on any date, I would like the date of the previous record to
carry over to a new one.

Allen Browne has a macro to carry over all the fields to a new record but I
only want to carry over the date field since the others are almost never the
same. The date goes in a Text Box named txtTranDate.

I tried to modify Allen's syntax but can't get it to work. In the form's
BeforeInsert Event you enter the Event Procedure "Call CarryOver(Me). The
code I have written for the sub CarryOver(frm As Form) is

Dim rst As DAO.Recordset

Set rst = frm.RecordsetClone

If rst.RecordCount > 0 Then
rst.MoveLast
frm.txtTranDate = rst("TranDate")
End If

But it does not work. Can anyone help ?

Stuart

 

[Allen Browne]

To carry over just one field, set its Default Value.

Details in:
Carry current value of a control to new records
at:
http://www.mvps.org/access/forms/frm0012.htm

 

[Stuart Grant]

Went to the link and put the code behind the "New" button on the data entry
form but I get the dreaded #NAME appearing in the text box although in its
properties the format is "d mmm yy". What must I be doing wrong ?

Stuart

 

[Allen Browne]

Try this code, replacing MyDate with the name of your date control:

With Me.[MyDate]
If Not IsNull(.Value) Then
.DefaultValue = Format(.Value, "\#mm\/dd\/yyyy\#")
End If
End With

 

[Stuart Grant]

I'm sorry but I still can't get it to work. The text box stays blank.

The code which I have is as follows

Private Sub cmdNew_Click()
Const cQuote = """"
On Error GoTo Err_cmdNew_Click

DoCmd.GoToRecord , , acNewRec
Me.txtTranDate.DefaultValue = cQuote & Me.txtTranDate.Value & cQuote
With Me.txtTranDate
If Not IsNull(.Value) Then
.DefaultValue = Format(.Value, "\#d mmm yy#\")
End If
End With
Exit_cmdNew_Click:
Exit Sub

Err_cmdNew_Click:
MsgBox Err.Description
Resume Exit_cmdNew_Click

End Sub

I'm sorry but the #NAME error was my fault. In one trial I apparently
accidentally deleted the "cQuote &" after the equals sign. This caused the
error. When I replaced it, the text box stayed blank. I'm sorry to bother
you so much but can you see what is wrong with my code ? The field in the
table is called TranDate and the text box in the Input form is txtTranDate.

Stuart

 

[Allen Browne]

No: the code to set the DefaultValue goes into the AfterUpdate event of the
txtTranDate text box. Then whenever you type a date in, it remembers that
date as the Default Value to use for a new record (until you close the
form.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm sorry but I still can't get it to work. The text box stays blank.

The code which I have is as follows

Private Sub cmdNew_Click()
Const cQuote = """"
On Error GoTo Err_cmdNew_Click

DoCmd.GoToRecord , , acNewRec
Me.txtTranDate.DefaultValue = cQuote & Me.txtTranDate.Value & cQuote
With Me.txtTranDate
If Not IsNull(.Value) Then
.DefaultValue = Format(.Value, "\#d mmm yy#\")
End If
End With
Exit_cmdNew_Click:
Exit Sub

Err_cmdNew_Click:
MsgBox Err.Description
Resume Exit_cmdNew_Click

End Sub

I'm sorry but the #NAME error was my fault. In one trial I apparently
accidentally deleted the "cQuote &" after the equals sign. This caused
the error. When I replaced it, the text box stayed blank. I'm sorry to
bother you so much but can you see what is wrong with my code ? The field
in the table is called TranDate and the text box in the Input form is
txtTranDate.

Stuart

Try this code, replacing MyDate with the name of your date control:

With Me.[MyDate]
If Not IsNull(.Value) Then
.DefaultValue = Format(.Value, "\#mm\/dd\/yyyy\#")
End If
End With