Many-to-One Relationship

[annie52]

I have four subforms in a tab control and just realized that we could actually have a Many-to-One relationship between the parent and one of the subforms. I've never dealt with that before and admit that it scares me. I've tried searching this site but keep getting timed out. Will this type of relationship even work in my current configuration?

 

[MajP]

That is not quite enough to go on. Explain the tables in general terms and the relationship. Explain the primary and foreign keys and in plain terms how they are related.

 

[jamaarneen]

Hi annie52

I don't really understand what your problem is, and you are scared of.

yes, there is a possibility of a one-to-many relationship between parent-child. Is your problem that you don't know how to make this relationship?

and what do you mean by [blue]'...in my current configuration'[/blue]?

Ja

 

[annie52]

Thank you both for responding. I realize my post was somewhat vague. The problem is that I'm not sure what issues may arise.

I'm quite familiar with one-to-many relationships in the parent-child arena. I have never worked with a many-to-one relationship with linked forms.

My main form holds information about requisitions. It is linked to three of the four subforms by ReqnID in a one-to-many relationship. It turns out that TicketNum, which is my primary key in the fourth subform can relate to more than one requisition (main form) so I end up with a many-to-one relationship.

I'm just trying to grasp how to maintain a many-to-one relationship alongside a one-to-many relationship in the same form.

 

[MajP]

I am assuming then that your requistion table looks something like

tblRequistion
reqnID (primary key)
ticketNum_FK (foriegn key to the tickets table)

If that is the case then there should be no problem with

subforms 1-3properties
linked Master fields: ReqnID
linked child fields: ReqnID (some foreign key to reqnID)

subform 4
linked Master fields: ticketNum_FK (the name of the fk in the requistion table)
linked child fields: ticketNum (the name of the pk in the ticket number db)

Now subform 4 will show the correct info, you probably will not be able to add to it in that view if you have enforced referential integrity.

 

[annie52]

Hi, MajP. Sorry for the delay; I was called away for another project.

Your assessment of my database is accurate. I had set up the relationship pretty much the way you suggest and I can add new records. However, I cannot associate more than one Requisition with a given TicketNumber.

When I requery the ReqnWatch table, I get:

Run-time error 2115:
The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing RPP Tracking System from Saving the data in the field.

The DLATicketNum in the ReqnWatch table is not indexed or required. None of the fields on my form or subform have a validation rule. I tried the following code in the BeforeUpdate, AfterUpdate, and then with no code behind either event but still can't figure out how to fix this.

Private Sub TicketNum_AfterUpdate()
On Error GoTo Err_TicketNum_AfterUpdate

Dim Julian As String, Ser As String

If Trim(Forms![frmReqnWatch]![frmDLATicket].Form![TicketNum] & "") <> "" Then
Forms!frmReqnWatch!DLATicketNum = Forms![frmReqnWatch]![frmDLATicket].Form![TicketNum]
Julian = Forms!frmReqnWatch!JD
Ser = Forms!frmReqnWatch!Serial
Forms![frmReqnWatch]![frmDLATicket].Form![TicketNum].Undo
Forms!frmReqnWatch.Requery
Forms!frmReqnWatch.Filter = "[JD]='" & Julian & "' AND [Serial] = '" & Ser & "'"
Forms!frmReqnWatch.FilterOn = True
Forms!frmReqnWatch!cmdAllReqns.Visible = True
End If

Exit_TicketNum_AfterUpdate:
Exit Sub

Err_TicketNum_AfterUpdate:
MsgBox "Error #" & Err.Number & Chr(10) & Chr(13) & _
Err.Description, vbOKOnly, "RPP Tracking System"

End Sub

 

[annie52]

Oh, boy. It turns out that I actually had a many-to-many relationship going on. Once I realized that and created a junction table, my issue was resolved.