Problem With Code

[mattc66 via AccessMonster.com]

I want this code to lookup the Item number if it finds it in the table. If it
doesn't find the record to add a new record. What is happens when I drop down
the combo box and select a record that is already in the table it tries to
add the record and I get a message about adding duplicates rather then the
code taking me the record already in the table. Can anyone help me.. Thanks

Private Sub LookUpItem_AfterUpdate()

Dim SID As String
Dim rsc As DAO.Recordset
Dim db As Database

Set rsc = Me.RecordsetClone
Set db = CurrentDb

SID = "ItemComponent" = Me.LookUpItem.Value

'Record not found add record
If DCount("ItemComponent", "tblItemComponent", SID) = 0 Then

DoCmd.GoToRecord , , acNewRec
Me.ItemComponent = Me.LookUpItem.Column(0)
Me.ItemDescription = Me.LookUpItem.Column(1)
Me.Refresh

ElseIf DCount("ItemComponent", "tblItemComponent", SID) >= 1 Then

'Go to record of original Number
rsc.FindFirst SID
Me.Bookmark = rsc.Bookmark

End If

Set rsc = Nothing


End Sub

 

[Tom van Stiphout]

On Tue, 01 Sep 2009 04:26:03 GMT, "mattc66 via AccessMonster.com"

That should be:
SID = "ItemComponent=" & Me.LookUpItem.Value

What you had was a boolean expression: is the string "ItemComponent"
equal to the LookUpItem.Value? Answer: No = False = 0. So the code
took the IF branche.

-Tom.
Microsoft Access MVP

 

[PieterLinden via AccessMonster.com]

It looks like you're after the Seek method of the DAO recordset object. Look
it up in the help.
It assumes you're looking for an indexed value.
If rs.NoMatch Then
'add the record
Else
'go to the record
End If