Add an item to combo box with vba

[thanosgr]

Hi i've created a simple form with a combo box, using wizard, and i create this code. for to add items when isnotinlist event.
after requery, old data are changed to primary id numbers/.

Eg

Nametxt, id=1
Surnamext, id=2
Agetxt id =3

When i add a new item
the first item nametxt, converts to 1. This is the id

here is the code

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim rs As New ADODB.Recordset

rs.ActiveConnection = cnn1

rs.Open " SELECT * from affectiontbl ", cnn1, adOpenDynamic, adLockPessimistic


Dim strMsg As String


strMsg = "'" & NewData & "' msg " & vbCrLf & vbCrLf
strMsg = strMsg & "msg;"
strMsg = strMsg & vbCrLf & vbCrLf & "msg"

If MsgBox(strMsg, vbQuestion + vbYesNo, "msg;") = vbNo Then
Response = acDataErrContinue
Else

rs.AddNew
rs!data = NewData
rs.Update
Response = acDataErrAdded
End If

rs.Close
Set rs = Nothing
Set cnn1 = Nothing

[RuralGuy]

I'm going to start by suggesting you not use Reserved Words for names of any of your objects like fields. http://www.allenbrowne.com/AppIssueBadWord.html#D
Could you also post the SQL for your RowSource?

[thanosgr]

SELECT [AffectionTbl].[AffectionId], [AffectionTbl].[data] FROM AffectionTbl;

[RuralGuy]

Great! Now what values do you have for:
1) Column Count -
2) Column Widths -
3) List Width -
4) Bound Column -

[thanosgr]

Here you are...


1) 2
2) 0cm.;7,434cm
3) 7,434cm.
4) 1

[RuralGuy]

Is the [AffectionId] an AutoNumber?

[thanosgr]

yes its autonumber.
when i insert a new record, it adds the record. The new records has a new id, and then updates the remaining record the first one, with the new id of the new record

[RuralGuy]

. The new records has a new id, and then updates the remaining record the first one, with the new id of the new record

Could you say that another way please. I'm uncertain what you mean.

[RuralGuy]

I think it is time to *import* everything into a fresh new db and see if the problem follows: http://www.btabdevelopment.com/ts/impnew

[Rawb]

rs.AddNew
rs!data = NewData
rs.Update

I'm not sure but this block MIGHT be part of the problem. After you Add the New Record, you don't move to that empty Record before updating the Fields. If you change this to a With block, it may solve your problem:

With rs
    .AddNew
    !data = NewData
    .Update
End With

This will make sure you are adding data to the new Record and not any Record that already exists.

[RuralGuy]

rs.AddNew
rs!data = NewData
rs.Update

I'm not sure but this block MIGHT be part of the problem. After you Add the New Record, you don't move to that empty Record before updating the Fields. If you change this to a With block, it may solve your problem:

With rs
    .AddNew
    !data = NewData
    .Update
End With

This will make sure you are adding data to the new Record and not any Record that already exists.

I believe those two pieces of code generate the exact same execution code.

[thanosgr]

Hi, i follow your advice, i create a new database... with a simple form and a combo box, i copied the code as i wrote it.. and it works...

hmmmm what happened?

But thanks anyway

[RuralGuy]

Corruption can do strange things. http://allenbrowne.com/ser-25.html