Update previous record

[bk1010]

All,

I currently use a subform where it allows the user to issue a vehicle to an individual , with the issue auto populated.
However, I can't think of how to auto populate a return date when the vehicle swaps owner (via query/code).

ID	Owner	Vechicle Reg	Issue Date	Return Date
2​	Joe Bloggs	XX10XAB	01/01/2020​
20​	Fred Jones	XX10XAB	15/01/2020​

Ideally I'd like the Return Date to be populated with the new Issue Date.


So any advice to be great appreciated.

Thanks in advance

 

[Gasman]

That does not make any sense to me.

I return a car today. The car is not issued to you until Wednesday.?

Enter a date when the car is returned, not whenever it goes back out again.?

 

[bk1010]

Okay, fair point. I can create an inputbox but I'm not sure how to populate the previous record

 

[arnelgp]

you'll be adding Code to the SubForm's AfterUpdate:

Private Sub Form_AfterUpdate()
 dim dteReturn As Date
dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
If (rs.BOF and rs.EOF) Then Exit Sub
with rs
.MoveLast
.Edit
![Return Date] = Null
.Update
dteReturn = ![Issue Date]
.MovePrevious
While Not .BOF
.Edit
![Return Date] = dteReturn
.Update
dteReturn = ![Issue Date]
.MovePrevious
Wend
 End With
End Sub

 

[Gasman]

Well this is how I think it would happen.
I return a car. You check it out, all is good.

You locate the car record in your DB.
You can click a button called Returned. This as well as entering the date returned, also carries out any other processing needed, like No Damage, Deposit returned. Whatever else that happens. Personally 'Id just use Ctrl + ; to enter todays date. You could even set the date as soon as you enter the control, if that is the only time you do so.?

I'm all for automation, but some tasks are just as easy, if not easier to carry out manually.?

From your original request you could look for the record for that reg with an empty date field and update that with todays date, but as I queried how often is that going to happen.?
Wouldn't you also need to record time out and time in?, in case the car comes back a little late?

HTH

 

[bk1010]

you'll be adding Code to the SubForm's AfterUpdate:

Private Sub Form_AfterUpdate()
dim dteReturn As Date
dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
If (rs.BOF and rs.EOF) Then Exit Sub
with rs
.MoveLast
.Edit
![Return Date] = Null
.Update
dteReturn = ![Issue Date]
.MovePrevious
While Not .BOF
.Edit
![Return Date] = dteReturn
.Update
dteReturn = ![Issue Date]
.MovePrevious
Wend
End With
End Sub

Thank you [U]arnelgp[/U] work a treat

 

[arnelgp]

you are welcome.