Changes to record in table while form is open - record locking

I have a design for a multi-user database: 1. Form A is used to update Table A and Table B (simultaneously On Click) 2. Form B is used to review Table B, approve records, then delete them from Table B

The problem is if Form B is opened on a record (with Primary Key CASENUMBER) and the record for that CASENUMBER is edited in Form A.

How do I put a lock on a specific record so that if it is being viewed in a form it cannot be viewed/edited in another?

Solution

To expand on your syntax question:

First you would need to modify your SQL statement to include the IN_USE_A and IN_USE_B. Once a record is pulled but before it is put into the form, you would set a recordset based on that record and then make the field for each table true.

Dim strSQL As String
Dim myR As Recordset

strSQL = "SELECT * FROM TABLE_A WHERE criteria_here"

Set myR = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
'myR is now that record and can be manipulated/data pulled from

myR.Edit
myR![IN_USE_A] = TRUE
myR.Update

'perform tasks and such

'then just before closing

myR.Edit
myR![IN_USE_A] = FALSE
myR.Update

Set myR = Nothing