display record in form then, .addnew

[JP]

(access 97) I have a form not tied to a table or query
that will write a new record using the .addnew method. I
want to find and display a existing record and then create
a new record with most of the existing data that is
displayed, only updating a few fields in the new record.
All records have a primary key incremented by 1 using VBA
so the new record will have a new key number but most of
the other fields will have the same data.

How do i do this and can i use the same form to create
original records, redisplay a existing record and do a
ADDNEW?

 

[Guest]

hi
here is code i use to do what you want. i did cut down on
the number if fields i am updating for example purposes.
just change the names of all of my fields and controls to
your fields and controls.

Private Sub cmdAccept_Click()
On Error Resume Next

Dim Dbs_Disc As Database
Dim rsDisc As Recordset
Dim dbs_tempDisc As Database
Dim rstempDisc As Recordset

If IsNull(Me!txtControlNbr) Then
MsgBox (" Enter a Control Number.")
Exit Sub

Else

If Me!txtControlNbr >= DLookup
("Disc_NN.Disc_NxN", "Disc_NN", "") Then
'here i check a table called NN(next number).
'If the number in Me!txtcontrolnbr is the next
'number then add new record else undate
'existing record.
Set Dbs_Disc = CodeDb()

Set rsDisc = Dbs_Disc.OpenRecordset("Disc", dbOpenDynaset)
With rsDisc
.AddNew
!dis_ControlNbr = txtControlNbr
!dis_ItemID = txtItemID
!dis_Description = txtDescription
!dis_VendorName = txtVendorName
!dis_POonPackSlip = txtPOonPackSlip
!dis_PackSlipNbr = txtPackSlipNbr
!dis_PackSlipQty = txtPackSlipQty
!dis_POQty = txtPOQty
!dis_OKICountQty = txtOKICountQty
!dis_OverUnderPO = txtOverUnderPO
!dis_OverUnderPS = txtOverUnderPS
!dis_Other = txtOther
!dis_Recdby = txtRecdBy
!dis_DateRecd = txtDateRecd
!dis_Status = txtStatus
!dis_InstructionComments = txtInstructionComments
!dis_Buyer = txtBuyer
!dis_DateDispositioned = txtDateDispositioned
.Update
End With
rsDisc.Close
Dbs_Disc.Close
MsgBox (" Record was added.")
Me.Requery


Call Clear_Form_Click
'here i call another sub that clears all the forms
'controls i.e. set them to null
Else
Set dbs_tempDisc = CodeDb()

Set rstempDisc = dbs_tempDisc.OpenRecordset
("tempDisc", dbOpenDynaset)
'here i use a temp table to house the record i am
'updating
With rstempDisc
.Edit
!dis_ControlNbr = txtControlNbr
!dis_ItemID = txtItemID
!dis_Description = txtDescription
!dis_VendorName = txtVendorName
!dis_POonPackSlip = txtPOonPackSlip
!dis_PackSlipNbr = txtPackSlipNbr
!dis_PackSlipQty = txtPackSlipQty
!dis_POQty = txtPOQty
!dis_OKICountQty = txtOKICountQty
!dis_OverUnderPO = txtOverUnderPO
!dis_OverUnderPS = txtOverUnderPS
!dis_Other = txtOther
!dis_Recdby = txtRecdBy
!dis_DateRecd = txtDateRecd
!dis_Status = txtStatus
!dis_InstructionComments = txtInstructionComments
!dis_Buyer = txtBuyer
!dis_DateDispositioned = txtDateDispositioned
.Update
End With
rsDisc.Close
Dbs_Disc.Close
MsgBox (" Record was Updated.")
Me.Requery
DoCmd.OpenQuery "qryVMDUpdate", acViewNormal, acEdit
'this is an append query that appends the content
'contents of the temp table to the main table
Call Clear_Form_Click

End If

End Sub