none
Reference column in a different table based on combo object

    Question

  • 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
    

    Keith


    • Edited by Keith732 Wednesday, August 28, 2013 4:38 PM
    Wednesday, August 28, 2013 4:37 PM

Answers

  • Any way you can work in the

    .[Column](2)

    or

    .[Column](3)

    Or whichever column you need?


    Chris Ward

    • Marked as answer by Keith732 Wednesday, August 28, 2013 6:27 PM
    Wednesday, August 28, 2013 4:44 PM

All replies

  • Any way you can work in the

    .[Column](2)

    or

    .[Column](3)

    Or whichever column you need?


    Chris Ward

    • Marked as answer by Keith732 Wednesday, August 28, 2013 6:27 PM
    Wednesday, August 28, 2013 4:44 PM
  •  

    Thanks Chris but it gives me the error Method or Data Method not found.

    I have also attached an image.


    Keith

    Wednesday, August 28, 2013 4:51 PM
  • I changed Me.DefectCategory to Me.Combo45.Column(1) and it worked. I suppose DefectCategory is the actual field access is seeing rather than the object which displays that fields data.

    Thanks.


    Keith

    Wednesday, August 28, 2013 6:27 PM