I'm using an ODBC connection to a PSQL database to link these tables into Access. When linking...
By mschorr75
Hi,I'm nearing completion of an Access 2000 program that will be sold over the web, and could use so...
By glynn
...help me build one or download a template. i've found hundreds of books,video types but many n...
By robbie
I got a drop-down list on my form that lists table: Health_Production,Life_Production and Health_Act...
By bentonviaaccessmonster_com
My situtation:I'm not very good at Access. I've created a report (R_SalesHist) listing 1000 customer...
By mehrhardt, 4 Comments
I want to run the following batch file from Scheduler each night from acommand line:'m:\dpc data\cre...
By bobdarlington, 5 Comments
Using Access 97, linking 10 tables on a server accessed using a remotemicrowave link 5 klms away is ...
By msnews, 1 Comments
It is possible to run a batch file from Access? If so howwould I go about doing so? Additionally, ca...
By bob, 3 Comments
I would like to be able to type a zip code into a form, and have itautomatically return the City and...
By scottwhetsell_a_s_wvsp, 8 Comments
Linda
So, you're saying that if there's only one "hit", you'll take it, but if
there's more than one, you need to see a list so you can pick the correct
one?
Another way to approach this would be to use a combo box that returns unique
combinations of zip + City. That way, you could pick the zip (& City & ...)
that you need.
Regards
Jeff Boyce
<Office/Access MVP>
"Linda in Iowa" <tandembent...mchsi.com> wrote in message
news:N4qlf.383953$084.196244...attbi_s22...
>A few days ago someone gave me some code so I can enter the zipcode and it
>will automatically fill in the city and state. It works great, but I
>didn't realize a zipcode can have more than one city. So even though I can
>select the correct zip/city it will only enter the first city if more than
>one does exist. How do I select the correct zip and city and have it enter
>the correct city?
> An example is zip 50322 and it is associated with Des Moines, Windsor
> Heights, and Urbandale. It will only enter Des Moines in the City field
> even though I select one of the others from the drop down list.
> code is on the zip field:
> Private Sub ZIP_AfterUpdate()
> Dim strSql As String
> Dim rs As DAO.Recordset
> If IsNull(Me.ZIP) Then
> Me.City = Null
> Me.State = Null
> Else
> strSql = "select City, state from tblzipcitystate where zip = """ &
> Me.ZIP & """;"
> Set rs = DBEngine(0)(0).OpenRecordset(strSql)
> If rs.RecordCount > 0 Then
> Me.City = rs!City
> Me.State = rs!State
> End If
> rs.Close
> End If
> Set rs = Nothing
> End Sub
> Thanks
> Linda
>
jeffboyce | Fri, 06 Jun 2008 12:59:00 GMT |
On the form it is a combo box and I can see which city I want, so even
though I select zip 50322 with urbandale or windsor heights I still get Des
Moines in the test box for city.
"Jeff Boyce" <nonsense...nonsense.com> wrote in message
news:OUJXLgs%23FHA.532...TK2MSFTNGP15.phx.gbl...
Linda
So, you're saying that if there's only one "hit", you'll take it, but if
there's more than one, you need to see a list so you can pick the correct
one?
Another way to approach this would be to use a combo box that returns unique
combinations of zip + City. That way, you could pick the zip (& City & ...)
that you need.
Regards
Jeff Boyce
<Office/Access MVP>
"Linda in Iowa" <tandembent...mchsi.com> wrote in message
news:N4qlf.383953$084.196244...attbi_s22...
>A few days ago someone gave me some code so I can enter the zipcode and it
>will automatically fill in the city and state. It works great, but I
>didn't realize a zipcode can have more than one city. So even though I can
>select the correct zip/city it will only enter the first city if more than
>one does exist. How do I select the correct zip and city and have it enter
>the correct city?
> An example is zip 50322 and it is associated with Des Moines, Windsor
> Heights, and Urbandale. It will only enter Des Moines in the City field
> even though I select one of the others from the drop down list.
> code is on the zip field:
> Private Sub ZIP_AfterUpdate()
> Dim strSql As String
> Dim rs As DAO.Recordset
> If IsNull(Me.ZIP) Then
> Me.City = Null
> Me.State = Null
> Else
> strSql = "select City, state from tblzipcitystate where zip = """ &
> Me.ZIP & """;"
> Set rs = DBEngine(0)(0).OpenRecordset(strSql)
> If rs.RecordCount > 0 Then
> Me.City = rs!City
> Me.State = rs!State
> End If
> rs.Close
> End If
> Set rs = Nothing
> End Sub
> Thanks
> Linda
>
lindainiowa | Fri, 06 Jun 2008 13:00:00 GMT |
Linda
What code are you using in the combo box's AfterUpdate event? If it is the
code you posted originally, that only finds the first instance.
Instead, your AfterUpdate event could include something like:
Me!txtCity = Me!cboZipCode.Column(1)
Me!txtState = Me!cboZipCode.Column(2)
Read over the syntax HELP on the .Column() property, as it is zero-based and
can be confusing.
Regards
Jeff Boyce
<Office/Access MVP>
"Linda in Iowa" <tandembent...mchsi.com> wrote in message
news:N5slf.602234$x96.134882...attbi_s72...
> On the form it is a combo box and I can see which city I want, so even
> though I select zip 50322 with urbandale or windsor heights I still get
> Des Moines in the test box for city.
> "Jeff Boyce" <nonsense...nonsense.com> wrote in message
> news:OUJXLgs%23FHA.532...TK2MSFTNGP15.phx.gbl...
> Linda
> So, you're saying that if there's only one "hit", you'll take it, but if
> there's more than one, you need to see a list so you can pick the correct
> one?
> Another way to approach this would be to use a combo box that returns
> unique
> combinations of zip + City. That way, you could pick the zip (& City &
> ...)
> that you need.
> Regards
> Jeff Boyce
> <Office/Access MVP>
> "Linda in Iowa" <tandembent...mchsi.com> wrote in message
> news:N4qlf.383953$084.196244...attbi_s22...
>
jeffboyce | Fri, 06 Jun 2008 13:01:00 GMT |