updating textbox from combobox for editing

[JJSHEP89]

Background: I have a form that is used by DB administrators only that allows them to edit Tooling and part data, the tooling data is auto populated based on a global variable when the form is opened. Because one tool can produce multiple parts, in this same form I have a combobox where the user can select which specific part number's data to edit. On the combobox's After Update property, I have several lines of code updating all the subsequent textboxes and comboboxes using the .Column(x) property.

Now here's the problem, when I hit the "save" button to save the changes made on the form I get an error and any changes made to the textboxes with part number specific data do not get saved.

Can i not edit text fields that have been populated using the .Column() property and expect the changes to be reflected in the base tables after its saved?

Below is all the code on the form for reference.

Option Compare Database
Dim SaveButtonClick As Boolean
Private Sub Form_Load()
    Me.Caption = "Edit Data For Tool Number " & varToolNumber & ""
    cboPM_PartNumber.RowSource = "SELECT DISTINCT partmatrix.PM_PartNumber,  partmatrix.PM_PartDescription, partmatrix.PM_PartRev, partmatrix.PM_DrawingRev, " & _
                                                "partmatrix.PM_Customer, partmatrix.PM_MaterialSpec, partmatrix.PM_MaterialGrade, partmatrix.PM_PartWeight,  " & _
                                                "partmatrix.PM_PartFamily, partmatrix.PM_PartStatus, partmatrix.PM_PartFinish, partmatrix.PM_EEOP, partmatrix.PM_Notes " & _
                                 "FROM partmatrix " & _
                                 "WHERE (PartMatrix.PM_ToolNumber = '" & varToolNumber & "');"
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Not SaveButtonClick Then
        Cancel = True
        Me.Undo
    End If
End Sub

Private Sub cboPM_PartNumber_AfterUpdate()
    txtPartDescription = Me.cboPM_PartNumber.Column(1)
    txtPartRev = Me.cboPM_PartNumber.Column(2)
    txtDrawingRev = Me.cboPM_PartNumber.Column(3)
    txtCustomer = Me.cboPM_PartNumber.Column(4)
    txtMaterialSpec = Me.cboPM_PartNumber.Column(5)
    txtMaterialGrade = Me.cboPM_PartNumber.Column(6)
    txtPartWeight = Me.cboPM_PartNumber.Column(7)
    cboPartFamily = Me.cboPM_PartNumber.Column(8)
    
End Sub
'------------------------------------------------------------
'Button Save Changes
'------------------------------------------------------------
Private Sub btnSaveChanges_Click()
Dim Count As Integer
Dim MsgStr As String
    
    On Error GoTo ErrorHandler:
    SaveButtonClick = True
    
'verifies a value is present for each required field
    If IsNull(Me.TM_ToolType) Or IsEmpty(Me.TM_ToolType) Then
        Count = Count + 1
        MsgStr = MsgStr & vbNewLine & Chr(149) & "Tool Type"
    End If
    If IsNull(Me.TM_ToolNumber) Or IsEmpty(Me.TM_ToolNumber) Then
        Count = Count + 1
        MsgStr = MsgStr & vbNewLine & Chr(149) & "Tool Number"
    End If
    If IsNull(Me.TM_OperatingPlant) Or IsEmpty(Me.TM_OperatingPlant) Then
        Count = Count + 1
        MsgStr = MsgStr & vbNewLine & Chr(149) & "Operating Plant"
    End If
    
    If Count > 0 Then
        MsgBox "Please input a value for the following fields." & vbNewLine & MsgStr & "."
        Count = 0
        SaveButtonClick = False
        Exit Sub
    End If
    
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close
    SaveButtonClick = False
Exit Sub
    
ErrorHandler:
        MsgBox "Unknown Database error, try again.  If the error persists, close and re-open the Info Center.", , "Database Error"
        Exit Sub

End Sub
'------------------------------------------------------------
'Button Cancel New Tool
'------------------------------------------------------------
Private Sub btnCancelNewTool_Click()
    SendKeys ("{ESC}")
    DoCmd.Close acForm, "Entry_NewTool", acSaveNo
End Sub

 

[JJSHEP89]

just thinking about this a bit, and correct me if im wrong...

this block of code, would only output the actually value of the column to the textbox, but the data would not be bound to the base table, meaning there is nothing to tie it back in when the data is trying to be saved.

    txtPartDescription = Me.cboPM_PartNumber.Column(1)
    txtPartRev = Me.cboPM_PartNumber.Column(2)
    txtDrawingRev = Me.cboPM_PartNumber.Column(3)
    txtCustomer = Me.cboPM_PartNumber.Column(4)
    txtMaterialSpec = Me.cboPM_PartNumber.Column(5)
    txtMaterialGrade = Me.cboPM_PartNumber.Column(6)
    txtPartWeight = Me.cboPM_PartNumber.Column(7)
    cboPartFamily = Me.cboPM_PartNumber.Column(8)

Right?

 

[arnelgp]

If the textboxes and combo us unbound it will not get saved.

 

[missinglinq]

It always helps, of course, if an error is being popped, to tell us what error your'e receiving, if you want help!

You should also not that things like

IsEmpty(Me.TM_ToolNumber)

are inappropriate, as IsEmpty only applies to Variables, not to Controls, and may cause errors, as well!

Linq ;0)>

 

[JJSHEP89]

IsEmpty(Me.TM_ToolNumber)

are inappropriate, as IsEmpty only applies to Variables, not to Controls, and may cause errors, as well!

Linq ;0)>

Ah, thanks for this tip! There's a lot I do not know about VBA that resources like this forum have taught me.

Im not sure what error was causing the crash, it went straight to my error handler which didnt document the error number at the time. I got pulled off for another project but i'll try and recreate and log the error when i get back on it.