I'll start off by saying that if you have spaces in your field names at all, you have to put square brackets around them in your VBA code. For example, [ZipCode 4] = "1234."

What does your form look like?

I don't have spaces. The field Name for ZipCode 4 is actually ZipCode+4.
But it seems I have to put brackets because it takes out the + sign every time.

You mentioned the AddressType is a combo box on your form. Try moving your code to the After Update event for your combo box.

No, that didn't work.

If your database is setup correctly, AddressType should not be a string... it should be a number.

The RowSource for the combo box should have an ID field, then the description as you described as a second column in that field.

If this is correct, you need to find the ID for the Main Office then change your code to this:

If AddressType = # Then
        Address1 = "6478 Prentiss Dr."
        Address2 = "Suite 900"
        City = "Irvine"
        State = "CA"
        ZipCode = "90200"
        ZipCode 4 = "1234"
    End If

-Raymond

YES!!!!
Thank you Raymond!!!

Ok, quick question; looking around the Internet some people had the code like this:

If AddressType = "Agency Core Office" Then
        Me.Address1 = "6478 Prentiss Dr."
        Me.Address2 = "Suite 900"

Ignoring the first line, we now know that is incorrect; would this method work as well?
If yes; is it better?
What advantages or disadvantages does it have?

It works, but you don’t need the “Me.” keyword in front of the address field. Me is automatically assumed when writing field names in VBA.

Bang! vs. Dot: https://599cd.com/blog/display-article.asp?ID=1561

It depends on how the database and combo box are set up.

A good database should have a table for the combo box options.  Using your example, your table could look like this:

AddressTypeID    AddressType
1                       Main Office
2                       Work
3                       Home

In your combo box... you set the RecordSource to "SELECT AddressTypeID, AddressType FROM YOURTABLE"

The combo box properties will have a "Bound Column" which is the AddressTypeID, (which is a number).

=================================================================
You could have a Table that just has the AddressType (which is a string)

Then your combo RecordSource  would be "SELECT AddressType FROM YOURTABLE"

Using this method, the original code you posted would have worked, but is not he correct way to set this up your database.


-Raymond