I have a file with 44000 records consisting of zip, state, city.  And I 

have dozens of data entry name-and-address-projects.  What I want to do is 

to type in the zipcode and have access 2000 fill in the city and state.  

I've tried the lookup wizard with no success.  Can somebody walk me 

through?



Vic

Victor



Here's an Access 97 answer using DAO rather than ADO.



For the code snippet below, I'm making the following 

assumptions. You'll need to modify it to suit your puposes:



The 44000 records are held in a local table called tblAddress.



This table has three fields named fldZIP, fldCity and 

fldState.



The field fldZip is the Primary Key for this table, and has 

an index named 'PrimaryKey' built on it.



I have a form with three text boxes named txtZIP, txtCity and 

txtState.



The following code goes into the AfterUpdate event of txtZIP 

text box.



Private Sub txtZIP_AfterUpdate()

Dim myDB As Database

Dim rstLookup As Recordset



    Set myDB = CurrentDb

    Set rstLookup = myDB.OpenRecordset("tblAddress")

    

    rstLookup.Index = "PrimaryKey"

    rstLookup.Seek "=", Me![txtZIP]

    

    If Not (rstLookup.NoMatch) Then

        Me![txtCity] = rstLookup.Fields("fldCity")

        Me![txtState] = rstLookup.Fields("fldState")

    Else

        Me![txtCity] = Null

        Me![txtState] = Null

    End If

    

    rstLookup.Close

    

End Sub



This code will populate the txtCity and txtState boxes if the 

ZIP code exists in the table, and blank them out if an 

unknown ZIP code is entered.



Hope it helps



BDS

---- Original message ----

>I have a file with 44000 records consisting of zip, state, 

city.  And I 

>have dozens of data entry name-and-address-projects.  What I 

want to do is 

>to type in the zipcode and have access 2000 fill in the city 

and state.  

>I've tried the lookup wizard with no success.  Can somebody 

walk me 

>through?

>

>Vic

>---

Brian,



Thanks for the snippet.  It works like a charm using the three text boxes 

as you stated.  But what I need <g> is to be able to call the routine from 

inside a data-entry database.  So how do I have two databases/recordsets 

open one entering data and the other the lookup table?



Thanks,



Vic





> Victor

> 

> Here's an Access 97 answer using DAO rather than ADO.

> 

> For the code snippet below, I'm making the following 

> assumptions. You'll need to modify it to suit your puposes:

> 

> The 44000 records are held in a local table called tblAddress.

> 

> This table has three fields named fldZIP, fldCity and 

> fldState.

> 

> The field fldZip is the Primary Key for this table, and has 

> an index named 'PrimaryKey' built on it.

> 

> I have a form with three text boxes named txtZIP, txtCity and 

> txtState.

> 

> The following code goes into the AfterUpdate event of txtZIP 

> text box.

> 

> Private Sub txtZIP_AfterUpdate()

> Dim myDB As Database

> Dim rstLookup As Recordset

> 

>     Set myDB = CurrentDb

>     Set rstLookup = myDB.OpenRecordset("tblAddress")

>     

>     rstLookup.Index = "PrimaryKey"

>     rstLookup.Seek "=", Me![txtZIP]

>     

>     If Not (rstLookup.NoMatch) Then

>         Me![txtCity] = rstLookup.Fields("fldCity")

>         Me![txtState] = rstLookup.Fields("fldState")

>     Else

>         Me![txtCity] = Null

>         Me![txtState] = Null

>     End If

>     

>     rstLookup.Close

>     

> End Sub

> 

> This code will populate the txtCity and txtState boxes if the 

> ZIP code exists in the table, and blank them out if an 

> unknown ZIP code is entered.

> 

> Hope it helps

> 

> BDS

> ---- Original message ----

> >I have a file with 44000 records consisting of zip, state, 

> city.  And I 

> >have dozens of data entry name-and-address-projects.  What I 

> want to do is 

> >to type in the zipcode and have access 2000 fill in the city 

> and state.  

> >I've tried the lookup wizard with no success.  Can somebody 

> walk me 

> >through?

> >

> >Vic

> >---