Reference Data in an alternate table from current record

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

Open in new window

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

Open in new window

kwoznica732Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
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.


Not sure, but it sounds like all you need is a lookup:

Dlookup("DefectCatType", YourDefectCategoryTableName","DefectCategory=" &  Me.DefectCategory.Value)


For example, If you are wanting the CustomerName  and you only have the CustomerID, you can use an expression like this to get the Customer Name:

Dlookup("CustomerName", "tblCustomers", "CustomerID=" & me.txtCustomerID)


Then you can use this in your code like this:
SomeThing=Dlookup("CustomerName", "tblCustomers", "CustomerID=" & me.txtCustomerID)

You can see the help files on the Dlookup function for more information...


;-)


JeffCoachman
0
Jeffrey CoachmanMIS LiasonCommented:
Remember I don't know your object names bit it might be like this as well...

Dlookup("DefectCatType", YourDefectCategoryTableName","DefectCategoryID=" &  Me.DefectCategory.Value)

In other words, the last argument will be the exact field name of the Primary key in the table being searched...

I am sure you can see from what I am posting how to try and get this working...
;-)

Jeff
0
IrogSintaCommented:
Doesn't your DefectCategory combo box display the DefectCatType to the user?  I am assuming that this combo box has 2 columns with the BoundColumn being 1 and the width of the first column being 0" so that the user only sees the DefectCatType and not the ID.  I am also assuming that the RowSource property is
SELECT ID, DefectCatType FROM DefectCategory
If these assumptions are correct, all you need in your code is:
stDefect = Me.DefectCategory.Column(1)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.