Auto-Enter Serial replicated into other tables

[brisom]

If this is answered somewhere already, please let me know, thanks.

For now I have only 5 tables in my solution;

tblContactIDs, tblPersons, tblOrganizations, tblVenues, and tblContacts_Related.

The tblContactIDs table has only two fields. The first field is ID_Contact that controls the creation of assigning a serial number to each entry. The second field is Type_Contact which has three values; Person, Organization, Venue

How do I build my solution, such that, when I create a new record in tblContacts and assign a contact type from the value list it opens the appropriate layout as it creates a new record in that layout's table with the newly created ID_Contact value?

Thanks!

[bcooney]

I would suggest the "Graham Method:"

Thread

[brisom]

Thanks Barbara,

I am reviewing the thread and the links now as it looks very close to what I am doing. I will post again in a few days if I have any more questions.

[bcooney]

I hope you do come back with questions. It's a fairly complex setup, but will prove worth it to you, imho. It's a way to "have your cake and eat it, too" in that you'll have separate list views of Persons vs Organizations vs Venues, and yet have all their common fields in one table, so that reporting on all is a breeze.

But, you need to consider if you really need this. It will allow for the form view of a Contact to be completely different from a Venue form view.

However, all this could be achieved with the clever use of tab panels. In one form view you would have three tab panels and a common area. The common area contains the fields "common" to all types. When a user selects from the type popup, the tab panel that is appropriate to that type will come to the front (and this is done with a script trigger--the user doesn't see the hidden tab panel). Each tab panel contains the fields specific to that type.

Yes, you'll have "holes" in the record in that some of the fields are only particular to that contact type.

So, compare the two approaches. Each has tradeoffs/benefits.

By the way, don't hardcode the contact type, use a value list of TypeIDs. You'll need a table that simply has an ID and a Type Name. This way, you can change the name of the type without breaking anything because you'll store the TypeID, not the type name in any related record.