Preventing edits on a form after record creation

[Theguyinthehat]

I'm designing my database to keep a queue of lots run--However, I am afraid that using my "Job Entry" Form (there's also a job working, for when the lot is run, and Job Completed Form, for after the job has finished), those logging the jobs will accidently edit previous lotnumbers, etcetera.
On the Job Working and Job Completed forms, I've locked all the fields except Lot Number. However, when one tries to create a new job, the first record appears, and I'm afraid that people will overwrite pre-existing jobs. How do I lock the record so they can't overwrite? If this involves locking the entire record, how do I unlock the record later when they edit it the null fields with Job Working and Job Completed?

 

[boblarson]

In the form's ON CURRENT event put

Me.AllowEdits = Me.NewRecord

and then use a button to enable edits so they know specifically that they want to have edits.

Me.AllowEdits = True

 

[Theguyinthehat]

Worked. Thanks!

 

[Kryst51]

Hey Bob!

I used this code here in an example for someone, but now the lookup combo box I put on the form won't work. Is there code I can use to just make this lookup work, even when the rest of the controls are not editable?

 

[boblarson]

Hey Bob!

I used this code here in an example for someone, but now the lookup combo box I put on the form won't work. Is there code I can use to just make this lookup work, even when the rest of the controls are not editable?

You would need to lock the controls individually (by using something like this):

Dim ctl As Control

For Each ctl In Me.Controls
  If ctl.Name <> "Whatever control you don't want to lock" Then
     Select Case ctl.ControlType
     Case acTextBox, acCheckBox, acComboBox, acListBox
         ctl.Locked = True
     End Select
  End If
Next ctl

Or you can put a tag in each control you want to lock and use:

Dim ctl As Control

For Each ctl In Me.Controls
   ctl.Locked = (ctl.Tag = "Whatever")
Next ctl

Some controls you can't lock like labels, so that is why you need one or the other.

And this can go in the On Current event.

 

[Kryst51]

Hi Bob, Thanks for responding, I tried your first suggestion, and though it will now let me make a selection it will not go to that order number. (NEVERMIND TO THIS AS WHEN I CHANGED THE COMBO BOX NAME, THE CODE WAS STILL UNDER THE OLD NAME, THIS NOW WORKS.) Also, how do I use this in conjunction with the code at the beginning of the thread so that when I click a button I am able to edit the record, as well as add a new record. Would it help to add something in the if part of the code similar to "AND record is not a new record" Then lock the controls. To take care of the lock on new records?

The second option throws up runtime error 438 and won't let me go into the nondesign view of the form. When I click debug it highlights the line:

ctl.Locked = (ctl.Tag = "Locked")

Where Locked is the tag I used.

 

[boblarson]

To check for a new record you can use

If Not Me.NewRecord Then.... etc.

Go to debug when that error occurs and in the immediate window type:


?ctl.Name

to find out which control is having an issue. It might be you put the tag on a label which doesn't have a locked property, or some other control similar.

 

[Kryst51]

Ok.... Everything works now except for the edit button. The code I have
on the On click event is

Me.AllowEdits = True

An error doesn't pop up, it just doesn't do anything. I've tried putting the same code for locking the controls into the on click event, and changing "locked" from true to false hoping that it would work, but get a compile error: Next without for, and it highlights the "Next" in the next ctl part of the code. Is my idea to use this code correct? Or is there something else I should be using?

 

[boblarson]

You can't use the Allow Edits as you're using the other method. You would need to use the same code as before but change it for True:

Dim ctl As Control

For Each ctl In Me.Controls
   ctl.Locked = Not (ctl.Tag = "Whatever")
Next ctl

 

[Kryst51]

Ok, I did that and it is throwing up the runtime error. So I put in the immediate the
?ctl.name and it came up with a label name. Which does not have any tag on it.

I am mightily confused.

 

[boblarson]

Okay, change your code to add

If ctl.Tag = "Locked" Then
...put everything else here
End If

so it bypasses anything without the tag. I thought it would with the other code, but was apparently mistaken.

 

[Kryst51]

Yes!!! (in Napoleon Dyanamite style, with a Mary Catherine Gallagher from SNL type stance in the middle of my office.)

Awesome!!!! Thank you!!!