Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2003
    Posts
    7

    Unanswered: How Do you Fill Fields Automatically

    Is it possible to have some fields filled in automatically as soon as a
    certain value has been entered into another field?

    I found this answer @ http://www.mvps.org/access/forms/frm0009.htm

    I tried it and I get a Run-time error '3078'
    Microsoft Jet Database Engine cannot find input table or querry
    tblZipCode. Make sure it exist and that its name is spelled correctly.

    this is the code I used with their answer to the code question

    A typical example of this is getting state and city name from Zipcodes.

    If you have a Zip-Code table, you'll never have to enter the State/City
    again. Add this simple code in the OnExit() event-handler for the field
    containing the Zipcode.

    '************* Code Start **************
    ' This code was originally written by Erika Yoxall.
    ' It is not to be altered or distributed,
    ' except as part of an application.
    ' You are free to use it in any application,
    ' provided the copyright notice is left unchanged.
    '
    ' Code Courtesy of
    ' Erika Yoxall


    Sub Zip_OnExit(Cancel As Integer)
    Dim varState, varCity As Variant
    varState = DLookup("State", "tblZipCode", "ZipCode =[Zip] ")
    varCity = DLookup("City", "tblZipCode", "ZipCode =[Zip] ")
    If (Not IsNull(varState)) Then Me![State] = varState
    If (Not IsNull(varCity)) Then Me![City] = varCity
    End Sub

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    do you have a table or query called tblZipCode containing fields State, City, and ZipCode?

    izy

  3. #3
    Join Date
    Mar 2003
    Posts
    7
    Originally posted by izyrider
    do you have a table or query called tblZipCode containing fields State, City, and ZipCode?

    izy
    Table is call Zipcode and it has ZipCode, State, City fields

  4. #4
    Join Date
    Mar 2003
    Posts
    7

    Question Re: How Do you Fill Fields Automatically

    ok this is what I have done now I change the table and added ID with autonumber and made it the primary key. so the table looks like this

    ID AutoNumber
    ZipCode text
    State text
    City text

    the form is called ZipCode and the fileds are Zipcode, State, City

    here is the code

    Private Sub ZipCode_Exit(Cancel As Integer)
    Dim varState As Variant
    Dim varCity As Variant
    varState = DLookup("State", "Zipcode", "ZipCode = " & Me.ZipCode)
    varCity = DLookup("City", "Zipcode", "ZipCode = " & Me.ZipCode)
    If (Not IsNull(varState)) Then Me![State] = varState
    If (Not IsNull(varCity)) Then Me![City] = varCity
    End Sub


    Now when I try to run this I get a Run-time error '3464'
    Data Type mismatch in criteria expression

    when you go to debug it turns this line yellow

    now what?

    varState = DLookup("State", "Zipcode", "ZipCode = " & Me.ZipCode)


    Originally posted by Soup
    Is it possible to have some fields filled in automatically as soon as a
    certain value has been entered into another field?

    I found this answer @ http://www.mvps.org/access/forms/frm0009.htm

    I tried it and I get a Run-time error '3078'
    Microsoft Jet Database Engine cannot find input table or querry
    tblZipCode. Make sure it exist and that its name is spelled correctly.

    this is the code I used with their answer to the code question

    A typical example of this is getting state and city name from Zipcodes.

    If you have a Zip-Code table, you'll never have to enter the State/City
    again. Add this simple code in the OnExit() event-handler for the field
    containing the Zipcode.

    '************* Code Start **************
    ' This code was originally written by Erika Yoxall.
    ' It is not to be altered or distributed,
    ' except as part of an application.
    ' You are free to use it in any application,
    ' provided the copyright notice is left unchanged.
    '
    ' Code Courtesy of
    ' Erika Yoxall


    Sub Zip_OnExit(Cancel As Integer)
    Dim varState, varCity As Variant
    varState = DLookup("State", "tblZipCode", "ZipCode =[Zip] ")
    varCity = DLookup("City", "tblZipCode", "ZipCode =[Zip] ")
    If (Not IsNull(varState)) Then Me![State] = varState
    If (Not IsNull(varCity)) Then Me![City] = varCity
    End Sub

  5. #5
    Join Date
    Mar 2002
    Location
    Sacramento, CA
    Posts
    120
    Data Type mismatch in criteria expression usually means you are trying to compare 2 different data types.
    Example - Trying to compare a 'number' with a 'date' field.

    I am probably off base here, but did you try:

    Dim varState As String
    Dim varCity As String



    Michael
    Last edited by mrabrams; 03-29-03 at 20:05.

  6. #6
    Join Date
    Mar 2002
    Location
    Sacramento, CA
    Posts
    120

    Here's a sample

    I have created a mini version for you.

    See attached.

    When you open the form, type in 11212 in the zip field.
    The rest fills in when you tab out.

    Go to next record.
    Type in 95841
    Tab out.

    Michael
    Attached Files Attached Files

  7. #7
    Join Date
    Mar 2003
    Posts
    7
    Thank you so much! I have been working so hard on this. This is a great web site and group!

  8. #8
    Join Date
    Mar 2003
    Posts
    7
    Once again thanks

    Fill Fields automatically on form based on a control's value

    This one works have two tables one that is called mytable and one called tblZipCode
    in the table called mytable the fields are listed below

    MYID AutoNumber
    Zip2 Text
    City2 Text
    State2 Text

    the 2nd table called tblZipCode

    ZIPID AutoNumber
    ZIPCODE Text
    CITY Text
    STATE Text

    The form has the fields from table mytable and it's called myTableForm. On Zip2 the On Exit has [Event Procedure] that follows.



    Private Sub ZIP2_Exit(Cancel As Integer)

    Dim txtState As String
    Dim txtCity As String
    txtState = DLookup("State", "tblZipCode", "ZipCode =[Zip2] ")
    txtCity = DLookup("City", "tblZipCode", "ZipCode =[Zip2] ")
    If (Not IsNull(txtState)) Then Me![STATE2] = txtState
    If (Not IsNull(txtCity)) Then Me![CITY2] = txtCity
    End Sub

  9. #9
    Join Date
    Sep 2011
    Posts
    2
    Hello;

    I benefited from this code!!! so thanks a lot!

    I have a small problem; how do I post a personalised message box for the error 94?

    Thank you,

    Patheo;

    Quote Originally Posted by Soup View Post
    Once again thanks

    Fill Fields automatically on form based on a control's value

    This one works have two tables one that is called mytable and one called tblZipCode
    in the table called mytable the fields are listed below

    MYID AutoNumber
    Zip2 Text
    City2 Text
    State2 Text

    the 2nd table called tblZipCode

    ZIPID AutoNumber
    ZIPCODE Text
    CITY Text
    STATE Text

    The form has the fields from table mytable and it's called myTableForm. On Zip2 the On Exit has [Event Procedure] that follows.



    Private Sub ZIP2_Exit(Cancel As Integer)

    Dim txtState As String
    Dim txtCity As String
    txtState = DLookup("State", "tblZipCode", "ZipCode =[Zip2] ")
    txtCity = DLookup("City", "tblZipCode", "ZipCode =[Zip2] ")
    If (Not IsNull(txtState)) Then Me![STATE2] = txtState
    If (Not IsNull(txtCity)) Then Me![CITY2] = txtCity
    End Sub

  10. #10
    Join Date
    Aug 2012
    Location
    Heredia, Costa Rica
    Posts
    4

    Lightbulb Don't let it happen :-)

    Quote Originally Posted by PATHEO View Post
    Hello;

    I benefited from this code!!! so thanks a lot!

    I have a small problem; how do I post a personalised message box for the error 94?

    Thank you,

    Patheo;
    Patheo, I know this is and old thread but, for what is worth and because it might help others, here are my two cents. I would begin by avoiding the error in the first place, instead of using a Text Box I would use a ComboBox and limit the option to the list of ZIP codes.

    Having said that here is what the Access 2003 help file has to say about error 94:
    Invalid use of Null (Error 94)

    Null is a Variant subtype used to indicate that a data item contains no valid data. This error has the following cause and solution:

    You are trying to obtain the value of a Variant variable or an expression that is Null. For example:
    MyVar = Null
    For Count = 1 To MyVar
    . . .
    Next Count

    Make sure the variable contains a valid value.

    If for some odd reason yo do not use want to use ComboBox as suggested then here is my rewrite of the code (based on the DB originally attached to this thread by mrabrams):

    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub ZIP2_Exit(Cancel As Integer)
    
    Dim txtState As String
    Dim txtCity As String
       On Error GoTo ZIP2_Exit_Error 'Error handler
    
    txtState = DLookup("State", "tblZipCode", "ZipCode =[Zip2] ")
    txtCity = DLookup("City", "tblZipCode", "ZipCode =[Zip2] ")
    If (Not IsNull(txtState)) Then Me![STATE2] = txtState
    If (Not IsNull(txtCity)) Then Me![CITY2] = txtCity
    
       On Error GoTo 0
       Exit Sub
    
    ZIP2_Exit_Error:
        If Err.Number = 94 Then 'Invalid use of null, Entered Zip Code  does not exist
            MsgBox "Invalid use of null, Entered Zip Code  does not exist in database.", vbInformation, "Done"
        Else
    
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ZIP2_Exit of VBA Document Form_myTableForm"
        End If
        
    
    End Sub
    Enjoy, I hope someone find this helpful.

  11. #11
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Since we're in an educational mode, today, we ay as well address the original errors!

    Amazingly, for Access, the Access Gnomes actually did pinpoint, accurately, the source of both errors!

    Quote Originally Posted by Access Gnomes

    Run-time error '3078' Microsoft Jet Database Engine cannot find input table or query
    Correct! The OP was using tblZipCode as the Domain Name (which he got from the MVPS.Org site) in his code, while the actual name of the Table was simply ZipCode; hence the error! This should make obvious the importance, when adapting code found on internet sites, including this forum, of replacing names used in the code with your actual names!


    Then, from Post # 4

    Quote Originally Posted by Access Gnomes

    Run-time error '3464' Data Type mismatch in criteria expression

    ...with the line

    varState = DLookup("State", "Zipcode", "ZipCode = " & Me.ZipCode)

    highlighted

    The problem here was also just as stated! The OP stated that the ZipCode Field was defined as Text, but the syntax

    Code:
    varState = DLookup("State", "Zipcode", "ZipCode = " & Me.ZipCode)

    would only be correct if ZipCode were defined as a Number Datatype!

    The line of code should have been

    Code:
    varState = DLookup("State", "Zipcode", "ZipCode = '" & Me.ZipCode & "'")

    since ZipCode, in this case, was a Text Datatype.

    As my signature says, The Devil's in the Details!

    Linq ;0)>
    Last edited by Missinglinq; 08-19-12 at 13:21.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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