Table Relationships and Automation

[Len]

Hi folks,

Somewhat a newbie with Access - trying to do the following:

Have two tables Repairs ZipCodes

Have one form Repairs

Trying to get access to auto-enter City and State to the form when an
entered Zip Code in the for matches a ZipCode listed in the ZipCode table.

There is a One-to-Many relationship setup between the two tables. I can get
drop-down boxes to appear listing all of the Zip Codes, Cities and States
but can not get an automated entry to work with the Cities/States fields.

Any assistance would be gratefully accepted...

Len

 

[David Straker]

Hi Len,
Your combobox that shows the ZipCode needs at least three hidden columns.
The query for the RowSource of the combobox should be something like "Select
ZipCode.ZipCodeID, ZipCode.ZipCode, ZipCode.City, ZipCode.State FROM
ZipCode". Other properties for the combobox are BoundColumn = 1,
ColumnCount=4, ColumnWidths = "0,1,0,0" (to hide every column except for the
actual ZipCode from the user).

Add two text boxes ZipCodeCity and ZipCodeState. make them Locked=True,
Enabled=True, TabStop=False. Make them a different color to denote
"not-updateable" to the user.

Then you need to add event procedures in VB for the combobox.
________________________________
Private Sub ZipCode_AfterUpdate()
' Catch the user changing the ZipCode
DisplayCityAndState
End Sub
_________________________________
Private Sub DisplayCityAndState()
' Show the matching City and State from the combobox hidden columns
Me.ZipCodeCity = Me.ZipCode.Column(3)
Me.ZipCodeState = Me.ZipCode.Column(4)
End Sub
__________________________________
Private Sub ZipCode_KeyUp(KeyCode As Integer, Shift As Integer)
' Catch the user hitting the cancel key inside the combobox
' Reset the City and State
Const constKeyCodeForEscape = 27
If KeyCode = vbKeyCancel _
Or KeyCode = constKeyCodeForEscape Then
DisplayCityAndState
End If
End Sub


hth,
David Straker

 

[Len]

Hi David,

This is a very crestfallen Len first thanking you for your response and
assistance but stating also that I'm not sure how to utilize your excellent
advice.

I seem to remember being able to do simular back in my days with Access
version 2 - haven't done anything with the program since then! We are now
using Access 2003 version 11 - and what seemed to be easy back in '96~'97
seems really confusing!

What I have is two tables: Repairs and Zipcodes. Also have one form:
Repairs. All I need to have happen is that when a user enters a ZipCode in
the form access matches it with a Zipcode form the ZipCode Table and enters
the City and State.

I don't need or want drop-down menus (which is the only thing that access
has allowed me so far. My understanding was that the software was becoming
more user friendly... not quite sure of that!

Trying what you suggested gives me errors and most surely the errors are
mine in implimentation! Having said that... is there a more direct approach
to getting where I need to be or some resources to help get my sorely
outdated knowledge of access updated?

Thanks again for your response! If you have the time any additional
information/insite would be appreciated!

Len

 

[David Straker]

No sweat Len,
I've posted a sample mdb (it's Access 2000 but Access 2003 should be able to
open it) at http://www.estraker.com/ - Click on "For Developers" - you'll
see your name at the bottom with a zip file to download. I'll leave it there
for a week or so in case you don't get to it right away.

I may not have used the same names, column numbers etc. in this mdb that I
gave you in my last reply - don't try to match them. Be sure to look at the
Tools/Relationships from the Access Menu - I've added a State table with a
join to the Zip Codes.

There are other ways to do this but I think this is the easiest - using a
combo box.

hth,
David Straker