This should work.
A simple calculation such as SerialNoField & "-" & RelatedTable::StoreTypeCode & "-" & RelatedTable::RegionCode
Should do the trick.
What have you tried exactly and how did it fail?
Well the store region and type are selected from a drop down menu with the options being pulled in from the related dev_Region and dev_Channel (the store type) tables. The codes i'm trying to use to genrate the serial number are part of the record on these tables. For example on the dev_Region table i have 2 fields one for the region name ( ex. Northern California, Southern Califronia) and the code associated with that region (ex. 01, 02 respectively for N Cali / S Cali) and i want the dealer number generated on the Shops table ( which i hv related to these 2 tables). Is there some formula im missing to select the code value if the Shop table has the value selected from the drop down menu to go back to te related table and somehow pull the associated code # with it?
I don't see where the difficulty is here.
Let's check my understanding here.
Three tables: dev_Region, dev_Channel, Shop
Somehow the three tables are related (you don't say how).
You have two drop down fields in Shop, one displays values from dev_Region and one displays values from dev_Channel.
I recommend that you set these fields up as two column value lists with column 1, the code and column 2 the name.
Let's call these two value list formatted fields: Region and Type.
I'd link my tables in relationships this way:
Shop::Region = dev_Region::code
Shop::Type = dev_Channel::code
If the value lists are short, I'd make them pop-up menus and hide column 1. If they are longer, I'd keep them as drop downs, but add the fields for column 2 from the dev_Region and dev_Channel tables to the layout, placing them next to the drop down lists.
You have another field set up as a serial number field in shop. We'll call it shopID--and I'd use this field to link to other tables and use the field we are discussing for report and label purposes only.
The calculation for DealerNumber might be:
Region & Type & ShopID
That cleared it all up! Thank you for bearing with my inexperince and helping me solve this problem