Creating a new record (1 Viewer)

[Peter Bellamy]

I am looking for suggestions on how to code this:

I have a table that contains some base information used in pricing parts for various customers. (table SetupData)
Everytime there is a price increase or change of terms I add a new record to the table and move my 'current record' flag (Yes/No) to the new one.

In one instance this change only requires a date and percentage changed and I want to progamatically add the record.
This means, copy of last 'current record' data into the new record, update the date and % fields in the new record to ones provided on a form, and then change the 'current record' flag to this new record.

Using a Recordset seems the best way to .AddNew but how can I copy the last current record into it?

Cheers

 

[Peter Bellamy]

No suggestions then ??

 

[DCrake]

What you could do is to employ an array. When you open your recordset and move to the last record

Dim RecArray()

For I = 0 to Rs.Fields.Count-1
     RecArray(I) = Rs(I)
Next

This will pass the contents of all the fields into the array

Then use the .AddNew

Rs.AddNew
For I = 0 To Rs.Fields.Count - 1
   Rs(I) = RecArray(I)
Next
Rs.Update

Obviously this code is very crude but the logic is there.

 

[Peter Bellamy]

Thanks David for your response.
I will try your suggstion and repost when I (inevitably) hit a snag!

Peter

 

[Peter Bellamy]

This seems to work ok. but there maybe some surplus lines
Perhaps the experts can advise?

Dim db As Database
Dim rec As Recordset
Dim RecArray(20)
Dim i As Integer

Set db = CurrentDb()
Set rec = db.OpenRecordset("SetupData")

rec.FindFirst "param_current = -1"
For i = 1 To rec.Fields.Count - 1
     RecArray(i) = rec(i)
Next

rec.Edit
rec("param_current") = No
rec.Update
rec.MoveLast
rec.AddNew

For i = 1 To rec.Fields.Count - 1
   rec(i) = RecArray(i)
Next
rec.Update

rec.MoveLast
rec.Edit
rec("param_inc_date") = Me.ImpDate
rec("param_increase") = Me.Inc
rec("param_notes") = "List price increased by " & Me.Inc & "%"
rec.Update

Cheers