Lock Bound Control

[dee]

I will try to explain this as clearly as I can!

I have a MainForm and a SubForm.

MainForm contains a bound combo box control, PptNo, in addition to Date and
Time controls. It also contains a NewRecord button that provides the user
with a new, blank MainForm, from which they select the PptNo, fill in the
Date and Time. When they hit Tab after entering the Time, an append query
runs that populates the SubForm.

All of this works very well.

Except - I don't want users to be able to select another PptNo from the
combo box once they have generated a MainForm/SubForm for an individual, as
this will obviously assign the SubForm data to another (wrong) PptNo.

I DO want the users to be able to modify the Date and Time in the MainForm,
and also be able to modify the data in the SubForm.

I have tried Allen Brown's form locking and it worked, but doesn't exactly
suit my needs. I tried all kinds of other things, but am stuck.

This is what I would like:
To have the PptNo control automatically lock once the initial form (data
entry) has been completed for the Ppt, so that no edits can be made to that
control.

I managed to do this through my trials and errors, but then when I click the
New Record button, it won't give me a new blank form in which to input
another Ppt's data.

Is there some way that, when I click on the New Record button, that the
PptNo combo unlocks, allows the selection of a PptNo, the input of Date and
Time and the append query to run, and then re-lock the PptNo combo box?

I tried adding code, but kept getting errors.

Thanks so much for any advice and help!

 

[Arvin Meyer [MVP]]

In the after update event try something like:

Sub PptNo_AfterUpdate()
If Len(Me.PptNo & vbNullString) = 0 Then
Me.PptNo.Locked = True
Me.PptNo.Enabled = False
End If
End Sub

Then in the form's current event:

Sub Form_Current()
If Len(Me.PptNo & vbNullString) > 0 Then
Me.PptNo.Locked = False
Me.PptNo.Enabled = True
End If
End Sub

 

[dee]

Hi there,

Thanks so much for your response. I tried your suggestion, but the ppt
combo still doesn't lock.

Any other ideas?
--
Thanks!

Dee

In the after update event try something like:

Sub PptNo_AfterUpdate()
If Len(Me.PptNo & vbNullString) = 0 Then
Me.PptNo.Locked = True
Me.PptNo.Enabled = False
End If
End Sub

Then in the form's current event:

Sub Form_Current()
If Len(Me.PptNo & vbNullString) > 0 Then
Me.PptNo.Locked = False
Me.PptNo.Enabled = True
End If
End Sub

 

[Arvin Meyer [MVP]]

I made 2 errors which is what can happen when you type code in a post. Try
this:

In the after update event:

Sub PptNo_AfterUpdate()
If Len(Me.PptNo & vbNullString) > 0 Then
Me.PptNo.Locked = True
End If
End Sub

Then in the form's current event:

Sub Form_Current()
If Len(Me.PptNo & vbNullString) = 0 Then
Me.PptNo.Locked = False
End If
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi there,

Thanks so much for your response. I tried your suggestion, but the ppt
combo still doesn't lock.

Any other ideas?
--
Thanks!

Dee

 

[dee]

Thanks - that seems to be working well.

The only problem I have now is that I can no longer click the Undo cmd
button I had added to the MainForm, that undid any changes I made to the
sub-form before moving to the next record.

If you have any suggestions on how to add code to Undo that makes the PPt
control locked = False, that would be great.

I'm almost there - thanks to you! I appreciate it!
--
Thanks!

Dee

 

[Arvin Meyer [MVP]]

I'm not sure what the code in that button may have looked like, but you
can't simply undo or not save a record once you've moved off the subform. If
you can see the ID of the record, you can delete it, you should be able to
easily find it if you have an incrementing autonumber.

DMax("ID", "Mytable", "ID = " & Me.ID)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[dee]

Hi again,

The Undo button was created by choosing Undo Record as the action when
creating a command button. The code is:
On Error GoTo Err_Command29_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

Exit_Command29_Click:
Exit Sub

Err_Command29_Click:
MsgBox Err.Description
Resume Exit_Command29_Click


It, indeed is used before moving away from the sub-form, but allows users to
undo anything in the sub-form before moving on. It was working fine, but when
locking the ppt combo, it no long works. That was why I was hoping to add
some code that would instruct to unlock the ppt combo, do the Undo and then
relock.

Does this make any sense? Thanks so much!
--
Thanks!

Dee

 

[Arvin Meyer [MVP]]

The undo code is old, but since you already have it working, just use:

Me.PptNo.Locked = False
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Me.PptNo.Locked = True
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[dee]

Hi there,

Thanks so much for all of your help. I tried this and it gave me the
message that Undo wasn't available at the moment.

I will create another form and try it - I've been trying so many things in
this one.

Thanks again.
--
Thanks!

Dee