October 29, 2014 at 8:11 am
I'm about to give up so please help me if you can.
I am using an Access 2010 .adp (I know .adp's are deprecated...) connected to a database on SQL Server 2008. The database uses surrogate keys for all reference/look-up table values.
The database
The database is used to manage information about regulatory requests for approval. An applicant makes a request for approval to do a thing (e.g. drill a well on government-owned land). The main data table in the database contains information about the request. The regulator may need to refer the request to one or more specialists for assessment (e.g. geology, engineering etc.). These referrals fall into one of a handful of categories (e.g. internal, external, other).
The database structure
I have a reference table for Referral Categories and one for Referral Types. There is a one to many relationship between Referral Categories and Referral Types. I then have a Referral Details table with a FK to the Referral Types table (i.e. One to many from Referral Types to Referral Details).
The form
I have a form (the request) with a subform (the referrals details for the request). I'd prefer a datasheet but could use a continuous form if that would make a difference to a successful solution. I would like to use cascading/filtered combo boxes for the category and referral i.e. The user selects a value from a Referral Category combo box to filter the values available in a Referral Type combo box. A selection in the Referral Type combo box would populate the FK in the Referral Details record.
The problem
On the face of it, this would seam to be a straight forward problem with an easy solution. If you've ever tried this you know that it's not. The problem is with the way that MS Access forms handle drop-down lists - one dropdown list for all records for a particular value. If you change the dropdown list of the "child" combo box for one row, based on the selection in the "parent" combo box, it's changed for all rows :(. This has the effect of hiding (filtering out) values in the filtered "child" combo box for records that don't meet the filter criteria defined by the "parent" combo box. It also changes the value in all rows in the "parent" combo box to the selected value(double :().
My web research so far has uncovered solutions/workarounds which all appear to be based on using an MS Access database (rather than an SQL Server database) and rely on creating an MS Access view which includes the name values from the reference tables and using them in bound text boxes strategically placed over top of the combo box. Unfortunately any view which joins SQL Server tables becomes not updateable.
Does anyone have a solution?... Please... This shouldn't be this hard.
P.S. I'd prefer to avoid the complexity of things like instead of update triggers or stored procedures and such if at all possible but if that's the only way...
October 29, 2014 at 11:08 am
You are spot on in terms of the complexity of trying to do this in an ADP - it is much simpler if you use the .mdb/accdb approach and link to the SQL Server tables or views. But have you considered using an indexed SQL Server view with your ADP? At least in theory, it should be update-able, though I've not actually tested it.
But as have a one to many relationship between Categories and Types, could a bit of de-normalization simplify things? Since you only have a few categories, you could put that into a single table, and eliminate the need for cascading combo boxes to filter a subset of the details.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
October 30, 2014 at 10:14 am
Thank you Wendell for your reply. It is as I had feared.
Indexed View
I've tried this to no success (so far) but I wonder if it's because I'm not doing something right. The MS Access form says that the recordset for the view is not updateable. I don't know whether that's because I haven't got the view defined properly or whether it's because the view simply is not updateable
Here is the DDL for the tables, some reference values to give you an idea of what I'm looking at, and the indexed view that I've tried. Does this look right to you?
-----------------------------------------------------------------------------------------------------------------
-- Drop Everything
-----------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[dbo].[V_Information_Request]') AND OBJECTPROPERTY(id, 'IsView') = 1)
DROP VIEW [dbo].[V_Information_Request]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[dbo].[Information_Request]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [dbo].[Information_Request]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[dbo].[R_Information_Request_Type]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [dbo].[R_Information_Request_Type]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[dbo].[R_Information_Request_Category]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [dbo].[R_Information_Request_Category]
GO
-----------------------------------------------------------------------------------------------------------------
-- R_Information_Request_Category
-----------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[dbo].[R_Information_Request_Category]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [dbo].[R_Information_Request_Category]
GO
CREATE TABLE [dbo].[R_Information_Request_Category] (
[DB_Key] INT IDENTITY(1,1) CONSTRAINT [PK_R_Information_Request_Category] PRIMARY KEY CLUSTERED
,[Name] VARCHAR(100) NOT NULL
,[Definition] VARCHAR(256) NOT NULL
,[Effective_Date] DATETIME NOT NULL CONSTRAINT [DF_R_Information_Request_Category_Effective_Date] DEFAULT CONVERT(VARCHAR(10), GETDATE(), 101)
,[Sort_Order] NUMERIC(6,2) NULL
,[Obsolete_Date] DATETIME NULL
,[Comment] VARCHAR(256) NULL
,[Created_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_R_Information_Request_Category_Created_By] DEFAULT [dbo].[FN_Get_User_Name]()
,[Creation_Date_Time] DATETIME NOT NULL CONSTRAINT [DF_R_Information_Request_Category_Creation_Date_Time] DEFAULT GETDATE()
,[Last_Updated_By] VARCHAR(51) NULL
,[Last_Update_Date_Time] DATETIME NULL
,[RowVersion] ROWVERSION NOT NULL
,[Corporate_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_R_Information_Request_Category_Corporate_GUID] DEFAULT NEWID()
,CONSTRAINT [UIX_R_Information_Request_Category] UNIQUE ([Name], [Obsolete_Date])
,CONSTRAINT [CK_R_Information_Request_Category_Obsolete_Date_GE_Effective_Date] CHECK ([Obsolete_Date] >= [Effective_Date])
,CONSTRAINT [CK_R_Information_Request_Category_Last_Update_Date_Time_GE_Creation_Date_Time] CHECK ([Last_Update_Date_Time] >= [Creation_Date_Time])
)
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[dbo].[R_Information_Request_Category_Update]') AND OBJECTPROPERTY(id, 'IsTrigger ') = 1)
DROP TRIGGER [dbo].[R_Information_Request_Category_Update]
GO
CREATE TRIGGER [dbo].[R_Information_Request_Category_Update] ON [dbo].[R_Information_Request_Category] FOR UPDATE AS
SET NOCOUNT ON
IF NOT UPDATE([Last_Updated_By])
UPDATE TBL SET
TBL.[Last_Updated_By] = [dbo].[FN_Get_User_Name]()
FROM [dbo].[R_Information_Request_Category] TBL
JOIN inserted INS
ON TBL.[DB_Key] = INS.[DB_Key]
IF NOT UPDATE([Last_Update_Date_Time])
UPDATE TBL SET
TBL.[Last_Update_Date_Time] = GETDATE()
FROM [dbo].[R_Information_Request_Category] TBL
JOIN inserted INS
ON TBL.[DB_Key] = INS.[DB_Key]
GO
-----------------------------------------------------------------------------------------------------------------
-- R_Information_Request_Type
-----------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[dbo].[R_Information_Request_Type]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [dbo].[R_Information_Request_Type]
GO
CREATE TABLE [dbo].[R_Information_Request_Type] (
[DB_Key] INT IDENTITY(1,1) CONSTRAINT [PK_R_Information_Request_Type] PRIMARY KEY CLUSTERED
,[Name] VARCHAR(100) NOT NULL
,[Definition] VARCHAR(256) NOT NULL
,[Effective_Date] DATETIME NOT NULL CONSTRAINT [DF_R_Information_Request_Type_Effective_Date] DEFAULT CONVERT(VARCHAR(10), GETDATE(), 101)
,[Sort_Order] NUMERIC(6,2) NULL
,[Obsolete_Date] DATETIME NULL
,[Comment] VARCHAR(256) NULL
,[Information_Request_Category_DB_Key] INT NOT NULL CONSTRAINT [FK_R_Information_Request_Category] REFERENCES [dbo].[R_Information_Request_Category] ([DB_Key])
,[Created_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_R_Information_Request_Type_Created_By] DEFAULT [dbo].[FN_Get_User_Name]()
,[Creation_Date_Time] DATETIME NOT NULL CONSTRAINT [DF_R_Information_Request_Type_Creation_Date_Time] DEFAULT GETDATE()
,[Last_Updated_By] VARCHAR(51) NULL
,[Last_Update_Date_Time] DATETIME NULL
,[RowVersion] ROWVERSION NOT NULL
,[Corporate_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_R_Information_Request_Type_Corporate_GUID] DEFAULT NEWID()
,CONSTRAINT [UIX_R_Information_Request_Type] UNIQUE ([Name], [Obsolete_Date])
,CONSTRAINT [CK_R_Information_Request_Type_Obsolete_Date_GE_Effective_Date] CHECK ([Obsolete_Date] >= [Effective_Date])
,CONSTRAINT [CK_R_Information_Request_Type_Last_Update_Date_Time_GE_Creation_Date_Time] CHECK ([Last_Update_Date_Time] >= [Creation_Date_Time])
)
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[dbo].[R_Information_Request_Type_Update]') AND OBJECTPROPERTY(id, 'IsTrigger ') = 1)
DROP TRIGGER [dbo].[R_Information_Request_Type_Update]
GO
CREATE TRIGGER [dbo].[R_Information_Request_Type_Update] ON [dbo].[R_Information_Request_Type] FOR UPDATE AS
SET NOCOUNT ON
IF NOT UPDATE([Last_Updated_By])
UPDATE TBL SET
TBL.[Last_Updated_By] = [dbo].[FN_Get_User_Name]()
FROM [dbo].[R_Information_Request_Type] TBL
JOIN inserted INS
ON TBL.[DB_Key] = INS.[DB_Key]
IF NOT UPDATE([Last_Update_Date_Time])
UPDATE TBL SET
TBL.[Last_Update_Date_Time] = GETDATE()
FROM [dbo].[R_Information_Request_Type] TBL
JOIN inserted INS
ON TBL.[DB_Key] = INS.[DB_Key]
GO
-----------------------------------------------------------------------------------------------------------------
-- Information_Request
-----------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[dbo].[Information_Request]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [dbo].[Information_Request]
GO
CREATE TABLE [dbo].[Information_Request] (
[DB_Key] INT IDENTITY(1,1) CONSTRAINT [PK_Information_Request] PRIMARY KEY CLUSTERED
,[Start_Date] DATETIME NOT NULL CONSTRAINT [DF_Information_Request_Start_Date] DEFAULT GETDATE()
,[End_Date] DATETIME NULL
,[Milestone_Dates_DB_Key] INT NOT NULL CONSTRAINT [FK_Information_Request_to_Milestone_Dates] REFERENCES [dbo].[Milestone_Dates] ([DB_Key])
,[Information_Request_Type_DB_Key] INT NOT NULL CONSTRAINT [FK_Information_Request_to_R_Information_Request_Type] REFERENCES [dbo].[R_Information_Request_Type] ([DB_Key])
,[Created_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_Information_Request_Created_By] DEFAULT [dbo].[FN_Get_User_Name]()
,[Creation_Date_Time] DATETIME NOT NULL CONSTRAINT [DF_Information_Request_Creation_Date_Time] DEFAULT GETDATE()
,[Last_Updated_By] VARCHAR(51) NULL
,[Last_Update_Date_Time] DATETIME NULL
,[RowVersion] ROWVERSION NOT NULL
,[Corporate_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_Information_Request_Corporate_GUID] DEFAULT NEWID()
,CONSTRAINT [CK_Information_Request_Last_Update_Date_Time_GE_Creation_Date_Time] CHECK ([Last_Update_Date_Time] >= [Creation_Date_Time])
)
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[dbo].[Information_Request_Update]') AND OBJECTPROPERTY(id, 'IsTrigger ') = 1)
DROP TRIGGER [dbo].[Information_Request_Update]
GO
CREATE TRIGGER [dbo].[Information_Request_Update] ON [dbo].[Information_Request] FOR UPDATE AS
SET NOCOUNT ON
IF NOT UPDATE([Last_Updated_By])
UPDATE TBL SET
TBL.[Last_Updated_By] = [dbo].[FN_Get_User_Name]()
FROM [dbo].[Information_Request] TBL
JOIN inserted INS
ON TBL.[DB_Key] = INS.[DB_Key]
IF NOT UPDATE([Last_Update_Date_Time])
UPDATE TBL SET
TBL.[Last_Update_Date_Time] = GETDATE()
FROM [dbo].[Information_Request] TBL
JOIN inserted INS
ON TBL.[DB_Key] = INS.[DB_Key]
GO
-----------------------------------------------------------------------------------------------------------------
-- Indexed view
-----------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[dbo].[V_Information_Request]') AND OBJECTPROPERTY(id, 'IsView') = 1)
DROP VIEW [dbo].[V_Information_Request]
GO
CREATE VIEW [dbo].[V_Information_Request] WITH SCHEMABINDING AS (
SELECT
IR.[DB_Key]
,IR.[Start_Date]
,IR.[End_Date]
,IR.[Milestone_Dates_DB_Key]
,IR.[Information_Request_Type_DB_Key]
,IR.[Created_By]
,IR.[Creation_Date_Time]
,IR.[Last_Updated_By]
,IR.[Last_Update_Date_Time]
,IR.[RowVersion]
,IR.[Corporate_GUID]
,IRT.[Name] AS [IR_Type_Name]
,IRT.[Information_Request_Category_DB_Key] AS [IRC_DB_Key]
,IRC.[Name] AS [IR_Category_Name]
FROM [dbo].[Information_Request] IR
JOIN [dbo].[R_Information_Request_Type] IRT
ON IRT.[DB_Key] = IR.[Information_Request_Type_DB_Key]
JOIN [dbo].[R_Information_Request_Category] IRC
ON IRC.[DB_Key] = IRT.[Information_Request_Category_DB_Key]
)
GO
CREATE UNIQUE CLUSTERED INDEX IX_V_Information_Request ON [dbo].[V_Information_Request] ([DB_Key])
--------------------------------------------------------------------------------------------
-- Load information request Categories
--------------------------------------------------------------------------------------------
INSERT INTO [dbo].[R_Information_Request_Category] ([Name], [Created_By], [Definition], [Sort_Order]) VALUES ('Internal Referral', 'Initial Load', 'Referral to a business unit within the AER', 1)
INSERT INTO [dbo].[R_Information_Request_Category] ([Name], [Created_By], [Definition], [Sort_Order]) VALUES ('External Referral', 'Initial Load', 'Referral to a regulatory authority outside of the AER', 2)
INSERT INTO [dbo].[R_Information_Request_Category] ([Name], [Created_By], [Definition], [Sort_Order]) VALUES ('Applicant', 'Initial Load', 'Referral back to the applicant', 3)
INSERT INTO [dbo].[R_Information_Request_Category] ([Name], [Created_By], [Definition], [Sort_Order]) VALUES ('Hold', 'Initial Load', 'A hold on the Application for some reason not included in any other category', 4)
--------------------------------------------------------------------------------------------
-- Load information request Types
--------------------------------------------------------------------------------------------
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('SIR', 'Initial Load', 'Supplemental Information Request', 1, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'Applicant'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('Draft Approval Review', 'Initial Load', 'Supplemental Information Request', 2, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'Applicant'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('Geology Referral', 'Initial Load', 'A Referral to the geology business unit for geological input/advice regarding the application', 3, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'Internal Referral'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('Hydrology Referral', 'Initial Load', 'A Referral to the Hydrology business unit for hydrological input/advice regarding the application', 4, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'Internal Referral'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('Engineering Referral', 'Initial Load', 'A Referral to the Enginerring business unit for engineering input/advice regarding the application', 5, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'Internal Referral'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('Law Referral', 'Initial Load', 'A Referral to the Law business unit for legal input/advice regarding the application', 6, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'Internal Referral'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('EIA Referral', 'Initial Load', 'A Referral to the EIA business unit for environmental impact assessment input/advice regarding the application', 7, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'Internal Referral'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('ESRD', 'Initial Load', 'A Referral to Alberta Environment and Sustainable Resources for input/advice regarding the application', 8, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'Internal Referral'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('Ducks Unlimited', 'Initial Load', 'A Referral to the Ducks Unlimited for input/advice regarding the application', 9, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'External Referral'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('Fisheries and Oceans', 'Initial Load', 'A Referral to the Fisheries and Oceans for input/advice regarding the application', 11, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'External Referral'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('Related application', 'Initial Load', 'Being held becuase of a dependency on or relationship to another application', 12, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'Hold'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('FNC adequacy confirmation', 'Initial Load', 'Awaiting confirmation that FNC is adequate', 13, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'External Referral'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('ACO', 'Initial Load', 'A Referral to the Aboriginal Consultation Office for input/advice regarding the application', 10, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'External Referral'))
Denormalization
Yes, I have experimented with it and this would be my fallback approach. Still use the two reference tables for defining Request Categories and Request Types but define the rowsource for the in the Information Request which refers to the Information Request Type to display both columns (Request Category and Request Type). My only hesitation is that this would display the full list of Information Request Types which I fear could grow quite large (40-60 rows) over time which is a bit unwieldy for a dropdown list, hence the desire to have cascading combo boxes.
As I'm writing this it occurs to me that if the Information Request Table contained a FK to Request Category AS WELL AS a FK to Request Type I could probably use the FK the user selects in Request Category to filter the rowsource for Request Type. Is that what you were thinking of by denormalization? I will try this and report back.
Any advice you could provide would be much appreciated.
October 30, 2014 at 3:49 pm
It appears that Access refuses to let you update views, whether they are indexed or not. We use views extensively as ODBC linked tables and as long as they have an index, then they can be updated. Have you tried updating the view in SQL Server Management Studio to see if it is updateable there?
And yes, the idea
As I'm writing this it occurs to me that if the Information Request Table contained a FK to Request Category AS WELL AS a FK to Request Type I could probably use the FK the user selects in Request Category to filter the rowsource for Request Type. Is that what you were thinking of by denormalization? I will try this and report back.
is one way of implementing it, although I was actually thinking more of the idea of a single table that contained all of the combinations. If you think that may actually grow to 50 or 60, then that does get a bit messy.
I've never really worked with the ADP format enough to know much about the limitations. Does the data source for a form always have to be a sproc, or can you dynamically change the data source using VBA? If so, that might be another alternative to applying filters to a subform...
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
November 3, 2014 at 6:53 am
My Report
My idea of creating a FK to both tables and constraining the combo box of the Request Type by the selection in the Request Category still has the same problem I described in my first post. 🙁
Have you tried updating the view in SQL Server Management Studio to see if it is updateable there?
Upon your question I tested this. Yes, SQL Server permits this when using SQL Server Management Studio directly.
INSERT INTO [dbo].[V_Information_Request] ([Start_Date], [Milestone_Dates_DB_Key], [Information_Request_Type_DB_Key])
VALUES (GETDATE(), (SELECT TOP 1 [DB_Key] FROM [dbo].[Milestone_Dates]), (SELECT TOP 1 [DB_Key] FROM [dbo].[R_Information_Request_Type]))
I think it's because in the insert statement I'm directly specifying what columns to update. Now that I know that the view is updateable from the SQL Server side I'm going to experiment some more with the .adp to see if I can get something that works. Perhaps it's just a question of property settings.
How an ADP works
A from in an ADP is connected directly to the table in the database. That's part of what makes it so simple, nice and clean. No ODBC connection to set up - the ADP is connected directly to the database. No need to create a link object as in a .mdb or .accdb file - the form connects directly to the table or view (or to a stored procedure although 've never actually tried that). The downside seems to be that if a view includes multiple tables it is not updateable. More testing to be done...
The fallback position
If I am unable to get an updatable view in the .adp I'm going to have to fall back to a single combo box which displays the Request Category and Request Type in the dropdown. Although that's kind of problematic too since, the natural way to display the dropdown list would be Category in column 1 and Type in column 2 but with the lookup displayed this way, when the user makes a selection it would be Category that gets displayed in the combo box and not the Type - which is what the user would naturally expect to see. Ugh! So, I would have to display Type in the first column and Category in the second. Not as naturally intuitive but a workable compromise...
Thanks again for hashing this out with me. If you have any other thoughts I'd sure be eager to hear them. It may be worth exploring the .accdb approach although it would mean reworking the code in the rest of the solution to use MS Access SQL and passthrough SQL as appropriate..
November 7, 2014 at 7:41 am
I've made a breakthrough / discovery regarding updatable views in a .adp file that I'm compelled to share for the good of the community. Hopefully this will help someone else some day (although with .adp files being deprecated perhaps not, but I think the general principle, as it relates to "This recordset is not updateable" applies)
I learned, this morning from this blog post https://groups.google.com/forum/#!topic/microsoft.public.access.adp.sqlserver/YIakYHmqEKU (John's reply) that in order for a view which joins multiple tables to be updateable the primary keys from ALL of the joined tables must be included in the view.[/u] Once I did that my view was updateable just like a table!
So; back to my original problem... Now that I have an updatable view which can include the name values of the referenced tables, my cascading combo boxes in a continuous form or datasheet should work (I haven't gotten that far yet). If it doesn't, I'll report back.
Thanks for your support Wendell!
November 7, 2014 at 8:25 am
Well I learned some things too - glad you were able to work it out and thanks for sharing.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
November 12, 2014 at 10:56 am
Just thought I'd provide a brief update. At the top of my post I noted that I was using a .adp file. Despite the fact that I still like the .adp format very much - it's just so direct to the SQL database, I've switched to a .accdb file. Performance issues I've experienced in the past with .accdb VS. .adp don't appear to be occurring. I'm going to chalk that up to something I was doing wrong before with the .accdb.
The good news is, I've got my app working the way I want, cascading combo boxes in a continuous form. Yea me!
Here's a shocker though. Something that gives me pause. My .adp file was less than 1mb in size. The same app as a .accdp is more than 21mb! What's up with that?!
November 12, 2014 at 11:21 am
Regarding the size growth, the first question is whether you have done a compact and repair? It's much more important in .accdb and .mdb files than it is with .adp files. The former file types will always be a fair bit larger, as you are storing most of the logic in the front-end instead of the back-end, but unless you have many forms and reports I wouldn't expect that kind of size. For example, we have an app that has about 100 forms and 70 reports, and quite a bit of VBA logic, and in a compacted state it runs around 25 to 30 MB.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply