'Allow Additions' Causing Unexpected Results

[Cro]

Dear Access Developers,

The 'Allow Additions' property of my form is causing unexpected
results.

I am developing a form that has its 'Default View' property set to
'Continuous Forms' and am displaying records that match an SQL
statement entered in the 'Record Source' property of the form.

The form behaves correctly and displays the records as expected. The
form also displays a 'blank record' after the last record to allow a
user to add a new record. I wish to remove this unwanted and unsightly
'blank record'. To do so, I set 'Allow Additions' to 'No'.

However, changing this property has the catastrophic effect of the
form showing no records at all. By simply adjusting the 'Allow
Additions' property back to 'Yes', the form behaves correctly once
more and shows all the expected records (as well as the unsightly
'new' record).

I understand that the detail section of a form will be completely
blank if a) there are no records to display and b) no new records can
be added. But there definitely are records to display so I am confused
as to why the records are not being displayed.

Surely my form should still show the expected records?

Do any Access Gurus know what the problem is? Could you be kind enough
to suggest a solution?

My only idea is to rebuild all of the functionality of my form in a
report. This wouldn't be ideal as some of the behavior of my form
wouldn't map well or easily to a report, so any other suggestions
would be welcomed.

Thank you for your help and expertise,

Cro

[Allen Browne]

Something else is wrong here.

Are you certain you have not set the Data Entry property of the form to Yes
as well? That will prevent existing records being loaded.

Alternatively, is anything filtering the form?

Or, is there anything in the SQL statement that is depending on a value in
the form? Or perhaps a calculated field that could have its data type
misunderstood?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Cro" <th*******@hotmail.com> wrote in message
news:24**************************@posting.google.c om...

Dear Access Developers,

The 'Allow Additions' property of my form is causing unexpected
results.

I am developing a form that has its 'Default View' property set to
'Continuous Forms' and am displaying records that match an SQL
statement entered in the 'Record Source' property of the form.

The form behaves correctly and displays the records as expected. The
form also displays a 'blank record' after the last record to allow a
user to add a new record. I wish to remove this unwanted and unsightly
'blank record'. To do so, I set 'Allow Additions' to 'No'.

However, changing this property has the catastrophic effect of the
form showing no records at all. By simply adjusting the 'Allow
Additions' property back to 'Yes', the form behaves correctly once
more and shows all the expected records (as well as the unsightly
'new' record).

I understand that the detail section of a form will be completely
blank if a) there are no records to display and b) no new records can
be added. But there definitely are records to display so I am confused
as to why the records are not being displayed.

Surely my form should still show the expected records?

Do any Access Gurus know what the problem is? Could you be kind enough
to suggest a solution?

My only idea is to rebuild all of the functionality of my form in a
report. This wouldn't be ideal as some of the behavior of my form
wouldn't map well or easily to a report, so any other suggestions
would be welcomed.

Thank you for your help and expertise,

Cro

[Cro]

Hi Allen,

Thanks for replying. It is nice to know that a developer who "knows
more about Access than any other developer in Australia" is helping
out a developer in England who can't fathom out the side effects of
the 'Allow Additions' form property.

Are you certain you have not set the Data Entry property of the form to Yes
as well? That will prevent existing records being loaded.
Data Entry is and always has been set to 'No'.
Alternatively, is anything filtering the form?
Nothing is or ever has been filtering the form.
Or, is there anything in the SQL statement that is depending on a value in
the form? Or perhaps a calculated field that could have its data type
misunderstood?
This is my SQL statement:

SELECT tblStaffHours.*
FROM tblStaffHours
WHERE (((Forms!frmStaffHours!txtDate)=tblStaffHours.Date ));

You can see that the SQL statement does rely on a value in the form,
namely a date value. The user can increment and decrement this date
value (as well as jump to today and tomorrow's date) with command
buttons. The 'On Click' event of these command buttons first adjusts
the value within the date text box ('txtDate') and performs a requery
on the form ("DoCmd.Requery"), so that the SQL statement (that is the
record source of the form) is re-invoked.

How would the fact that the SQL statement relies on something in the
form affect its behavior? This is especially confusing as the form
behaves correctly when 'Allow Additions' is set to 'Yes', but the form
behaves very differently (as described in the first post of this
thread) when 'Allow Additions' is set to 'No'.

Incidentally, I have a second form using this exact same concept
(adjusting 'txtDate' to load appropriate records) that works
perfectly. For this second form, I need to be able to add records and
so 'Allow Additions' is set to 'Yes'. This form also fails in the same
way when 'Allow Additions' is set to 'No'.

Thank you for your help with this problem. It is very much
appreciated.

Regards,

Cro

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40**********************@per-qv1-newsreader-01.iinet.net.au>... Something else is wrong here.

Are you certain you have not set the Data Entry property of the form to Yes
as well? That will prevent existing records being loaded.

Alternatively, is anything filtering the form?

Or, is there anything in the SQL statement that is depending on a value in
the form? Or perhaps a calculated field that could have its data type
misunderstood?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Cro" <th*******@hotmail.com> wrote in message
news:24**************************@posting.google.c om...

Dear Access Developers,

The 'Allow Additions' property of my form is causing unexpected
results.

I am developing a form that has its 'Default View' property set to
'Continuous Forms' and am displaying records that match an SQL
statement entered in the 'Record Source' property of the form.

The form behaves correctly and displays the records as expected. The
form also displays a 'blank record' after the last record to allow a
user to add a new record. I wish to remove this unwanted and unsightly
'blank record'. To do so, I set 'Allow Additions' to 'No'.

However, changing this property has the catastrophic effect of the
form showing no records at all. By simply adjusting the 'Allow
Additions' property back to 'Yes', the form behaves correctly once
more and shows all the expected records (as well as the unsightly
'new' record).

I understand that the detail section of a form will be completely
blank if a) there are no records to display and b) no new records can
be added. But there definitely are records to display so I am confused
as to why the records are not being displayed.

Surely my form should still show the expected records?

Do any Access Gurus know what the problem is? Could you be kind enough
to suggest a solution?

My only idea is to rebuild all of the functionality of my form in a
report. This wouldn't be ideal as some of the behavior of my form
wouldn't map well or easily to a report, so any other suggestions
would be welcomed.

Thank you for your help and expertise,

Cro

[GD]

Cro wrote:

I am developing a form that has its 'Default View' property set to
'Continuous Forms' and am displaying records that match an SQL
statement entered in the 'Record Source' property of the form.

The form behaves correctly and displays the records as expected. The
form also displays a 'blank record' after the last record to allow a
user to add a new record. I wish to remove this unwanted and unsightly
'blank record'. To do so, I set 'Allow Additions' to 'No'.

However, changing this property has the catastrophic effect of the
form showing no records at all.

Post the SQL statement.

[Allen Browne]

Okay there is a cyclic dependency here. The form's RecordSource depends on a
value in the form, and the form doesn't get its value until the RecordSource
is loaded.

Particularly, when AllowAdditions is No, it is possible that the form will
have no record at all (as distinct form having the New Record when
AllowAdditions is Yes). When you have no records at all in a form - not even
the new record - the entire detail section goes blank, and trying to refer
to one of the non-existent text boxes causes an error. In addition, there
are bugs in the way Access handles and displays the values in sections other
than the detail section, as discussed in:
http://allenbrowne.com/bug-06.html

So, it seems that the RecordSource's depenency on a value from the form is
preventing it from loading any records at all when AllowAdditions is No.

There are several workarounds. One would be to programmatically assign a
value to the RecordSource of the form after txtDate changes. Another would
be to leave AllowAdditions to Yes, and cancel Form_BeforeInsert event to
prevent any new records.

BTW, if you really do have a field named "Date", there is a good chance that
Access will get that confused as well. In VBA, Date is a reserved word (for
the system date).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Cro" <th*******@hotmail.com> wrote in message
news:24**************************@posting.google.c om...

Thanks for replying. It is nice to know that a developer who "knows
more about Access than any other developer in Australia" is helping
out a developer in England who can't fathom out the side effects of
the 'Allow Additions' form property.

Are you certain you have not set the Data Entry property of the form to Yes as well? That will prevent existing records being loaded.

Data Entry is and always has been set to 'No'.

Alternatively, is anything filtering the form?

Nothing is or ever has been filtering the form.

Or, is there anything in the SQL statement that is depending on a value in the form? Or perhaps a calculated field that could have its data type
misunderstood?

This is my SQL statement:

SELECT tblStaffHours.*
FROM tblStaffHours
WHERE (((Forms!frmStaffHours!txtDate)=tblStaffHours.Date ));

You can see that the SQL statement does rely on a value in the form,
namely a date value. The user can increment and decrement this date
value (as well as jump to today and tomorrow's date) with command
buttons. The 'On Click' event of these command buttons first adjusts
the value within the date text box ('txtDate') and performs a requery
on the form ("DoCmd.Requery"), so that the SQL statement (that is the
record source of the form) is re-invoked.

How would the fact that the SQL statement relies on something in the
form affect its behavior? This is especially confusing as the form
behaves correctly when 'Allow Additions' is set to 'Yes', but the form
behaves very differently (as described in the first post of this
thread) when 'Allow Additions' is set to 'No'.

Incidentally, I have a second form using this exact same concept
(adjusting 'txtDate' to load appropriate records) that works
perfectly. For this second form, I need to be able to add records and
so 'Allow Additions' is set to 'Yes'. This form also fails in the same
way when 'Allow Additions' is set to 'No'.

Thank you for your help with this problem. It is very much
appreciated.

Regards,

Cro

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<40**********************@per-qv1-newsreader-01.iinet.net.au>...

Something else is wrong here.

Are you certain you have not set the Data Entry property of the form to Yes as well? That will prevent existing records being loaded.

Alternatively, is anything filtering the form?

Or, is there anything in the SQL statement that is depending on a value in the form? Or perhaps a calculated field that could have its data type
misunderstood?
"Cro" <th*******@hotmail.com> wrote in message
news:24**************************@posting.google.c om...

Dear Access Developers,

The 'Allow Additions' property of my form is causing unexpected
results.

I am developing a form that has its 'Default View' property set to
'Continuous Forms' and am displaying records that match an SQL
statement entered in the 'Record Source' property of the form.

The form behaves correctly and displays the records as expected. The
form also displays a 'blank record' after the last record to allow a
user to add a new record. I wish to remove this unwanted and unsightly
'blank record'. To do so, I set 'Allow Additions' to 'No'.

However, changing this property has the catastrophic effect of the
form showing no records at all. By simply adjusting the 'Allow
Additions' property back to 'Yes', the form behaves correctly once
more and shows all the expected records (as well as the unsightly
'new' record).

I understand that the detail section of a form will be completely
blank if a) there are no records to display and b) no new records can
be added. But there definitely are records to display so I am confused
as to why the records are not being displayed.

Surely my form should still show the expected records?

Do any Access Gurus know what the problem is? Could you be kind enough
to suggest a solution?

My only idea is to rebuild all of the functionality of my form in a
report. This wouldn't be ideal as some of the behavior of my form
wouldn't map well or easily to a report, so any other suggestions
would be welcomed.

Thank you for your help and expertise,

Cro

[Cro]

GD <gy**********@jmjservices.com> wrote in message news:<EW***************@fe1.columbus.rr.com>...

Cro wrote:

I am developing a form that has its 'Default View' property set to
'Continuous Forms' and am displaying records that match an SQL
statement entered in the 'Record Source' property of the form.

The form behaves correctly and displays the records as expected. The
form also displays a 'blank record' after the last record to allow a
user to add a new record. I wish to remove this unwanted and unsightly
'blank record'. To do so, I set 'Allow Additions' to 'No'.

However, changing this property has the catastrophic effect of the
form showing no records at all.

Post the SQL statement.

Please see message 3 of this thread.

[Cro]

> Okay there is a cyclic dependency here. The form's RecordSource depends on a

value in the form, and the form doesn't get its value until the RecordSource
is loaded.
This is not a cyclic dependancy. The record source depends on the
value in a text box, but the value in the text box does not depend on
the record source. The value of the text box is dependant on a text
box value within another form (this other form is always open and
always has an appropriate data value).
Particularly, when AllowAdditions is No, it is possible that the form will
have no record at all (as distinct form having the New Record when
AllowAdditions is Yes).
I have tested the form with date values I know for certain contain
records. Again, when 'Allow Additions' is set to 'No' these records
are not displayed.
When you have no records at all in a form - not even
the new record - the entire detail section goes blank, and trying to refer
to one of the non-existent text boxes causes an error.
The text box that is used in the record source is in the header. It is
always present, regardless of whether there are records to display in
the detail section. Interestingly, when 'Allow Additions' is set to
'No', the value of this text box is completely blank. Why should
'Allow Additions' make the text box blank in this case? The value of
the text box relies on the value in another form (as mentioned
earlier) and this shouldn't be affected by 'Allow Additions'.
In addition, there
are bugs in the way Access handles and displays the values in sections other
than the detail section, as discussed in:
http://allenbrowne.com/bug-06.html

So, it seems that the RecordSource's depenency on a value from the form is
preventing it from loading any records at all when AllowAdditions is No.
Can we be sure it is because the record source is dependant on a value
from the form?
There are several workarounds. One would be to programmatically assign a
value to the RecordSource of the form after txtDate changes.
Unfortunately, as mentioned earluer, txtDate displays nothing (it is
blank). The controls that work perfectly when 'Allow Additions' is set
to 'Yes' do not work at all when 'Allow Additions' is set to 'No'.
Another would
be to leave AllowAdditions to Yes, and cancel Form_BeforeInsert event to
prevent any new records.
This would prevent any new records being added, but it would still
display the unsighly 'new blank record'.
BTW, if you really do have a field named "Date", there is a good chance that
Access will get that confused as well. In VBA, Date is a reserved word (for
the system date).
Thank you for the warning. I have changed the name of the field and it
has made no difference to the problem.

With this further information, what do you now suppose the problem is
and what would be a solution?

Thank you for the continued help with this problem.

Regards,

Cro

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40**********************@per-qv1-newsreader-01.iinet.net.au>... Okay there is a cyclic dependency here. The form's RecordSource depends on a
value in the form, and the form doesn't get its value until the RecordSource
is loaded.

Particularly, when AllowAdditions is No, it is possible that the form will
have no record at all (as distinct form having the New Record when
AllowAdditions is Yes). When you have no records at all in a form - not even
the new record - the entire detail section goes blank, and trying to refer
to one of the non-existent text boxes causes an error. In addition, there
are bugs in the way Access handles and displays the values in sections other
than the detail section, as discussed in:
http://allenbrowne.com/bug-06.html

So, it seems that the RecordSource's depenency on a value from the form is
preventing it from loading any records at all when AllowAdditions is No.

There are several workarounds. One would be to programmatically assign a
value to the RecordSource of the form after txtDate changes. Another would
be to leave AllowAdditions to Yes, and cancel Form_BeforeInsert event to
prevent any new records.

BTW, if you really do have a field named "Date", there is a good chance that
Access will get that confused as well. In VBA, Date is a reserved word (for
the system date).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Cro" <th*******@hotmail.com> wrote in message
news:24**************************@posting.google.c om...

Thanks for replying. It is nice to know that a developer who "knows
more about Access than any other developer in Australia" is helping
out a developer in England who can't fathom out the side effects of
the 'Allow Additions' form property.

Are you certain you have not set the Data Entry property of the form to Yes as well? That will prevent existing records being loaded.

Data Entry is and always has been set to 'No'.

Alternatively, is anything filtering the form?

Nothing is or ever has been filtering the form.

Or, is there anything in the SQL statement that is depending on a value in the form? Or perhaps a calculated field that could have its data type
misunderstood?

This is my SQL statement:

SELECT tblStaffHours.*
FROM tblStaffHours
WHERE (((Forms!frmStaffHours!txtDate)=tblStaffHours.Date ));

You can see that the SQL statement does rely on a value in the form,
namely a date value. The user can increment and decrement this date
value (as well as jump to today and tomorrow's date) with command
buttons. The 'On Click' event of these command buttons first adjusts
the value within the date text box ('txtDate') and performs a requery
on the form ("DoCmd.Requery"), so that the SQL statement (that is the
record source of the form) is re-invoked.

How would the fact that the SQL statement relies on something in the
form affect its behavior? This is especially confusing as the form
behaves correctly when 'Allow Additions' is set to 'Yes', but the form
behaves very differently (as described in the first post of this
thread) when 'Allow Additions' is set to 'No'.

Incidentally, I have a second form using this exact same concept
(adjusting 'txtDate' to load appropriate records) that works
perfectly. For this second form, I need to be able to add records and
so 'Allow Additions' is set to 'Yes'. This form also fails in the same
way when 'Allow Additions' is set to 'No'.

Thank you for your help with this problem. It is very much
appreciated.

Regards,

Cro

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<40**********************@per-qv1-newsreader-01.iinet.net.au>...

Something else is wrong here.

Are you certain you have not set the Data Entry property of the form to Yes as well? That will prevent existing records being loaded.

Alternatively, is anything filtering the form?

Or, is there anything in the SQL statement that is depending on a value in the form? Or perhaps a calculated field that could have its data type
misunderstood?
"Cro" <th*******@hotmail.com> wrote in message
news:24**************************@posting.google.c om...
> Dear Access Developers,
>
> The 'Allow Additions' property of my form is causing unexpected
> results.
>
> I am developing a form that has its 'Default View' property set to
> 'Continuous Forms' and am displaying records that match an SQL
> statement entered in the 'Record Source' property of the form.
>
> The form behaves correctly and displays the records as expected. The
> form also displays a 'blank record' after the last record to allow a
> user to add a new record. I wish to remove this unwanted and unsightly
> 'blank record'. To do so, I set 'Allow Additions' to 'No'.
>
> However, changing this property has the catastrophic effect of the
> form showing no records at all. By simply adjusting the 'Allow
> Additions' property back to 'Yes', the form behaves correctly once
> more and shows all the expected records (as well as the unsightly
> 'new' record).
>
> I understand that the detail section of a form will be completely
> blank if a) there are no records to display and b) no new records can
> be added. But there definitely are records to display so I am confused
> as to why the records are not being displayed.
>
> Surely my form should still show the expected records?
>
> Do any Access Gurus know what the problem is? Could you be kind enough
> to suggest a solution?
>
> My only idea is to rebuild all of the functionality of my form in a
> report. This wouldn't be ideal as some of the behavior of my form
> wouldn't map well or easily to a report, so any other suggestions
> would be welcomed.
>
> Thank you for your help and expertise,
>
> Cro