before update event

[Lori]

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 W. Vinson]

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

 

[tina]

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]

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?

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

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 W. Vinson]

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?

 

[Lori]

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]

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?

 

[Lori]

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.

 

[Lori]

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.

 

[Lori]

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?

 

[John W. Vinson]

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.