Create record with pervious data

  • Thread starter Thread starter Stephen Mak
  • Start date Start date
S

Stephen Mak

We are enhancing a social club membership database and
there is an end user request that

"When a member renew his / her membership, is it possible
to create a record automatically carries last year
information - Like 'Receive Newsletter By Post' "?

Thanks
 
Hi:

You could use some VBA code like this on the renew button (basically you
duplicate the record and move to that record) --

Dim db as DAO.Database
Dim rs as DAO.Recordset, rs1 as DAO.Recordset
Dim fld as DAO.Field
Dim lngID as Long

Set db = CurrentDB
'Here I assume that there is a ID field on the current form and MyTable is
the Table Name
Set rs = db.OpenRecordset("Select * from [YourTable] Where [ID] = " &
Me![ID],dbopenSnapshot)

Set rs1 = db.OpenRecordset("Select * from [YourTable]",dbOpenDynaset)

'this duplicates the record field by field ignoring the ID field -- ignore
any other non relevant fields
rs1.AddNew
For each fld in rs.Fields
If not ucase(fld.name) = "ID" then
if not isnull(fld.value) then
rs1.fields(fld.Name) = fld.value
end if
end if
Next
lngID = rs1.Fields("ID")
rs1.Update
rs1.Close
Set rs1 = Nothing
rs.Close
Set rs = Nothing

'now move to this new record on form by setting filter to new ID
Me.Filter = "[ID] = " & lngID
Me.FilterOn = True


Regards,

Naresh Nichani
MS Access MVP
 
Back
Top