Resynch returs 0 for identity field (PK)

[Christian Vonäsch]

Hi NG

I add a new record in a form.
The form's recordsource is a view "PrjSel":

CREATE VIEW PrjSel
WITH VIEW_METADATA
AS
SELECT *
FROM PrjTbl
WHERE (ForPrjManagersOnly=0) OR
(IS_MEMBER('PrjManagers') = 1)

The view shows all projects, if the current user is a member of the role
'PrjManagers'
If the user is not a member of this role, he only gets the projects NOT
ForPrjManagersOnly.

To avoid access to all projects to everybody I deny access to the table
PrjTbl directly:
DENY SELECT ON PrjTbl TO ALL

The WITH VIEW_METADATA in PrjSel is necessary to allow data addition in the
view through a form.
(Otherwise adp tries to add a record directly to the table, but if
select-access is denied it fails.)

The form's properties are:
RecordSource = "PrjSel"
Serverfilter = ""
UniqueTable = "PrjSel" (this works because of the WITH VIEW_METADATA!)
Resynch = "select * from PrjSel where PrjID=?"

Now the problem is, that the control which displays the PK: PrjID gets a 0
instead of the new identity-value.
Therefore my subform linked via PrjID (masterfields-childfields) doesn't
work and displays no record!
Only after requerying and jumping to the newly added record, the PrjID is
displayed correctly and the subform works.

Thank you for your help. (adp2000; SQL2000)

Christian Vonäsch

 

[Christian Vonäsch]

Hi NG

I answer to myself, if anybody is interested in the problem/solution:

I was not quite right in expressing my problem. By simplifying it for better
understandig, I simplifyed too much. The view, my form is based on joins two
tables. And that was the problem. It still makes no sense, that access
doesn't give back the correct (AutoNumber) Primary Key after resynch, but I
solved the problem by taking a view with only one table behind to add a new
record.

Bye
Chris

 

[Sylvain Lafontaine]

Access have a lot of bugs when it come to multiple joined tables.

It often help to use stored procedures instead of views or user defined
functions.

S. L.