SimonT
2010-03-26 12:36:47 UTC
Hi Guys,
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
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