Check if record already exists in table Access 2010 VBA

Question

I have created a table in Access 2010 that holds a list of employee names, using an ID as the primary key. I have another table which uses these names as a foreign key, via the ID, in a drop down box, which allows users to select an employee's name, and then record training to that name using a form.

What I would like to ask is, how would I write the VBA code that would check if an employee's name had already been added to the training table, thus checking if an employee had already completed that training, and then return a message box alerting the user; "This employee has already completed their training, are you sure you want to proceed?"

I am relatively new to VBA, however I assume I need some sort of Count method on the employee ID. Thanks in advance!

Check out DlookUp office.microsoft.com/en-ie/access-help/… — Fionnuala
– Fionnuala, Commented Jan 20, 2014 at 11:08
Thanks, this shows the function, but how would I put that into a statement that returns a message box if the count is greater than 1? — Chipsaird
– Chipsaird, Commented Jan 21, 2014 at 10:08
That is a new question, if you need the number of times a record is entered, you can use DCOUNT, or a recordset. — Fionnuala
– Fionnuala, Commented Jan 21, 2014 at 10:17

Fionnuala · Accepted Answer · 2014-01-21 14:58:39Z

1

I figured it out, thanks for your help!

'Save button

'Activated on click

'Runs macro if that employee has not already been added to the WelfareTraining database

Private Sub SaveRecord_Click()

    Dim stDocName As String

    stDocName = "SaveRecordTraining"

    If DCount("[Employee]", "WelfareTraining", "[Employee] = " & Me![Employee] & "  ") > 0 Then
        MsgBox ("This employee has already completed their training")
        Else
        DoCmd.RunMacro stDocName
    End If

End Sub

edited Jan 21, 2014 at 14:58

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

answered Jan 21, 2014 at 14:53

Chipsaird

211 gold badge1 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fionnuala Over a year ago

This implies that you are using an unbound form. The default behaviour in a bound form is to save the record.

Collectives™ on Stack Overflow

Check if record already exists in table Access 2010 VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related