New Records not created in a nested form having subforms

[raylopez99]

This apparently is a common problem, but I can't quite grasp the
solution. In creating a form and subform (nested relationship), I
foolishly tried (being a beginner in Access forms, though I do have
some newbie experience coding in C# for dBs and know how to write a
SQL query, set up tables, etc) to access data from a outside table
from the subform nested inside the parent form. This apparently is
frowned upon by the default settings (I'm sure you can do it if you
code in Visual Basic the "OnLoad" property of the form perhaps).

I tried running a query that accessed the outside table, and this did
work in getting rid of the error messages, but now the form is "no new
records allowed" and will not allow new records to be created (you can
only edit existing records). BTW the offending data field in the
outside table is a primary key, if that matters.

Several questions.

First, in a subform, how do you access records from the parent (other
than writing a SQL query) in the Properties tab of the RecordSource of
the form (found in the upper left square of the form)? The parent,
not an external table (if that matters). Just curious. Do you have
to do an [Event Procedure] in the "OnLoad" event for the form or
subform? I say "other than writing a SQL query" because apparently (if
you read the below post after my signature) a SQL query is not
updatable, and is a sort of virtual table that presumeably will not
allow you to write a new record in any table queried by the query.

Second, and perhaps related, how can I quickly fix this problem and be
able to add New Records? I did get rid of the offending external
field, but still the database is "no new records"--how to solve this
without learning Visual Basic (I know C++, C# and a bit of Java, but
don't want to learn VB unless I have to--this is a one time Access dB
solution).

Thank you

RL

PS--I did check that "Data Entry" property was set to "yes" and "Allow
Additions" was set to "yes", but what's funny is that another "wizard
created" form/sub-form has these properties set to "no" and it works
fine, so these properties are not important in this particular problem


From another post a few years ago...


[USER HAVING PROBLEM CREATING NEW RECORDS IN A FORM THAT HAS
SUBFORMS...]

I have ensured that the properties of the form allow
addition of new records, and that all required fields
contain data.

Any ideas?

Thanks,

Todd

Nikos Yannacopoulos View profile
More options Mar 12 2004, 3:06 am

Todd,

Just guessing here... is your form's recordsource a query? If so, does
the
query contain several tables? If that's the case then chances are the
recordset (returned by the query...) is not updatable. To verify open
the
query in datasheet view and try to change or add a record, to see if
it lets
you do it.
If this is indeed the problem, then the reason why you get no error
message
is probably a SetWarnings action in a query / method in code.

HTH,
Nikos

 

[Klatuu]

The way to relate records in the subform the the parent records in the main
form is to use the Link Master Fields and Link Child Fields of the subform
control on the main form. You enter the name of the field or fields in the
parent record the relate to the child record in the Link Master Fields
property and the name of the field or fields in the child record that relate
to the parent record. This keeps the two in sync so that when you change
records in the main form, the related records show in the sub form.

As to not being able to add records, there are a couple of things. It is
possible when using a query as a record source that has more than one table
to create an uneditable recordset. If this is not the case, there are
properties in the form that determine whether you can add, delete, or edit.
Check those. If neither of those resolve the problem, post back with more
detail and let's see what might be causing the problem.
--
Dave Hargis, Microsoft Access MVP

This apparently is a common problem, but I can't quite grasp the
solution. In creating a form and subform (nested relationship), I
foolishly tried (being a beginner in Access forms, though I do have
some newbie experience coding in C# for dBs and know how to write a
SQL query, set up tables, etc) to access data from a outside table
from the subform nested inside the parent form. This apparently is
frowned upon by the default settings (I'm sure you can do it if you
code in Visual Basic the "OnLoad" property of the form perhaps).

I tried running a query that accessed the outside table, and this did
work in getting rid of the error messages, but now the form is "no new
records allowed" and will not allow new records to be created (you can
only edit existing records). BTW the offending data field in the
outside table is a primary key, if that matters.

Several questions.

First, in a subform, how do you access records from the parent (other
than writing a SQL query) in the Properties tab of the RecordSource of
the form (found in the upper left square of the form)? The parent,
not an external table (if that matters). Just curious. Do you have
to do an [Event Procedure] in the "OnLoad" event for the form or
subform? I say "other than writing a SQL query" because apparently (if
you read the below post after my signature) a SQL query is not
updatable, and is a sort of virtual table that presumeably will not
allow you to write a new record in any table queried by the query.

Second, and perhaps related, how can I quickly fix this problem and be
able to add New Records? I did get rid of the offending external
field, but still the database is "no new records"--how to solve this
without learning Visual Basic (I know C++, C# and a bit of Java, but
don't want to learn VB unless I have to--this is a one time Access dB
solution).

Thank you

RL

PS--I did check that "Data Entry" property was set to "yes" and "Allow
Additions" was set to "yes", but what's funny is that another "wizard
created" form/sub-form has these properties set to "no" and it works
fine, so these properties are not important in this particular problem


From another post a few years ago...


[USER HAVING PROBLEM CREATING NEW RECORDS IN A FORM THAT HAS
SUBFORMS...]

I have ensured that the properties of the form allow
addition of new records, and that all required fields
contain data.

Any ideas?

Thanks,

Todd

Nikos Yannacopoulos View profile
More options Mar 12 2004, 3:06 am

Todd,

Just guessing here... is your form's recordsource a query? If so, does
the
query contain several tables? If that's the case then chances are the
recordset (returned by the query...) is not updatable. To verify open
the
query in datasheet view and try to change or add a record, to see if
it lets
you do it.
If this is indeed the problem, then the reason why you get no error
message
is probably a SetWarnings action in a query / method in code.

HTH,
Nikos

 

[raylopez99]

Thanks for your help.

After reading some stuff on the net, and inbetween the lines, I
figured out a few things:

1/ The left upper corner box that has "Properties", with a small
black dot in the center when you click on it, is NOT the same thing as
you described below in "Link Master Fields and Link Child Fields of
the subform". I did not know this, thanks. To get to the latter, you
have to click on the entire subform outline and then right click to
get Properties, then proceed as before to find the right tab.

2/ It appears, as I suspected, that you cannot change data "upstream"
of the parent. That is, the data must be changed first "upstream" of
the parent, then it flows to the parent, then it can flow to the
subform. This is I believe the source of all my problems--I am trying
to create a primary key for a new record that is in a table outside
the parent (and outside the subform as well of course). If anybody
knows something to the contrary, that doesn't require heavy Visual
Basic (I'm trying to not touch any code in VB except trivial stuff
like locking a field to make it read only, changing color font
properties, etc), please let me know. I was under the mistaken
impression that a form is simply a way of entering data into a table--
it is not, it's more like what is known in C# and elsewhere as "Data
Binding", which in the .NET framework is done by a class DataSource.
So it's not a 'dumb' form that simply allows you to insert data into
fields in a table--because if it were, it would actually suit me
better. Instead, the Data Binding enforces relationships, such as not
duplicating primary keys, etc, and is "safer" at the expense of being
harder to configure.

3/ Never, ever do anything outside the Wizard (for my purposes--
remember I don't want to create customized VB code), unless it's as
simple as dragging and dropping a subform onto a form, or changing in
a trivial way the SQL query in the left upper corner box of a form in
Form View.

4/ the Master Field and Child Field is nothing more than (for the
most part) the relationship between Primary Key and Foreign key in a
Parent/Child relationship table.

Any comments welcome.

RL

 

[Klatuu]

--
Dave Hargis, Microsoft Access MVP

Thanks for your help.

After reading some stuff on the net, and inbetween the lines, I
figured out a few things:

1/ The left upper corner box that has "Properties", with a small
black dot in the center when you click on it, is NOT the same thing as
you described below in "Link Master Fields and Link Child Fields of
the subform". I did not know this, thanks. To get to the latter, you
have to click on the entire subform outline and then right click to
get Properties, then proceed as before to find the right tab.

Don't confuse a subform with a subform control. The subform control is a
control that provides the ability to embedd one form within another. The
actual form object is identified in the control's Source Object property.

2/ It appears, as I suspected, that you cannot change data "upstream"
of the parent. That is, the data must be changed first "upstream" of
the parent, then it flows to the parent, then it can flow to the
subform. This is I believe the source of all my problems--I am trying
to create a primary key for a new record that is in a table outside
the parent (and outside the subform as well of course). If anybody
knows something to the contrary, that doesn't require heavy Visual
Basic (I'm trying to not touch any code in VB except trivial stuff
like locking a field to make it read only, changing color font
properties, etc), please let me know. I was under the mistaken
impression that a form is simply a way of entering data into a table--
it is not, it's more like what is known in C# and elsewhere as "Data
Binding", which in the .NET framework is done by a class DataSource.
So it's not a 'dumb' form that simply allows you to insert data into
fields in a table--because if it were, it would actually suit me
better. Instead, the Data Binding enforces relationships, such as not
duplicating primary keys, etc, and is "safer" at the expense of being
harder to configure.

I would never recommend changing data "upstream". The form does not enforce
relationships. Neither do the Link Master/Child field properties.
Referential Integrity is defined in the Relationships window.

3/ Never, ever do anything outside the Wizard (for my purposes--
remember I don't want to create customized VB code), unless it's as
simple as dragging and dropping a subform onto a form, or changing in
a trivial way the SQL query in the left upper corner box of a form in
Form View.

Most professinals never do anything with a wizard.

4/ the Master Field and Child Field is nothing more than (for the
most part) the relationship between Primary Key and Foreign key in a
Parent/Child relationship table.

Not necessarily, but usually. But, doesn't this resolve the problem you
were having with relating the data in the form and subform?

Any comments welcome.

RL

 

[raylopez99]

Not necessarily, but usually.  But, doesn't this resolve the problem you
were having with relating the data in the form and subform?

Thanks for your help, it was worth it.

What I did was this: I browsed the web and concluded that my
architecture was messed up--not logically but flowwise, because, as
you say, you should never change data "upstream"--since relationships
might get messed up. So I rearranged the tables so that data flows
from parent to children better, made liberal use of Autonumbering and
long-integers/ Replication ID GUIDs (contrary to good database
practice, but that's ok), and made sure that data does not flow back
to parents but always flows parent to child. This was easy to do and
after setting up the proper relationships and using the wizards I was
able to set up a decent looking form with subforms, all inside of a
switchboard. I think I read that Access 2003 apparently allows up to
seven "nested" or tiered parent-child-grandchild relationships in form/
sub-form, which for me and most people is plenty good.

I also browsed the web and found stuff on how to add a "popup window"
to a switchboard, and how to "password protect" (very crudely, not
real protection) a button on a switchboard, so that each user of the
switchboard can have their own pseudo-password protected button, to
prevent casual snooping.

All in all, I see why Access is so popular--it allows, with very
little programming, if at all (the password VB code I got off the net--
so far I'm glad I haven't really had to do any Visual Basic
programming), for you to "rapid code" something very quickly--in my
case, a matter of hours with no real knowledge of Visual Basic.

This is the real value add of Access-the front end is very quick. If
you tried to do the same thing using VC++.NET or C#.NET you would have
to take longer--it can be done, since with Wizards the layout is
nearly the same (after all Microsoft makes both products) but it's
much more complicated in Visual Studio and many ways to mess up even
with IntelliSense autocompleting of programming keywords and objects.

The real fun stuff is the backend--doing the SQL queries, by hand or
using the graphical relationship arrows, and the Reports, which I
think should allow you to combine data from different tables much more
easily than with the Forms (since, after all, with a report you're not
entering data but simply reading it from a table) and that's what I'll
do next.


RL

 

[Klatuu]

Seems you are doing good research and making some sound decisions.

I would not agree with those who say using autonumbers for primary keys is a
bad idea. The one thing to keep in mind with autonumbers is that they should
have no intelligence. That is, the value should mean nothing other than to
relate tables.

Yes, with Access, you can create some pretty good applications with little
or no VBA, but VBA is not a difficult language to learn and it will open some
possibilities. There are some things that can be done better with VBA. For
example, using only Macros, error trapping is very limited.

Best of Luck.