Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Location
    Nashville
    Posts
    52

    Unanswered: Auto populate with a value?

    Hello eveyone!

    This is my first time posting. Please bear with me.

    I have two tables in a Microsoft Access 2000 database.

    Instructor_Information_Table
    Holds personal information about Instructors, such as, Name, address, city, Zip, county code, county name and region.

    TN_Information_Table
    Holds information for lookup. Zip, county code, county name and region. This table is for the state of Tennessee only.

    I have a data entry form setup.
    In the zip code text box, I wrote code in ON EXIT that allows the data entry person to enter the zip code and the city, county code, county name and region auto populates.

    Private Sub Zip_Exit(Cancel As Integer)

    'Auto populates the city based on the zip code
    Dim varCity As Variant
    varCity = DLookup("city_name", "TN_Information_Table", "Zip = '" & [Zip] & "'")
    If (Not IsNull(varCity)) Then Me![City] = (varCity)

    'Auto populates the county name based on the zip code
    Dim varCounty_Name As Variant
    varCounty_Name = DLookup("county_name", "TN_Information_Table", "Zip = '" & [Zip] & "'")
    If (Not IsNull(varCounty_Name)) Then Me![County_Name] = (varCounty_Name)

    'Auto populates the county code based on the zip code
    Dim varCounty_Code As Variant
    varCounty_Code = DLookup("county_code", "TN_Information_Table", "Zip = '" & [Zip] & "'")
    If (Not IsNull(varCounty_Code)) Then Me![County_Code] = (varCounty_Code)

    'Auto populates the region based on the zip code
    Dim varRegion As Variant
    varRegion = DLookup("region_code", "TN_Information_Table", "Zip = '" & [Zip] & "'")
    If (Not IsNull(varRegion)) Then Me![Region] = (varRegion)



    End Sub

    This works perfect. However, we sometimes have Instructors who live out of state.

    I would like to add code that will automatically fill in values for county code, county name and region if the zip code entered does not match the zip codes in the TN_Information_Table.

    County Code = 96
    County Name = Out of State
    Region = 0

    Any suggestion? If so, can I enter it in the same sub procedure as the code above?

    Thanks for your help!

  2. #2
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    Code:
    Dim varCity As Variant
    Dim varCounty_Name As Variant
    Dim varCounty_Code As Variant
    Dim varRegion As Variant
    Dim strConnection As String
    Dim rst As Recordset
    
        strConnection = "SELECT * FROM [TN_Information_Table] WHERE Zip = '" & [Zip] & "'"
        Set rst = CurrentDb.OpenRecordset(strConnection)
        If rst.BOF = True And rst.EOF = True Then
            Me![City] = ""
            Me![Country_Name] = "Out of Country"
            Me![Country_Code] = 96
            Me![Region_Code] = 0
        Else
            With rst
                If Not IsNull(![City]) Then Me![City] = ![City]
                If Not IsNull(![Country_Name]) Then Me![Country_Name] = ![Country_Name]
                If Not IsNull(![Country_Code]) Then Me![Country_Code] = ![Country_Code]
                If Not IsNull(![Region_Code]) Then Me![Region_Code] = ![Region_Code]
            End With
        End If
        rst.Close
    Last edited by Saila; 04-21-04 at 14:31.

  3. #3
    Join Date
    Feb 2004
    Posts
    142
    You could use a DCount on the zip and if 0 is returned then take an alternative action to populate

    or conversly you could open a recordset and use .recordcount = 0 to do the test and for counts > 0 you have a recordset with the fields you need to populate the other fields to avoid the overhead of all those variants and dlookups.
    KC

  4. #4
    Join Date
    Apr 2004
    Location
    Nashville
    Posts
    52
    Hello Salia,

    Thanks for the code. However, I get a Run-Time error 13, Type Mismatch when I test it.
    I'm really not skilled enough to recognize the problem but it highlight the code Set rst = CurrentDb.OpenRecordset(strConnection) ???

  5. #5
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    In the visual basic windows, goto Tools-->References. Add a reference to Microsoft DAO 3.6 Object library.
    change the line from:
    Dim rst As Recordset
    to:
    Dim rst As DAO.Recordset
    If you don't have DAO 3.6, it can be downloaded here:

    http://www.datagrip.com/Html/distribution.htm

    Also check that the table name is correct, and that the field is correct. Let me know if this doesn't help.

Posting Permissions

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