Mapping data between Tables using a Macro

[Garry in Perth]

Hi folks,

Hopefully a simple one.

I have two tables to keep track of my customers; Companies and Customers. Some companies may have several locations; so the relationship needs to be a One:Many. But in a lot of cases, there is only one customer per company, so the basic Customer details (name, address, phone, fax, etc.) are the same as the Company details.

When I need to add a new Company & Customer pair, I use a simple Add New Company form to input the Company data. So far so good.

Then I open up the Add New Customer form, and in a rectangle (option group) I input the Company ID to display the recently inputted Company data (name, address, etc.). I have been then copying and pasting each field from the Companies table to the Customers table. I want to create a button to invoke a macro that will copy (or "map") all the corresponding fields from the Company table into the Customer table in one go.

For example,
Company.Name ---> Customer. Name
Company.Address ---> Customer Address
etc.

Could SKS please start me off on the design of such a macro.
I am not a SQL programmer, so I prefer to use the Macro Builder.

Thanks

 

[Andrew Fergus]

I think this can be done with a macro and an append query. The macro would be linked to a button on your Companies form with a title similar to "Copy this Company to Customers" (so you would copy the record over from the Company data entry form, not the Customer form). The macro would do three things : save the record that you are currently working on by going to a new record (the Goto -> New command) which would save the current record automatically, run an append query (Open Query) and then stop the macro. If you wanted to, you could also turn the "SetWarnings" Off (i.e. No) at the start of the macro and then turn it back on (i.e. Yes) immediately before the Stop command.

Assuming all Companies already have Customer records, except for the Comapny you just entered, the append query would identify Companies from the Companies table that did not have any corresponding records in the Customers table and then append these records into the Customer table.

The way to build the query is as follows:

New Query -> Add Company and Customer Tables -> Ok;
Click Query -> Append Query -> Select Customer Table -> Ok;
Include all fields in the query from the Company table that you wish to append to the Customer table -> select the corresponding field from the Customer table in the "Append To" line of the query for each field you included;
make sure the two tables are linked by the Company ID in the top half of the screen -> double click the line joining the tables to access the Join Properties -> Select option 2 : "Include all records from the Company table and only those records from the Customer table where the joined fields are equal" -> Ok;
Add the Company ID from the Customer table to the query -> delete the "Append To" entry for that field and enter the criteria "Is Null" (without the quotes) for that same field.
Save.

This query is the query that you will "Open" in your macro.

HTH, Andrew.

 

[Garry in Perth]

Andrew,

Thanks for your reply. I tried your approach of creating an Append Query and then creating a Macro to envoke it. A few hiccups.

Firstly there are valid instances where a Companies record may NOT have a corresponding Customer record. I also need to be able to add a new Customer record for an EXISTING Company record. So the criteria "Is Null" in the append query won't be possible. I need to be able to nominate the precise Company ID when I am creating a new Customer. So I have added the select criteria [Enter required Company ID], instead of the "Is Null" criteria. I made this change and successfully got the required Companies data into the Customers table. So far, so good.

After the required fields have been mapped into the Customers table, I need to open a form to display the "mapped" data and to continue adding new pieces of Customer data for this new Customer. How can I incorporate this into the macro.

TIA.

 

[Andrew Fergus]

Hi Garry

Change the condition in your append query from [Enter required Company ID] to : [Forms]![Your company form name]![Your company field identifier] and you were correct to remove the "Is Null" part (bad assumption on my part).

Change your macro to this :

1st line = GoToRecord : Next
2nd line = GoToRecord : Previous (this forces the record to be saved)
3rd line = Open Query : Your Query Name, Datasheet, Edit
4th line = Open Form : Your Customer form name, View Form, Where [Your Customer Field Identifier] = [Forms]![Your company form name]![Your company field identifier], Data Mode Edit.
5th line = Stop Macro

Again, you may want to turn the SetWarnings off at the start of the macro and turn them back on at the end.

HTH, Andrew.

 

[Garry in Perth]

Andrew,

You said in your first reply that "the macro would be linked to a button on your Companies form with a title similar to 'Copy this Company to Customers' ". As I mentioned, I may need to add a new Customer based on an existing Company.

So I probably need to have the macro linking button on my 'Add a Customer' form instead.

In the form header of my 'Add a Customer' form, I would first need to select the appropriate Company ID (say using a combo box), then hit the macro button - which contains [Forms]![Add a Customer]![Companies.Company ID]. I would then like all the Customer fields, including the newly mapped fields from Companies, to be displayed in the Details section of my 'Add a Customer' form and so then I can continue to add more data in other fields for the new customer.

I have removed the GoToRecord:Next and the GoToRecord:Previous from the macro - as by then the desired Companies.Company ID is already being displayed on the 'Add a Customer' form.

Likewise I have removed the OpenForm:Add Customers, as by now the 'Add a Customer' form is already open.

My macro currently reads:-
SetWarnings:No
OpenQuery:AppendQuery:Datasheet:Edit
SetWarnings:Yes
StopMacro

When I hit the macro button the following error message is returned.
"Invalid bracketing of name '[Forms]![Add a Customer]![Companies.Company ID]' OK Help". Clicking OK brings up the Action Failed dialog box at the OpenQuery action. I click on Halt. The underlying Customer table now includes a new record which is blank apart from the selected Company ID. Any suggestions?

 

[Andrew Fergus]

Hi Garry

I based my original suggestion on this :

When I need to add a new Company & Customer pair, I use a simple Add New Company form to input the Company data. So far so good

The (modified) append query & macro was designed to pick up the record you are working on from the company form and transfer it across to the companies form / table. However, you have based your solution on selecting a company from a combo box on the customer form and the solution I recommended was not designed for that scenario. I selectively chose to ignore this bit (at my peril) :

Then I open up the Add New Customer form

Given you have partially created the record on the customer form when you select a company from the combo box, then I think we need to change the append query to an update query (to fill in the missing blanks). I think we also need to change the [Forms]![Add a Customer]![Companies.Company ID] part to something like [Forms]![Add a Customer]![Your Customer ID from your Add a Customer form]. Also, the query will need to be modified to pick up all of the fields from the customer table (at the moment it is picking up fields from the company table) and then updating these values with the relevant info from the company table.

Please note I haven't tested this and my first test run is giving some odd results - I will look at this later today when time permits.

Andrew

 

[Garry in Perth]

Andrew,

As soon as I select the desired Company ID from my combo box, a new Customer record is created with an autonumbered Customer ID, an automatically generated Creation Date/Time field, and the selected Company ID is updated into [Customers].[Company ID].

I then hit the Run Macro button.

I have changed the append query to an update query as you recommended. The update query has been modified to select the relevant fields in the Customers table and update these values with the corresponding data from the Companies table.

My update query design looks like this:-
Field: Customer Name
Table: Customers
Update To: [Companies].[Company Name]
Criteria:

Field: Customer Postal Address1
Table: Customers
Update To: [Companies].[Company Postal Address1]
Criteria:

{and so on for about 8 other fields, eg suburb, postcode, phone, etc}

Field: Company ID
Table: Customers
Update To: <left blank, as a value is already attached to the new Customer record>
Criteria: [Companies].[Company ID] - Is this correct here? Should there be an equals sign at the front? I have tried various combinations of [Forms]![Add a Customer]![Company ID] here but nothing seems to work. I just keep getting the Enter Parameter Value dialog box.

The new values in the various Customers field are not being automatically displayed on my Add a Customer form (and they are not being saved into my underlying Customers table). Is there anything else I need to be doing in the macro?

 

[Andrew Fergus]

Hi Garry

In your query, change the criteria on the Company ID field to this : "[Forms]![AddCustomers]![Customer_ID]" (without the quotes, but use your own form name and customer identifier field name). Given you are creating this from the customer form (rather than the company form, which would be so much easier...) then you need to reference your query to the company you have selected on the customer form.

In the form, Click Edit -> Select Form, Click View -> Properties -> Data -> Order By - > "[Customer table name].[customer autonumber field]" (without the quotes and use your own table and field names).

In your macro, add a "Requery" command between the "Open Query" command and the "Setwarnings (On)" command, and add a "GoToRecord" (Last) command below the "Requery" line.

I expect you will find the new entry duplicated - once with just the customer number and a second time with the customer number and company details. Let me know if this is the case - this is what I am seeing in my test database and this is the anomaly I referred to this morning. We could identify that record via a query and delete it as part of the macro but let's get this part working first.

HTH, Andrew

 

[Garry in Perth]

Andrew,

You asked me to set the criteria for the Company ID field to [Forms]![Add a Customer]![Customer ID]. Shouldn't I be setting it to [Company ID] instead? For example, on my test database, I select Company ID =14 as my desired company. As soon as I select that Company, my Add a Customer form presents me with Customer ID = 3887 (autonumbered) and plonks 14 into the [Customers].[Company ID] field. Then when I run the update query I want the query to lookup the Companies table using the data for Company ID = 14. The Customer ID doesn't get stored in the Companies table. I'm confused.

You asked me to "Order By" the form on my Customer ID field. I did that , but I see no noticeable difference. BTW, my Add a Customer form shows only 1 of 1 records; not the entire 844 Customer records. Does that affect things?

I made the necessary changes to the macro.

Now when I select a Company ID, get my new partial Customer record, and then hit the macro button, the form loses all displayed data (i.e. Customer ID reverts to (AutoNumber) ). The underlying Customers table shows the new record with only the Customer ID (autonumbered), the Creation Date/Time (automatic) and the Company ID. All other fields are blank. There is no duplicate record.

{If you're wondering how come I get an autonumber of 3887 if there are only 844 records in the Customers table, then it is due to some very poor updating that created masses of duplicated Customers at one time - which I had to then delete. I'm running the database in a multi-user environment across a server and if I try to compact the database, other people cannot access the database. It's a long story.}

 

[Andrew Fergus]

I wouldn't normally do this but you have mail....