Adding Data In ComboBox

[baul14]

hello all, please help.

I have this combobox, which contain list of something.
what i want is once the user enters or type a value in that combobox, but which is not in the list yet.
i want it to add in the table but my code is not working i don't know why. pls help.

table:
ID (primary key)
Category (text)

code below:

Private Sub Combo0_BeforeUpdate(Cancel As Integer)

Dim cn As ADODB.Connection, RS As New ADODB.Recordset
Dim strSQL As String
Dim i As Integer
Dim blnExist As Boolean

Set cn = CurrentProject.Connection
cn.BeginTrans

RS.Open "tbl_ComboTest", cn, adOpenKeyset, adLockOptimistic

blnExist = False

RS.MoveFirst
For i = 1 To RS.RecordCount
If Me.Combo0.Text = RS![Category] Then
blnExist = True
End If
RS.MoveNext
Next i

If Not blnExist Then
RS.AddNew
RS![Category] = Combo0.Text
RS.Update
End If

RS.Close

 

[dcx693]

Why do you have this line:
cn.BeginTrans

If you must use transactions, use this at the end of the sub:
cn.CommitTrans

Most people use the OnNotInList event for this.

 

[Mile-O]

This forum does have a search function (although sometimes I wonder why )

I posted both a DAO and an ADO solution on this thread yesterday so it should be near the top of a search on combo's, ADO, etc.

 

[dcx693]

Why search for the solution when you can make people work to post it again?

 

[Mile-O]

Why search for the solution when you can make people work to post it again?

Work for no pay at that. It's a good question. I shall go write out an ADO function 10 times.

 

[dcx693]

Why write it tens times? If you search the forums Mile, you can find out how to write a loop to just write it once and.... Oh - you were joking....