Carry over data to next entry


S

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
 
Ad

Advertisements

S

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
 
A

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
 
S

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
 
Ad

Advertisements

A

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.

Stuart Grant said:
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 said:
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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top