SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using value in combo box based on Foreign key relationship


Using value in combo box based on Foreign key relationship

Author
Message
kwoznica
kwoznica
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 481
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



WendellB
WendellB
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2203 Visits: 1838
If you want to display the content of the combo box control, then you need to use the syntax that chooses the correct column in the combo box row properties. It should look something like:
stDefect = Me.DefectCategory.Column(X)

where X is the column number where the Defect Category is displayed. Note that the columns numbering begins with 0, so in many cases it would be Column(1).

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
kwoznica
kwoznica
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 481
Thanks for the reply Wendel. I did try your suggestion but received the error message Method or Data Member not found. I also attached a print screen.
do you think I'm missing a reference?
Attachments
Capture.JPG (22 views, 69.00 KB)
kwoznica
kwoznica
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 481
Disregard my last message. I changed Me.DefectCategory to Me.Combo45.Column(1) and it worked.
Thanks for that idea.
lshanahan
lshanahan
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1547 Visits: 438
kwoznica (8/28/2013)
Disregard my last message. I changed Me.DefectCategory to Me.Combo45.Column(1) and it worked.
Thanks for that idea.


That indicates the name of your combobox control is Combo45. Your initial code was trying to find a combobox named DefectCategory on your form. If you change the name of Combo45 on the form to DefectCategory, you would be able to use Me.DefectCategory.Column(1).

____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search