Checking for existing record befor proceeding with append querry

[scooteman]

I have a form that has two subforms on it in datasheet view. One shows the training an employee should have based on there job assigment and the other form shows what they actually have. I have the subform that shows what they should have with an on click event that adds the training type to the current selected employee. The current event is:

Private Sub Training_Name_Click()
Dim trainmsg As Integer
trainmsg = MsgBox("Are you sure you want to add this training type to the selected employee?", 1, "Training Addition Message")
If trainmsg = 1 Then
DoCmd.SetWarnings warningsOff
DoCmd.OpenQuery "Query_Append_missingtraining"
DoCmd.SetWarnings warningsOn
MsgBox "A new record has been added.", vbInformation + vbOKOnly, "Record Added"
Forms![Spot_Missing_Training]![What_Employee_does_Have].Requery
End If
End Sub

Is there away I can set this up so that it looks first to see if the employee has the training type that is selected and if not, that it runs the append query and if does a message pops up saying that the a record exists and ends the event?

I thought of running the below querry and setting the event to occur depending on if the result is null or equal to but I don't know how to code that.

SELECT Training_Completed.Training_ID, Training_Completed.Employee_ID, Training_Completed.Training_Type
FROM Training_Completed
WHERE (((Training_Completed.Employee_ID)=[Forms]![Spot_Unassigned_Training]![employee_id]) AND ((Training_Completed.Training_Type)=[forms]![spot_Unassigned_Training]![What Employee Should Have]![id]));

 

[vbaInet]

There's a way Look into this function: DCount(), it returns the number of records based on what you set. So if the return value is greater than 0 then you know that record already exist. You would normally check this on the Before Update event if you are using bound controls.

 

[scooteman]

There's a way Look into this function: DCount(), it returns the number of records based on what you set. So if the return value is greater than 0 then you know that record already exist. You would normally check this on the Before Update event if you are using bound controls.

VBAInet,
Thanks so much for the help!!! The DCount() worked. This is how I did it:

Private Sub Training_Name_Click()
Dim trainmsg As Integer
Dim LTotal As Long
LTotal = DCount("Training_ID", "Query_Find_Null")
If LTotal = 0 Then

trainmsg = MsgBox("Are you sure you want to add this training type to the selected employee?", 1, "Training Addition Message")
If trainmsg = 1 Then
DoCmd.SetWarnings warningsOff
DoCmd.OpenQuery "Query_Append_unassignedtraining"
DoCmd.SetWarnings warningsOn
Forms![spot_unassigned_training]![What_Employee_does_Have].Requery
MsgBox "A new record has been added.", vbInformation + vbOKOnly, "Record Added"
End If
Else
If LTotal >= 1 Then
MsgBox "Training type already exists and will not be added.", vbInformation + vbOKOnly, "No Record Added"
End If
End If
End Sub

 

[vbaInet]

Excellent job scooteman. I'm glad you looked it up and sourced it out. Have a look at this though:

[QUOTE
LTotal = DCount("Training_ID", "Query_Find_Null", "[EmployeeID] = " & something)
[/QUOTE]

I think you need another paramter there, the WHERE clause bit (highlighted in red). That would search for the employee and return the right records. What do you think?

 

[scooteman]

Excellent job scooteman. I'm glad you looked it up and sourced it out. Have a look at this though:

[QUOTE
LTotal = DCount("Training_ID", "Query_Find_Null", "[EmployeeID] = " & something)

I think you need another paramter there, the WHERE clause bit (highlighted in red). That would search for the employee and return the right records. What do you think?[/quote]

Thanks for the suggestion. I will take a look and play with that. I originally didn't add the "Employee ID" as a where clause because it is built into the "Query_Find_Null as a criteria,(which shows me as a true novice at VBA) But it probably would be cleaner and better in the Dcount. I've tested it out on different records and It does find the right employee with the criteria in the query.

Thanks

 

[vbaInet]

Goodie. You're welcome. Have fun experimenting!