Setting Record Readonly

[ganeshvenkatram]

Hi

I created a form in access which has multiple fields.I have locked the rows to prevent edit or delete after inserting the record by means of allowedits and allowdelete properties of the form.
How ever i need to change a field alone after insertion.Hence i wrote a piece of code in that control's key press event stating ,Raising the input box and getting the value and then im updating that field.
But after updating that field the entire record went to editable mode after i wrote the following code.

 a = InputBox("Enter the STATUS", "Option")
        Set db = CurrentDb()
        dsql = "UPDATE tbl_PaperTeam SET tbl_PaperTeam.Status = '" & a & "' WHERE tbl_PaperTeam.ID=" & txtID & ""
        db.Execute dsql, dbFailOnError
        Set db = Nothing
        MsgBox "Status Updated", vbCritical + vbOKOnly, "Error"
        Me.AllowEdits = No

After i executed the above code.all fields went to editable mode

So please assist me what should be done to make the entire record read only
after updating a single field

[pkstormy]

You could also have set the .locked property to true or false for each of the fields on the form versus the allowedits.

My guess is that somewhere (prior to the code you posted), the allowedits is set to true and that this code is possibly failing (I'm not sure since this is DAO and I code using ADO) where it then doesn't execute the me.allowedits = No (shouldn't it also be me.allowedits = False versus No?)

I would change the me.allowedits = No to me.allowedits = False and put in a message box after the me.allowedits = False (ie. msgbox "AllowEdits now set to False" and see if it popups up that message which would indicate that it executed the me.allowedits line of code.)

With using the Inputbox code, don't forget to also test if someone clicks the cancel button or doesn't enter any value and clicks ok.

[ganeshvenkatram]

You could also have set the .locked property to true or false for each of the fields on the form versus the allowedits.

My guess is that somewhere (prior to the code you posted), the allowedits is set to true and that this code is possibly failing (I'm not sure since this is DAO and I code using ADO) where it then doesn't execute the me.allowedits = No (shouldn't it also be me.allowedits = False versus No?)

I would change the me.allowedits = No to me.allowedits = False and put in a message box after the me.allowedits = False (ie. msgbox "AllowEdits now set to False" and see if it popups up that message which would indicate that it executed the me.allowedits line of code.)

With using the Inputbox code, don't forget to also test if someone clicks the cancel button or doesn't enter any value and clicks ok.

Thanks for ur response.

Here i cant use lock property , since if i set the locked property to yes, then i cant able to key in when inserting the new record and also if set and unset again when insert the entire records will be editable. So i cant go with that option.

I didnt set Allowedits to true anywhere in my code since its unworthy . Can u plz give me any other suggestion???

[pkstormy]

Since it sounds like you only want to update 1 field, here's 2 options I can think of:

1. Put a button on the form which opens a popup type form that has this 1 field and allows editing of it.

2. Write a function (in a module) which will update the record with whatever info. You again, could have a button which when clicked prompts for a value and then writes to the recordset.

There are some examples in the code repository. I can't recall which one but several of them show how to write functions in a module which updates records.