Query Adds Record, DoCmd.GoToRecord , , acNewRec Won"t...Why?

All,

I have a Query (SQL below) I can successfully use in datasheet mode to
add records to two linked tables (TBL_People and TBL_Answers). The
tables have a one-to-many relationship, linked by an ID with "enforce
referential integrity" set to yes in the relationship. "TBL_People"
is on the one side, "TBL_Answers" is on the many side.

I have a form (a sub-form, really) that uses the Query as it's data
source.

I want to add a button to my form to add a new record. (Due to the
number of sub-forms, I don't want to use the built-in nav buttons).

I expected to be able to use DoCmd.GoToRecord , , acNewRec behind
the button to add a new record.

When I try, I get an error that tells me I cannot add or change a
record because a related record is required in 'TBL People'.

I can, however, add a record if I open the form on it's own - not as a
sub form.

Any suggestions? I'm sure it's something simple...

Thanks,

Patrick

SELECT TBL_People.ClientNum, TBL_Answers.MatterNum,
TBL_Answers.MatterDesc, TBL_Answers.Position, TBL_People.ID,
TBL_Answers.TestatorID, TBL_People.PreTitle, TBL_People.Title,
TBL_People.LastName, TBL_People.FirstName, TBL_People.MiddleName,
TBL_People.MaidenName, TBL_People.Suffix, TBL_People.SufTitle,
TBL_People.Add_Nm, TBL_People.Dear_Nm, TBL_People.IndivInformalNm,
TBL_People.Gender, TBL_People.Address1, TBL_People.Address2,
TBL_People.City, TBL_People.State, TBL_People.County, TBL_People.Zip,
TBL_People.Country, TBL_People.EmailAddress, TBL_People.[2ndAddress1],
TBL_People.[2ndAddress2], TBL_People.[2ndCity], TBL_People.[2ndState],
TBL_People.[2ndCounty], TBL_People.[2ndZip], TBL_People.[2ndCountry],
TBL_People.Notes, TBL_Answers.Update_Date, TBL_People.C_Rel,
TBL_People.M_Rel, TBL_Answers.Delivery, TBL_Answers.Personal,
TBL_Answers.Closing
FROM TBL_People INNER JOIN TBL_Answers ON TBL_People.ID =
TBL_Answers.TestatorID
ORDER BY TBL_Answers.MatterNum, TBL_People.LastName,
TBL_People.FirstName;

Re: Query Adds Record, DoCmd.GoToRecord , , acNewRec Won"t...Why?

Open TBL_Answers in design view.
Select the TestatorID field.
In the lower pane, remove the 0 from the Default Value property.

If this does not solve the problem, you probably have several issues
interacting here. Merely moving to a new record should not cause it to start
filling anything in, so it should not complain at that point. This suggests
you have something that is dirtying the form unnecessarily, e.g. in the
Current event of the form, or perhaps its AfterUpdate event, or perhaps an
event of a control.

The best approach is to bind the main form to one table (e.g. TBL_People)
and the subform to a different table (e.g. TBL_Answers) rather than trying
to bind it to a query. This will avoid the issue of which table a new record
gets written to (which may ultimately be the cause of your problem here.)

--
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.

"Patrick A" wrote in message
news:ef747d42-d388-47b2-8fc6-fa73a2fe72da@f3g2000hsg.googleg roups.com...
>
> I have a Query (SQL below) I can successfully use in datasheet mode to
> add records to two linked tables (TBL_People and TBL_Answers). The
> tables have a one-to-many relationship, linked by an ID with "enforce
> referential integrity" set to yes in the relationship. "TBL_People"
> is on the one side, "TBL_Answers" is on the many side.
>
> I have a form (a sub-form, really) that uses the Query as it's data
> source.
>
> I want to add a button to my form to add a new record. (Due to the
> number of sub-forms, I don't want to use the built-in nav buttons).
>
> I expected to be able to use DoCmd.GoToRecord , , acNewRec behind
> the button to add a new record.
>
> When I try, I get an error that tells me I cannot add or change a
> record because a related record is required in 'TBL People'.
>
> I can, however, add a record if I open the form on it's own - not as a
> sub form.
>
> Any suggestions? I'm sure it's something simple...
>
> Thanks,
>
> Patrick
>
> SELECT TBL_People.ClientNum, TBL_Answers.MatterNum,
> TBL_Answers.MatterDesc, TBL_Answers.Position, TBL_People.ID,
> TBL_Answers.TestatorID, TBL_People.PreTitle, TBL_People.Title,
> TBL_People.LastName, TBL_People.FirstName, TBL_People.MiddleName,
> TBL_People.MaidenName, TBL_People.Suffix, TBL_People.SufTitle,
> TBL_People.Add_Nm, TBL_People.Dear_Nm, TBL_People.IndivInformalNm,
> TBL_People.Gender, TBL_People.Address1, TBL_People.Address2,
> TBL_People.City, TBL_People.State, TBL_People.County, TBL_People.Zip,
> TBL_People.Country, TBL_People.EmailAddress, TBL_People.[2ndAddress1],
> TBL_People.[2ndAddress2], TBL_People.[2ndCity], TBL_People.[2ndState],
> TBL_People.[2ndCounty], TBL_People.[2ndZip], TBL_People.[2ndCountry],
> TBL_People.Notes, TBL_Answers.Update_Date, TBL_People.C_Rel,
> TBL_People.M_Rel, TBL_Answers.Delivery, TBL_Answers.Personal,
> TBL_Answers.Closing
> FROM TBL_People INNER JOIN TBL_Answers ON TBL_People.ID =
> TBL_Answers.TestatorID
> ORDER BY TBL_Answers.MatterNum, TBL_People.LastName,
> TBL_People.FirstName;
>
>

Re: Query Adds Record, DoCmd.GoToRecord , , acNewRec Won"t...Why?

Thanks, Allen

I'll try what you described in terms of the Defaut Value field.

One bigger picture question if I may - the subform itself is fed by a
query that pulls from two tables, to enable the user to enter
information into two related tables at once.

Is this not a good idea? I'm not sure how else to do it and keep the
records aligned...

Patrick


On Nov 16, 8:17 pm, "Allen Browne" wrote:
> Open TBL_Answers in design view.
> Select the TestatorID field.
> In the lower pane, remove the 0 from the Default Value property.
>
> If this does not solve the problem, you probably have several issues
> interacting here. Merely moving to a new record should not cause it to start
> filling anything in, so it should not complain at that point. This suggests
> you have something that is dirtying the form unnecessarily, e.g. in the
> Current event of the form, or perhaps its AfterUpdate event, or perhaps an
> event of a control.
>
> The best approach is to bind the main form to one table (e.g. TBL_People)
> and the subform to a different table (e.g. TBL_Answers) rather than trying
> to bind it to a query. This will avoid the issue of which table a new record
> gets written to (which may ultimately be the cause of your problem here.)
>
> --
> 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.
>
> "Patrick A" wrote in message
>
> news:ef747d42-d388-47b2-8fc6-fa73a2fe72da@f3g2000hsg.googleg roups.com...
>
>
>
> > I have a Query (SQL below) I can successfully use in datasheet mode to
> > add records to two linked tables (TBL_People and TBL_Answers). The
> > tables have a one-to-many relationship, linked by an ID with "enforce
> > referential integrity" set to yes in the relationship. "TBL_People"
> > is on the one side, "TBL_Answers" is on the many side.
>
> > I have a form (a sub-form, really) that uses the Query as it's data
> > source.
>
> > I want to add a button to my form to add a new record. (Due to the
> > number of sub-forms, I don't want to use the built-in nav buttons).
>
> > I expected to be able to use DoCmd.GoToRecord , , acNewRec behind
> > the button to add a new record.
>
> > When I try, I get an error that tells me I cannot add or change a
> > record because a related record is required in 'TBL People'.
>
> > I can, however, add a record if I open the form on it's own - not as a
> > sub form.
>
> > Any suggestions? I'm sure it's something simple...
>
> > Thanks,
>
> > Patrick
>
> > SELECT TBL_People.ClientNum, TBL_Answers.MatterNum,
> > TBL_Answers.MatterDesc, TBL_Answers.Position, TBL_People.ID,
> > TBL_Answers.TestatorID, TBL_People.PreTitle, TBL_People.Title,
> > TBL_People.LastName, TBL_People.FirstName, TBL_People.MiddleName,
> > TBL_People.MaidenName, TBL_People.Suffix, TBL_People.SufTitle,
> > TBL_People.Add_Nm, TBL_People.Dear_Nm, TBL_People.IndivInformalNm,
> > TBL_People.Gender, TBL_People.Address1, TBL_People.Address2,
> > TBL_People.City, TBL_People.State, TBL_People.County, TBL_People.Zip,
> > TBL_People.Country, TBL_People.EmailAddress, TBL_People.[2ndAddress1],
> > TBL_People.[2ndAddress2], TBL_People.[2ndCity], TBL_People.[2ndState],
> > TBL_People.[2ndCounty], TBL_People.[2ndZip], TBL_People.[2ndCountry],
> > TBL_People.Notes, TBL_Answers.Update_Date, TBL_People.C_Rel,
> > TBL_People.M_Rel, TBL_Answers.Delivery, TBL_Answers.Personal,
> > TBL_Answers.Closing
> > FROM TBL_People INNER JOIN TBL_Answers ON TBL_People.ID =
> > TBL_Answers.TestatorID
> > ORDER BY TBL_Answers.MatterNum, TBL_People.LastName,
> > TBL_People.FirstName;

Re: Query Adds Record, DoCmd.GoToRecord , , acNewRec Won"t...Why?

In general, you will find it easiest to bind a form to just one table if you
want to add new records there. It's okay to add a look up table to get extra
fields to display, but not if you are trying to add fields to the 2nd table
only.

There are several alternatives, suitable for different scenarios. One
approach is to use a subsubform (i.e. a subform inside a subform.)

Another approach is to place 2 subforms on the main form, and set up the 2nd
one so it shows records related to the current record of the first subform.
The Northwind sample database that installs with Access has an example of
this: the form is named Customer Orders.

--
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.

"GM7" wrote in message
news:41ad234e-3a5b-4636-b523-9cb598597b9c@c29g2000hsa.google groups.com...
> Thanks, Allen
>
> I'll try what you described in terms of the Defaut Value field.
>
> One bigger picture question if I may - the subform itself is fed by a
> query that pulls from two tables, to enable the user to enter
> information into two related tables at once.
>
> Is this not a good idea? I'm not sure how else to do it and keep the
> records aligned...
>
> Patrick
>
>
> On Nov 16, 8:17 pm, "Allen Browne" wrote:
>> Open TBL_Answers in design view.
>> Select the TestatorID field.
>> In the lower pane, remove the 0 from the Default Value property.
>>
>> If this does not solve the problem, you probably have several issues
>> interacting here. Merely moving to a new record should not cause it to
>> start
>> filling anything in, so it should not complain at that point. This
>> suggests
>> you have something that is dirtying the form unnecessarily, e.g. in the
>> Current event of the form, or perhaps its AfterUpdate event, or perhaps
>> an
>> event of a control.
>>
>> The best approach is to bind the main form to one table (e.g. TBL_People)
>> and the subform to a different table (e.g. TBL_Answers) rather than
>> trying
>> to bind it to a query. This will avoid the issue of which table a new
>> record
>> gets written to (which may ultimately be the cause of your problem here.)
>>
>> "Patrick A" wrote in message
>>
>> news:ef747d42-d388-47b2-8fc6-fa73a2fe72da@f3g2000hsg.googleg roups.com...
>>
>> > I have a Query (SQL below) I can successfully use in datasheet mode to
>> > add records to two linked tables (TBL_People and TBL_Answers). The
>> > tables have a one-to-many relationship, linked by an ID with "enforce
>> > referential integrity" set to yes in the relationship. "TBL_People"
>> > is on the one side, "TBL_Answers" is on the many side.
>>
>> > I have a form (a sub-form, really) that uses the Query as it's data
>> > source.
>>
>> > I want to add a button to my form to add a new record. (Due to the
>> > number of sub-forms, I don't want to use the built-in nav buttons).
>>
>> > I expected to be able to use DoCmd.GoToRecord , , acNewRec behind
>> > the button to add a new record.
>>
>> > When I try, I get an error that tells me I cannot add or change a
>> > record because a related record is required in 'TBL People'.
>>
>> > I can, however, add a record if I open the form on it's own - not as a
>> > sub form.
>>
>> > Any suggestions? I'm sure it's something simple...
>>
>> > Thanks,
>>
>> > Patrick
>>
>> > SELECT TBL_People.ClientNum, TBL_Answers.MatterNum,
>> > TBL_Answers.MatterDesc, TBL_Answers.Position, TBL_People.ID,
>> > TBL_Answers.TestatorID, TBL_People.PreTitle, TBL_People.Title,
>> > TBL_People.LastName, TBL_People.FirstName, TBL_People.MiddleName,
>> > TBL_People.MaidenName, TBL_People.Suffix, TBL_People.SufTitle,
>> > TBL_People.Add_Nm, TBL_People.Dear_Nm, TBL_People.IndivInformalNm,
>> > TBL_People.Gender, TBL_People.Address1, TBL_People.Address2,
>> > TBL_People.City, TBL_People.State, TBL_People.County, TBL_People.Zip,
>> > TBL_People.Country, TBL_People.EmailAddress, TBL_People.[2ndAddress1],
>> > TBL_People.[2ndAddress2], TBL_People.[2ndCity], TBL_People.[2ndState],
>> > TBL_People.[2ndCounty], TBL_People.[2ndZip], TBL_People.[2ndCountry],
>> > TBL_People.Notes, TBL_Answers.Update_Date, TBL_People.C_Rel,
>> > TBL_People.M_Rel, TBL_Answers.Delivery, TBL_Answers.Personal,
>> > TBL_Answers.Closing
>> > FROM TBL_People INNER JOIN TBL_Answers ON TBL_People.ID =
>> > TBL_Answers.TestatorID
>> > ORDER BY TBL_Answers.MatterNum, TBL_People.LastName,
>> > TBL_People.FirstName;

Re: Query Adds Record, DoCmd.GoToRecord , , acNewRec Won"t...Why?

Allen,

Thanks for the advice. I thought I was being "efficient' doing things
the way I had, but I see your point.

If my two tables have a 1 to many relationship with referential
integrity being enforced, won't I get myself into a place where I
can't add records in one table at a time? Just thinking out loud,
I'll restructure and see.

Patrick

On Nov 17, 6:00 pm, "Allen Browne" wrote:
> In general, you will find it easiest to bind a form to just one table if you
> want to add new records there. It's okay to add a look up table to get extra
> fields to display, but not if you are trying to add fields to the 2nd table
> only.
>
> There are several alternatives, suitable for different scenarios. One
> approach is to use a subsubform (i.e. a subform inside a subform.)
>
> Another approach is to place 2 subforms on the main form, and set up the 2nd
> one so it shows records related to the current record of the first subform.
> The Northwind sample database that installs with Access has an example of
> this: the form is named Customer Orders.
>
> --
> 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.
>
> "GM7" wrote in message
>
> news:41ad234e-3a5b-4636-b523-9cb598597b9c@c29g2000hsa.google groups.com...
>
>
>
> > Thanks, Allen
>
> > I'll try what you described in terms of the Defaut Value field.
>
> > One bigger picture question if I may - the subform itself is fed by a
> > query that pulls from two tables, to enable the user to enter
> > information into two related tables at once.
>
> > Is this not a good idea? I'm not sure how else to do it and keep the
> > records aligned...
>
> > Patrick
>
> > On Nov 16, 8:17 pm, "Allen Browne" wrote:
> >> Open TBL_Answers in design view.
> >> Select the TestatorID field.
> >> In the lower pane, remove the 0 from the Default Value property.
>
> >> If this does not solve the problem, you probably have several issues
> >> interacting here. Merely moving to a new record should not cause it to
> >> start
> >> filling anything in, so it should not complain at that point. This
> >> suggests
> >> you have something that is dirtying the form unnecessarily, e.g. in the
> >> Current event of the form, or perhaps its AfterUpdate event, or perhaps
> >> an
> >> event of a control.
>
> >> The best approach is to bind the main form to one table (e.g. TBL_People)
> >> and the subform to a different table (e.g. TBL_Answers) rather than
> >> trying
> >> to bind it to a query. This will avoid the issue of which table a new
> >> record
> >> gets written to (which may ultimately be the cause of your problem here.)
>
> >> "Patrick A" wrote in message
>
> >>news:ef747d42-d388-47b2-8fc6-fa73a2fe72da@f3g2000hsg.googl egroups.com...
>
> >> > I have a Query (SQL below) I can successfully use in datasheet mode to
> >> > add records to two linked tables (TBL_People and TBL_Answers). The
> >> > tables have a one-to-many relationship, linked by an ID with "enforce
> >> > referential integrity" set to yes in the relationship. "TBL_People"
> >> > is on the one side, "TBL_Answers" is on the many side.
>
> >> > I have a form (a sub-form, really) that uses the Query as it's data
> >> > source.
>
> >> > I want to add a button to my form to add a new record. (Due to the
> >> > number of sub-forms, I don't want to use the built-in nav buttons).
>
> >> > I expected to be able to use DoCmd.GoToRecord , , acNewRec behind
> >> > the button to add a new record.
>
> >> > When I try, I get an error that tells me I cannot add or change a
> >> > record because a related record is required in 'TBL People'.
>
> >> > I can, however, add a record if I open the form on it's own - not as a
> >> > sub form.
>
> >> > Any suggestions? I'm sure it's something simple...
>
> >> > Thanks,
>
> >> > Patrick
>
> >> > SELECT TBL_People.ClientNum, TBL_Answers.MatterNum,
> >> > TBL_Answers.MatterDesc, TBL_Answers.Position, TBL_People.ID,
> >> > TBL_Answers.TestatorID, TBL_People.PreTitle, TBL_People.Title,
> >> > TBL_People.LastName, TBL_People.FirstName, TBL_People.MiddleName,
> >> > TBL_People.MaidenName, TBL_People.Suffix, TBL_People.SufTitle,
> >> > TBL_People.Add_Nm, TBL_People.Dear_Nm, TBL_People.IndivInformalNm,
> >> > TBL_People.Gender, TBL_People.Address1, TBL_People.Address2,
> >> > TBL_People.City, TBL_People.State, TBL_People.County, TBL_People.Zip,
> >> > TBL_People.Country, TBL_People.EmailAddress, TBL_People.[2ndAddress1],
> >> > TBL_People.[2ndAddress2], TBL_People.[2ndCity], TBL_People.[2ndState],
> >> > TBL_People.[2ndCounty], TBL_People.[2ndZip], TBL_People.[2ndCountry],
> >> > TBL_People.Notes, TBL_Answers.Update_Date, TBL_People.C_Rel,
> >> > TBL_People.M_Rel, TBL_Answers.Delivery, TBL_Answers.Personal,
> >> > TBL_Answers.Closing
> >> > FROM TBL_People INNER JOIN TBL_Answers ON TBL_People.ID =
> >> > TBL_Answers.TestatorID
> >> > ORDER BY TBL_Answers.MatterNum, TBL_People.LastName,
> >> > TBL_People.FirstName;- Hide quoted text -
>
> - Show quoted text -