I am trying to update a record with new information. When I select the next record in a combo box, I run code that updates the record. The problem is that, the record that gets updated in the table is the new record that was selected in the combo box instead of the previous record in the the combo box.

I have tried running the code in the before and after update properties but did not seem to work.

Okay, a little more info would help...

From what you wrote I THINK you are saying

1. You have an unbound combo box to select a record
2. You select a record from the combo and code moves you to that record in the form.
3. You want code to update the record that you are moving to.

If that is correct, let me know.

Next, what exactly are you updating?

1. I have an unbound combo box to select a record
2. I select a record from the combo and code moves you to that record in the form.
3. The form is updated with details from the record. I then have an unbound text field that I enter additional information in to.
4. I then select the next record in the combo box. Prior to displaying the next record, the data that was entered into the unbound text field needs to be recorded into a recordset.

The problem is that in the VB code I use the cbo.value as the primary key in the other table where the unbound text box data is to be recorded. But instead of using the original value it uses the text from the newly selected item in the cbo box.

Why is the text field unbound? Is this data recorded in a seperate table? What does the code look like that you are using to do this?

Here is the code that runs from the cbo box.


I have also inclued a screen shot.

The text field is unbound because my cbobx recordset is distinct but my table is not. So I then enter file names in the txt box, I then update all occurances of the part in the table with the entry in the txt field.


Private Sub cboParts_BeforeUpdate(Cancel As Integer)
Call LookUpPart(Me.cboProjects.Value, Me.cboParts.Value)
End Sub


Function LookUpPart(ProjectID As Long, Part As String)
Set db = CurrentDb

If Me.ckChange Then
Call ParseLitTxt(Me.txtManual.Value, ProjectID, Part)
End If
Call Parts(ProjectID, Part)

db.Close
Set db = Nothing

Me.ckChange.Value = False
End Function

Function RecordLit(ProjectID As Long, Part As String, Qty As Integer)
Dim rslit As Recordset

SQL = "SELECT tblProject.ProjectID, tblPart.Part_Number, tblPart.PartID FROM (tblProject INNER JOIN tblSkid ON tblProject.ProjectID = tblSkid.ProjectID) INNER JOIN tblPart ON tblSkid.SkidID = tblPart.SkidID WHERE (((tblProject.ProjectID)=" & ProjectID & ") AND ((tblPart.Part_Number)='" & Part & "'));"
Set rs = db.OpenRecordset(SQL)
Set rslit = db.OpenRecordset("tblPartLiterature", dbOpenDynaset)

rs.MoveFirst
Do
For x = 0 To Qty
With rslit
.AddNew
!PartID = rs!PartID
!Literature = Lit(x)
.Update
End With
Next x
rs.MoveNext
Loop Until rs.EOF

rs.Close
rslit.Close
Set rs = Nothing
Set rslit = Nothing
End Function

'ParseLitTxt: Parses the vendor data file names into individual strings
Function ParseLitTxt(LitString As String, ProjectID As Long, Part As String)
Dim x As Integer
Dim Pos As Integer

'Clears array of data
For x = 0 To 20
Lit(x) = ""
Next x
x = 0
Length = Len(LitString)
Pos = InStr(1, LitString, vbCr)
If Pos Then
Do
Lit(x) = Mid(LitString, 1, Pos - 1)
LitString = Mid(LitString, Pos + 1)
If InStr(1, LitString, vbCr) Then
Pos = InStr(1, LitString, vbCr)
Else
x = x + 1
Lit(x) = LitString
Exit Do
End If
x = x + 1
Loop Until Pos = 0
Else
Lit(x) = LitString
End If
Call RecordLit(ProjectID, Part, x)
End Function

Obviously I need to try summerizing my problem again.

I want the before update action of my combo box to activate code which takes the value from the combo box and performs an action before the value is updated to the new selection in the box. Is this the way Before_Update works for combo boxes. If so, is the correct reference to the combo box

me.combobox.value

or do I need something else?

I have tried both before and after Update an seem to get the same result either way.