Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2010
    Posts
    4

    Unanswered: How to make one field on a form to automatically fill in another

    I have spent the entire day trying to figure out how I can make it so that when i fill in the zip code field on my form, another field automatically fills in whether or not this zip code is in a certain area using a yes/no field on the form and in my corresponding table indicating whether or not its in the area.

    I have a list of zip codes that are in that area, and i have been trying to use if, then expressions to make it so that if zip code = this or this or this zip code, then Within Area = -1, 0. i've tried it using "yes", "no" also.

    IIf([Agencies]![Zip Code]=10451 Or [Agencies]![Zip Code]=10452 Or [Agencies]![Zip Code]=10453 Or [Agencies]![Zip Code]=10454 Or [Agencies]![Zip Code]=10455 Or [Agencies]![Zip Code]=10456 Or [Agencies]![Zip Code]=10457 Or [Agencies]![Zip Code]=10459 Or [Agencies]![Zip Code]=10460 Or [Agencies]![Zip Code]=10474 Or [Agencies]![Zip Code]=10026 Or [Agencies]![Zip Code]=10027 Or [Agencies]![Zip Code]=10029 Or [Agencies]![Zip Code]=10030 Or [Agencies]![Zip Code]=10035 Or [Agencies]![Zip Code]=10037 Or [Agencies]![Zip Code]=10039 Or [Agencies]![Zip Code]=11206 Or [Agencies]![Zip Code]=11207 Or [Agencies]![Zip Code]=11208 Or [Agencies]![Zip Code]=11212 Or [Agencies]![Zip Code]=11216 Or [Agencies]![Zip Code]=11221 Or [Agencies]![Zip Code]=11233 Or [Agencies]![Zip Code]=11237 Or [Agencies]![Zip Code]=11205 Or [Agencies]![Zip Code]=11213,[Agencies]![DPHO]-1,0)

    is the expression i've been using. and it seems to work occasionally when i use it in a query, but i cannot figure out how to make it so everything links up as i explained today. i tried putting it as the control source mainly.

    is there an easier way to do this? what do i put it as the control source for? how do i make it so that i can use an expression like this as the control source, and also have it update in my table when i enter new data in my form?

    if anyone could help me i would be soooooooooooo grateful. thanks.

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    You can use the Select Statement in the After Update Event of the Zip code field e.g.

    Select case me![Zip Code]

    Case 10451,10452, etc
    Me![DPHO]=True

    Case Else
    Me![DPHO]=False
    End Select

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    unless you 'know' that this agency zip code logic is never ever going to change I think you should redesign this such that the relevant data is pulled in from another table. that way round any changes in zip codes are handled by user changes to the data rather than an application change
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jul 2010
    Posts
    4
    Hey Poppa smurf, I tried using this expression:

    =(Select case me![Zip Code]

    Case 10451,10452,10453,10454,10455,10456,10457,10459,10 460,10474,10026,10027,10029,10030,10035,10037,1003 9,11206,11207,11208,11212,11216,11221,11233,11237, 11205,11213
    Me![DPHO]=True

    Case Else
    Me![DPHO]=False
    End Select)

    I put the expression above into the expression builder box of the After Update property of zip code. Should it be the DPHO in the table or the form? I put it as the one in the form.

    when I opened it in form mode and entered new data into the zip code field on my form, I get the following error:

    The expression After Update you entered as the event property setting produced the following error: No current record.
    *The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]
    *There may have been an error evaluating the function, event, or macro.


    Is my expression wrong? Did I put it in the wrong place? Should it be a macro instead of an expression? If you have any insight into why it did not work, that would be great. thank you so much for the help, this is now my second day working on this one problem!

    And healdem, the zip codes i enter into my form will chance, but the zip codes that define whether or not it is in the area will always be the same.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no the code may well be valid
    the problem is you have placed it in the after update event, which is fired after the row is updated. I'm guessing you want to place that code in the before update event
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Healdem's earlier message regarding placing the zipcodes in a table is more efficient that using the hard code method. Using a table you make changes quickly whereas using the coding method you have to chnage the code and test and also with al arge number of zipcodes conditions you may miss a zipcode when making the changes.

    I have attached a sample database that uses a table as the source for a combo box see form frm_zipcodes. When you select the zip code the Within the area Yes/no box is updated.

    It uses the following code
    Private Sub zipcodes_AfterUpdate()
    Me!dpho = Me!zipcodes.Column(1)
    End Sub

    me!zipcodes is a combo box with two fields Zipcode as the first field and Within_area as the second field. The Within_area field is not displayed but is used to populate the Yes/No field by settting the code to column(1) of the combox box. For coding purposes the first field of the combo box starts at 0
    Attached Files Attached Files

  7. #7
    Join Date
    Jul 2010
    Posts
    4
    using the expression

    Me!DPHO = Me!Zip.Column(1) in the after update box in the properties for my zip code works in terms of getting my DPHO checkbox to know whether or not it is within the area.

    After I did this, my Street field comes up with an error every time I enter data into it. It says you cannot add a null value to a primary or indexed field. I checked many times, and it is the right type of data I am entering. I did some experiments and found out that this error only comes up after I put the expression above in the zip code field. For some reason doing that action is causing me to have problems with the street field.

    The way my table is set up, I have several fields that together work as the primary field in order to make unique records. An Autonumber would not work for what I need to do, which is a long story. I am assuming that because my zip code field pulls down values from my DPHO Area table and not my original table, even though it saves in my original table, that this is the problem. How can I fix this??

  8. #8
    Join Date
    Jul 2010
    Posts
    4
    I figured it out using conditions in the macro builder. Took a long time, but it is done with. Thank you both of you for all of your help. I really appreciate it.

Posting Permissions

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