Unanswered: Multiple Forms updating/adding same record
I am trying to fix a previous developers creation. Here is what I am working with: (Access 2003) First table is a "Supplier Table" which only contains data about the supplier and contact info (supplier name-unique field). The next table is the "Business Unit Table (B.U.)" which contain multiple occurances of transactions, relationships is set as (Supplier "one" to Business Unit "many"). The amount of data that is collect in the Business Unit Table is expansive and requires 4 forms to group and neatly organize for entry/viewing which are driven from the Supplier form. I have the 4 (B.U.) form's data source useing this giant table but when the forms are used, it build records instead of updating the original record started by the first form opened/populated. (These 4 forms need to be available for users to randomly select which one to populate first.) Should I split the table in 4 sections, use an update query, or setup a tabbed form? Or is there a simpliar method. All suggestions are welcomed!
If you've four different single record data entry forms with the same RecordSource, which just display different fields, then tab control can be used to group the data fields.
If each tab page represented a different record "type" (within the same table) then use the Form_Open (if launch from a display form) or Form_Current (if displayed as a subForm) to determine which type the record is and set the tab control value to the desired page.
This should get round having to maintain four seperate forms for data entry.
If you need a subform displaying multiple business unit records on the supplier form you could create queries based on the four types of business unit record and change the subform RecordSource. It's up to you if you allow recordset changes through this (implement jet security and make them read only for all users if you don't), or another single record subform (synched to the selected record on the subform using the original subform OnCurrent event) on the same supplier (parent) form This will work well if only one type of business unit record is associated with one supplier.
When you've got all types of business unit records associated with the supplier, if you display them in 4 different subforms you've grouped them - not useful if you need transactions displayed sequentially. Then you're going to have to decide which fields all business unit records have in common (to display them in one list) and display all other data (and possibly the common fields) in a synched subform. Make the list subForm dataset read only to avoid possible read/write conflicts.
It's the last scenario that would make me want to break the B.U.T. into a parent descriptive table and 4 child tables. The parent child relationship would be one to one; there would be common fields in the parent record + a summary field (to concatonate essential details of child record), and records in the other tables would be "properties" of the original B.U.T. records.