input form- Many tables

[mike02]

Hey, I have searched through many forums and i cant figure out how to do this. I have tried doing it as told in similar threads but I have to many tables.
Tables
Address Information PK-CompanyID
Manager Section PK-DivisionID, FK-CompanyID
Service Providers PK-serviceID, FK-CompanyID, FK-DivisionID
Decision PK-DecisionID, FK-CompanyID, FK-DivisionID
Supporting Documents PK-DocumentID, FK-CompanyID, FK-DivisionID
Products PK-ProductID, FK-CompanyID, FK-DivisionID

All relationships are one-to-many. I have tried using subforms etc. and linking child and master fields, however im not having much luck. The first thing that needs to be done is insert Company Name, (AutoNumber for the ID) and then insert Division ID(AutoNumber As Well). Once these to fields are updated, I need it to automatically input the CompanyID, and Division ID into other tables. This is where I am getting stuck. any responses are greatly appreciated. thanks!

[orange]

Do you have a data model or have you created Tables and Relationships? Perhaps you could show us a jpg of same so we can understand your set up.

[mike02]

I have created tables, and a relationships. I currently have the form linked to Address INformation and Manager Section. I then have tabs set up with the remaining 5 tables set up as subforms. I am thinking the easiest way to do this would be to set up an expression afterupdate, to the form, Company ID, and Division ID. now the ID corresponds with a text name. Ideally, This Input form would allow me to enter the company Name, and the Division Name, this would then update Address Information Table, and the Manager Section. I then need the corresponding ID, to update into the other 5 tables automatically. once the update is complete. I will be sifting through the tab controls to input the information in the corresponding fields. Once done, I save the record and everything is updated.

With my current setup up I cant get the 5 tables, to update with the new Company ID and Division ID #s. the 5 tables in subforms on the input form all have the same master Child Relation Ship,
Master: Address_Information.CompanyID;Manager_Section.Divi sionID
Child: CompanyID;DivisionID

[mike02]

Attachment 12122 I adjusted some tables and relationships, but here is an example of what I am doing. Thanks,

[mike02]

still not having anyluck. thanks,

[June7]

Where is Division ID? Not seeing table or field for Division.

The subform RecordSource queries don't need the WHERE clause. The subform container control Master/Child links properties will synchronize the related records. These RecordSources don't even have to be queries, can be just the tables.

Trying to do data entry and get odd error:
The object doesn't contain the Automation object 'Service_Providers.'
You tried to run a Visual Basic procedure to set a property or method for an object ...
Bizarre because there is only one VBA procedure and get the error even after removing.

I changed all the RecordSource properties to just the tables and reset all the subform Master/Child links properties then data entry works.

Recommend no spaces in field names.

I find setting the form as Popup and disabling the shortcut menu on form inconvenient for during development. I don't bother with disabling shortcut menu with form property. I disable it in Project options. Then I open db by holding down shift key so the optional setting is negated. I prefer the Overlapping Windows option (shift key won't override this setting).

[ssanfu]

Well, I downloaded the example dB and found different errors.

All relationships are one-to-many.

What I found was 1 to 1 relationships. The "FundNameID" FKs were set to Indexed (NO Duplicates).
I renamed the PK/FK fields and took out the spaces in other field. I also renamed the PKs for the tables. "ID" as a field name is pretty useless & confusing.
I deleted the links and recreated them.... now they are 1 to many.

The table "Portfolio_Manager_Section" is not normalized......

You have criteria for the subforms as "Like [Forms]![Add_Fund].[Form]![txtfundid] & "*" "
1) You don't have a control named "txtfundid" that I could find
2) The criteria shouldn't be needed because of the linking of the main form to the subforms.
3) "FundNameID_FK" is a Long, so I don't think "Like ...... * " will work - probably error or not return any records. (I've never tried "Like" on a long int)

I normally have the subforms in the footer, not the detail section........

I commented out the form open code. I could not find any controls named "txtfundid" or "txtcompanyid".

I attached my modified dB in case you want to see what I did.

[mike02]

hey, thanks for the input. I have it working now. One issue that does come up, is that when I dont select a tab, to open a subform and input data, IT doesnt update the new entry into that table. How can I make it happen even If i dont select the tab? thanks again guys!

[June7]

By new entry you mean a new fund? Record is committed to table when move to another record, close form, or run code. The Save button successfully commits record to table. Don't understand issue.

[mike02]

yes, when i enter a new fund at the top of the form, i have to the click through the tabbed subforms. for it to update the new fund into the other tables i have to enter data into at least one of the fields in each of the tabbed subforms. the problem is, when adding a new fund, i wont always update it all at once and sometimes i may come back and update it later. i have built an editform for this,

[June7]

Should not be necessary to click on tab control to commit new fund. Either click the Save button or close form. Can't work with your db right now to verify but that's how form should work.