Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Location
    Glendale, California, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Field validation to external table (Access 2003)

    I have a table DRB Exemptions that consists of a date, address info, notes, and a person's initials. I've set up the table to record the street address information into discrete fields--street number, street direction, street name, street type and unit number. I also have a separate Street Names table. In this table I've separated the information into street name and street type (among other fields.) The DRB Exemptions table resides in a folder on my Y: network drive. The Street Names table resides in a folder on my R: network drive. In order to reduce data entry errors, I'd like to have the SitusName field in DRB Exemptions validate against the StrName field in the Street Names table. There are several hundred street names, so I don't want to present the user with a pick-list of names. I want him to type a name, then hit enter or Tab, then have Access issue a warning if the name is either typed incorrectly or was not found. I don't seem to be able to figure this out. I tried using this expression:

    DLookUp("[StrName]","[Street Names]")

    in the Validation Rule property for the SitusName field in DRB Exemptions, but I got an error message and it didn't work. I've looked at my Access 2000 manual, I've searched the Access 2003 online help, I've searched this forum and don't see an answer. The two tables are zipped together in the attached file. Hopefully someone can help me.

    Jeff

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

    Re: Field validation to external table (Access 2003)

    You should create a link to the Street Names Listing table in your DRB Exemptions database (File | Get External Data | Link Tables...)

    Instead of separate SitusName and SitusType fields for the address in the DRB Exemptions Log table, I would use a single number field that links to the ID (or IDNum) field in the Street Names Listing table.
    I would use a combo box bound to this number field for data entry, with the Street Names Listing table (or a query based on it) as Row Source, with the ID column hidden (by setting its column width to 0). The user doesn't have to drop down the list, (s)he can start typing the street name and Access will automatically complete it if possible, and display an error message if the user enters a street name that is not in the list.

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Location
    Glendale, California, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field validation to external table (Access 2003)

    Thank you very much for taking the time to reply to my post. Unfortunately, I'm not experienced enough with Access to understand your proposed solution.

    I linked to the Street Names table. I created a field in DRB Exemptions of the same type (Number, Float) as the IDNum field in Street Names, then I created a query linking the tables based on the common field, but the query produced no results when I tried to display all the DRB Exemptions records. Probably because the StrNum field in DRB Exemptions that I added to the table has no values in it. Do I need to populate that field in all the existing records so that there's a valid relationship between the two tables?

    I tried to create a combo box bound to the number field, but I can't figure out how to bind it to the number field while simultaneously setting the Row Source to the Street Names table.

    If this would all take too much time and trouble to explain, I understand. I'll bug my IS staff. I was hoping this was easy enough for me to do.

    Jeff

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

    Re: Field validation to external table (Access 2003)

    See the attached database. You'll have to relink the Street Names Listing table.

    I did the following:
    - Created a make-table query qryMakeTypes that makes a table tblTypes with the abbreviations used in SitusType in the DRB Exemption Log table.
    - Made SitusType the primary key.
    - Added a text field LongType to this table.
    - Populated it with the full names (Street etc.)
    - Added a number field SitusID to the DRB Exemption Log table.
    - Created a query qryDRBExemptions based on DRB Exemption Log and tblTypes with a calculated field S that returns the street name in the form used in the Street Names Listing table.
    - Created a query qryMissing that lists the street names in DRB Exemption Log that don't occur in Street Names Listing.
    - Created an update query qryUpdate that fills in the new SitusID field in DRB Exemption Log where available.
    - Created a query qryStreets based on Street Names Listing to act as row source for the combo box.
    - Replaced the SitusName and SitusType text boxes on the data entry form with a combo box bound to SitusID, with qryStreets as Row Source.

  5. #5
    New Lounger
    Join Date
    Feb 2004
    Location
    Glendale, California, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field validation to external table (Access 2003)

    Thanks very much for taking the time to help a rookie.

    Jeff

  6. #6
    New Lounger
    Join Date
    Feb 2004
    Location
    Glendale, California, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field validation to external table (Access 2003)

    I've been examining what you sent and have a few questions. 1) When I enter a new record using the data entry form you modifed (such as 1234 Broadway, dated 12/29/2006) it appears to work properly, but when I open the table DRB Exemptions, the street information is not stored in the table. Yet when re-open the form, the street name information displays as I entered it. How does that magic happen? 2) You stated that qryUpdate "fills in the new SitusID field in DRB Exemption Log where available", but when I examine the query, it appears to only update the SitusID field in qryDRBExemptions. Am I missing something? 3) When I run qryMissing, I see a listing of several streets that presumably don't appear in Street Names. Yet when I look at the Street Names table, those street names seem to be there correctly. Any idea what's happening?

    Thanks very much again.

    Jeff

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

    Re: Field validation to external table (Access 2003)

    1) Only the SitusID is stored in the table, not the separate SitusName and SitusType information. You don't need these two fields any more, since that information is held in the Street Names Listing table.

    2) The qryUpdate query only needs to be run once. It updates the SitusID field in qryDRBExemptions, but since that field comes from the DRB Exemption Log, it actually updates that table.

    3) This is partly my fault. I entered "Circus" instead of "Circle" as LongType for "Circ." in tblTypes, and "Boulevard" instead of "Blvd" for "Blvd.". The rest are real discrepancies:

    Dryden Avenue vs Dryden Street
    Foothill Blvd vs Foothill Bl
    Frances Avenue vs Frances Court
    Glenoaks Blvd vs Glenoaks Bl
    Greenwood Drive (no match)
    J Lee Circle vs J.Lee Circle
    Maryland Avenue vs Maryland Avenue - Glen and Maryland Avenue - La Cres
    Raymond Avenue vs Raymond Avenue - Glen and Raymond Avenue - La Cres
    Verdguo Loma Drive vs Verdugo Loma Drive
    Vista Court vs Vista Court - Glen and Vista Court - La Cres

    Computers are unforgiving and stupid! They don't handle those differences automatically.

  8. #8
    New Lounger
    Join Date
    Feb 2004
    Location
    Glendale, California, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field validation to external table (Access 2003)

    Thanks very much for the explanation. I've really learned lots.

    Jeff

Posting Permissions

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