Duplicate Record Errors

[EddyLLC]

I have a form with a combo box (CompNameSelect) whose control source is Table tblCompany. I want to take a selection from the combo box, test if a record exists in Table tbleToDo for the selection, if not make one, then make the selection the current record for the form. I'm using the following code but getting an error regarding duplicate records. Any help is appreciated.

Dim cmdSQL As String
Dim rs As Recordset
Set db = CurrentDb
cmdSQL = "select * from tblToDo where NameofComp='" + CompNameSelect + "'"
'cmdSQL = "select * from tblToDo where NameofComp='" + InputValue + "'"
Set rs = db.OpenRecordset(cmdSQL, dbOpenDynaset)
If rs.BOF And rs.EOF Then
With rs
.AddNew
!NameOfComp = CompNameSelect
.Update
End With
Else
Me.Bookmark = rs.Bookmark
End If
rs.Close
Me.CompNameSelect.Requery

 

[Golom]

Try using "&" instead of "+" to concatenate.

cmdSQL = "select * from tblToDo where NameofComp='" [red]&[/red] CompNameSelect [red]&[/red] "'"

 

[EddyLLC]

Using the code below a new record is created but when I try input data into the form it is put into the first record in the table not the newly created record.

Dim rs As ADODB.Recordset
Dim SQLStmnt As String
strRef = CompNameSelect

SQLStmnt = "SELECT * FROM tblToDo WHERE NameofComp = '" & strRef & "'"

Set rs = New ADODB.Recordset
rs.Open SQLStmnt, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rs.EOF Then
rs.AddNew
rs![NameofComp] = strRef
rs.Update
End If

 

[Golom]

rs" is a temporary recordset that you created to add the new record. It is not the recordset on which your form is based. You need to position that recordset to the new record after you add it.