VBA BeforeUpdate Help (1 Viewer)

SirMegaton · Dec 13, 2015

Hello, I'm having trouble understanding how to autocomplete something using VBA Form_BeforeUpdate.

This is a practice exam I took a few days ago, and I still don't understand this piece. The example my Professor gave is to pretend that I was told that the Zip field for Meriden have changed from 10050 to 10050-0050. So I want to create a function procedure so that if I type in Zip 10050 that the Form File I'm using will automatically update that record to 10050-0050, and that the underlying table will be updated when form records are updated as well.

The book I am using for this course only shows one example with BeforeUpdate, and it doesn't relate to this.

This is how I thought it should be but it doesn't automatically change the Zip 10050 to 10050-0050.
Code:

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim Zip As String
    If Not IsNull([City]) And Not IsNull([Zip]) Then
        Zip = Left([Zip], 5)
        Select Case [City]
            Case "Meriden"
                If Zip = "10050" Then
                    Zip = "10050-0050"
                End If
        End Select
    End If
End Sub

Anything to help understand this would be great. Thanks.

pbaldy · Dec 13, 2015

Well, that's probably overly complicated, but you've made the mistake of naming your variable the same as the field. Your process may be working correctly, but setting the value of the variable rather than the data.

SirMegaton · Dec 13, 2015

pbaldy said:
Well, that's probably overly complicated, but you've made the mistake of naming your variable the same as the field. Your process may be working correctly, but setting the value of the variable rather than the data.

I changed it to this.

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim ZipFirstFive As String
    If Not IsNull([City]) And Not IsNull([Zip]) Then
        ZipFirstFive = Left([Zip], 5)
        Select Case [City]
            Case "Meriden"
                If ZipFirstFive = "10050" Then
                    ZipFirstFive = "10050-0050"
                End If
        End Select
    End If
End Sub

I checked if it worked but nothing changed. I enter 10050, but the rest doesnt come up for records with the city Meriden.

pbaldy · Dec 13, 2015

You've set the value of a variable. If you check, it very well may have changed. If you want to change the value of the field...

SirMegaton · Dec 13, 2015

pbaldy said:
You've set the value of a variable. If you check, it very well may have changed. If you want to change the value of the field...

I'm sorry but I dont understand how this code works. When Im in Form View and change the city Meriden zip field to 10050 it stays the same, when it should change to 10050-0050. Sorry if this is confusing.

pbaldy · Dec 13, 2015

Try

Me.Zip = "10050-0050"

SirMegaton · Dec 13, 2015

pbaldy said:
Try

Me.Zip = "10050-0050"

Oh my goodness, thank you. What is the purpose for Me. ? When is it best to use that?

pbaldy · Dec 14, 2015

"Me" can be used in VBA code to refer to the object containing the code (in this case the form). I always use it (or the full form reference) to disambiguate between variables, form references, etc. In other words, these refer to the same textbox when the code is in "FormName":

Forms!FormName.TextboxName

Me.TextboxName

You also get intellisense when using it (a dropdown of available options).

I think your code can be reduced to:

Code:

If Me.City = "Meriden" And Me.Zip = "10050" Then
  Me.Zip = "10050-0050"
End If

VBA BeforeUpdate Help (1 Viewer)

SirMegaton

New member

pbaldy

Wino Moderator

SirMegaton

New member

pbaldy

Wino Moderator

SirMegaton

New member

pbaldy

Wino Moderator

SirMegaton

New member

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread