place duplicate record in 2nd table

[NeoPa]

No problem. Glad I could help.
By the way, when running queries from the code it's often considered neater to hide any query warnings that indicate how many records are deleted / copied etc. If you want to include that then try :

Call DoCmd.SetWarnings(False)
'Query(ies) here
Call DoCmd.SetWarnings(True)

[shaffy08]

so my code will look like this? if so, it didn't work.

Call DoCmd.SetWarnings(False)
Private Sub add_Click()
On Error GoTo Err_add_Click
 
    Call DoCmd.RunCommand(acCmdRecordsGoToNext)
    DoCmd.OpenQuery "NOI", acNormal, acEdit
    DoCmd.OpenQuery "delete", acNormal, acEdit
    Call DoCmd.RunCommand(acCmdRecordsGoToPrevious)
 
Exit_add_Click:
    Exit Sub
 
Err_add_Click:
    MsgBox Err.Description
    Resume Exit_add_Click

Call DoCmd.SetWarnings(True)
End Sub

[NeoPa]

Not exactly. The only code between the two new lines is the queries.
IE :

Private Sub add_Click()
On Error GoTo Err_add_Click

    Call DoCmd.RunCommand(acCmdRecordsGoToNext)
    [U]Call DoCmd.SetWarnings(False)[/U]
    DoCmd.OpenQuery "NOI", acNormal, acEdit
    DoCmd.OpenQuery "delete", acNormal, acEdit
    [U]Call DoCmd.SetWarnings(True)[/U]
    Call DoCmd.RunCommand(acCmdRecordsGoToPrevious)

Exit_add_Click:
    Exit Sub

Err_add_Click:
    MsgBox Err.Description
    Resume Exit_add_Click

End Sub

[shaffy08]

Thanks! it works great. Is there a way to move automatically to next record after deletion...as of right now, it shows #delete in the fields?

[NeoPa]

Try the following after the code that does the work :

Call Me.ReQuery()

[shaffy08]

nope. that didn't work :(

[PianoMan64]

just ry me.requery at the end off that sub routine

[NeoPa]

nope. that didn't work :(

So what does it do? What did you notice?

[shaffy08]

So what does it do? What did you notice?

it works now :-) I was placing it at the wrong place - I was placing it after the code that hide the warring message instead of right after the code that runs the delete query.

thanks for the help.

[NeoPa]

No worries Shaffy. I'm pleased you got it sorted :)