stored procedure recordsource - new record causes problems

[Not Me]

Hi,

I have a stored procedure as a recordsource for a form, taking a
parameter from another form, it displays records that are already
present without any problems. I had to add a resync command to allow
edits, but it works ok for now.

The problem is when creating new records. If I open the form with
datamode acFormAdd, I get the blank record fine, I can populate the
fields ok. but when I come to close the record, I get the error
"line1:incorrect syntax near '.'". To which line1 is this referring to?

If I close the record, I get the usual 'you can't save at this time',
but it still creates the record. If I jump 'away' from the fields in
that particular form and back again, I can close the form without the
second error (notably, the (autonumber) field becomes populated when I
do this.. although as I say it works anyway even if I do close straight
away).

Any tips? I'm not sure if I'm going about this the right way...

Cheers,
Chris

[Zlatko Matic]

Are you sure that you are using stored procedure and not in-line function ?
I had a similar problem whith in-line function. When I changed it to stored
procedure everything worked fine...
"Not Me" <no*****@da.com.hk.org> je napisao u poruci interesnoj
grupi:d1**********@ucsnew1.ncl.ac.uk...

Hi,

I have a stored procedure as a recordsource for a form, taking a parameter
from another form, it displays records that are already present without
any problems. I had to add a resync command to allow edits, but it works
ok for now.

The problem is when creating new records. If I open the form with
datamode acFormAdd, I get the blank record fine, I can populate the fields
ok. but when I come to close the record, I get the error "line1:incorrect
syntax near '.'". To which line1 is this referring to?

If I close the record, I get the usual 'you can't save at this time', but
it still creates the record. If I jump 'away' from the fields in that
particular form and back again, I can close the form without the second
error (notably, the (autonumber) field becomes populated when I do this..
although as I say it works anyway even if I do close straight away).

Any tips? I'm not sure if I'm going about this the right way...

Cheers,
Chris

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The error refers to an error in the stored procedure (SP). Something is
wrong right before a period in line 1 of the SP.

I was under the impression that SPs were read only; therefore, you can't
add/edit!? I must be wrong.

When the "autonumber" field populates it is 'cuz the record was saved.
In SQL'r an Identity number (same as AutoNumber) isn't created until the
record is saved. In Access the AutoNumber is created when you start to
enter data into the form. If you Escape & Undo the data entry the
AutoNumber disappears.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQji8d4echKqOuFEgEQI0pQCfTsnAHfc0ampePDRsNRgp1W ETqegAoKrA
/q63bBrtR5t/4Wgwmr/wiIdV
=Jo+U
-----END PGP SIGNATURE-----
Not Me wrote:

Hi,

I have a stored procedure as a recordsource for a form, taking a
parameter from another form, it displays records that are already
present without any problems. I had to add a resync command to allow
edits, but it works ok for now.

The problem is when creating new records. If I open the form with
datamode acFormAdd, I get the blank record fine, I can populate the
fields ok. but when I come to close the record, I get the error
"line1:incorrect syntax near '.'". To which line1 is this referring to?

If I close the record, I get the usual 'you can't save at this time',
but it still creates the record. If I jump 'away' from the fields in
that particular form and back again, I can close the form without the
second error (notably, the (autonumber) field becomes populated when I
do this.. although as I say it works anyway even if I do close straight
away).

Any tips? I'm not sure if I'm going about this the right way...

[Not Me]

Zlatko Matic wrote:

Are you sure that you are using stored procedure and not in-line function ?
I had a similar problem whith in-line function. When I changed it to stored
procedure everything worked fine...

hmm, nah it's definately a stored procedure! Ta anyway.

Cheers,
Chris

[Not Me]

MGFoster wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The error refers to an error in the stored procedure (SP). Something is
wrong right before a period in line 1 of the SP.
Here's the SP, it's simple enough?

CREATE PROCEDURE spEventBooking
(
@eventID int = null
)
AS

SELECT EventsRecord.*, Events.Name, Events.TypeOfEvent,
Venue.VenueName, Users.Name 'EventContact'
FROM
EventsRecord INNER JOIN Events ON EventsRecord.Event = Events.EventsRef
INNER JOIN Venue ON Events.Venue = Venue.VenueID
LEFT OUTER JOIN Users ON Events.Contact = Users.UniqueUserID
WHERE eventsrecord.uniqueid = @eventid
GO

uniquetable is "eventsrecord", resync command is "spEventBooking
[me].[uniqueid]" (from the form)
I was under the impression that SPs were read only; therefore, you can't
add/edit!? I must be wrong.
Yup, I think that's what you need the uniquetable/resync properties, so
it knows what's being used, and can create update queries itself based
on that info.
When the "autonumber" field populates it is 'cuz the record was saved.
In SQL'r an Identity number (same as AutoNumber) isn't created until the
record is saved. In Access the AutoNumber is created when you start to
enter data into the form. If you Escape & Undo the data entry the
AutoNumber disappears.

Could it be something to do with the resync command? It's asking for the
autonumber back, but maybe asking too early?

Cheers,
Chris

[Not Me]

Not Me wrote:

Could it be something to do with the resync command? It's asking for the
autonumber back, but maybe asking too early?

In addition to this, I tried removing the resync command. The error I
get is: "Key value for this row was changed or deleted at the data
store, the local row is deleted"

Does that shed any light on the problem?

Chris

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The SELECT clause has an extra column w/o a preceding comma:

SELECT EventsRecord.*, Events.Name, Events.TypeOfEvent,
Venue.VenueName, Users.Name 'EventContact'

If you want Users.Name to have an alias as EventContact, you have to
delimit the alias w/ square brackets or double quotes (SQL standard).
Throw in an "AS" to remove any doubt. E.g.:

Users.Name As "EventContact"

I avoid ADO as much as possible, 'cuz I can get DAO to do it w/ less
work (and I'm stuck on it ;-) ).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjpPjIechKqOuFEgEQJEEQCfcGahxpZ155XWFVH9m+LT8g Lr+ggAoMm0
Y4KRjy4L6ljr2YKkJXz1WUHS
=t42Q
-----END PGP SIGNATURE-----

Not Me wrote:

MGFoster wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The error refers to an error in the stored procedure (SP). Something is
wrong right before a period in line 1 of the SP.

Here's the SP, it's simple enough?

CREATE PROCEDURE spEventBooking
(
@eventID int = null
)
AS

SELECT EventsRecord.*, Events.Name, Events.TypeOfEvent,
Venue.VenueName, Users.Name 'EventContact'
FROM
EventsRecord INNER JOIN Events ON EventsRecord.Event = Events.EventsRef
INNER JOIN Venue ON Events.Venue = Venue.VenueID
LEFT OUTER JOIN Users ON Events.Contact = Users.UniqueUserID
WHERE eventsrecord.uniqueid = @eventid
GO

uniquetable is "eventsrecord", resync command is "spEventBooking
[me].[uniqueid]" (from the form)

I was under the impression that SPs were read only; therefore, you can't
add/edit!? I must be wrong.

Yup, I think that's what you need the uniquetable/resync properties, so
it knows what's being used, and can create update queries itself based
on that info.

When the "autonumber" field populates it is 'cuz the record was saved.
In SQL'r an Identity number (same as AutoNumber) isn't created until the
record is saved. In Access the AutoNumber is created when you start to
enter data into the form. If you Escape & Undo the data entry the
AutoNumber disappears.

Could it be something to do with the resync command? It's asking for the
autonumber back, but maybe asking too early?

Cheers,
Chris