New Record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am adding a new record using the following query:

SELECT ACCOUNT.FK_SalesRep, ACCOUNT.AccountName, ACCOUNT.ContactName,
ACCOUNT.ContactTelephone, ACCOUNT.ContactEmail, ACCOUNT.SIMAP, ACCOUNT.AMEX,
ACCOUNT.WAF, ACCOUNT.PAC, ACTIVITY.Date, ACTIVITY.Comments,
ACTIVITY.Direction, ACTIVITY.Commodity, ACTIVITY.Origin,
ACTIVITY.Destination, ACTIVITY.Volume, ACTIVITY.Service, STATUS.StatusFullName
FROM STATUS INNER JOIN (REP INNER JOIN (ACCOUNT INNER JOIN ACTIVITY ON
ACCOUNT.AccountName = ACTIVITY.FK_AccountName) ON REP.[Sales Rep] =
ACCOUNT.FK_SalesRep) ON STATUS.StatusShortName = ACTIVITY.FK_StatusInitials;

The issue is that I create a new record through a form with this query as
the recordsource. When entering data into this new record, it creates a new
account as well as a new activity associated to that new account. Both the
table "account" and "activity" are updated (related by the "SalesRep"). I
think the problem is that it won't take a new activity because the new
account name has not yet been added to the account table. Any thoughts?
Maybe I should do the new record a different way?
 
Maybe I should do the new record a different way?
Click to expand...

Yes; I'd suggest using a Form (for accounts) with a Subform, rather
than trying to update a Grand Master Query which includes all your
tables.

John W. Vinson[MVP]
 
Ok, i can do that. so you would suggest having an underlying query of
accounts and another for acitivies, in which, after I update one part of the
form and move to the subquery, it will have added in the new account and will
take the activity without telling me that i cannot have a null value as a
primary key?
 
Ok, i can do that. so you would suggest having an underlying query of
accounts and another for acitivies, in which, after I update one part of the
form and move to the subquery, it will have added in the new account and will
take the activity without telling me that i cannot have a null value as a
primary key?
Click to expand...

If you use a *FORM* with a *SUBFORM* - rather than working in table or
query datasheets - then yes, the subform's Master/Child Link Field
properties will automatically fill in the account number.

If you use table or query datasheets, you must keep track of all of
that yourself; Access won't help you. That's one of many reasons that
forms are preferable for data entry - they do some of the work for
you!

John W. Vinson[MVP]
 
Back
Top