Check Table for Existing Record, if not, then add it (1 Viewer)

[jmaty23]

I am trying to check if a value that is entered into a combo box currently exists in a table, and if it doesn't then I would like to insert it. This is the code that I have come up with but I can't seem to make it work. Any help would be great.

Private Sub cboreason_AfterUpdate()
If DCount("downtime_reason", "tbldowntime_reason", """" & Me.cboreason & """") = 0 Then
DoCmd.RunSQL "Insert into tbldowntime_reason (downtime_reason) Values (""" & Me.cboreason.Value & """)"
End If
End Sub

 

[John Big Booty]

Welcome to the Forum.

You would really want to utilise the Not In List event rather than the After Update event.

Have a look at the attached sample for one method of adding items to a Combo Box list.

 

[John Big Booty]

Caveat Emptor; This method, in the sample above, will only work where the record being added only requires the one piece of data to be added. It will not work if you are trying to add items to a price list, for example, where the product code, product description and unit price are require, for that you would need to use the process described in the link in my first post.

 

[jmaty23]

Thanks for the reply. I came up with a code that will do what I want it to using DCOUNT. It seems to be working fine. See below.

Private Sub cboreason_AfterUpdate()
If IsNull(Me.cboreason) Then
GoTo procedureexit
End If

Dim recnum As String
Dim sql As String

sql = "Insert into tbldowntime_reason (downtime_reason) values (""" & Me.cboreason.Value & """);"
recnum = DCount("downtime_reason", "tbldowntime_reason", "downtime_reason = '" & Me.cboreason.Value & "'")

If recnum = 0 Then
DoCmd.SetWarnings False
DoCmd.RunSQL sql
DoCmd.SetWarnings True
End If

Me.cboreason.Requery

procedureexit: Exit Sub
End Sub

 

[John Big Booty]

I a little interested why you don't use the native Not In List event

 

[jmaty23]

I didn't realize this event existed until you told me. I have already made the code and implemented it. I will keep it in mind for the future. Thanks for the examples.