How to not allow edits on record?

[Guest]

Hi all

I'm hoping someone out there can help me with this. I have a form with a subform where once the record is "Finalized" i do not want to allow the user to go back and edit or delete the record. I have it so you can't edit the main form. But using the below code in the ONCurrent event for the subform records what happens is you cant edit any records whether the updateinv flag is true or false - I think because its finding true on some of the records so it makes the code below say no edits. What I need to do is for this Receipt only if the updateinv flag is true then don't allow edits. I've tried differtn ways but it either seems to make all records editable or all records not editable. How can apply the criteria to the below
I thought maybe trying to do it at the detail level of the record but I didn't see an appropriate event

Set dbs = CurrentD
Set rst = dbs.OpenRecordset("tblReceiptsLine"

blUpdateInv = rst.Fields("UpdateInv").Valu

If blUpdateInv = True The
Me.AllowEdits = Fals
Els
Me.AllowEdits = Tru
End I

Any help is appreciated
Set dbs = Nothing

 

[Guest]

REVISION to Original
I thought I had it so if the Finalized flag was pressed you couldn't update the current record. BUT it won't let you update any record - why does this On Current find other records in the table? How can I filter to just this record finalized? i.e based on this receipt no
Thanks so much in advance

 

[Dirk Goldgar]

Hi all,

I'm hoping someone out there can help me with this. I have a form
with a subform where once the record is "Finalized" i do not want to
allow the user to go back and edit or delete the record. I have it
so you can't edit the main form. But using the below code in the
ONCurrent event for the subform records what happens is you cant edit
any records whether the updateinv flag is true or false - I think
because its finding true on some of the records so it makes the code
below say no edits. What I need to do is for this Receipt only if
the updateinv flag is true then don't allow edits. I've tried
differtn ways but it either seems to make all records editable or all
records not editable. How can apply the criteria to the below? I
thought maybe trying to do it at the detail level of the record but I
didn't see an appropriate event.

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblReceiptsLine")

blUpdateInv = rst.Fields("UpdateInv").Value

If blUpdateInv = True Then
Me.AllowEdits = False
Else
Me.AllowEdits = True
End If

Any help is appreciated!
Set dbs = Nothing

The recordset you are opening appears to be based on the whole table,
tblReceiptsLine, without any criteria or search to find a record
corresponding to the current subform record. So you will always be
setting AllowEdits based on the value of the UpdateInv field in whatever
happens to be the first record in that recordset -- probably the one
with the lowest value for its primary key.

But I'm a little confused. Is this "tblReceiptsLine" the base table for
the subform? If so, don't you have access to the UpdateInv field
directly in that form, so that your code in the subform's Current event
would be:

Private Sub Form_Current()

Me.AllowEdits = Not (Me.UpdateInv)

End Sub

? Or have I misunderstood the setup and what you are trying to achieve?
Which record is marked as Finalized by the UpdateInv field, the
subform's record or the main form's record?

 

[Guest]

Hi
Hopefully I'm not too late with this thread...I wasn't able to look at yesterday...
Sorry for the confusion between my msg's. On the Main form I use a command button which updates the Main table Reciepts Finalized to true and the ReceiptLines for that record UpdateINv to True. When these flags are true I don't want the user to be able to edit the record (as qty has been written to inventory with the finalize cmd button).
I use the flag to determine if the record is editable or not. i.e. If the Receipt (main tbl) has a True Finalized flag the line items (receiptlines) shouldn't be updated

I tried to use a select query for the recordset with the criteria of just that record but then I get a data type mismatch error - but I don't know why. ReceiptNo is a number field

("Select * from ReceiptsLine where tblReceiptsLine.ReceiptNo = ' & Me!ReceiptNo & ' ")

Any ideas

Thanks for your help.
Take care
Shawn

 

[Dirk Goldgar]

Hi,
Hopefully I'm not too late with this thread...I wasn't able to look
at yesterday....
Sorry for the confusion between my msg's. On the Main form I use a
command button which updates the Main table Reciepts Finalized to
true and the ReceiptLines for that record UpdateINv to True. When
these flags are true I don't want the user to be able to edit the
record (as qty has been written to inventory with the finalize cmd
button). I use the flag to determine if the record is editable or
not. i.e. If the Receipt (main tbl) has a True Finalized flag the
line items (receiptlines) shouldn't be updated.

I tried to use a select query for the recordset with the criteria of
just that record but then I get a data type mismatch error - but I
don't know why. ReceiptNo is a number field.

("Select * from ReceiptsLine where tblReceiptsLine.ReceiptNo = ' &
Me!ReceiptNo & ' ")

Any ideas?

Last things first. I don't know what you're trying to use this
query for or how it relates to the problem you've been desribing, but if
ReceiptNo is a number field you have to take out the single-quotes. You
also have to fix the double-quoting around the string expression. Try
this -- if in fact you end up needing this query:

("Select * from ReceiptsLine where tblReceiptsLine.ReceiptNo = " & _
Me!ReceiptNo)

Now to the main point. From your description -- though of course there
may be more to it than you've told me -- I don't see any reason to have
the field UpdateInv in tblReceiptsLine at all. My understanding from
what you've said so far is that, logically, the Finalized field (or
whatever its name is) in the main table ("tblReceipts", is it?) is the
sole determiner of whether the related records in tblReceiptsLines are
updatable or not. If the Finalized field in the main table is True, the
related records in the subform should be editable; if not, the subform
should be uneditable.

If this is true, you could define a function like this in the General
section of the main form's code module:

'----- start of code -----
Private Sub SetSubformEditability()

With Me.sfReceiptsLines.Form
If Me.Finalized Then
.AllowEdits = False
.AllowAdditions = False
.AllowDeletions = False
Else
.AllowEdits = True
.AllowAdditions = True
.AllowDeletions = True
End If
End With

End Sub
'----- end of code -----

You have to substitute the name of your subform control (the control on
the main form that displays the subform, which may or may not be the
name of the form object it displays) for "sfReceiptsLines" in the above
code.

You would then call this function in the Current event of the main form
....

'----- start of event procedure code -----
Private Sub Form_Current()

SetSubformEditability

End Sub
'----- end of event procedure code -----

.... and also as part of any code you execute that changes the value of
the Finalized field for the current record; for example, your command
button that finalizes the receipts.