Checking status of form - EDIT MODE

[darinmc]

I'm trying to find a simple solution to an employees form / table.

the form is set to edited record, and record - locking in progress.
A user has to sign into the database.

I was thinking of Creating a field in the table,
LockedBy = text
Then hide a txtbox on the Employee form... As soon as the user does something to put the form into EDIT status???, I would like the (LockedBy) txtbox to contain The name from the logged in user taken from the switchboard form...

1) Where would I write this code - As soon as the form goes into edit status? (Placing user name in (LockedBy) txtbox)

2) I have an EDIT button which unlocks controls etc BUT I would like this to first check if txtBox is null or not, and CHECK if the record is in the EDIT status (what simple code checks that?)
I would then show a msgbox showing who is using the record

3) when I close the form, I would make the lockedBy field NULL, assuming i do that on the form UNload?

Thx For any help
Darin

 

[nim180]

Hello darinmc how are ya.....

1. Put this code into the control Source of the txtbox on the form. Replace "SWITCHBOARD NAME" with the name of your Switchboard form, replace "SWITCHBOARD TXTBOX NAME" with the name of the txtbox on the switchboard that contains the logged in user.

=" " & [Forms]![SWITCHBOARD NAME]![SWITCHBOARD TXTBOX NAME] & " "

2. on the onclick of the event button put this in on the top of your main code.

If isnull (TXTBOX NAME) then
msgbox("No user logged in")

else

msgbox ("Record is being used by " & Forms!FORM NAME.[TXTBOX NAME] & " ")

"REST OF CODE"

end if 'above end sub

3. im assuming you have a close button on the form, if so on the onclick event of the close button put something like

if not isnull(TEXTBOX NAME) then
TEXTBOX NAME = null

"REST OF CODE"

end if 'above end sub

Hope that helps,

Nim

 

[Remou]

darinmc
Do you wish to lock every record on the form when one record is being edited or only the record that is being edited?

In either case, a textbox containing data that has not been saved to the database will not be available to users on other PCs. A more common scenario is to maintain a small table that can be updated with the unique ID of the locked record.

ADO offers a great deal in the way of locking. It is something else to consider.

 

[darinmc]

Thx to both for quick reply..
I have a tabctl form for the employees, I would like it so that all users could open the record in read only, or snapshot view. Most if not all the controls are locked when form loads unless its a new record. The command buttons are disabled untill a user presses the edit button.

When a user clicks the edit button, I would like that 1 record to be locked.
If another user tries to edit, it will then show which person is using that form in edit mode...
A person logs into the database with a password form, this places there username on the switchboard as well as their passID which is hidden.

I have found bits and pieces but still not sure which the best and easiest solution is to follow.
Please help

Thx
Darin

 

[Remou]

Here is a sketched idea, it is not tested.

First create a table, let's call it tblLocks, with three fields:
[tt]LockID
UserName
LockDate ->Set the default value in the table to Now, this field is for you to check when things go wrong.[/tt]

Set the form properties of the employees form:
[tt]Allow Edits: No[/tt]

Add code to the current event of the employees form. Something like this:

strEdit=DlookUp("UserName","tblLocks","LockID=" & Me.ID)

If Not IsNull(strEdit) Then
'If the record is being edited ...
  'Fill in user name textbox
  'You may wish to use the visible property.
  Me.txtEditedBy=strEdit
  'Disable the edit command button 
  Me.cmdEdit.Enabled=False
Else
  'You may wish to use the visible property.
  Me.txtEditedBy=""
  Me.cmdEdit.Enabled=True
End If

Add some code to the edit button, something like:

strSQL="INSERT INTO tblLocks (LockID, UserName) " _
& "VALUES (" & Me.ID & ",'" & Forms!Switchboard!txtUserName & "')"
'Write to the lock table
CurrentDB.Execute strSQL
'Allow edits
Me.AllowEdits=True

Add some code to the save button, something like:

strSQL="DELETE FROM tblLocks " _
& "WHERE ID=" & Me.ID
'Disallow edits
Me.AllowEdits=False
'Delete from the lock table
CurrentDB.Execute strSQL

The above is only a sketch and is not suitable for a database with a large number of users editing records at the same time. You will, of course, need to change the names of everything to match your own database. I have assumed that username is a text field, that you have a unique ID and that the ID is numeric.

 

[darinmc]

Thx Remou

Will have a look at that shortly. Yes, ur correct with the assumptions..
Thx
Darin

 

[Remou]

Oops. This:

strSQL="DELETE FROM tblLocks " _
& "WHERE ID=" & Me.ID

Should read:
strSQL="DELETE FROM tblLocks " _
& "WHERE LockID=" & Me.ID

 

[darinmc]

On my TabCtl form, In the data properties, I have allowedits to no, but when that form opens, it still lets me type into a txtbox that has not been locked??

Any Ideas?

Thx
Darin

 

[Remou]

I wonder if there is code that is changing the setting? I half remember an unusual situation in which allowedit can change but by far the most usual is a little code.

 

[darinmc]

I've done a few tests, quite a few...
could it be that its not locking TabCtl?

Should I upload all the code to have a quick run through or just the form events?

Darin

 

[Remou]

Please try something. Add a click event to one of the form controls with code to tell you the AllowEdits state of the form:


MsgBox "Allowedits: " & Me.AllowEdits

 

[darinmc]

I tried that...
It said false BUT I was still allowed to type into the date field.

I have started building a new Tab form, not looking forward to adding all the code etc
I tried looking through all the procedures and cant seem to find code thats making it in edit state.
Anyway, its strange that is shows false but still allows me to insert data..

Darin

 

[Remou]

You can attach your database in a stripped down format, if you wish.

 

[darinmc]

not sure what u mean stripped down, assume just employy table and the emp form?

How do you Attach a file? my clip looks disabled?
Thx
Darin

 

[Remou]

I meant that you can leave out anything sensitive, if there is any such data. The File Storange and Upload to box links seem to be broken. have you got a site that you can upload to?