Move table based macro to form

[Kev86]

Yes we have a backend stored on the companies server and then everyone has a frontend stored locally on their PC's. I will have a read of that article you sent me to see whether it can help with what i need it to do at all. Thank you.

[Kev86]

Minty yes good point that the table based macro then works regardless of where the update is being made, i will need to add code to all the relevant forms if i move it over to a form based control rather than table based.

You mention i have nothing to lose by moving the process to the appropriate forms but do you know how i can do this? I can't find a way to produce the same end result using VBA in a form as i have done with using the macro in the table, any ideas on how to do this?

[orange]

Kev86,

You may want to read through the (loong) dialog in this thread regarding an experience with Audit Log with forms by Martin Green's approach.

[Minty]

The basic premise would be to use a function/module to do the insert and call it wherever you need it.
I do a similar thing for logging who runs reports which can't be detected by a data change, this simply records who (a global empid set at user login), when (Now()) and the report name, but your's would be two simple processes, something like (AirCode Untested)

Public Sub subUpdateQuoteSent(strSAP as String)
    
       
    strSQL = "INSERT INTO TATQuotesSent  (SAP , ChangedDate) VALUES ('" & strSAP & "' , NOW() )"
    'Debug.Print strSQL
    CurrentDb.Execute strSQL , dbfailonerror + dbseechanges

End SUB

I've assumed that SAP is a String in the above.
You would simply pass the SAP value into the sub from wherever it is updated.

Create a similar sub for your other insert/updates in the JobCat

[Kev86]

Thank you Orange, i will take a look at that.

[Kev86]

Minty sorry but i am a real amateur when it comes to code and like i said in my original post all my build is based on reading online. I believe i have built the module now and i have named it UpdateQuoteSent with the code you provided above. However how do i call upon that module to run within my VBA code for the forms AfterUpdate event?

Also if it helps SAP is our main reference number so i want whatever the SAP number is in my Jobs table to be entered into the TATQuotesSent table

Thank you for helping with me this.

[Minty]

Assuming you can identify the correct event to put this against in your form you would simply use

UpdateQuoteSent  Me.YourSapNumberControl

That's it! Obviously replace YourSapNumberControl with the name of the control on your form(s)

[Kev86]

Hi Minty, thank you for your help with this. I am not too used to all the code so i am struggling a bit to be honest. I have created the module and named it 'UpdateQuoteSent' with the code you kindly provided. See below..



I have then opened the form i want the update to occur on and within the QuoteSent field of that form i have selected an AfterUpdate event and added the following VBA code...



However when i then open the form in View and update the QuoteSent field i am met with an error which says 'Compile Error: expected variable or procedure, not module'.

I am obviously doing something very wrong as i am not even sure if the name of the control on the form is correct as it gave me lots of options and i selected the AfterUpdate control but not sure if this is correct. I even set the code to UpdateQuote Sent Me.QuoteSent and this still didn't work so not sure what I need to do to fix this.

[Minty]

Your second bit isn't correct.
You need to pass the sub the value of your SAP number, so you need the name of the control that holds the SAP number on the form.


UpdateQuoteSent Me.Put the name of the control that holds the SAP number in here


Also ensure that you have saved the Code into a module that ISN'T called UpdateQuoteSent .
The module name cannot be same as the function.
Call It modAuditing or something similar.

[Kev86]

Minty, thank you. I have amended the name of the module to modQuoteSentLog as i had originally named it UpdateQuoteSent. I then amended the AfterUpdate code to UpdateQuoteSent Me.SAP as the SAP field is the name of the field that holds the SAP reference number.

However i have just tried this again and still get the same error saying that the Sub or Function not defined. Sorry to be such a pain.

[Minty]

In the module code you posted up I think its called subUpdateQuoteSent not just UpdateQuoteSent

Change one or the other so they match, sorry I didn't spot that earlier.

[Kev86]

minty it works, thank you so much for helping. I want to look into it in more detail as i Have 17 macros currently built into the main table which i need to look into and replicate into the forms so i might need some extra help with them but hopefully most of them are straight forward ones i can work out myself. I have already tested this for a Cancelled update and created a new module called modCancelled and created an AfterUpdate reference to log the change and that worked also.

How easy is it to make the module add the user name to a new field within one of my tables. So for example i currently have a table called LDEAllocatingLog which has 4 fields, these are SAP, Job Cat, Change Date & User. I think i am ok with SAP, Job Cat and Change Date but how do i get it to include the user name of who made the change?

if it helps here is a screenshot of the current macro linked to the table...



Thanks again for all you time and help you have given me so far.

[Minty]

You already have a function that is producing that called fOSUsername pretty certain you can use that in your insert query directly.
The easy way to create the function is to initially create a query in the designer that will insert a fixed record, and then adjust to take passed in values.
Then switch to SQL view to give you the correct syntax.

So something like

INSERT Into LDEAllocatinglog (SAP, User, ChangeDate, NewValue) 
VALUES ('132456ABC' , fOSUsername(), Now(), 'MyTestValue' )

Would be the end result.

You then need to create the module to take the changeable elements (in this case the SAP number and the new value) and concatenate them into the SQL string.
Remember that any string values need to be delimited either by single quotes ' ' or triple quotes to escape any quote characters that might be present. So the above would be

Now () , '" & strNewValue & "' )"

or """ & & strNewValue & """ )"

I hope that makes sense.

[Kev86]

So i attempted something before you wrote back and ended up with the following code but i got an error when i tried to run it from a form. I have probably done something wrong with the single or triple quotes...

[Minty]

It looks okay to me.
If you uncomment the debug.print it will show you what it is evaluating to in the immediate window.
Press ctrl + G to see it in the vba editor.

Also what error, and can you post up the actual generated SQL string (Not a photo!)