VBYes VBNo

[naturalsn]

Good Morning

I was hoping someone could possible assist.

I currently have some code. That with a current record on the form. The user can append to a different table.

Once user click a message box come up asking Are you sure you would like to "append" the following record, if user says yes. Brilliant it "appends", if user says No, it cancel the whole "append"

Curerntly miine works when user says Yes, but upon clicking No. It still appends the record.

I hoped someone could asssit.

Private Sub Command107_Click()
On Error GoTo Err_Handler

Dim db As DAO.Database
Set db = CurrentDb()
Dim strSQL As String
Dim iAnswer As Integer

iAnswer = MsgBox("Are you sure you would like to copy the record for " _
        & Me.[First Name] & " " & Me.Surname & " " _
        & "to the HR Table?" _
        , vbCrLf & vbYesNoCancel)
    
    If vbYes Then
    
    DoCmd.SetWarnings False

strSQL = "INSERT INTO TblCandidates (1,2,1,)"
strSQL = strSQL & "SELECT 1,2,1 FROM tblPersonnelInfo "
strSQL = strSQL & "WHERE ID = Forms!form1!id ;"
DoCmd.RunSQL strSQL

If vbNo Then GoTo Exit_Command107_Click:
      'User doesn't want to append data
            
    End If
  
Exit_Command107_Click:
    Exit Sub
    
Err_Handler:
 MsgBox "Error: " & Err.Number & " - " & Err.Description
 Resume Exit_Command107_Click

 

[PHV]

Replace this:
If vbYes Then
with this:
If iAnswer = vbYes Then

Furthermore put your cursor inside the If word in your code and press the F1 key to discover the syntax.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

And then you can get rid of this:
If vbNo Then GoTo Exit_Command107_Click:
'User doesn't want to append data

 

[TheAceMan1]

or perhaps the following:

[blue]Private Sub Command107_Click()
   Dim db As DAO.Database, SQL As String
   Dim Msg As String, Style As Integer, Title As String
   
   Set db = CurrentDb()
   
   Msg = "Are you sure you would like to copy the record for " & _
          Me.[First Name] & " " & Me.Surname & " " & _
          "to the HR Table?"
   Style = vbQuestion + vbYesNo
   Title = "Affirm Copy?"
   
   If MsgBox(Msg, Style, Title) = vbYes Then
      DoCmd.SetWarnings False
   
      SQL = "INSERT INTO TblCandidates (1,2,1,) " & _
            "SELECT 1,2,1 FROM tblPersonnelInfo " & _
            "WHERE ID = " & Forms!form1!id & ";"
      db.Execute SQL, dbFailOnError
      DoCmd.SetWarnings True
   End If
   
   Set db = nothimg

End Sub[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[naturalsn]

Brilliant

Thank you very much everyone...

SN