Field validation to external table (Access 2003)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Jeff

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Jeff

Reply | Quote