|
|
I have a form and subform. In the subform, I want to allow entry only if one
of the fields in the main form has one of two values. This is the code I put
in the BeforeUpdate event of the subform but nothing happens. Any
suggestions would be great - especially if there was a way I could disable
the location field in the subform. The subform is in tabular form for
multiple records (not datasheet) Thank you.
If (Me.Parent![Location] = "MSH" Or Me.Parent![Location] = "SSH") And
([Location] = " " Or [Location] = Null) Then
MsgBox ("With Still Hunt, Location Must be Entered Here")
Cancel = True
End If
If (Me.Parent![Location] <> "MSH" And Me.Parent![Location] <> "SSH") And
([Location] <> " " And [Location] <> Null) Then
MsgBox ("Location Can Only Be Entered Here with Still Hunt")
Cancel = True
End If
|
|
On Sun, 14 Dec 2008 10:39:00 -0800, Lori <Lori[ at ]discussions.microsoft.com>
wrote:
[Quoted Text] >I have a form and subform. In the subform, I want to allow entry only if one
>of the fields in the main form has one of two values. This is the code I put
>in the BeforeUpdate event of the subform but nothing happens. Any
>suggestions would be great - especially if there was a way I could disable
>the location field in the subform. The subform is in tabular form for
>multiple records (not datasheet) Thank you.
>
>If (Me.Parent![Location] = "MSH" Or Me.Parent![Location] = "SSH") And
>([Location] = " " Or [Location] = Null) Then
> MsgBox ("With Still Hunt, Location Must be Entered Here")
> Cancel = True
>End If
>If (Me.Parent![Location] <> "MSH" And Me.Parent![Location] <> "SSH") And
>([Location] <> " " And [Location] <> Null) Then
> MsgBox ("Location Can Only Be Entered Here with Still Hunt")
> Cancel = True
>End If
NULL is a funny beast: it means "this value is undefined, uninitialized,
unknown". As such, nothing is equal to NULL, or even UNequal to NULL. So your
expressions [Location] = NULL and [Location] <> NULL are neither true nor
false, but NULL!
Use the IsNull() function instead, or concatenate a null string and compare
with a null string:
If (Me.Parent![Location] = "MSH" Or Me.Parent![Location] = "SSH") And
([Location] & "" = "") Then
MsgBox ("With Still Hunt, Location Must be Entered Here")
Cancel = True
End If
If (Me.Parent![Location] <> "MSH" And Me.Parent![Location] <> "SSH") And
([Location] & "" <> "") Then
MsgBox ("Location Can Only Be Entered Here with Still Hunt")
Cancel = True
--
John W. Vinson [MVP]
|
|
to control the subform update, try
If (Me.Parent!Location = "MSH" Or Me.Parent!Location = "SSH") Then
If (Me!Location & "") = "" Then
MsgBox ("With Still Hunt, Location Must be Entered Here")
Cancel = True
End If
ElseIf (Me!Location & "") <> "" Then
MsgBox ("Location Can Only Be Entered Here with Still Hunt")
Cancel = True
End If
to enable/disable the Location control in the subform, try adding the
following code to the subform control's Enter event (within the mainform),
as
Me!SubformControlName.Form!Location.Enabled = (Me!Location = "MSH" Or
Me!Location = "SSH")
replace SubformControlName with the name of the subform control within the
mainform. if you're not sure what i mean, see
http://home.att.net/~california.db/instructions.html, and click the
"SubformControlName" link for instructions on how to determine the subform
control name.
you need to consider if there's a possibility that the user may edit the
value of Location in the mainform, *without* moving into the subform. in
that scenario, the above code does nothing to enforce your business rule
"With Still Hunt, Location Must be Entered Here".
hth
"Lori" <Lori[ at ]discussions.microsoft.com> wrote in message
news:19FCCDBB-3E43-4DFA-B6F5-FE21C94CF421[ at ]microsoft.com...
[Quoted Text] > I have a form and subform. In the subform, I want to allow entry only if
one
> of the fields in the main form has one of two values. This is the code I
put
> in the BeforeUpdate event of the subform but nothing happens. Any
> suggestions would be great - especially if there was a way I could disable
> the location field in the subform. The subform is in tabular form for
> multiple records (not datasheet) Thank you.
>
> If (Me.Parent![Location] = "MSH" Or Me.Parent![Location] = "SSH") And
> ([Location] = " " Or [Location] = Null) Then
> MsgBox ("With Still Hunt, Location Must be Entered Here")
> Cancel = True
> End If
> If (Me.Parent![Location] <> "MSH" And Me.Parent![Location] <> "SSH") And
> ([Location] <> " " And [Location] <> Null) Then
> MsgBox ("Location Can Only Be Entered Here with Still Hunt")
> Cancel = True
> End If
|
|
John and Tina,
Thank you for your replies. I tried putting both in my Before Update event
procedure for the subform and they didn't work. I was able to enter a
Location even though the value of the Location on the main form was not MSH
or SSH. I tried putting Msgbox("in Before Update") in the event procedure
just to make sure I was there and this did not appear. Is it possible it's
not going into this event?
"tina" wrote:
[Quoted Text] > to control the subform update, try > > If (Me.Parent!Location = "MSH" Or Me.Parent!Location = "SSH") Then > If (Me!Location & "") = "" Then > MsgBox ("With Still Hunt, Location Must be Entered Here") > Cancel = True > End If > ElseIf (Me!Location & "") <> "" Then > MsgBox ("Location Can Only Be Entered Here with Still Hunt") > Cancel = True > End If > > to enable/disable the Location control in the subform, try adding the > following code to the subform control's Enter event (within the mainform), > as > > Me!SubformControlName.Form!Location.Enabled = (Me!Location = "MSH" Or > Me!Location = "SSH") > > replace SubformControlName with the name of the subform control within the > mainform. if you're not sure what i mean, see > http://home.att.net/~california.db/instructions.html, and click the > "SubformControlName" link for instructions on how to determine the subform > control name. > > you need to consider if there's a possibility that the user may edit the > value of Location in the mainform, *without* moving into the subform. in > that scenario, the above code does nothing to enforce your business rule > "With Still Hunt, Location Must be Entered Here". > > hth > > > "Lori" <Lori[ at ]discussions.microsoft.com> wrote in message > news:19FCCDBB-3E43-4DFA-B6F5-FE21C94CF421[ at ]microsoft.com... > > I have a form and subform. In the subform, I want to allow entry only if > one > > of the fields in the main form has one of two values. This is the code I > put > > in the BeforeUpdate event of the subform but nothing happens. Any > > suggestions would be great - especially if there was a way I could disable > > the location field in the subform. The subform is in tabular form for > > multiple records (not datasheet) Thank you. > > > > If (Me.Parent![Location] = "MSH" Or Me.Parent![Location] = "SSH") And > > ([Location] = " " Or [Location] = Null) Then > > MsgBox ("With Still Hunt, Location Must be Entered Here") > > Cancel = True > > End If > > If (Me.Parent![Location] <> "MSH" And Me.Parent![Location] <> "SSH") And > > ([Location] <> " " And [Location] <> Null) Then > > MsgBox ("Location Can Only Be Entered Here with Still Hunt") > > Cancel = True > > End If > > > |
|
On Mon, 15 Dec 2008 13:54:03 -0800, Lori <Lori[ at ]discussions.microsoft.com>
wrote:
[Quoted Text] >John and Tina,
>
>Thank you for your replies. I tried putting both in my Before Update event
>procedure for the subform and they didn't work. I was able to enter a
>Location even though the value of the Location on the main form was not MSH
>or SSH. I tried putting Msgbox("in Before Update") in the event procedure
>just to make sure I was there and this did not appear. Is it possible it's
>not going into this event?
The BeforeUpdate event will fire only if the subform is actually updated (by
dirtying some bound control and either moving off that record, setting focus
to the mainform, or closing the form). Are you doing so?
--
John W. Vinson [MVP]
|
|
I believe I am. It's in tabular form and I tab through each field, entering
data. When I get to the last field on the form, it moves to a new record
without any issues, even when I put in invalid data. When I added a msgbox
"in before update", this did not display either. Does Before Update work
with new records or only existing records that are being changed?
"John W. Vinson" wrote:
[Quoted Text] > On Mon, 15 Dec 2008 13:54:03 -0800, Lori <Lori[ at ]discussions.microsoft.com>
> wrote:
>
> >John and Tina,
> >
> >Thank you for your replies. I tried putting both in my Before Update event
> >procedure for the subform and they didn't work. I was able to enter a
> >Location even though the value of the Location on the main form was not MSH
> >or SSH. I tried putting Msgbox("in Before Update") in the event procedure
> >just to make sure I was there and this did not appear. Is it possible it's
> >not going into this event?
>
> The BeforeUpdate event will fire only if the subform is actually updated (by
> dirtying some bound control and either moving off that record, setting focus
> to the mainform, or closing the form). Are you doing so?
> --
>
> John W. Vinson [MVP]
>
|
|
On Mon, 15 Dec 2008 16:39:01 -0800, Lori <Lori[ at ]discussions.microsoft.com>
wrote:
[Quoted Text] >I believe I am. It's in tabular form and I tab through each field, entering
>data. When I get to the last field on the form, it moves to a new record
>without any issues, even when I put in invalid data. When I added a msgbox
>"in before update", this did not display either. Does Before Update work
>with new records or only existing records that are being changed?
It works in both, just so long as data *is in fact changed*. Just tabbing
through won't do so. There's something fishy here! What's the Recordsource of
the form? Please post the SQL if it's a query. And please post the complete
BeforeUpdate event code. This *is* the Form's BeforeUpdate, not just the
BeforeUpdate of the textbox, right?
--
John W. Vinson [MVP]
|
|
This is the code in the BeforeUpdate Event of my subform:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If (Me.Parent![Location] = "MSH" Or Me.Parent![Location] = "SSH") And
([Location] & "" = "") Then
MsgBox ("With Still Hunt, Location Must be Entered Here")
Cancel = True
End If
If (Me.Parent![Location] <> "MSH" And Me.Parent![Location] <> "SSH") And
([Location] & "" <> "") Then
MsgBox ("Location Can Only Be Entered Here with Still Hunt")
Cancel = True
End If
End Sub
The RecordSource for the form is my Game Stats table.
Yes, it's definitely the Form BeforeUpdate.
I appreciate how much time you're spending helping me on this. Thank you.
"John W. Vinson" wrote:
[Quoted Text] > On Mon, 15 Dec 2008 16:39:01 -0800, Lori <Lori[ at ]discussions.microsoft.com>
> wrote:
>
> >I believe I am. It's in tabular form and I tab through each field, entering
> >data. When I get to the last field on the form, it moves to a new record
> >without any issues, even when I put in invalid data. When I added a msgbox
> >"in before update", this did not display either. Does Before Update work
> >with new records or only existing records that are being changed?
>
> It works in both, just so long as data *is in fact changed*. Just tabbing
> through won't do so. There's something fishy here! What's the Recordsource of
> the form? Please post the SQL if it's a query. And please post the complete
> BeforeUpdate event code. This *is* the Form's BeforeUpdate, not just the
> BeforeUpdate of the textbox, right?
> --
>
> John W. Vinson [MVP]
>
|
|
For some odd reason, I went back into the form to check something else and
thought I'd give it one more try to see if my code reasoning might be wrong.
Of course, this time it worked perfectly!!! I don't think I changed
anything but I must have somewhere. So, thanks for the help and it looks
like I'm set on this issue for now.
"John W. Vinson" wrote:
[Quoted Text] > On Mon, 15 Dec 2008 16:39:01 -0800, Lori <Lori[ at ]discussions.microsoft.com>
> wrote:
>
> >I believe I am. It's in tabular form and I tab through each field, entering
> >data. When I get to the last field on the form, it moves to a new record
> >without any issues, even when I put in invalid data. When I added a msgbox
> >"in before update", this did not display either. Does Before Update work
> >with new records or only existing records that are being changed?
>
> It works in both, just so long as data *is in fact changed*. Just tabbing
> through won't do so. There's something fishy here! What's the Recordsource of
> the form? Please post the SQL if it's a query. And please post the complete
> BeforeUpdate event code. This *is* the Form's BeforeUpdate, not just the
> BeforeUpdate of the textbox, right?
> --
>
> John W. Vinson [MVP]
>
|
|
I enabled macro's for something else I was working on. Would this have done
it? Is there any reason, on a dedicated computer, not to have macros always
enabled?
"Lori" wrote:
[Quoted Text] > For some odd reason, I went back into the form to check something else and
> thought I'd give it one more try to see if my code reasoning might be wrong.
> Of course, this time it worked perfectly!!! I don't think I changed
> anything but I must have somewhere. So, thanks for the help and it looks
> like I'm set on this issue for now.
>
>
> "John W. Vinson" wrote:
>
> > On Mon, 15 Dec 2008 16:39:01 -0800, Lori <Lori[ at ]discussions.microsoft.com>
> > wrote:
> >
> > >I believe I am. It's in tabular form and I tab through each field, entering
> > >data. When I get to the last field on the form, it moves to a new record
> > >without any issues, even when I put in invalid data. When I added a msgbox
> > >"in before update", this did not display either. Does Before Update work
> > >with new records or only existing records that are being changed?
> >
> > It works in both, just so long as data *is in fact changed*. Just tabbing
> > through won't do so. There's something fishy here! What's the Recordsource of
> > the form? Please post the SQL if it's a query. And please post the complete
> > BeforeUpdate event code. This *is* the Form's BeforeUpdate, not just the
> > BeforeUpdate of the textbox, right?
> > --
> >
> > John W. Vinson [MVP]
> >
|
|
On Tue, 16 Dec 2008 11:03:00 -0800, Lori <Lori[ at ]discussions.microsoft.com>
wrote:
[Quoted Text] >I enabled macro's for something else I was working on. Would this have done
>it? Is there any reason, on a dedicated computer, not to have macros always
>enabled?
That's a business/security decision on your part. Microsoft disables macros
out of fear - since a macro or VBA code could (if written to do so) delete or
overwrite arbitrary files on your disk, for example. If you trust your code
and trust everyone who can meddle with the code, I don't see why not.
--
John W. Vinson [MVP]
|
|
|