How to write this VBA code?

[sjs94704]

Hi:

Doing what I am asking below will be a brand new skill for me to learn!

I am in the beginning stages of developing my application. So, for this example I am going to use CUSTOMERS.

So, the beginning of my customer table looks like this:

CustomerID
FirstName
Middle
LastName

In also have two other tables. Customer Phone Numbers a Customer Addresses. They look like this:

CustomerPhoneID
CustomerID
CusomerPhoneTypeID (Mobile, Fax, Other)
CustomerPhoneNumber

Customer AddressID
CustomerID
CustomerAddressTypeID
CustomerAdress1
CustomerAddress2
CustomerCity
CustomerState
CustomerZIP



I have a CUSTOMER form I also have 2 POPUP forms, one for the Phone Number Details and the other for the address Details.

Now, I have the EXACT SAME tables for vendors, contractors, agents and instead of using the word CUSTOMER in the table name and field names they are replaced with these names respectively.

QUESTION:

What I would like to do is that if from the main menu the user selects CUSTOMER, there would be a VBA routine that would assign ALL THREE forms the correct table names and field names for ALL THREE forms at the same time.

Also, once the user EXITS the customer main form, there is a routine to CLEAR the record source and field names of all three forms at the same time.

END RESULT GOAL:

I just don't want to have a seperate address form for each table and I want to resuse forms as much as I can!

Thanks in advance for yourt help...

 

[Uncle Gizmo]

Here is a basic database with three of the table specified if anybody wants to have a play.

Note that the CustomerPhoneTypeID field in tblComms is defined as text.

 

[Gasman]

Big mistake.
Have a field that identifies whether a customer, agent, vendor etc

 

[plog]

This is a very common issue around here--You've created a problem that need not exist and now are trying to code your way out of it. It's good that you've recognized that your types of people are so similar that you should efficiently handle them. But VBA is not the way--properly structured tables are.

Now, I have the EXACT SAME tables for vendors, contractors, agents and instead of using the word CUSTOMER in the table name and field names they are replaced with these names respectively

You are storing what should be a value in a field as the name of tables/fields. "Customer", "Vendor", "Agent" should be held in a field (e.g. [RoleType]) and not as prefixes to field and table names (VendorPhoneNumbers, CustomerAddresses, AgentState).

First, make a copy of your database (that's always step 1 when working with a database). Then rename CUSTOMERS to People, change all instance of "Customer" in your fields to "Person" and add a field [RoleType]. That People table wil now holds all the data from CUSTOMERS, VENDORS, AGENTS, CONTRACTORS. Then do the same for the Address and Phone number tables such that they are genericized enough to hold everyones address and phone numbers (you don't kneed a RoleType for these tables because its in People already).

Then, you only need one set of forms and no fancy VBA to switch everything.

Also, I don't see a date or priority field in your Address and Phone number tables. How is one to know the current address or prefered phone number?

 

[CJ_London]

I would add that for GDPR reasons a customer may say ‘don’t contact me on this number’ . Ok you could delete it - until another user comes along and adds it again.
Better to have a ‘do not use’ flag so it cannot be re-entered

 

[Pat Hartman]

I agree with Plog. If you don't like the word people since some might be individuals and some might be companies, you could use something like "Entity" You also have to decide how to handle individuals/companies. Must a company always have a person as a contact?

Then you have to determine if there is overlap between roles. Can a customer also be a vendor? You certainly don't want to maintain multiple addresses in this case so Role is probably a 1-m table to allow any entity to be multiple types.

As to addresses, you may want to assign a primary type to an address such as shipping or billing or mailing, etc.

Then we get to the "phone" table. But people have multiple ways to be contacted, not just phone. There is email, FB, and twitter for starters. That means another type table and you should also include a format type so that you know whether the contact is a phone or an email address or some other format.

Is your head spinning yet

 

[jdraw]

More info than you need, but a reference for the PEOPLE or ENTITY set up that has been suggested.

 

[sjs94704]

Thanks to EVERYONE ! I am new here to this forum and looks like it might be a source of some great information.

The good news is that I am just in the beginning stages of my DB and can very, very easily implement some of your great ideas and information!

With gratitude,
-Steven

 

[sjs94704]

So, I took a look at the link provided by jdraw called ' reference' that has all the diagrams of the database tables, etc. For me, it is packed with tons of good ideas!

FOLLOW UP QUESTION:

As far as in the example provided it called all the people/companies/contacts, etc..... Party.... OK, got that.

As far as a party's extended details, those will obviously be different depending on the TYPE of entry the party is. So, to avoid going down yet another rabbit hole, what migh be the best way to manage extended details for all potential various types of entries that there might be?

Now, of course, I do realize that at some point there is going to be some consultation with the client to see what their specific needs will be, but, what I am talking about is the ground work to cover as much bases of COMMON possabilities as possible that will be utilized by MOST projects?

 

[Gasman]

More info than you need, but a reference for the PEOPLE or ENTITY set up that has been suggested.

Persons may have a gender

Pretty up to date as well?

 

[jdraw]

Persons may have a gender

Pretty up to date as well?

Paul,
Did you ever think you would need a lookup table for gender(with multiple possible values ), and/or allow an individual to have multiple record/values for this and/or hair colour?

 

[Gasman]

Nope, Gender was one of the few options I hardcoded as Male or Female.

 

[Pat Hartman]

what migh be the best way to manage extended details for all potential various types of entries that there might be?

What kind of application are you developing? Is this an enterprise wide ERP where you have to be all things to all people? There is a point where catering to "everything" becomes too much.

Do some analysis and tell us just how many non-overlapping attributes you have uncovered. Once you start building "sub" tables to hold three attributes, you start to interfere with the purpose of defining a "party" to begin with because you will need to include all the "sub" tables in your general queries using left joins to handle the differences so perhaps, having the fields in the party table might end up being less trouble going forward.

 

[sjs94704]

What kind of application are you developing? Is this an enterprise wide ERP where you have to be all things to all people? There is a point where catering to "everything" becomes too much.

Work Order database, so I don't need personal details about oeople to that degree. It's mostly for business purposes.

 

[Pat Hartman]

There is a difference between anticipating certain types of growth and over engineering. Sounds like you're on the over engineering path based on a different thread. Technically, creating separate sub type tables to hang off the single entity table is correct when you have a lot of subtypes, very little overlap where an entity serves more than one role, and lots of extra attributes. But, there are diminishing returns. So, you have to consider how the tables will be used in queries and are you making everything else in the app more complicated for the sake of purity? Did you gain anything by making the entity/party at all? Or, did you just add complexity? These extra attributes are not in the same category as repeating groups which should always be implemented as child tables. Here we are talking about optional attributes. For example an entity (party) that is an organization rather than an individual would need a contact name but that would be redundant for an individual type since the individual would be the contact. Sometimes, just hiding/showing the groups of variable attributes is easier to manage than actually creating multiple sub tables. It all comes down to how many columns and how little overlap?

 

[sjs94704]

There is a difference between anticipating certain types of growth and over engineering. Sounds like you're on the over engineering path based on a different thread. Technically, creating separate sub type tables to hang off the single entity table is correct when you have a lot of subtypes, very little overlap where an entity serves more than one role, and lots of extra attributes. But, there are diminishing returns. So, you have to consider how the tables will be used in queries and are you making everything else in the app more complicated for the sake of purity? Did you gain anything by making the entity/party at all? Or, did you just add complexity? These extra attributes are not in the same category as repeating groups which should always be implemented as child tables. Here we are talking about optional attributes. For example an entity (party) that is an organization rather than an individual would need a contact name but that would be redundant for an individual type since the individual would be the contact. Sometimes, just hiding/showing the groups of variable attributes is easier to manage than actually creating multiple sub tables. It all comes down to how many columns and how little overlap?

Point Taken! Thanks for that ..

 

[Pat Hartman]

Also, for example, you can use ContactFirstName and ContactLastName as the name for the primary contact which would also be the ONLY contact in the case of an individual. Then the second field, which could be CompanyName is only required for institution types of parties.

 

[sjs94704]

Also, for example, you can use ContactFirstName and ContactLastName as the name for the primary contact which would also be the ONLY contact in the case of an individual. Then the second field, which could be CompanyName is only required for institution types of parties.

Yes, sounds good..

 

[sjs94704]

Also, for example, you can use ContactFirstName and ContactLastName as the name for the primary contact which would also be the ONLY contact in the case of an individual. Then the second field, which could be CompanyName is only required for institution types of parties.

Yes, your right. One of my thoughts is that since this is a Work Order DB that there will be a another table to store the names of other people who work at the customers company who might call in to place a purchase order to track who is authorized to place purchase orders as well as other people who might have a reason to call on a purchase order and what their permissions are.

 

[GPGeorge]

You might find some useful implementation features in the recently released Northwind Developers template. It illustrates most of the features being discussed in this thread. Where it differs, you'll need to pull your thinking cap snug and decide how to handle your requirements.