Fill field from partial info...

[Bonnie]

Hi there. Using A02 on XP. I couldn't figure out how to
describe this one. I have a mainform where we key in
checks received and assign them for processing. When you
key in the contract number a subform is peopled with the
client name/info and who is the tech (ABC, JGS, CZS, etc)
so you can verify the client name and address. On the
mainform, you key in the [DepAmt], [CkNum], [GiveTo], etc.
The [GiveTo] field is a list box with senior techs and
locations (ABC-SL1, JGS-SL2, CZS-FEE, etc.) I have a field
on my main form 'reading' the tech initials but cannot
figure out how to get the field [GiveTo] to update to
match the initials since the data is not an exact match.
Just an idea to keep the keystrokes to a minimum.

Thanks in advance for any help or advice!!! LUV U GUYS!

 

[Ken Ismert]

Bonnie,

Thanks for the luv. The problem with initials is they can change over
time, and two people can share the same initials. So, to truly fix
your form, you will likely have to revisit the data that drives it. I
suggest two tables, similar to:

tblTechs
======
TechID - Autonumber
TechName - Text
TechInitials - Text
LocationID - Long - key to tblLocations

tblLocations
=========
LocationID - Autonumber
LocationName - Text
LocationInitials - Text

In the Relationships window, make a relationship between tblLocations
and tblTechs on LocationID.

Your list box will then have a query that joins tblLocations and
tblTechs, showing the initials for each. What your listbox will be
store (in the bound column) is the TechID, which captures both the Tech
and, by relationship, the tech's Location.

While more time to set up, this approach will allow your form to
capture consistent, unambigous data.

-Ken