Sunday, November 21, 2010

DAO VBA: Auto fill customer address within order form

I created a customer and inventory management database for a small business. One function is a product order form. A ContactID(Customer) is chosen from a drop-down menu for each order. After the ContactID is filled, an AfterUpdate event is fired with the following VBA that automatically fills the Contact(customers) shipping address.





Private Sub ContactID_AfterUpdate()


Form.Refresh



Dim FillAddress As String

FillAddress = "Select FirstName, LastName, Address, City, State, Zip "

FillAddress = FillAddress & "FROM tblContacts "

FillAddress = FillAddress & "WHERE ContactID = " & ContactID & ""



Dim db As DAO.Database

Dim rst As DAO.Recordset



Set db = CurrentDb

Set rst = db.OpenRecordset(FillAddress)



Dim FirstName, LastName, Address, City, State, Zip



FirstName = rst![FirstName]

LastName = rst![LastName]

Address = rst![Address]

City = rst![City]

State = rst![State]

Zip = rst![Zip]



If Me.ContactID Then

Me.ShipName.Value = FirstName & " " & LastName

Me.ShipAddress.Value = Address

Me.ShipCity.Value = City

Me.ShipState.Value = State

Me.ShipZip.Value = Zip



End If

End Sub

1 comment:

  1. I hope this works for me when I go to make changes. However, I had to comment and give you kudos for writing the most straightforward and easy to follow vba example of auto populating a form when the user updates a particular field. I have spent 24 hours trying to find a simple straightforward example. Thank you very much.

    ReplyDelete