RE: New Records not created in a nested form having subforms

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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


"raylopez99" wrote:

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

.

Relevant Pages

  • Re: Queries involving subforms
    ... Are you saying you want to see ALL child rows for a given parent row? ... is there anyway to base the criteria for a query ... I have a main form with 1 subform, I want to make a query that returns ...
    (microsoft.public.access.queries)
  • RE: new record and form
    ... So i have both a subform and a query embedded in my ... anything in the link master nor in the child master field. ... new open form BUT the subforms or query inside my new form still have ...
    (microsoft.public.access.gettingstarted)
  • the expression is typed incorrectly... error link child fields
    ... I have a form that is based on a query and returns all the items. ... subform is also based on a query and it returns all the orders. ... try to link the orders (Child) to the Items (Parent) using the link ...
    (microsoft.public.access.forms)
  • Re: Questioning my form design.
    ... I will try what you said with the subform. ... To answer your question of why do I want display customer info in the ... If you need a query with criteria from both tables, ... Storing data redundantly in the child table is NOTHING BUT TROUBLE and will be ...
    (microsoft.public.access.formscoding)
  • Re: ODBC LINK giving error with filter
    ... I have a main form with subform. ... Combo box which is filled with a query. ... When I do the child / master I get an ambiguous message: ...
    (microsoft.public.access.queries)