Select Case won't update all fields

[KyleB]

I'm attempting to put in some VBA code to simplify data entry, by updating controls on a subform based on prior entries. In essence, as the user enters values going from left to right, these values can in some cases, modify values further along the list accordingly. However, my problem seems to be that some of the controls aren't updating as the code says, and the savecommand isn't executing either. Though this may be because I misunderstand the sequence of events in the Afterupdate of the control. I'm wanting the saverecord command to execute because this cascades through subsequent cells and performs calculations based on the changes occuring here.

Private Sub Cost_Code_AfterUpdate()
Select Case [Cost Code]
    Case Is = 421
        Me.Material_Type = "Waste"
        Me.Height = 15
        Me.Width = 15
    Case Is = 422
        If (Parent.[Zone] = "US" Or Parent.[Zone] = "UN" Or Parent.[Zone] = "UE") Then
            Me.Material_Type = "Ore"
            Me.Height = 15
            Me.Width = 20
        Else
            Me.Material_Type = "Ore"
            Me.Height = 15
            Me.Width = 25
        End If
    Case Is = 423
        If (Parent.[Zone] = "US" Or Parent.[Zone] = "UN" Or Parent.[Zone] = "UE") Then
            Me.Material_Type = "Ore"
            Me.Height = 15
            Me.Width = 20
        Else
            Me.Material_Type = "Ore"
            Me.Height = 15
            Me.Width = 25
        End If
    Case Is = 441
        If (Parent.[Zone] = "SM") Then
            Me.Material_Type = "Ore"
            Me.Height = 17
            Me.Width = 16
        Else
            Me.Material_Type = "Ore"
            Me.Height = 15
            Me.Width = 15
        End If
    Case Is = 442
        Me.Material_Type = "Ore"
        Me.Height = 15
        Me.Width = 15
    Case Else
        Me.Material_Type = "Ore"
        Me.Height = 15
        Me.Width = 15
    End Select
    DoCmd.RunCommand acCmdSaveRecord
End Sub

The width entry for some reason will not update on the form no matter which case I select. I have checked my form and the control name is indeed Width. The material_type, and the height will update fine, but never the width, and the saverecord command isn't being called at all, though this may be because once a case is met the subroutine ends and the saverecord is never reached, is that how it works?

I would also appreciate any constructive criticism on the formatting of the above. To me is seems that I'm missing something obvious in terms of a method of simplyfying this code.

Kyle

 

[Shadez]

the problem is probably to do with

me.height
me.width

they are form properties that change the height and width of the form. If the form is maximised then you wont see a difference


[edit]
Are height and width fields\textboxes? if so try renameing the textbox to something that is not a form property.

 

[KyleB]

Thanks, sometimes you miss the most obvious things. This partially solves my problem, thank you, I hadn't considered that conflict quite that way. I'd modified the field name thinking it might be messed up, but it didn't fix the problem, I suspect because I hadn't exited and re-opened the file. In short, this did fix the control modification problem. I still don't have the SaveRecord command working though. Do I need to move this command somewhere else to get it to execute?

Kyle

 

[boblarson]

AfterUpdate is AFTER THE RECORD IS SAVED. If you want to either save or discard, you need to accomplish this on the BeforeUpdate event.