How to apply code process to all forms from single onclick

[Tim Mullin]

Hello All,

I have created a table (CommissionStatements) in Access that will store all of our data that we import from Excel.

Certain fields of the CommissionStatements table are computed using data from a linked table (Clients) via a button that is programed with an onlcick. This button is on the CommissionStatements form that contains all the fields of that table. The code is as follows:

Me!Agent1 = Me!ClientName.Column(1)
Me!Agent1Percent = Me!ClientName.Column(2)
Me!Agent2 = Me!ClientName.Column(3)
Me!Agent2Percent = Me!ClientName.Column(4)
Me.Refresh

This code does the job exactly how i want it - however because we're going to be importing 100s of records at a time, it is a hassle to go through all of the forms and click the button on every single record to compute the missing fields. Is there a way to write the code or create a macro that will apply that onclick to every single form all at once?

Or does anyone have any better ideas for this process?

Sorry if my explanation is hard to follow...

Thanks, Tim

[NeoPa]

It's possible - but not advised.

The best advice is for you to leave the data where it is and reference it as and when required. It's never a good idea to store the same data more than once in a database (For more on this see Database Normalisation and Table structures). That way leads to all sorts of problems as you'll see if you take a minute or two to digest the linked article. I strongly recommend it for anyone serious about database design. It will save you truck loads of problems going forward.

As I said, it's possible. You would use an UPDATE query to update all the records (or even all of a filtered set) of the table in a single statement. I can give no further details on this though, as you question missed out all the details of table and field names. I strongly suggest you don't choose this path forwards though of course.

[Tim Mullin]

Thank you for the response NeoPa. At this point in time we are just testing out a few different options on how to automate certain processes and this seems like the best way at the moment.

If possible I would like any help I can to get this operating...I've included table names and such below...

Source Table: Clients
Source Fields: ClientName, Agent1, Agent1Percent, Agent2, and Agent2Percent

The data that we are given to import in Access only has the client name listed, which is where the onclick update came in. The VBA code is set to automatically populate the following fields on the Destination Table: Agent1, Agent1Percent, Agent2, and Agent2Percent. Like I mentioned, the auto-populate function is already working correctly.

The destination table is labeled CommissionStatements

Please let me know if you need any other information to help me out with this! And again thank you very much for your help, it is very much appreciated.

[NeoPa]

This isn't sounding good.

Apart from not conforming to one of the most basic tenets of database theory (normalisation) this problem also appears to want to link data together based on a name string (which apparently doesn't even exist in [CommissionStatements] by the way). Anyone without any experience at all needs only a little imagination to see the flood of problems which are bound to ensue from this. Experienced developers hardly need that of course.

I'm not going to leave you without an answer to your question, but you should interpret what I am saying as the strongest possible warning that if you treat database work as cavalierly as this it will bite you back. Absolute guarantee on that one.

Assuming for now then, that the field you forgot to tell me about in [CommissionStatements] is actually called [ClientName], you would need a query of the form :

UPDATE [CommissionStatements] AS tCS
       INNER JOIN
       [Clients] AS tC
    ON tCS.ClientName = tC.ClientName
SET    tCS.Agent1 = tC.Agent1
       tCS.Agent1Percent = tC.Agent1Percent
       tCS.Agent2 = tC.Agent2
       tCS.Agent2Percent = tC.Agent2Percent

[Tim Mullin]

NeoPa,

My sincere apoligies for falling off the map on this one, have not had time to work on the database in the past couple of weeks. I have taken your suggestion and am using a query to match up the data and form a report instead of applying a code process within the forms. It seems to be working flawlessly, however I have run across some new issues (unrelated to this one), but I will post a new topic for it. Thanks again for all your help, it is truly appreciated.

[NeoPa]

That sounds like a good response to me Tim. I'll look out for your new thread (which was the correct way to handle this BTW).