How do I write a simple invoice database in MS Access 2003?

In a form, How do I return an Address (on the same form), from a Name picked from a combo box (again on the same form). It's a small invoice database that I wrote for my small business. I made a customer table with names, address, phone number, etc. I have the invoice form, using a combo box, enabling me to pick the name from the Customer table, that is working fine. I can not figure out any way to return the rest of the Address etc. information from that name selection.
Using a List box, I can return all the Addresses, but I need to select just the one record that matches the name that I select from the combo box.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Does your invoice form's recordset include the Address info? Probably not (assuming you're storing the CustomerID with the Invoice in your Invoice table), in which case you can do this several ways:

1) Use a recordset. In the AfterUpdate event of your combo:

Dim rst AS New ADODB.Recordset
rst.Open "SELECT * FROM YourCustomerTable WHERE CustomerIDField=" & Me.YourCombo

If not (rst.EOF and rst.BOF) Then
  Me.SomeTextbox = rst("strAddress")
End If

2) Include the address info in the combo. To do this, just add columns to the .RowSource of your combo:

SELECT strName, strAddress, strCity, strState FROM SomeTable

NOw set your combo's .ColumnCount and .ColumnWidths appropriately. To "hide" a column, set the width = 0:

.ColumnCount = 5
.ColumnWidt = 1;0;0;0;0

This would show only the name.

Now in the AFterupdate event, you can refer to the .Columns of your combo:

Sub YourCombo_AfterUpdate
  Me.SomeTextbox = Me.YourCombo.Column(1)
End Sub

NOte that columns are zero based, so the first column is 0, second is 1, etc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.