troubleshooting Question

Reference Data in an alternate table from current record

Avatar of kwoznica732
kwoznica732 asked on
Microsoft AccessMicrosoft SQL Server
3 Comments1 Solution470 ViewsLast Modified:
Hello all,

I am trying to use the below VBA code for sending an email. I would like to use the data in the DefectCategory combo box to pull the correct data the current record. The issue is that I only have the foreign key id in the combo box and would like the more descriptive field called DefectCatType in the table where the defect categories are listed.

There are two tables involved in this scenario. dbo.NonConfData and dbo.DefectCategory

dbo.NonConfData is the table with the combo box that references dbo.DefectCategory

The line I am having an issue with in vb is stDefect = Me.DefectCategory.Value

I need to have this display the data in the DefectCatType field of the dbo.DefectCategory table for the record in dbo.NonConfData.

Please let me know your thoughts on how this can be accomplished.

Private Sub Command75_Click()
Dim stId As Integer
Dim db As Database
Dim rs As DAO.Recordset
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim stText As String
Dim stDefect As String
'==Save the current record
If Me.Dirty Then Me.Dirty = False
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
stId = Me.Id
stDefect = Me.DefectCategory.Value
stText = "NonConformance Generated IR #" & stId & Chr$(13) & _
         Chr$(13) & "Defect Category:" & stDefect
With MailOutLook
    .BodyFormat = olFormatRichText
    .To = "keith@company.com"
    ''.cc = ""
    ''.bcc = ""
    .Subject = "NonConformance Generated IR #" & stId
    .HTMLBody = stText
    .Send
End With
End Sub
Below are my table definitions

-- Create CaseData Table   
CREATE TABLE dbo.NonConfData
   (
   Id INT IDENTITY(100,1) 
   ,CaseDate DATE NOT NULL Default GetDate()
   ,Creator INT NOT NULL
   ,ProducedBy INT NOT NULL
   ,DefectCategory INT NOT NULL
   ,Quantity Decimal(7,2) Default(1.00) NOT NULL
   ,DispositionAuthority VARCHAR (50)
   ,NonConfDetails NVARCHAR(MAX) NOT NULL
   ,InspectorDetails NVARCHAR(MAX) NOT NULL
   ,IRAttachments VARBINARY(MAX)
   ,CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED 
		(
		[ID] ASC
		)WITH 
			(
				PAD_INDEX  = OFF
				, STATISTICS_NORECOMPUTE  = OFF
				, IGNORE_DUP_KEY = OFF
				, ALLOW_ROW_LOCKS  = ON
				, ALLOW_PAGE_LOCKS  = ON
			) ON [PRIMARY]
	) ON [PRIMARY]
 GO
 
--Create the Defect Category table 
CREATE TABLE dbo.DefectCategory
   (
   ID INT IDENTITY(1,1) NOT NULL
   ,DefectCatType VARCHAR (25) NOT NULL
   ,DateStamp Date Default GetDate() NOT NULL
   ,CONSTRAINT [PK_DefectID] PRIMARY KEY CLUSTERED 
		(
		[ID] ASC
		)WITH 
			(
				PAD_INDEX  = OFF
				, STATISTICS_NORECOMPUTE  = OFF
				, IGNORE_DUP_KEY = OFF
				, ALLOW_ROW_LOCKS  = ON
				, ALLOW_PAGE_LOCKS  = ON
			) ON [PRIMARY]
	) ON [PRIMARY];
GO

--Add the foreign key constraint to the NonConfData table and DefectCategory table to establish a relationship

ALTER TABLE dbo.NonConfData  WITH CHECK ADD CONSTRAINT FK_DefectCategory_DefectCatType FOREIGN KEY(DefectCategory)
REFERENCES dbo.DefectCategory (ID)
GO
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros