January 14, 2012 at 10:44 am
I am trying to create a simple help desk application using access 2007 as the front end but am falling short in with some concepts. I have attached images(please see attached images) of the acces form as well as sql code to run on your own sql server.
I have two simple tables. A CaseData table and a Category table. The CaseData table will hold the marjority of the data and be on the main form in access but I want to have a combo box lookup list that points to the category table. when I put such an object in the access form I can pick the category but when I look in the casedata.category column referntial integrity is lost as the field only shows null in sql server. You will see this in the untitled4.jpg I have attached.
I would like the casedata.category column to be updated with the correct number from the category.id column. The untitled5.jpg message shows the category options.
what could I be missing?
I have added a foreign key constraint on the casedata.category column to the category.id column but this doesn't seem to have taken any effect.
Below is the sql code to create the database, tables, and user objects
CREATE DATABASE [GKHELPDESK] ON PRIMARY
( NAME = N'GKHELPDESK', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GKHELPDESK.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'GKHELPDESK_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GKHELPDESK_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [GKHELPDESK] SET COMPATIBILITY_LEVEL = 100
GO
ALTER DATABASE [GKHELPDESK] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [GKHELPDESK] SET ANSI_NULLS OFF
GO
ALTER DATABASE [GKHELPDESK] SET ANSI_PADDING OFF
GO
ALTER DATABASE [GKHELPDESK] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [GKHELPDESK] SET ARITHABORT OFF
GO
ALTER DATABASE [GKHELPDESK] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [GKHELPDESK] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [GKHELPDESK] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [GKHELPDESK] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [GKHELPDESK] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [GKHELPDESK] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [GKHELPDESK] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [GKHELPDESK] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [GKHELPDESK] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [GKHELPDESK] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [GKHELPDESK] SET DISABLE_BROKER
GO
ALTER DATABASE [GKHELPDESK] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [GKHELPDESK] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [GKHELPDESK] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [GKHELPDESK] SET READ_WRITE
GO
ALTER DATABASE [GKHELPDESK] SET RECOVERY FULL
GO
ALTER DATABASE [GKHELPDESK] SET MULTI_USER
GO
ALTER DATABASE [GKHELPDESK] SET PAGE_VERIFY CHECKSUM
GO
USE [GKHELPDESK]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY')
ALTER DATABASE [GKHELPDESK] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
USE GKHELPDESK;
GO
SET NOCOUNT ON;
-- Create CaseData Table
CREATE TABLE dbo.CaseData
(
Id INT IDENTITY(100,1) PRIMARY KEY
,CaseDate DATE NOT NULL
,Customer VARCHAR (50) NOT NULL
,Category TINYINT NOT NULL
,CaseDescription VARCHAR (30) NOT NULL
,CaseStatus VARCHAR(7) NOT NULL
,ClosedDate DATE NULL
,Resolution VARCHAR (400) NULL
);
CREATE TABLE dbo.Category
(
ID INT IDENTITY(1,1) PRIMARY KEY
,CatType VARCHAR (25) NOT NULL
);
GO
--Add values to the Category Table
USE GKHELPDESK
go
INSERT INTO Category (CatType)
VALUES
('Equipment')
,('Email')
,('Asset')
,('Navision')
,('User')
,('Desktop')
,('Laptop')
,('Disaster Recovery')
,('Phone')
,('Citrix')
,('Software')
,('Hardware')
,('PDF')
,('Sales')
,('Server')
,('Security')
,('Database')
,('Printing')
,('Meeting')
,('Project')
,('Microsoft Office');
USE GKHELPDESK
go
INSERT INTO CaseData(CaseDate, Customer, Category, CaseDescription, CaseStatus)
VALUES
('01/07/2012', 'Keith', '17', 'Building GK Help Desk Database', 'Pending');
--01/12/2012 recieved a mismatch error in access so changing this to INT datatype
alter table CaseData Alter column Category INT
--01/13/2012 trying to add referential integrity for a category drop down list in access
USE GKHELPDESK
GO
Alter Table dbo.CaseData
Add Constraint FK_Category_CatType Foreign Key (Category)
References dbo.Category (ID);
--01/14/2012 change the Case Status column to a BIT datatype
alter table CaseData Alter column CaseStatus BIT
Any suggestions are welcome.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply