> Okay there is a cyclic dependency here. The form's RecordSource depends on a[color=blue]
> value in the form, and the form doesn't get its value until the RecordSource
> is loaded.[/color]
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).
[color=blue]
> 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).[/color]
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.
[color=blue]
> 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.[/color]
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'.
[color=blue]
> 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.[/color]
Can we be sure it is because the record source is dependant on a value
from the form?
[color=blue]
> There are several workarounds. One would be to programmatically assign a
> value to the RecordSource of the form after txtDate changes.[/color]
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'.
[color=blue]
> Another would
> be to leave AllowAdditions to Yes, and cancel Form_BeforeInsert event to
> prevent any new records.[/color]
This would prevent any new records being added, but it would still
display the unsighly 'new blank record'.
[color=blue]
> 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).[/color]
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" <AllenBrowne@SeeSig.Invalid> wrote in message news:<40f7b550$0$1293$5a62ac22@per-qv1-newsreader-01.iinet.net.au>...[color=blue]
> 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" <thisiscro@hotmail.com> wrote in message
> news:2497786f.0407160119.268b2d12@posting.google.c om...[color=green]
> >
> > 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.
> >[color=darkred]
> > > Are you certain you have not set the Data Entry property of the form to[/color][/color]
> Yes[color=green][color=darkred]
> > > as well? That will prevent existing records being loaded.[/color]
> >
> > Data Entry is and always has been set to 'No'.
> >[color=darkred]
> > > Alternatively, is anything filtering the form?[/color]
> >
> > Nothing is or ever has been filtering the form.
> >[color=darkred]
> > > Or, is there anything in the SQL statement that is depending on a value[/color][/color]
> in[color=green][color=darkred]
> > > the form? Or perhaps a calculated field that could have its data type
> > > misunderstood?[/color]
> >
> > 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" <AllenBrowne@SeeSig.Invalid> wrote in message[/color]
> news:<40f54243$0$1294$5a62ac22@per-qv1-newsreader-01.iinet.net.au>...[color=green][color=darkred]
> > > Something else is wrong here.
> > >
> > > Are you certain you have not set the Data Entry property of the form to[/color][/color]
> Yes[color=green][color=darkred]
> > > 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[/color][/color]
> in[color=green][color=darkred]
> > > the form? Or perhaps a calculated field that could have its data type
> > > misunderstood?
> > >
> > >
> > > "Cro" <thisiscro@hotmail.com> wrote in message
> > > news:2497786f.0407140547.48c39397@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[/color][/color][/color]
