SQL Backend issue - help
SimonT
I am having a real problem trying to sort this issue out. I converted my
application to a BE SQL 2005 with access FE.
I was having problems with my invoice form, in particular the lineitems
subform. So I strip everything back to basics and have created 2 new tables
on the SQL dbo_Table_productsTest and dbo_Table_lineitems2
I then linked them by ODBC to a new access FE, so nothing of the old
application. I then created a simple form using both tables with a combo box
from dbo_Table_productsTest to select the products.
The forms records source is:
SELECT dbo_Table_lineitems2.productID, dbo_Table_lineitems2.qty,
dbo_Table_productTest.item, dbo_Table_productTest.price
FROM dbo_Table_productTest INNER JOIN dbo_Table_lineitems2 ON
dbo_Table_productTest.productID = dbo_Table_lineitems2.productID;
With the combo box control source being productID and row source:
SELECT dbo_Table_productTest.productID, dbo_Table_productTest.item,
dbo_Table_productTest.price
FROM dbo_Table_productTest;
The problem I have is the form does show any field items and cannot enter
any new records. However if I change to row source to a LEFT JOIN the
controls are available, but do not allow me to add a record, giving me this
error:
Cannot add record(s) Primary key for dbo_Table_lineitems2 not in recordset
These are the table structures on the SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_lineitems2](
[lineID] [int] IDENTITY(1,1) NOT NULL,
[productID] [int] NULL,
[qty] [int] NULL,
[price] [decimal](18, 2) NULL,
[timestamp] [timestamp] NULL,
CONSTRAINT [PK_Table_lineitems2] PRIMARY KEY CLUSTERED
(
[lineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_productTest](
[productID] [int] IDENTITY(1,1) NOT NULL,
[item] [nvarchar](50) NULL,
[price] [decimal](18, 2) NULL,
[timestamp] [timestamp] NULL,
CONSTRAINT [PK_Table_productTest] PRIMARY KEY CLUSTERED
(
[productID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Can anyone help with this please and advise what the problem is.
Thanks
Si
Alex Dybenko
if you open your tables in access - can you edit/add records to each one?
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
"SimonT" <Si_tyler@mymail_hotmail.co.uk> wrote in message
news:OvABfEOz...@TK2MSFTNGP05.phx.gbl...
SimonT
"Alex Dybenko" <ale...@PLEASE.cemi.NO.rssi.SPAM.ru> wrote in message
news:%23rjeELP...@TK2MSFTNGP05.phx.gbl...
Max Right
is it not displaying at all?
is this happening for new records, or for all records?
SimonT
dbo_Table_productTest.item then opened the form, which shows me the data,
however,it will not allow me to add a new record, simply modify an existing
record.
"Max Right" <risky...@gmail.com> wrote in message
news:7a593109-351e-4b24...@t34g2000prm.googlegroups.com...
Max Right
unlike access, SQL does not create a primary key until the record has
been commited.
because of this your subform recordsource is comming up with no values
and thus not displaying the subform for new records...
depending on your skill level you can use VBA to reference @@IDENTITY
which retrives the primary key value.
SimonT
This example is not a subform, its a single form?
"Max Right" <risky...@gmail.com> wrote in message
news:3e28a9b6-aa8a-45d2...@k5g2000pra.googlegroups.com...
Max Right
you're using a single form and when you go to new record, the form
disappears. if that is the case I would try to play around with the
record source of the form, try running that recordsource query and see
what you come up with...