How to use User Defined Function in VBA

[sbehera]

Hi,


I am trying to develop a Multiuser MS access application. For this I wanted to check whether table is locked by any other user, if yes pause a while and update, else update directly. I have been trying on searching for this kind of coding and got the below.But the issue is I am not able use this Function. Could any please help on how to use this Function. I am getting error of Argument is not optional. I just wanted to learn how to use the below function

Also if anybody can help me finding any learning links, it be a great kindness.

Best Regards,

I am using FUNCTION as:

Public Sub UpdateRecord()

UpdateUnitsInStock("Northwind Traders Fruit Cocktail",5,5)

End Sub

Below is the FUNCTION code

Function UpdateUnitsInStock(strProduct As String, intUnitsInStock _
   As Integer, intMaxTries As Integer)
   Dim dbs As Database, rstProducts As Recordset
   Dim blnError As Boolean, intCount As Integer
   Dim intLockCount, intChoice As Integer, intRndCount As Integer, intI As Integer
   Const conFilePath As String = "E:\SQL Planner"
   On Error GoTo ErrorHandler
   ' Open the database in shared mode.
   Set dbs = OpenDatabase(conFilePath & "Northwind.mdb")
   ' Open the table for editing.
   Set rstProducts = dbs.OpenRecordset("Products", dbOpenDynaset)
   With rstProducts
      ' Set the locking type to pessimistic. Setting LockEdits to
      ' False would use optimistic locking.
      .LockEdits = True
      .FindFirst "ProductName = " & Chr(34) & strProduct & Chr(34)
      If .NoMatch Then
         ' conErrNoMatch is defined at the module level as a public
         ' constant of type Integer with a value of -32737.
         UpdateUnitsInStock = conErrNoMatch
         GoTo CleanExit
      End If
      ' Attempt to edit the record. If a lock error occurs, the
      ' error handler will attempt to resolve it. Because this procedure
      ' uses pessimistic locking, errors are generated when you begin to edit a
      ' record. If it used optimistic locking, lock errors would occur when you
      ' update a record.
      .Edit
      ![UnitsInStock] = intUnitsInStock
      .Update
   
   End With
   
CleanExit:
   rstProducts.Close
   dbs.Close
   Exit Function
ErrorHandler:
   Select Case Err
      Case 3197
         ' Data in the recordset has changed since it was opened.
         ' Try to edit the record again. This automatically refreshes
         ' the recordset to display the most recent data.
         Resume
      Case 3260                     ' The record is locked.
         intLockCount = intLockCount + 1
         ' Tried to get the lock twice already.
         If intLockCount > 2 Then   ' Let the user cancel or retry.
            intChoice = MsgBox(Err.Description & " Retry?", _
               vbYesNo + vbQuestion)
            If intChoice = vbYes Then
               intLockCount = 1
            Else
               ' conErrRecordLocked is defined at the module level as a public
               ' constant of type Integer with a value of -32,736.
               UpdateUnitsInStock = conErrRecordLocked
               Resume CleanExit
            End If
         End If
         ' Yield to Windows.
         DoEvents
         ' Delay a short random interval, making it longer each time the
         ' lock fails.
         intRndCount = intLockCount ^ 2 * Int(Rnd * 3000 + 1000)
         For intI = 1 To intRndCount: Next intI
            Resume         ' Try the edit again.
      Case Else            ' Unanticipated error.
         MsgBox "Error " & Err & ": " & Error, vbOKOnly, "ERROR"
         ' conFailed is defined at the module level as a public
         ' constant of type Integer with a value of -32,761.
         UpdateUnitsInStock = conFailed
         Resume CleanExit
   End Select
End Function

[burrina]

Are you wanting to specify the time limit the record can be locked as in Record Locking / Edit Record Method?

[sbehera]

Thanks Burrina, for looking into my problem. I guess no. I just wanted the other user to wait till the current user is updating, then the current user disconnects and the other user is connected to update. So the current user doesn't have any time limit. Well the update.should not be taking more than few mili seconds but if some other user trying to update in the same time frame, then he/she should wait for the current user to finish.
Thank you again!

[burrina]

This can done via Record Locking in Access under Client Settings / Default Record Locking / Edited Record.
This will force the current record to either be saved or abandoned before allowing another use to change.

HTH

[sbehera]

Ok. Thank you . I will do..But cant we do this by VBA. I want that one user should not open the backend table till the time it is busy with another user. And could you please tell me how to use the above function code. I want to know how to use that.
Please accept my gratitude for your help.

[burrina]

Open your db in design mode by holding down the shift key when opening, then click on File/Options/Client Settings.
Is your db a multi-user db? You ONLY want one user in the db at a time? I really don't know how to code what you are describing thru vba,Sorry!

Maybe someone else can assist, never used that function myself.

Good Luck!

[rstruck]

ok, What I would do is put a breakpoint at the beginning of the function. Then when you call the function it will stop there and you can step through the code line by line. if you can step through all the code , then I sometimes comment out all the code so it is just an empty function.
Function UpdateUnitsInStock(strProduct As String, intUnitsInStock As Integer, intMaxTries As Integer)

msgbox "this works"

End function

I also just notice that you are not returning anything. I usually do a Function blaH( mystring as string) as Boolean then inside I do a return Blah as true if it works or blah = false if it fails .

[ItsMe]

What you have there is a function that uses Pessimistic record locking and error trapping to update a table using the .Edit method. I have not seen that code before so I am not sure how or why you would use the function. It is not locking an entire table. Locking occurs to a row when the record has focus and the .Edit method is employed. The exception is when you use dbOptimistic. If you use dbOpenDynaset + dbOtimistic, you will place the record in lock mode at the moment when the .Update method is attempted.

dbOptimistic is default so
Set rstProducts = dbs.OpenRecordset("Products", dbOpenDynaset)

is the same as
Set rstProducts = dbs.OpenRecordset("Products", dbOpenDynaset + dbPessimistic)

an alternative is
Set rstProducts = dbs.OpenRecordset("Products", dbOpenDynaset + dbOptimistic)

I do not see a need for
.LockEdits = True

in versions 2003 and later and maybe before too. I am not sure when the constants ddbOptimistic and dbPessimistic were introduced.

What I see the function doing is making sure all necessary records get updated by employing error trapping. I do not see any table security that LOCKS a table in that function. There is not intrinsic table locking in Access. For that you would need something like SQL server. I am sure there is a way to secure areas of the DB from certain users using VBA. Most developers employ security and enforce constraints using VBA. I just do not see security as the purpose of the function you posted here.