Continuous form - recordset issue

R

Rick

I have converted a 2003 .mdb to 2007 .accede format and have a problem with
one form.
It is the Client Index and is based upon a rolodex form I found some years
ago. Along the button of the form are buttons A ... Z and ALL.
This thing has worked fine, even in ACC2007, until last week when I must
have changed something and I can't figure out just what. For some reason the
(new record) in the underlying table is being brought in and displayed in
each and every button (A .. Z). By this I mean if there are 5 clients whose
last name start with A, clicking on the A button should display just the 5
clients (one line each). BUT, the (new record)
(or * - I don't know what it is called) is also displaying as the last line.
It is blank except for the fields that have a default value (when a new
record is created).
Does anyone know how I go about shutting off this 'feature'?

Thank you for your help.

.... rick
 
D

Dirk Goldgar

Rick said:
I have converted a 2003 .mdb to 2007 .accede format and have a problem with
one form.
It is the Client Index and is based upon a rolodex form I found some years
ago. Along the button of the form are buttons A ... Z and ALL.
This thing has worked fine, even in ACC2007, until last week when I must
have changed something and I can't figure out just what. For some reason
the (new record) in the underlying table is being brought in and displayed
in each and every button (A .. Z). By this I mean if there are 5 clients
whose last name start with A, clicking on the A button should display just
the 5 clients (one line each). BUT, the (new record)
(or * - I don't know what it is called) is also displaying as the last
line. It is blank except for the fields that have a default value (when a
new record is created).
Does anyone know how I go about shutting off this 'feature'?
Click to expand...


It's hard to say without seeing the details of the form, but the simple
answer would be that the form's AllowAdditions property is now set to Yes
when it should be set to No. But then, if you need to be able to add a new
record under some circumstances (using this form) , there has to be a way to
set AllowAdditions to Yes again.
 
R

Rick

Thank you Dirk.
The AllowAdditions property was set to NO (False). But, just for giggles I
turned every property ON, ran the program - still the record is there. So I
turned everything OFF and ran - same thing, record is still there.

Is there something beyond the property settings of the form that could be
interacting with the way it displays?

Thanks.

.... rick
 
D

Dirk Goldgar

Rick said:
Thank you Dirk.
The AllowAdditions property was set to NO (False). But, just for giggles I
turned every property ON, ran the program - still the record is there. So
I turned everything OFF and ran - same thing, record is still there.

Is there something beyond the property settings of the form that could be
interacting with the way it displays?
Click to expand...


First, are you sure you aren't dealing with a subform on the main form? The
subform, of course, has its own AllowAdditions property.

Second, it could well be that there is code (or macros) behind this form
that turn AllowAdditions on. Check whether the form -- or subform -- has a
code module, and if it does, check whether there's code in there that messes
with AllowAdditions.
 
R

Rick

This is a stand-alone form, no subforms, no macros - just VBA.
I have compared this form properties and VBA, line for line, with the same
form in another project. Identical. I have deleted the form from the current
project and imported it (and VBA) from the original. When run within the
current project the darn (new record) gets dragged in.

In the form load I create the recordsource as follows:
Private Sub Form_Load()
If gbInstID > 0 Then
Me.RecordSource = "SELECT tblClients.ClientID, tblClients.Active,
tblClients.RegistrationNumber, tblClients.LastName, tblClients.HomePhone,
tblClients.AlternatePhone, tblClients.FirstName, tblClients.BirthDate,
tblClients.InstID, tblClients.WC" _
& " FROM tblClients" _
& " WHERE tblClients.InstID = " & gbInstID _
& " ORDER BY tblClients.LastName, tblClients.FirstName;"
Me.Caption = "Client Index (" & gbInstName & ")"
Else 'management
Me.RecordSource = "SELECT tblClients.ClientID, tblClients.Active,
tblClients.RegistrationNumber, tblClients.LastName, tblClients.HomePhone,
tblClients.AlternatePhone, tblClients.FirstName, tblClients.BirthDate,
tblClients.InstID, tblClients.WC" _
& " FROM tblClients" _
& " ORDER BY tblClients.LastName, tblClients.FirstName;"
Me.Caption = "Client Index (All Institutions)"
Me!lblInstID.Visible = True
Me!txtInstID.Visible = True
Me!chkSortWC.Visible = True
Me!WC.Enabled = True
End If
End Sub

By the way, if I run the form by d/click in the navigation panel the ELSE '
management kicks in because the global variable "gbInstID" has not been set
and is thus zero. This displays the data in the continuous from correctly -
i.e. no (new record) is brought in.

Thanks for your assistance.

.... rick
----- Original Message -----
From: "Dirk Goldgar" <[email protected]>
Newsgroups: microsoft.public.access
Sent: Friday, April 04, 2008 3:47 PM
Subject: Re: Continuous form - recordset issue
 
R

Rick

OK, solved it.
If the recordset type is Dynaset it looks like you can't get away from
having the new record show up. I set it to Snapshot and this worked. Except
when the form is in 'management' view where I have to set the recordset type
to dynaset (there is one field management needs to update from this form)
and tell them not to click on the new record.
 
D

Dirk Goldgar

Rick said:
OK, solved it.
If the recordset type is Dynaset it looks like you can't get away from
having the new record show up. I set it to Snapshot and this worked.
Except when the form is in 'management' view where I have to set the
recordset type to dynaset (there is one field management needs to update
from this form) and tell them not to click on the new record.
Click to expand...


To me this doesn't explain what is going on, though it's a usable
workaround. I'm still trying to figure out what there could be about this
form that makes it behave this way.
 
A

aaron.kempf

it could be that they have a nearly-blank record in their table.

I reccomend 'not allowing nulls anywhere' to prevent that

-Aaron
 
Top