Joining Tables

[TheStriker]

Complex Query Problem (Brainteaser)

Happy Friday,

I have a SELECT query consisting of 5 tables (i.e. Comments, Users, ActionType, IssueType, ContactType). The main table 'Comments' is on the MANY side of a one-to-many relationship with the other tables with the following fields and relationships:

Field Many Side ----------------------------------------------------------- Table One Side
CommentID (Primary Key)
SiteID Number
Comment Text
ContactTypeID Number <--------------------------------------------------- ContactType
IssueTypeID Number <----------------------------------------------------- IssueType
ActionTypeID Number <---------------------------------------------------- ActionType
UserID Text <-------------------------------------------------------------- Users
dteCreatedOn Date/Time

I am trying to find a way to join the tables in a way that would only update the Comments table and not the other tables whenever the user adds information in the comments table. Is there a way to accomplish this? Let me know if you need more info. Thanks in advance.

TheStriker

 

[GumbyD]

If you only want to update the tables in a specific table/query than only put the fields from that table/query into the grid of the query. As long as the query remains in an updatable state then it will only update the fields in that table/query. If the issue is that you want to create records that do not have related records (orphan records - which I would not recommend) then you need to remove the referencial integrity from the joins in the relationships window.

Not sure it that helps or not. Were you planning to build a form off of this query?

GumbyD

 

[TheStriker]

GumbyD,

I thought I would explain the full scenario for better clarification:

I have a Main form with a subform that will display comments about the record being displayed at any given time. Within the subform, I have a listbox that displays comments already recorded by on a record. When the user selects from the listbox the subform reflects the comment corresponding to the selection in the listbox. Since the listbox gets its values from a combo box in the main form, I had to use the following method to get the subform to display records selected from the listbox:

***************************************************************************
Private Sub List66_AfterUpdate()

Forms![New]![Comments_EthernetSwitch subform]![nSiteID].Value = Me.List66.Column(0)
Forms![New]![Comments_EthernetSwitch subform]![sComment].Value = Me.List66.Column(6)
Forms![New]![Comments_EthernetSwitch subform]![nContactType].Value = Me.List66.Column(3)
Forms![New]![Comments_EthernetSwitch subform]![nActionType].Value = Me.List66.Column(5)
Forms![New]![Comments_EthernetSwitch subform]![nIssueType].Value = Me.List66.Column(4)
Forms![New]![Comments_EthernetSwitch subform]![dtCreatedON].Value = Me.List66.Column(1)
Forms![New]![Comments_EthernetSwitch subform]![sInitials].Value = Me.List66.Column(2)
Forms![New]![Comments_EthernetSwitch subform]![nCommentID].Value = Me.List66.Column(7)
End Sub
***************************************************************************
My problem is when I want to add a new comment. For some reason when I go to add a new comment, the record selected in the listbox at the time the user clicks the 'Add' button gets updated with the comments when I only want the comments to get updated. BTW, I used the wizard to create the Add Record button along with some custom functions. Here is the code associated with the 'Add' button:

****************************************************************************
Private Sub Command76_Click()
On Error GoTo Err_Command76_Click
Dim Initials As Control


'If No Site is Selected then notify user to select a site
If IsNull(Forms!New!Combo3) Then
DoCmd.Beep
MsgBox "Please Select a Site"

'If user not logged in then notify user they must log in before performing this action
ElseIf IsNull(Forms!New!Initials) Then
DoCmd.Beep
MsgBox "You must be logged in to perform this action. Please log in and try your request again", vbInformation + vbOKOnly, "Not Logged In"
DoCmd.Close acForm, "New", acSaveNo
DoCmd.OpenForm "Login"

Else

'Prepare fields for user input
Me.sInitials.Value = Forms!New!Initials
Me.sInitials.Enabled = True
Me.sInitials.Locked = False
Me.sInitials.BackColor = 16777215

Me.dtCreatedON.Enabled = True
Me.dtCreatedON.Locked = False
Me.dtCreatedON.BackColor = 16777215

Me.sComment.Enabled = True
Me.sComment.Locked = False
Me.sComment.BackColor = 16777215

Me.nContactType.Enabled = True
Me.nContactType.Locked = False
Me.nContactType.BackColor = 16777215

Me.nIssueType.Enabled = True
Me.nIssueType.Locked = False
Me.nIssueType.BackColor = 16777215

Me.nActionType.Enabled = True
Me.nActionType.Locked = False
Me.nActionType.BackColor = 16777215

'Add New Record
DoCmd.GoToRecord , , acNewRec

Forms![New]![Comments_EthernetSwitch subform]![nSiteID].Value = Me.List66.Column(0)
Forms![New]![Comments_EthernetSwitch subform]![sComment].Value = ""
Forms![New]![Comments_EthernetSwitch subform]![nContactType].DefaultValue = "[SELECT]"
Forms![New]![Comments_EthernetSwitch subform]![nActionType].DefaultValue = "[SELECT]"
Forms![New]![Comments_EthernetSwitch subform]![nIssueType].DefaultValue = "[SELECT]"
Forms![New]![Comments_EthernetSwitch subform]![dtCreatedON].Value = Now()
Forms![New]![Comments_EthernetSwitch subform]![sInitials].Value = Forms![New]![Initials]

Exit_Command76_Click:
Exit Sub

Err_Command76_Click:
MsgBox Err.Description
Resume Exit_Command76_Click
End If


End Sub
*****************************************************************************

Again, I want to add records to the comments table without affecting the tables that are joined with it. How can I accomplish this?

Thanks,
TheStriker