Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Unanswered: Seting value of Text Box using SQL String

    Hi,
    I have an address form. I want to populate the City and State based on zipcode. I don't know how to set a value of a text box using sql.

    Can you help me?

  2. #2
    Join Date
    Apr 2002
    Location
    Illinois
    Posts
    133

    RE: Fill CITY and STATE fields

    This code assumes you have a table of zip codes.

    Private Sub ZIP_Exit(Cancel As Integer)
    Dim db As Database
    Dim cty As String, rst As Recordset
    Dim st As String
    Dim strSQL As String

    If IsNull(Me![ZIP]) Then
    Exit Sub
    Else
    If DCount("[Zip]", "tblZipCodes", "[Zip] = " & Me![ZIP]) = 0 Then
    MsgBox "This is not a valid Zip Code."
    Exit Sub
    Else

    Set db = CurrentDb()
    ' assign the SQL statement to retrieve city and state
    strSQL = "SELECT tblZipCodes.Zip, tblZipCodes.CITY, tblILZipCodes.STATE, " _
    & "FROM tblZipCodes Where [Zip] In(" & Me![ZIP] & _
    ");"
    Set rst = db.OpenRecordset(strSQL)
    rst.MoveFirst
    ' assign the values to fields on the form
    Me![CITY] = rst![CITY]
    Me![STATE] = rst![STATE]

    rst.Close
    Set db = Nothing
    End If
    End If
    end sub
    SteveH

  3. #3
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    Thanks

  4. #4
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Re: RE: Fill CITY and STATE fields

    Hi again,

    I am very confused. I undestod your code but I was trying to use it and faced a lot of problems. Basically I got the error "too few parameters"

    I finally solved the problem by using the following statment

    strSQL = "select * from tblzipcodes where [zipcode]like " & Me![home zip]
    Set rec = CurrentDb.OpenRecordset(strSQL)
    rec.MoveFirst
    Me![home City] = rec![City]
    Me![home state] = rec![STATE]


    Instead of using In I used Like. Do you know why? Its driving me crazy.
    Is it somthing related to DAO vs ADO and if so can you exaplain how I can know which method I'm using

    Thanks







    Originally posted by SteveH
    This code assumes you have a table of zip codes.

    Private Sub ZIP_Exit(Cancel As Integer)
    Dim db As Database
    Dim cty As String, rst As Recordset
    Dim st As String
    Dim strSQL As String

    If IsNull(Me![ZIP]) Then
    Exit Sub
    Else
    If DCount("[Zip]", "tblZipCodes", "[Zip] = " & Me![ZIP]) = 0 Then
    MsgBox "This is not a valid Zip Code."
    Exit Sub
    Else

    Set db = CurrentDb()
    ' assign the SQL statement to retrieve city and state
    strSQL = "SELECT tblZipCodes.Zip, tblZipCodes.CITY, tblILZipCodes.STATE, " _
    & "FROM tblZipCodes Where [Zip] In(" & Me![ZIP] & _
    ");"
    Set rst = db.OpenRecordset(strSQL)
    rst.MoveFirst
    ' assign the values to fields on the form
    Me![CITY] = rst![CITY]
    Me![STATE] = rst![STATE]

    rst.Close
    Set db = Nothing
    End If
    End If
    end sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •