Running/Calling VBA function from a Macro

[Tupacmoche]

I have a tried and true macro that calls 10 queries and has been running perfectly for years. It runs three delete queries followed by six other queries that populate the tables that were deleted. Now to the issue/problem. I want to add to the top of this macro VBA code as follows:

Option Compare Database
Public Sub LocRec()
Dim user As String
user = GetUserName()
If Forms("BuyBack menu f").RecordLocks = 1 Then
MsgBox ("Try later records are locked now " & user)
Else
MsgBox ("You can access records now " & user)
Forms("BuyBack menu f").RecordLocks = 1
End If
End Sub

Basically, it checks if the table/form is locked. If it is it should exit out and not run any of the 10 openQueries that follow it. But, if it is not locked, it should lock the records for the current users and run the balance of the queries.

I have added a 'RunCode' Action with the following Action Arguments :

Function IsFileLocked()
Call LockRec()
End Function

this is meant to call the above VBA code but I get the following error:

"You tried to run a Visual Basic procedure to set a property or method for an object. However, the component doesn't make the property or method available for Automation operations."

What is wrong? How can this be fixed?:banghead:

 

[pbaldy]

I believe it has to be a function, not a sub. It should also be in a standard module, rather than behind a form or report.

 

[Tupacmoche]

Since the other ten queries are being called from a Macro the RunCode calls a function which in turn calls a sub shouldn't that work?

 

[MarkK]

I don't think the Form.RecordLocks property does what you think. It is not an indicator of the current lock status of a record, nor does it indicate how many people are editing the record. It is a general setting about how a table (or the recordset, in the case of the Form.RecordLocks property) should handle concurrent edits. For instance, if you open a form and begin editing the current record, that action does not alter the value of that form's .RecordLocks property.

 

[Tupacmoche]

That is correct. That is why, I set .RecordLocks to 1 , I mentioned that at the start of this thread :

If Forms("BuyBack menu f").RecordLocks = 1 Then
MsgBox ("Try later records are locked now " & user)

This does not allow any others user from using the underlying table until it is unlocked. But getting back to the problem. How can, I get this piece of VBA code to run?:banghead:

 

[MarkK]

Have you implemented Paul's suggestions? Change it to a Function. Make sure it is in a standard, not a class, module.

 

[pbaldy]

The error would imply that the function is running, but hitting an error. Are you offered the option to debug? If so, where does it take you?