Run form event code remotely

[matt beamish]

Not sure of the best place to pose this question, as it can be approached a few different ways, but I've not found a good one yet.

I have a form in which job times for individuals is entered. As the data is entered, a macro fires to calculate various costs (Nat Ins/Pension/Total cost etc etc), and that data is stored in a table.

I now have written code to enable the inputting of the data into the database directly from the Excel sheet in which the data is first entered - this is great as it cuts out a data entry repeat and will be a big big time saver.

I now need to get the macro to fire on the data, but I am not being successful and could do with some advice.

If anyone might be able to help me, I will post up some further info.

thanks

 

[vbaInet]

I have a form in which job times for individuals is entered. As the data is entered, a macro fires to calculate various costs (Nat Ins/Pension/Total cost etc etc), and that data is stored in a table.

The simple answer is, calculated values should not be saved. Calculate it each time you need it.

 

[matt beamish]

Thanks for reply. I understand your point.
The parameters behind NI and pension change. The data therefore needs to be calculated at time of input, and stored.

 

[vbaInet]

As long as you have a justifiable reason for storing calculated values, which I think you might have.

Create a function that does the calculations, and call the function each time you need it.

 

[matt beamish]

Thanks.
I guess the gist of my question is how to fire the function now that the form is no longer the data entry point.

Do I try to run it when I enter the data remotely (using SQL/VBA) or do I run it an an event of the form (eg Oncurrent). I think logically it should run when the data is entered remotely from the Excel form as the data input form might not be opened, and then the values would remain zero/null, but that is a bit beyond me as I don't know how to replace a reference within the macro to a control on the form that is now not open (as the data is being entered via SQL), and from that how to call a query that sits behind the macro.

 

[vbaInet]

If you're accessing an Access form from elsewhere, form events won't fire anyway.

What is your current setup? How is Excel linked to Access and how are you inserting records into Access from Excel?

 

[matt beamish]

So I ended up with the code in this thread

http://www.access-programmers.co.uk/forums/showthread.php?p=1438371#post1438371

The code fires form a button in Excel

 

[vbaInet]

Ok, you can fire code in Access using appAccess.Run "NameOfFunction"

 

[matt beamish]

Aha. thanks!

 

[vbaInet]

You're welcome!