Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Decatur, AL
    Posts
    53
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Loop thru records to check for error (Access 2000)

    I created an application that has been in successful use for several years. Until recently, records were keyed directly into the application and a particular field (the Vehicle Identification Number, or VIN) was checked for errors through an AfterUpdate event (via code). This error check is very involved and mandated by the federal code. We have since changed the overall dealership software which affords us the opportunity to import data directly from it to this application. I am NOT a trained programmer, but have had much success and experience with Access. What I've never had to learn is how to loop thru records and perform operations. Unfortunately my attempts this far have been less than fruitful.

    What I want to accomplish is to open a report that identifies all records with invalid VIN's. It would show the RunNo (a distinct number), the VIN, and one of 4 different error descriptions (text). I tried to do this with a While Not rst.EOF loop that performed a RunSQL to append the records to a temporary table. It didn't work [img]/forums/images/smilies/sad.gif[/img] Probably because I didn't do it correctly, I'm sure.

    I've attached the code that does the error check as records are added to the "AvailAutos" table, which is also the RecordSource for the form the code runs from.

    Any help will be greatly appreciated.
    Kathi in Birmingham, AL
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop thru records to check for error (Access 2000)

    You could create a function that checks the VIN, and returns True (pass) or False (fail). You could then create a query that returns only those records that fail, and use this as record source for the report.

    I have attached the database with an attempt at the function in the module basFunction, and a query qryFail.
    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    Decatur, AL
    Posts
    53
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Loop thru records to check for error (Access 2000)

    Hans,
    You are my hero!!!! Exactly what I needed. Thanks, Kathi

Posting Permissions

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