Update a table based on ComboBox Selection

  • Thread starter Thread starter Jim Evans
  • Start date Start date
J

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
 
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
 
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


SC ChiefsFan said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top