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" <parkins@xxxxxxxxxxxx> wrote in message
news:ef747d42-d388-47b2-8fc6-fa73a2fe72da@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

I have a Query (SQL below) I can successfully use in data*** 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;



.