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:incorrec t 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 6 3657
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:incorrec t 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
-----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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQji8d4echKq OuFEgEQI0pQCfTs nAHfc0ampePDRsN Rgp1WETqegAoKrA
/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:incorrec t 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...
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
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.TypeOfEv ent,
Venue.VenueName , Users.Name 'EventContact'
FROM
EventsRecord INNER JOIN Events ON EventsRecord.Ev ent = Events.EventsRe f
INNER JOIN Venue ON Events.Venue = Venue.VenueID
LEFT OUTER JOIN Users ON Events.Contact = Users.UniqueUse rID
WHERE eventsrecord.un iqueid = @eventid
GO
uniquetable is "eventsreco rd", resync command is "spEventBoo king
[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 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
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
The SELECT clause has an extra column w/o a preceding comma:
SELECT EventsRecord.*, Events.Name, Events.TypeOfEv ent,
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 "EventConta ct"
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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQjpPjIechKq OuFEgEQJEEQCfcG ahxpZ155XWFVH9m +LT8gLr+ggAoMm0
Y4KRjy4L6ljr2YK kJXz1WUHS
=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.TypeOfEv ent, Venue.VenueName , Users.Name 'EventContact' FROM EventsRecord INNER JOIN Events ON EventsRecord.Ev ent = Events.EventsRe f INNER JOIN Venue ON Events.Venue = Venue.VenueID LEFT OUTER JOIN Users ON Events.Contact = Users.UniqueUse rID WHERE eventsrecord.un iqueid = @eventid GO
uniquetable is "eventsreco rd", resync command is "spEventBoo king [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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Shaun Stuart |
last post by:
I've got a webpage that calls some stored procedures with input variables.
The procedures return recordsets and also some output variables. We're
trying to get the values of the output variables. I've done this using the
method I found in MSDN, as shown in the code below. The problem is that we
believe doing it this way involves the use of the Microsoft Transaction
Server (IIS transaction server). Is this true? (The SQL Server and IIS...
|
by: Lauren Quantrell |
last post by:
I have a stored procedure named mySP that looks basically like this:
Select Field1, Field2
From tblMyTable
Where Field 3 = 'xyz'
What I do is to populate an Access form:
DoCmd.Openform "frmMyFormName"
Forms!myFormName.RecordSource = "mySP"
What I want to do in VBA is to open frmContinuous(a datasheet form) if
|
by: serge |
last post by:
I have an SP that is big, huge, 700-800 lines.
I am not an expert but I need to figure out every possible way that
I can improve the performance speed of this SP.
In the next couple of weeks I will work on preparing SQL statements
that will create the tables, insert sample record and run the SP.
I would hope people will look at my SP and give me any hints on how
I can better write the SP.
|
by: Lauren Quantrell |
last post by:
I have read the newsgroups and see this is a common issue but I saw no
resolution for it:
I have an Access2K frotn end and SQL Server 2K backend.
In access, I create a temp table using code in a module:
strSQL = "CREATE TABLE #tempTableName ("TempID int NOT NULL IDENTITIY
(1,1) PRIMARY KEY, Field1 int...)
CurrentProject.Connection.Execute strSQL,,adCmdText
so far so good...
|
by: Mark Flippin |
last post by:
This is for a reporting problem using:
Access 2000
SQL Server 2000
Both at SP3
I've a stored procedure in SQL Server 2000 which builds a result set
from a disparate set of tables, utilizing a temp table. The procedure
takes two parameters to specify the criteria on selecting the table
information for inclusion in the result set, builds the temp table,
| |
by: Sreeneet |
last post by:
Hi all,
I want to call a stored procedure which is written in SQL Server from
an ms-access query. It is having some parameters also and the stored
procedure will return some records.
Is there any way to do this?
Any help?
|
by: dbuchanan |
last post by:
Hello,
My insert results in two records - or better stated the temporary
record (with the temporary ID value of -1) stays as the stored
procedure returns the new record with its data base assigned ID value
Below is shown the ID field and one data field. Notice the last two
records shown.
1 bat
2 frog
|
by: fniles |
last post by:
I am using VB.NET 2003 and SQL2000 database.
I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1
parameter (varchar(10)) and returns the identity column value from that
table.
When calling the stored procedure from VB.NET, in the CommandText, can I
just say "INSERT_INTO_MYTABLE '12345'" instead of calling it with
"INSERT_INTO_MYTABLE" then do the following :
OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar, 10)...
|
by: kkk1979 |
last post by:
I have been using access as front end and SQL as Back end. I need help in generating an access report, by using a stored procedure with input parameters
as record source.
I tried the following code, but getting an error.Can't I use recordset as record source for the report.
Dim sDate As String
Dim eDate As String
Dim rsbatf As New ADODB.Recordset
sDate = InputBox("Enter Beginning Period", "Beginning Period")
eDate = InputBox("Enter...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |