Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    77

    Unanswered: Update field1 from field2

    Id like some assistance on the following if possible:

    I have a database where I want to update a field (Agree_ID) based on the data input in a second field (Pond_ID). Agree_ID uses only a portion of Pond_ID. Id like some routine (macro or VBA code) to assist.

    Example:
    Pond_ID keyin "05-04-1P"
    Agree_ID updates to "05-04"

    I used =Mid([Pond_ID],1,5) to update Agree_ID which appears to work. However, I have to close the record; run a macro that runs the code; then go back into the form and open the record. Id like this to happen automatically when I keyin the data in Pond_ID.

    Any ideas would be greatly appreciated.
    Thanks,
    SKK

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    In your AfterUpdate event (where you type in the pond id) put in some code to capture the Left 5 chars and assign that to the agree id ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jan 2004
    Posts
    77
    I believe that's what I did by having it run a macro the code. However, it won't update until I exit the form and go back in. I't like it won't let me update the field while the record is open (or something like that).

    SKK

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You will want to add code to the form. When you run the macro it updates the table to form is looking at, but the form won't show the change until you requery the data. That may or may not make sense, but here is what I would suggest:

    Open the form in design view, highlight the Pond_ID control, then choose Properties. From there click on the Event tab. Then choose [Event Procedure] from the After Update event. Then click on the elipses (...) to the right of the [Event Procedure]. Then between the Private line and the End line type:

    Agree_ID=Mid([Pond_ID],1,5)

    It should work. That said, in general, it is poor database design to store data that can be derived from another field. In other words, if you know that Agree_ID is always the first five characters of the Pond_ID then you don't have to store it. Whenever you print, display, etc. the Agree ID then use Mid to calculate it. That way if the way you come up with the AgreeID changes you don't have to modify every record in your table. You simply change the calculation.

Posting Permissions

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