Here is the approach I would take, which involves not creating a new record
until it is needed. It ends up being even simpler than I thought.
Make sure your form's record source is the table and has at least the
following three bound text boxes (you will probably have more with other
information) in the form's detail section:
emp
appraise_date
next_appraise_date
Now, create a button called ButtonCopy (or whatever you want to call it) in
the form's header. Copy the following code into the Click event for the
button.
Private Sub ButtonCopy_Click()
Dim empTemp As String
Dim appTemp As Date
empTemp = emp
appTemp = next_appraise_date
DoCmd.GoToRecord acForm, Me.Name, acNewRec
emp = empTemp
appraise_date = appTemp
next_appraise_date.SetFocus
End Sub
To test this, make sure you are on the employee's (presumably emp is
employee) most recent entry (i.e. the one with the highest appraise_date) and
click the button. Access records the emp and current next_appraise_date to
the variables, takes you to a new record, sets the emp and appraise_date to
the values stored in the variables, and puts the focus on the
next_appraise_date so that you can enter it. This new record will be saved
automatically when you go to another record or close the form.
Songoku said:
Hi Brian,
thanks for the post and yep any sql/VBA would be mucho appreciated..
Cheers
Brian said:
It sound like you want to copy the current record to a new record, but make
the appraise_date of the new record the next_appraise_date of the current
record.
Make an append query that creates a new record from the new record but where
the Field entry that appends to appraise_dateis
[Forms]![ThisForm]![next_appraise_date] but appends this to the appraise_date
in the table.
Just a suggestion here, though. What about not creating the new record when
you enter the next_appraise_date, but when the next_appraise_date arrives and
will become the current appraise_date? You can still identify the
next_appraise_date, but it will be from the completed record, not from a new,
partially-completed record. Then, when you create a new record, just append a
record that is a copy of the record whose next_appraise_date is equal to
DMax("[next_appraise_date]","[Appraisals]","[emp] =" & Me![emp]).
Repost if you need specific SQL and/or VBA syntax to do this.
:
Hi,
I have a table that contains appraise_date and next_appraise_date fields and
what I
need is when the user enters the next_appraise_date, it gets updated to the
next record as appraise_date. so i get the following occuring.
emp appraise_date next_appraise_date
1 10/07/05 21/08/05
1 21/08/05
sure it is straight forward......
any pointers appreciated
Cheers
