Access 2000 Subform Creating New Record

[JMB]

I have a subform in Access 2000 that is linked to a main form via a common
field (one to one relationship) where if the user accidentally uses the Page
Down key on the subform, the subform creates a new record. The subform is on
a tab page. There are several tabs on the main form and all contain a
subform. None of the other subforms create a new record when the Page Down
key is pressed, so I do not know why this particular form is causing
problems. Any ideas?

 

[Dirk Goldgar]

(re-posting, as my original reply hasn't appeared)

I have a subform in Access 2000 that is linked to a main form via a common
field (one to one relationship) where if the user accidentally uses the
Page
Down key on the subform, the subform creates a new record. The subform is
on
a tab page. There are several tabs on the main form and all contain a
subform. None of the other subforms create a new record when the Page
Down
key is pressed, so I do not know why this particular form is causing
problems. Any ideas?

My guess would be that there is code in the subform's Current event, or
possibly some other event, that is assigning a value to one of the fields on
the subform. Hence, every time the "new" record on the subform gets the
focus, it is dirtied and will be saved when the user next presses {Page
Down}, which will naturally move to the next record.

That's just a guess, of course.

 

[JMB]

I do have an event that is assigning a value to one of the fields on the
subform if it meets certain criteria. I need this even to 'happen' - how do
I fix the form to keep from adding a new record?

 

[Dirk Goldgar]

I do have an event that is assigning a value to one of the fields on the
subform if it meets certain criteria. I need this even to 'happen' - how
do
I fix the form to keep from adding a new record?

First, can you determine that this event procedure is the culprit? Would it
be executed and set the field even on a new record? If you remove it, does
the problem go away?

Without seeing the code, or even knowing what event you're using, it's hard
to know exactly what to change in the event procedure to keep it from
affecting a new record, but allow it to affect a record once you've started
editing it. Possibly some "If" condition that checks Me.NewRecord and maybe
Me.Dirty to see what the status is. If you can't figure out a solution,
post the code and a description of its intent, and I'll see if I can come up
with something.

 

[JMB]

I removed all references to all events from the subform - saved it and the
subform still creates a new record when pressing the Page Down Key, so I am
assuming it is not the code.

There are several fields that are coded On Enter - On Exit - On Got Focus
and On Lost Focus Events. What I am trying to accomplish (and it does
work!!!) is to Lock the field when a value of "Approved" is selected and to
stay Unlocked when other values are selected or if the field is Null. Here's
the code I am using on each one of the above mentioned events and this is
working correctly.

Private Sub cmb_CurrentROMStage_Enter()
If ([cmb_CurrentROMStage]) = "Approved" Then
Me![cmb_CurrentROMStage].Locked = True
End If
If ([cmb_CurrentROMStage]) = Null Then
Me![cmb_CurrentROMStage].Locked = False
End If
If ([cmb_CurrentROMStage]) = "Estimating" Then
Me![cmb_CurrentROMStage].Locked = False
End If
If ([cmb_CurrentROMStage]) = "Completed" Then
Me![cmb_CurrentROMStage].Locked = False
End If
If ([cmb_CurrentROMStage]) = "N/A" Then
Me![cmb_CurrentROMStage].Locked = False
End If

End Sub

=====
Is there a way that I could code the On Key Down event to NOT create a new
record? Would I have to do the coding on each field or could it just be done
at the form level? What would the code be?

THANKS!!

 

[Dirk Goldgar]

I removed all references to all events from the subform - saved it and the
subform still creates a new record when pressing the Page Down Key, so I
am
assuming it is not the code.

That would make sense, but I'm still not convinced.

There are several fields that are coded On Enter - On Exit - On Got Focus
and On Lost Focus Events. What I am trying to accomplish (and it does
work!!!) is to Lock the field when a value of "Approved" is selected and
to
stay Unlocked when other values are selected or if the field is Null.
Here's
the code I am using on each one of the above mentioned events and this is
working correctly.

Private Sub cmb_CurrentROMStage_Enter()
If ([cmb_CurrentROMStage]) = "Approved" Then
Me![cmb_CurrentROMStage].Locked = True
End If
If ([cmb_CurrentROMStage]) = Null Then
Me![cmb_CurrentROMStage].Locked = False
End If
If ([cmb_CurrentROMStage]) = "Estimating" Then
Me![cmb_CurrentROMStage].Locked = False
End If
If ([cmb_CurrentROMStage]) = "Completed" Then
Me![cmb_CurrentROMStage].Locked = False
End If
If ([cmb_CurrentROMStage]) = "N/A" Then
Me![cmb_CurrentROMStage].Locked = False
End If

End Sub

=====
Is there a way that I could code the On Key Down event to NOT create a new
record? Would I have to do the coding on each field or could it just be
done
at the form level? What would the code be?

The code above would *not* dirty the record, since it doesn't change the
value of any control, only the Locked property. The Enter event may not be
the right event for what you're doing, and this particular test:

If ([cmb_CurrentROMStage]) = Null Then

.... will never evaluate to True -- use "If IsNull(cmb_CurrentROMStage) Then"
instead -- but let's try to figure out what's causing the record-creation
before revising that.

Would you mind copy/pasting the whole, complete module code from the
subform? Maybe I can see something from that.

 

[JMB]

The complete module code is too long to post. Can I send to you personally?
--
JMB

I removed all references to all events from the subform - saved it and the
subform still creates a new record when pressing the Page Down Key, so I
am
assuming it is not the code.

That would make sense, but I'm still not convinced.

There are several fields that are coded On Enter - On Exit - On Got Focus
and On Lost Focus Events. What I am trying to accomplish (and it does
work!!!) is to Lock the field when a value of "Approved" is selected and
to
stay Unlocked when other values are selected or if the field is Null.
Here's
the code I am using on each one of the above mentioned events and this is
working correctly.

Private Sub cmb_CurrentROMStage_Enter()
If ([cmb_CurrentROMStage]) = "Approved" Then
Me![cmb_CurrentROMStage].Locked = True
End If
If ([cmb_CurrentROMStage]) = Null Then
Me![cmb_CurrentROMStage].Locked = False
End If
If ([cmb_CurrentROMStage]) = "Estimating" Then
Me![cmb_CurrentROMStage].Locked = False
End If
If ([cmb_CurrentROMStage]) = "Completed" Then
Me![cmb_CurrentROMStage].Locked = False
End If
If ([cmb_CurrentROMStage]) = "N/A" Then
Me![cmb_CurrentROMStage].Locked = False
End If

End Sub

=====
Is there a way that I could code the On Key Down event to NOT create a new
record? Would I have to do the coding on each field or could it just be
done
at the form level? What would the code be?

The code above would *not* dirty the record, since it doesn't change the
value of any control, only the Locked property. The Enter event may not be
the right event for what you're doing, and this particular test:

If ([cmb_CurrentROMStage]) = Null Then

.... will never evaluate to True -- use "If IsNull(cmb_CurrentROMStage) Then"
instead -- but let's try to figure out what's causing the record-creation
before revising that.

Would you mind copy/pasting the whole, complete module code from the
subform? Maybe I can see something from that.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

The complete module code is too long to post. Can I send to you
personally?

Normally I'd say no, but in the interest of sparing the newsgroup a whole
lot of unnecessary code, I'll say okay. Although it might be easier if you
can create a cut-down version of your database that contains only the
elements necessary to demonstrate the behavior, compacted and zipped to no
more than 3MB in size, and send that to me instead. Either way -- sending
just the code, or sending a cut-down demo DB -- you can send to the address
you get by removing "NO SPAM" and ".invalid" from the reply-address of this
message (or you can get the e-mail address from my website, as listed in my
sig, below).

Tell me in the message what I have to do to see the behavior, and I'll have
a look at it when time permits.

 

[JMB]

Just sent email to you with cut-down version of my database. Thanks!!