Lock Record

Hi xeb,

You could apply "soft locking", that is, have an additional column, say "Locked", that you would set to 'True' (or 'Y', or whatever) upon first usage, and reset to 'False' ('N' etc) when the use of the record was concluded (i.e. unlocked).

All users would query this "Locked" column on each record before usage, and if set to 'True' ('Y' etc), then would report that the record was locked.

BFN,

fp.

If you mean in a from, set the Allow Edit's property to false

If you mean in a table, not that i can think of...

Hi:

Thanks for the answers.

The form does not work for me. While it locks it for the session it makes it available during a subsequent session.

So I guess I mean in a table.

My users enter two items of data into a table via a form. After they do that I want the record to be locked.

Thanks,

xeb

It sounds to me like you want to lock the fields on the form... you can hide the database window in the startup options (you should also disable special keys) so that they can't get to the tables. the way to lock the fields is:

field1.locked = true

Hi:

My users start with a form that is query driven. They enter a number which matches with a number in the table and a second form pops up with that number and their points. They then enter their name and ID and press a command button that saves their name and ID to the table beside the number and points.

What I need is for the record to be locked after they post their name and ID so another user can't come along and change that record.

Thanks,

xeb

so let me make sure I am getting what you are asking for: you are using a bound form for this and you don't want the next user to come in there and use the navigation buttons to go to a different record and change somebody else's response

Hi:

Right.

A user goes to the designated computer with their number and enters it. It then matches with the same number in the computer. This is THEIR number. They then claim that number by entering and saving their name and ID in the table.

So I need it to allow them to enter and save to numbers (records) that have no name and ID stored but not to those that do because they have already been claimed.

Thanks,

xeb

Hi:

Okay, thanks.

Just so I understand, my table has four fields...

Number
Points
Name
ID

The first form asks for "Number" which the user enters and clicks Button1.

This opens the second form which displays the number and points with two blank fields for entering their name and ID. Once they enter their name and ID they click Button2 which saves their name and ID.

Is that right?

Thanks,

xeb

I've used a technique that looks at a key field in the form and if it has data I then lock the record.  I placed the code in the On Current event of the form so that it dynamically locks or unlocks the current record:

If [Locked] then             'Your key field could be the [Locked] field suggested by fanpages
    Me.AllowEdits = false
else
    Me.AllowEdits = true
end if

Your key field could be ID, or Number, etc.  Of course you can reverse the logic if I don't have it right.

This is pretty much what it should look like for your situation

private function Button1_Click()
   dim strQuery as string, rsResult as adodb.recordset

strquery = "SELECT Number, Points FROM yourtable Where Number=" & txtNumber.value 'dynamically build the query
set rsresult = currentproject.connection.execute(strquery) 'open a recordset

txtNumber.value = rsresult("Number")
txtPoints.value = rsresult("Points")

End Function

private function Button2_Click()
dim strQuery as string, rsResult as adodb.recordset

strquery = "UPDATE yourtable SET Name='" & txtName.value & "', ID='" & txtID.value & "' WHERE Number=" & txtNumber.value
currentproject.connection.execute strquery

end function

Arji: https://www.experts-exchange.com/help.jsp#hi242

I agree with jkorz's (<OT> jkorz: why ADO? </OT>) approach...you should definitely be handling the data updates through code as opposed to letting the user do it through form interaction.  However, to resolve your 'lock' issue, you also need to check for the existence of other data in the record prior to the update.  You still have two options to look for:

1) a 'lock' field as previously described by fanpages, myself, and Arji
2) a check for NULL/blank values in the Name/ID fields prior to the UPDATE.

routinet: I almost always use adp's and sql instead of mdb's so it's hard to break the adodb.recordset habit

in this case the recordset type is inconsequential

Agreed.  I'm used to DAO for anything within Access, and ADO/ODBC everywhere else.  And no, it doesn't really matter since either will work.  Purely preference.  :)

routinet - I did give credit to fanpages for his suggestion.  It's in a comment along side the code

Hi:

Okay, I'm going to switch things to jkorz's method.

Thanks, jkorz. I really appreciate the code.

Which "locking" method is best for my situation and could somebody detail what I need to do.

Thanks,

xeb

Hi:

I think I see how to do the "locking" field idea which I'll try. Thanks.

How do I check for NULL/blank values in the Name/ID fields prior to the UPDATE.

Thanks,

xeb

put the fields you want to check in the sql statement and use:

if isnull(recordset("Field")) or recordset("Field") = "" Then

'value is null

end if

Hi:

When I use the following code I keep getting "Invalid use of Null" although it saves it okay.

Private Sub Form_Current()

Me.AllowEdits = Not Me![Locked]

End Sub

Also, I have the "Locked" feature on a toggle switch on the form which works okay, however, the "Save" command button for the form uses this code...

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.RunCommand acCmdDataEntry
Exit_Command6_Click:
    Exit Sub

Err_Command6_Click:
    MsgBox Err.Description
    Resume Exit_Command6_Click

End Sub

Is there a way to combine the two buttons.

Thanks,

xeb

routinet,
Look more carefully at my comment....I gave you credit

Yup, my apologies Arji.  I was speeding through stuff that morning, and didn't see the commented section in your code.  Bygones?  :)

no problem bro..