No new record in datasheet with a calculated field?

[bhammer]

Access2003
I don't seem to be able to add anew record to my datasheet (bound to
qryIssues) that contains a calculated field that counts the number is
Citations associated with each Issue. The ControlSource is:

DCount("CitationID","qryCitationsIssues","Issue_ID= IssueID")

where IssueID is in the datasheet, so the calculation obviously can't be
done on an unsaved record. Can I somehow delay the calculation until the
IssueID is saved?

It's handy to view the counts in the list of Issues, but is there no way to
enter a new record in this datasheet?

 

[Jeff Boyce]

Are you saying that you are trying to save a calculated value? If so, why?
It is rarely necessary to save calculated values in a well-normalized
relational database. Not "never", just "rarely".

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[bhammer]

Aha. My oversight. I had another primary key field in the backing query
instead of the foreign key, causing the Access error "Cannot save the
record..."

Anyone else reading this--you can check the query in datasheet view, at the
New Record row, to see if you have more than one (AutoNumber) visible. If you
do, then you have more than one primary key in the query and won't be able to
save new records.

thx Jeff.

 

[Jeff Boyce]

Nice touch, covering the issue and solution. Someone else will undoubtedly
be looking for ideas on this in the future and find your (generous) post.

Regards

Jeff Boyce
Microsoft Office/Access MVP