updating textbox from combobox for editing (1 Viewer)

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

 

[Pat Hartman]

Problems with code:
1. You are not using the correct event to do the validation. Lots of things can cause Access to save the current record, not just your button. If Access takes it upon itself to save, NONE OF YOUR VALIDATION IS EXECUTED!! The best event to use for validation is the FORM's BeforeUpdate event. Think of this event as the flipper at the end of a funnel. It is the last event to run before a record is saved regardless of what prompted the save AND you can cancel it if you find an error and that will prevent the record from being saved.
Your validation code in the form's BeforeUpdate event should follow this pattern:

    If Me.TM_ToolType & "" = "" Then
        Cancel = True
        MsgStr = MsgStr & vbNewLine & Chr(149) & "Tool Type"
        Me.TM_ToolType.SetFocus
        Exit Sub
    End If

2. If the data is not being saved, make sure that the form is bound and that the controls you expect to be saved are bound.
3. prefix controls with "Me.". It will give you intellisense and it is more efficient for the compiler since it tells the compiler in which library the variable name is defined.

Me.txtPartDescription = Me.cboPM_PartNumber.Column(1)

3. If a form is bound to a query that joins to the part table, you don't need any of the code that pulls columns from the combo's RowSource. You can simply bind controls directly to the RecordSource. However, you should probably set the locked property of these "lookup" values to Yes to prevent accidental updates.
4. Use this case statement in your error handler. I added one specific error which I always ignore and the Else option. Just add other cases as needed.

Exit_Proc:
    Exit Sub
Err_Proc:
Select Case Err.Number
    Case 2501
        Resume Next
    Case Else
        Msgbox Err.Number & "--" & Err.Description
        Resume Exit_Proc
End Select
Exit Sub