Update a table based on ComboBox Selection

[Jim Evans]

I need to update a Field named CID in a table named tblCID based on the
selection in a combobox where Column 0 = the CID.

This code runs but doesn't update the field value, though it does find the
correct record on thr form. The MsgBoxes both show the correct value..

What an I doing incorrectly?

All help appreciated.

tia,

Jim Evans

Private Sub Combo50_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Dim dbs As Database
Dim rst As DAO.Recordset
Dim CID As Integer

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo50], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblCID", dbOpenTable)
With rst
.MoveFirst
.Edit
CID = Combo50.Column(0)
.Update
.Close
End With
MsgBox (Combo50.Column(0))
MsgBox (CID)
Combo50 = Null
FirstName.SetFocus
End Sub

 

[Guest]

Jim,

I haven't used the STR or NZ functions too often, but don't they return a
text string (instead of the integer your CID variable is set to). That could
be the problem...
Regards,
Doug Miller

 

[Jim Evans]

Doug,

The Functions that you refer to are in the part of the code where the proper
record is being located for display in the form.

The code I included with my post is actually performing to actions.
1. finding a record to display in the form.
2. updating a value in a non related table.

It was # 2 that I was having a problem with. The problem was as follows:

CID = Combo50.Column(0)

Should have been:

![CID] = Combo50.Column(0)

Jim Evans

Jim,

I haven't used the STR or NZ functions too often, but don't they return a
text string (instead of the integer your CID variable is set to). That could
be the problem...
Regards,
Doug Miller

I need to update a Field named CID in a table named tblCID based on the
selection in a combobox where Column 0 = the CID.

This code runs but doesn't update the field value, though it does find the
correct record on thr form. The MsgBoxes both show the correct value..

What an I doing incorrectly?

All help appreciated.

tia,

Jim Evans

Private Sub Combo50_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Dim dbs As Database
Dim rst As DAO.Recordset
Dim CID As Integer

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo50], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblCID", dbOpenTable)
With rst
.MoveFirst
.Edit
CID = Combo50.Column(0)
.Update
.Close
End With
MsgBox (Combo50.Column(0))
MsgBox (CID)
Combo50 = Null
FirstName.SetFocus
End Sub