A simple Question (I think)

[laythss]

A very simple question
I have 2 tables, a
tblClient:
ClientID(autoNumber)
ClientName
ClientCity
ClientState
ClientZip

and
tblAddress:
Zip
City
State

so now I made a form and I am trying to understand if it is possible without writing VB to be able to do the following:
1- input the client name, and
2- inputting the zip code and then the city and state boxes get updated automatically. (I was able to do this part on it's own, using the search for record wizard of a combobox).

But I want all the values that are produced in point2 to be added to the tblClient eventhough they come from tblAddress.

How can I achieve this?

Layth

 

[Newman]

I don't understand the need for that second table. If you need to find all the addresses all you'd have to do is a query that gets all the addresses from tblClients. If you have a very good reason to do so, you should explain it to us so that we could understand the meaning of it.

Also, I suggest that you split the ClientName in two: ClientFirstName and ClientLastName. You might not need it right now, but it might help you to be able to get them sorted by last name or by first name latter on.

 

[laythss]

Allright, I might have not explained myself clearly in the first post. My second table which is tblAddress is just a list of all zip codes and the cities and states they point to. It's main reason would be to lookup (automate) the form when a user enters a zip code. So when the user enters a zipcode the city and state would be populated into the city and state fields.
so my form would have the following:
ClientName(textbox)
ZipCode(Number)
State(text,Locked)
City(text,Locked)

so I am not sure what to bound or raw source each field to, to be able to do what I want to do.
I am attaching a file to show you what I am trying to do, and the Form I am having trouble with is the 'Enter Client Information' form

Layth

 

[Pat Hartman]

If you are going to keep the address table up to date, including updating zip codes in the client table when the Post Office splits an area, you don't need to store the city and state in the client record. You only need to keep the zip. The problem with this is you could end up in a pickle if your zip file ever gets the least bit outdated. An alternative is to use the zip table the way it seems that you are trying to use it - as a tool to save typing. So you have all three fields in the client table even though under some circumstances, you would really only need the zip code.

Populate the RowSource for the zip code with a query that selects all three column and orders by zip code. In the zipcode's AfterUpdate event, fill the client city and state fields with code similar to:

Me.ClientCity = Me.cboZip.Column(1) ' city is the second field in the RowSource
Me.ClientState = Me.cboZip.Column(2) ' state is the third field in the RowSource

Since Zip is the bound field, it is automatically populated. To accomidate additional zips if your table isn't up to the minute, you should still set the limit to list property to yes, but you should allow the user to enter a complete new address record if he wants to add a new zip code.