You can do an XLOOKUP into the same database rather than having a separate database. For example suppose you have a NAMES database with normal name, address, phone number info. No need for a separate ZipCode database.
You can do
City=@XLU(@FN,ZipCode,"frmName!ZipCode","City") to fill in the City automatically from the Zip code value. Do the same thing for the State and Area Code. If the Zip Code does not exist, then you can ThrowFocus to the City code and fill it in. Then save the record. the next name you enter that zipcode, the City will then be filled in automatically for all subsequent entries.
Could look something like this
untested code.
Quote://Prevent overwrite of existing values
If NOT @ISBLANK(City) Then {
ReadOnly(City,1)
ReadOnly(State,1)
ReadOnly(ZipCode,1) }
If @ISBLANK(City) Then {
City=@XLU(@FN,ZipCode,"frmName!ZipCode","City"
If @Error Then {
@MsgBox("This Zip Code was not found.","Please enter information manually","")
ThowFocus(City) }
Else {
State=@XLU(@FN,ZipCode,"frmName!ZipCode","State")
ZipCode=@XLU(@FN,ZipCode,"frmName!ZipCode","AreaCode")
ReadOnly(City,1)
ReadOnly(State,1)
ReadOnly(ZipCode,1)
ThrowFocus(Phone) }
}
This will result in a self building internal ZipCode database that only contains the zipcodes you actually use. Of course if you have more than one record with the same zipcode value, you need to make sure that no one changes any of the values in the fields you lookup, since the key record that is looked at will be a random selection of the multiple records with that zipcode. That is the purpose of the ReadOnly code in this sample.
The initial ReadOnly code could go into the OpenForm event.
The rest of the code could go into Exit Field event for ZipCode.
Could use ThrowFocus to move to the ZipCode field before going to the City/State/AreaCode fields, overriding the normal top to bottom, left to right tab flow.
Just trying to provide a possible example of how to do an "internal" lookup vs. creating another database.
====================
Original sample has been subsequently edited from SetFocus to ThrowFocus thanks to Erika's catch.
Additional change found by beagle: Added @ to ISBLANK to make @ISBLANK (told you it was untested).
Pages: 1
internal lookups - possible? (Read 1804 times)
