Subform Control

[ladybug via AccessMonster.com]

I have two cmd buttons. One to allow users to add, edit, and delete data
from "frmView." The other button to not allow users to add, edit, and delete
data from "frmView." The one where I allow I just have the properties to
allow this.

I have the following code in the cmd button where I do not want to allow
users to add, edit, and delete data:
On Error GoTo Err_Command15_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmView"

stLinkCriteria = "[Number]=" & Me![cboSearch]
DoCmd.OpenForm stDocName, , , stLinkCriteria
With Forms(stDocName)
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

There are multiple subforms on frmView. This code does not allow users to
add, edit, or delete from "frmView." However,the subforms still allow you to
edit, add, and delete. Can someone help me with the code above and how to
add the subforms? A couple examples of the subform names are "sfrmProducts"
and "sfrmClosed"

 

[Jeanette Cunningham]

Hi ladybug,
an easy way to open a form and prevent edits is to open it in read only
mode.
Read only is part of DoCmd.OpenForm

DoCmd.OpenForm "NameOfForm", , , , acReadOnly

With the subforms, you can set their allow edits etc to false like this:

With Me.[NameOfSubformControl].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

You must use the name of the subform control, not the name of the subform
that is inside the control.
Sometimes both the subform control and the subform have the same name,
sometimes they don't.

You could use the above code for each sub form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[ladybug via AccessMonster.com]

You are saying to put this code in each subform correct? I can't do that,
because this form (frmView) is opened from two different command buttons.
One command button opens the form and subforms as is. The form and subform
in properties is set to allow for additions and edits.
In the other button, I need it to open like you said in Read Only. I was
able to get the main form (frmView) to not allow edits. However, now I just
need to add my subforms in the code.

I tried the other suggestions before and this is what I have:
Dim varDocName As Variant
Dim stLinkCriteria As String
Dim varFormNames As Variant

varDocName = "frmView"

stLinkCriteria = "[Number]=" & Me![cboSearch]
DoCmd.OpenForm varDocName, , , stLinkCriteria
varFormNames = Array("frmView", "sfrmadverseeventview", "sfrmcategories",
_
"sfrmclosed")
For Each varDocName In varFormNames
With Forms(varDocName)
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

I get this error: Database can't find the form 'sfrmadverseeventview'
referred to in a macro expression or Visual Basic Code. The form exists so I
am fairly certain my code is not correct.
Thank you for your suggestion. Do you have any suggestions on how to make
the code above work?

Hi ladybug,
an easy way to open a form and prevent edits is to open it in read only
mode.
Read only is part of DoCmd.OpenForm

DoCmd.OpenForm "NameOfForm", , , , acReadOnly

With the subforms, you can set their allow edits etc to false like this:

With Me.[NameOfSubformControl].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

You must use the name of the subform control, not the name of the subform
that is inside the control.
Sometimes both the subform control and the subform have the same name,
sometimes they don't.

You could use the above code for each sub form.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have two cmd buttons. One to allow users to add, edit, and delete data
from "frmView." The other button to not allow users to add, edit, and

[quoted text clipped - 26 lines]

"sfrmProducts"
and "sfrmClosed"

 

[Jeanette Cunningham]

The code I suggested goes on the main form, frmView.
I am not familiar with using arrays, so I will keep to my suggestion and use
it like this instead of the code that uses arrays.

Try this code and see if you get any errors.
Comment out the code that starts at *Dim varDocName As Variant* and ends at
*End With*
and try the code I suggest.
The code below will be easier for you to debug.
Errors could appear if the particular subform is not loaded.
Errors could also appear if the name of the subform control is not used.


With Me.[sfrmadverseeventview].Form
.AllowEdits = False
AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmcategories].Form
.AllowEdits = False
AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmclosed].Form
.AllowEdits = False
AllowDeletions = False
.AllowAdditions = False
End With

You are saying to put this code in each subform correct? I can't do that,
because this form (frmView) is opened from two different command buttons.
One command button opens the form and subforms as is. The form and
subform
in properties is set to allow for additions and edits.
In the other button, I need it to open like you said in Read Only. I was
able to get the main form (frmView) to not allow edits. However, now I
just
need to add my subforms in the code.

I tried the other suggestions before and this is what I have:
Dim varDocName As Variant
Dim stLinkCriteria As String
Dim varFormNames As Variant

varDocName = "frmView"

stLinkCriteria = "[Number]=" & Me![cboSearch]
DoCmd.OpenForm varDocName, , , stLinkCriteria
varFormNames = Array("frmView", "sfrmadverseeventview",
"sfrmcategories",
_
"sfrmclosed")
For Each varDocName In varFormNames
With Forms(varDocName)
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

I get this error: Database can't find the form 'sfrmadverseeventview'
referred to in a macro expression or Visual Basic Code. The form exists
so I
am fairly certain my code is not correct.
Thank you for your suggestion. Do you have any suggestions on how to make
the code above work?

Hi ladybug,
an easy way to open a form and prevent edits is to open it in read only
mode.
Read only is part of DoCmd.OpenForm

DoCmd.OpenForm "NameOfForm", , , , acReadOnly

With the subforms, you can set their allow edits etc to false like this:

With Me.[NameOfSubformControl].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

You must use the name of the subform control, not the name of the subform
that is inside the control.
Sometimes both the subform control and the subform have the same name,
sometimes they don't.

You could use the above code for each sub form.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have two cmd buttons. One to allow users to add, edit, and delete data
from "frmView." The other button to not allow users to add, edit, and

[quoted text clipped - 26 lines]

"sfrmProducts"
and "sfrmClosed"

 

[ladybug via AccessMonster.com]

I can't put that in frmView. If I do that then when I open it using the
other command button (which should allow edits) it won't.
That is why I am putting the code in the button that opens frmView.

I tried to manipulate your code within the one that I have in the command
button that opens frmView and not allow edits.

Here is what I have:
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmView"

stLinkCriteria = "[Number]=" & Me![cboSearch]
DoCmd.OpenForm stDocName, , , stLinkCriteria
With Forms(stDocName)
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmAdverseEventView].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmCategories].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmClosed].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

When I run it I get this error:
Database can't find the field "I" referred to in your expression. Anymore
thoughts? Thank you for your help!

The code I suggested goes on the main form, frmView.
I am not familiar with using arrays, so I will keep to my suggestion and use
it like this instead of the code that uses arrays.

Try this code and see if you get any errors.
Comment out the code that starts at *Dim varDocName As Variant* and ends at
*End With*
and try the code I suggest.
The code below will be easier for you to debug.
Errors could appear if the particular subform is not loaded.
Errors could also appear if the name of the subform control is not used.

With Me.[sfrmadverseeventview].Form
.AllowEdits = False
AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmcategories].Form
.AllowEdits = False
AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmclosed].Form
.AllowEdits = False
AllowDeletions = False
.AllowAdditions = False
End With

You are saying to put this code in each subform correct? I can't do that,
because this form (frmView) is opened from two different command buttons.

[quoted text clipped - 62 lines]

 

[Jeanette Cunningham]

We need to add some more code.
When your form is in edit mode we need to tell it not to run the code for
the subforms, but when it has edits not allowed, then run the code for the
subforms.

Something like this on frmView, for the Load event.

If Me.AllowEdits = FalseThen
With Me.[sfrmAdverseEventView].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmCategories].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmClosed].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With
Else
'do nothing
End If

Try that and see how it goes.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I can't put that in frmView. If I do that then when I open it using the
other command button (which should allow edits) it won't.
That is why I am putting the code in the button that opens frmView.

I tried to manipulate your code within the one that I have in the command
button that opens frmView and not allow edits.

Here is what I have:
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmView"

stLinkCriteria = "[Number]=" & Me![cboSearch]
DoCmd.OpenForm stDocName, , , stLinkCriteria
With Forms(stDocName)
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmAdverseEventView].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmCategories].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmClosed].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

When I run it I get this error:
Database can't find the field "I" referred to in your expression. Anymore
thoughts? Thank you for your help!

The code I suggested goes on the main form, frmView.
I am not familiar with using arrays, so I will keep to my suggestion and
use
it like this instead of the code that uses arrays.

Try this code and see if you get any errors.
Comment out the code that starts at *Dim varDocName As Variant* and ends
at
*End With*
and try the code I suggest.
The code below will be easier for you to debug.
Errors could appear if the particular subform is not loaded.
Errors could also appear if the name of the subform control is not used.

With Me.[sfrmadverseeventview].Form
.AllowEdits = False
AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmcategories].Form
.AllowEdits = False
AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmclosed].Form
.AllowEdits = False
AllowDeletions = False
.AllowAdditions = False
End With

You are saying to put this code in each subform correct? I can't do
that,
because this form (frmView) is opened from two different command
buttons.

[quoted text clipped - 62 lines]

"sfrmProducts"
and "sfrmClosed"

 

[ladybug via AccessMonster.com]

First off I really want to thank you for being patient with me. I really
appreciate it.

So I left my code in the "Unable to edit" button as this:
On Error GoTo Err_Command15_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmView"

stLinkCriteria = "[Number]=" & Me![cboSearch]
DoCmd.OpenForm stDocName, , , stLinkCriteria
With Forms(stDocName)
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

This works still to not allow edits, additions, etc., on frmView (but not the
subforms)

Now I tried your code in frmView event on load and first it gave me a compile
error and highlighted If Me.AllowEdits = FalseThen
I determined that I need to put a space between FalseThen. It seemed to like
that (no error). However, it still is allowing me to edit, add, and delete
in the subforms. Any other suggestions?

We need to add some more code.
When your form is in edit mode we need to tell it not to run the code for
the subforms, but when it has edits not allowed, then run the code for the
subforms.

Something like this on frmView, for the Load event.

If Me.AllowEdits = FalseThen
With Me.[sfrmAdverseEventView].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmCategories].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmClosed].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With
Else
'do nothing
End If

Try that and see how it goes.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I can't put that in frmView. If I do that then when I open it using the
other command button (which should allow edits) it won't.

[quoted text clipped - 78 lines]

 

[Jeanette Cunningham]

Hi,
we are getting closer.
We need to move the code from the load event on frmView and put it with the
other code for the "Unable to edit" button.
Something like this:

Private sub Command15_Click
On Error GoTo Err_Command15_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmView"

stLinkCriteria = "[Number]=" & Me![cboSearch]
DoCmd.OpenForm stDocName, , , stLinkCriteria
With Forms(stDocName)
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmAdverseEventView].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmCategories].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmClosed].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

End Sub

Try that and see how it goes.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

First off I really want to thank you for being patient with me. I really
appreciate it.

So I left my code in the "Unable to edit" button as this:
On Error GoTo Err_Command15_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmView"

stLinkCriteria = "[Number]=" & Me![cboSearch]
DoCmd.OpenForm stDocName, , , stLinkCriteria
With Forms(stDocName)
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

This works still to not allow edits, additions, etc., on frmView (but not
the
subforms)

Now I tried your code in frmView event on load and first it gave me a
compile
error and highlighted If Me.AllowEdits = FalseThen
I determined that I need to put a space between FalseThen. It seemed to
like
that (no error). However, it still is allowing me to edit, add, and
delete
in the subforms. Any other suggestions?

We need to add some more code.
When your form is in edit mode we need to tell it not to run the code for
the subforms, but when it has edits not allowed, then run the code for the
subforms.

Something like this on frmView, for the Load event.

If Me.AllowEdits = FalseThen
With Me.[sfrmAdverseEventView].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmCategories].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmClosed].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With
Else
'do nothing
End If

Try that and see how it goes.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I can't put that in frmView. If I do that then when I open it using the
other command button (which should allow edits) it won't.

[quoted text clipped - 78 lines]

"sfrmProducts"
and "sfrmClosed"

 

[ladybug via AccessMonster.com]

Ok, I removed the code from the on load event and put your code in the unable
to edit button. I am back to this error:
Database can't find the field "I" referred to in your expression.

Hi,
we are getting closer.
We need to move the code from the load event on frmView and put it with the
other code for the "Unable to edit" button.
Something like this:

Private sub Command15_Click
On Error GoTo Err_Command15_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmView"

stLinkCriteria = "[Number]=" & Me![cboSearch]
DoCmd.OpenForm stDocName, , , stLinkCriteria
With Forms(stDocName)
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmAdverseEventView].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmCategories].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmClosed].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

End Sub

Try that and see how it goes.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

First off I really want to thank you for being patient with me. I really
appreciate it.

[quoted text clipped - 66 lines]

 

[Linq Adams via AccessMonster.com]

Something very basic is being missed here, unless I overlooked it in scanning.

Access handles AllowEdits/AllowDeletions/AllowAdditions differently when
you're dealing with a form/subform.

The first thing you have to understand is that how these properties are set
on the form being used as a subform means absolutely nothing! If you run the
form independently, they will perform as expected. But if the form is used as
a subform, they are completely ignored! The subform "container" is actually a
control on the main form, and as such, it is ruled by how these properties
are set on the main form.

If AllowDeletions is set to No on the main form, fields cannot be deleted on
the main form or on the subform. And to make things really confusing, since
the subform is considered to be a control on the main form, if AllowEdits is
set to No on the main form, records on the subform cannot be deleted! This
is true even if AllowDeletions is set to Yes on the main form ! That's
because Access doesn't consider what you're doing as "deleting a record" on
the subform, but rather as trying to "edit a field in a record" on the main
form!

So, to summarize, to be able to delete a record in a subform, the AllowEdits
and the AllowDeletions properties must be set to Yes on the main form.

 

[Jeanette Cunningham]

Would you post the actual line of the code where that error message appears?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Ok, I removed the code from the on load event and put your code in the
unable
to edit button. I am back to this error:
Database can't find the field "I" referred to in your expression.

Hi,
we are getting closer.
We need to move the code from the load event on frmView and put it with
the
other code for the "Unable to edit" button.
Something like this:

Private sub Command15_Click
On Error GoTo Err_Command15_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmView"

stLinkCriteria = "[Number]=" & Me![cboSearch]
DoCmd.OpenForm stDocName, , , stLinkCriteria
With Forms(stDocName)
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmAdverseEventView].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmCategories].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

With Me.[sfrmClosed].Form
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
End With

End Sub

Try that and see how it goes.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

First off I really want to thank you for being patient with me. I
really
appreciate it.

[quoted text clipped - 66 lines]

"sfrmProducts"
and "sfrmClosed"