How to write this VBA code?

[spaLOGICng]

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...

First, your Customers, Vendors, Contractors, and Agents should ALL be in the SAME TABLE. You would have an Entity_Type_ID column to represent what type it is. You would have an Entity Types Enums Table to give a Type Name. The ID (Autonumber) CC column in this Table would relate to the Entity_Type_ID in your Entities Table for queries and reports.

Now, you simply need to have one Screen to manage all three types. Now, you do not need to worry about the table name at all. All you need to know is their Record ID.