Moving

[natural]

Good Morning

I am trying via a cmd button on click To provide the user with an option to move the current record to a new table or to say no and keep it for later use.

Currently moving the record is working perfectly..

1st - If the user select Yes, if moves it perfecly as required.
2nd - However on No, It still moves the record and removes all records from the form. They are however still in the Table, but for some reason no records are visible..


I was hoping someone could possilbly assist in


Private Sub SendToOffer_Click()
On Error GoTo Err_Handler

DoCmd.SetWarnings False

Dim stDocName As String
Dim stLinkCriteria As String
Dim iAnswer As Integer

If IsNull(Me!ID) Then stLinkCriteria = ""
stLinkCriteria = "[ID]=" & Me![ID]

iAnswer = MsgBox("Are you sure you would like to move the record for " _
& Me.FirstName & " " & Me.Surname & " " _
& "to the offer table?" _
, vbCrLf & vbYesNoCancel)


If vbYes Then

DoCmd.RunSQL ("INSERT INTO tblOffer SELECT * FROM TblCandidates " & _
"Where ID = Forms!frmInterview!ID;")


DoCmd.RunSQL ("DELETE * FROM TblCandidates " & _
"Where ID = Forms!frmInterview!ID;")

Else

DoCmd.Beep


End If

Forms!FrmInterview.Requery
DoCmd.SetWarnings True


Exit_Status_Change:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Status_Change

End Sub

 

[ErikSnoek]

First thing I noticed is you have

If vbYes Then

which should be

If iAnswer = vbYes Then

 

[namliam]

Please use [ code ] and [ /code ] (without the spaces) around your posted code to make it much more readable on the forum

You want your answer to be vbYes... so you have to compare your answer in your IF to vbYes.
If iAnswer = vbYes Then

Edit: Erik beat me to it by a few seconds