Move table based macro to form

[Kev86]

Hi Minty, sorry about that. So I uncommented the debug.print and also did the ctrl + G to see the Immediate window at the bottom of VBA however nothing shows in there. I have set the After Update event in the form with the following code UpdateLDEAllocatingLog Me.SAP

When i try to update the record on the form i get the following error "Compile error: Argument not optional"

The code in my module is as follows...

Public Sub UpdateLDEAllocatingLog(strSAP As String, strNewValue As String)


strSQL = "INSERT INTO LDEAllocatingLog (SAP , NewValue , ChangeDate , User) VALUES ('" & strSAP & "' , '" & strNewValue & "', NOW() , fOSUsername() )"
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges






End Sub

[Minty]

If you are seeing nothing in the debug window, it's not getting that far. The error is telling you that you aren't passing both the elements into the sub.
You should be using something like

UpdateLDEAllocatingLog Me.SAP , Me.MyNewValueControl

You are missing the second argument to the function - the control with the new value.

[Kev86]

Worked it out in the end. I was entering the field name of where the value was going to in that new table rather than the field name of the table the data was being pulled from.

So rather than UpdateLDEAllocatingLog Me.SAP, Me.NewValue than I changed it to than UpdateLDEAllocatingLog Me.SAP, Me.JobCat and it works!!!

Thank you again Minty for everything you have helped me with. I want to leave this post open for now as i have 17 macro's in my current live database that i need to transfer into VBA code for the forms so i want to look through that and make the changes and no doubt i will hit a few stumbling blocks and might have to come back to you for more support.

I am hoping once i have done that and built it into the live system that everyone is using it will stop the 'Database is currently locked error' from occurring.

[Kev86]

Hi Minty, i have started to look at using modules and VBA within the forms to replicate what i currently have working in the macros from the table and i have come across an issue. One of the things the macro in the table does is record user updates, so if someone adds their name against a job and then removes their name i can see this in the log with the old table macro recording these changes.

However with the new module and VBA code in the form i can add the name and it records this however if i go to remove my name afterwards i am met with the following error "Run-time error '94': Invalid us of Null" can upon clicking debug it then references my VBA code in the form which is 'UpdateQDPAllocatingLog Me.SAP, Me.AllocatedQDP'.

Any ideas on how to get it to also record a removed entry without throwing up an error? The reason i have this is so that i can see if someone has taken work by entering their name and then placed it back in the pot by removing their name at any point.

[Minty]

If it's text and I assume that the null value could be present in the AllocatedQDP field.
Any time you might get a null you need to accommodate it - the easiest route is to pass a suitable replacement value in using the Nz() Function, something like

UpdateQDPAllocatingLog Me.SAP, Nz(Me.AllocatedQDP,"")

would pass in an empty string to the logging event.
If it was a number I would probably use 0 0r -1 to indicate it's been removed.

[Kev86]

Minty, that works perfectly, thanks again for all your help with this. I am looking to make all the changes in the New Year so if i have any other issues i might come back to you then.

[Kev86]

Minty, thanks again for all your help. I am pleased to say i implemented the changes about two months ago and the issue with the database locking has vastly reduced. Before the changes were made the database would lock at least couple of times a week but since the change i have only had it lock twice and both times relate to the same person doing the same update.

When this has occurred it is when two managers open the same form but it asks for their name so they can only see their jobs and they then try to update jobs within that form the database has locked which i can't understand why that would happen.

However thank you again as the locking issue has definitely improved thanks to your help.

[orange]

Kev86,

Here's a link to an article by Philipp Stiefel on Queue based applications. It may be overkill, and may not be the solution, but it could offer some insight.

[Minty]

Glad it helped.

It may be that the forms underlying recordset includes the same records, and is simply filtering the form to display that users records.
How does it restrict the records they can see?

[Kev86]

Yes the query is built to show lots of jobs but the managers only want to see and update their own so when they open the form it asks for the managers name so it then filters on their jobs.

When they go to update the jobs sometimes they get the error 'Record Locked' and they are not able to make the updates. They then have to close it down and leave it a few minutes.

It's rare but my guess is two managers are trying to make updates at the same time and maybe this is causing it.

[Minty]

Instead of filtering, change the rowsource of the form after they select there name to only bring in their specific records.

That should mean each manager is only locking the records they see.

[Kev86]

Sorry I hadn't explained that very well. The query has a Criteria which asks for the managers name and when they enter that when opening the form linked to that query it asks for their name and then it only shows their jobs and no others.