Message before Appending Records

[Lorlai]

I have a form that executes multiple queries when a button is pushed. One of these queries is to check if the employee exists in the master resource table. If they do not exist, I want to append the employee to the master table. This should only be done if the user agrees they are a new employee, however. (For example, if someone gets married, they are the same employee, just with a new name, and shouldn't be appended to the resource card). Is it possible to create a message box that displays the employee name, and if the user presses "Yes", appends the record to the master resource card, and if "No", the employee is not added?




Thank you for your help!

[pbaldy]

Sure. Look at the options available in VBA help for MsgBox. You want the Yes/No style, and you test the result for vbYes.

[Lorlai]

Thank you for your fast response Pbaldy. Is there a way to make this MsgBox work for each individual record? For instance, if I have 3 resources that are not in the master resource card:

Joe Smith
Smitty McSmith
Tracy LuLu

And I want to append Joe and Smitty, but not Tracy. Is there an easy way to display the individual record within the msgbox? Currently, when I run my message box code (shown below), all of the new resources are added when "yes" is pressed.

MsgBox("Do you want to add this Resource to the Master?", vbYesNo, "Add Resource?")

If vbNo Then


    'Append non existing Resource to Alert Table

    StrSQL = "INSERT INTO Blah blah blah SQL code works and would take too long to desensitize;"

        DoCmd.RunSQL StrSQL

Else
    'Append non exisiting Resource to Master

    StrSQL = "INSERT INTO Master ( Resource, [Cost] )SELECT Temp.[Name], Temp.[Rate] " & _
    "FROM Temp LEFT JOIN Master ON Temp.[Name] = Master.Resource;"

        DoCmd.RunSQL StrSQL
End If

Any help is greatly appreciated!

[orange]

Yes you could open a recordset which identifies those people not on the Master.
Loop through the recordset, include a message to determine if person should be added or not

Here's a rough sample from some of my data: (Student has not yet selected a Course)

Sub testRS()
    Dim rs As Recordset
    Dim Resp As Integer
    Set rs = CurrentDb.OpenRecordset("SELECT Student.id, Student.studentName, SelectedCourses.CourseId " _
    & " FROM SelectedCourses RIGHT JOIN Student ON SelectedCourses.StudentId = Student.id " _
    & " WHERE (((SelectedCourses.CourseId) Is Null));")

    Do While Not rs.EOF
        Resp = MsgBox("add " & rs!studentname & " to Master", vbYesNo)
        If Resp = vbYes Then
            MsgBox "Code to Add " & rs!studentname & " to Master goes here", vbOKOnly
           ' your code to add 
           'this record goes here 
           '
        Else
            MsgBox "Code to NOT Add " & rs!studentname & " to Master goes here, ignore this guy and get next ", vbOKOnly
           'your code to ignore this record 
           'and move on goes here
           '
        End If
        rs.MoveNext
        Loop
    End Sub

Good luck