none
Conditioanal One-to-many-many Table Link Using VBA Macros RRS feed

  • Question

  • This topic may interest anyone.

    I have One table that would conditionally link two tables.

    Here are the screenshots:

    Help with VBA Macros - Two Pictures

    Saturday, October 29, 2016 9:54 PM

Answers

  • On my own, I have found an answer.

    Here are some more screenshots:


    jp

    • Marked as answer by JohnDBCTX Sunday, October 30, 2016 6:22 AM
    Sunday, October 30, 2016 6:15 AM
  • And here is the code snippet:

    Option Compare Database

    Dim DBX As Database
    Dim BaseClsPubRoutCalcs As Recordset
    Dim BaseClsPubRoutForLoopBlk As Recordset

    Public Function LineNumberLinker() On Error GoTo cmdLineNumberLinker_Click_Err Set DBX = CurrentDb Set BaseClsPubRoutCalcs = DBX.OpenRecordset("BaseClassPublicRoutineCalculations", dbOpenDynaset) Set BaseClsPubRoutForLoopBlk = DBX.OpenRecordset("BaseClassPublicRoutineForLoopBlocks", dbOpenDynaset) Select Case Me.LineNumber.Value Case Is = BaseClsPubRoutCalcs.Fields("LineNumber").Value: Me.chdSubFormLinker.SourceObject = "Table.BaseClassPublicRoutineCalculations" Case Is = BaseClsPubRoutForLoopBlk.Fields("LineNumber").Value: Me.chdSubFormLinker.SourceObject = "Table.BaseClassPublicRoutineForLoopBlocks" Case Else: Me.chdSubFormLinker.SourceObject = "Table.BaseClassPublicRoutineCalculations" End Select cmdLineNumberLinker_Click_Err: If Err.Number >= 0 Then Resume Next End If End Function

    Private Sub cmdLineNumberLinker_Click()
    LineNumberLinker
    End Sub

    Private Sub Form_Current()
    LineNumberLinker
    End Sub

    Public Sub Form_AfterUpdate()
    LineNumberLinker
    End Sub


    See if you could figure this out for yourselves.

    Regards,

    JohnDBCTX


    jp

    • Marked as answer by JohnDBCTX Sunday, October 30, 2016 6:22 AM
    Sunday, October 30, 2016 6:20 AM

All replies

  • Pictures 1 and 2
    Saturday, October 29, 2016 9:56 PM
  • Now here is the relationship diagram.

    Relationship Diagram 001

    Saturday, October 29, 2016 10:00 PM
  • What I want to perform is the following:

    Conditionally filter between the two tables into one sub form using an automated macro.

    I do hope this would help me a great deal.

    Can anyone help me out on how to solve this so-called real world related problem?

    Regards,

    JohnDBCTX

    Saturday, October 29, 2016 10:03 PM
  • On my own, I have found an answer.

    Here are some more screenshots:


    jp

    • Marked as answer by JohnDBCTX Sunday, October 30, 2016 6:22 AM
    Sunday, October 30, 2016 6:15 AM
  • And here is the code snippet:

    Option Compare Database

    Dim DBX As Database
    Dim BaseClsPubRoutCalcs As Recordset
    Dim BaseClsPubRoutForLoopBlk As Recordset

    Public Function LineNumberLinker() On Error GoTo cmdLineNumberLinker_Click_Err Set DBX = CurrentDb Set BaseClsPubRoutCalcs = DBX.OpenRecordset("BaseClassPublicRoutineCalculations", dbOpenDynaset) Set BaseClsPubRoutForLoopBlk = DBX.OpenRecordset("BaseClassPublicRoutineForLoopBlocks", dbOpenDynaset) Select Case Me.LineNumber.Value Case Is = BaseClsPubRoutCalcs.Fields("LineNumber").Value: Me.chdSubFormLinker.SourceObject = "Table.BaseClassPublicRoutineCalculations" Case Is = BaseClsPubRoutForLoopBlk.Fields("LineNumber").Value: Me.chdSubFormLinker.SourceObject = "Table.BaseClassPublicRoutineForLoopBlocks" Case Else: Me.chdSubFormLinker.SourceObject = "Table.BaseClassPublicRoutineCalculations" End Select cmdLineNumberLinker_Click_Err: If Err.Number >= 0 Then Resume Next End If End Function

    Private Sub cmdLineNumberLinker_Click()
    LineNumberLinker
    End Sub

    Private Sub Form_Current()
    LineNumberLinker
    End Sub

    Public Sub Form_AfterUpdate()
    LineNumberLinker
    End Sub


    See if you could figure this out for yourselves.

    Regards,

    JohnDBCTX


    jp

    • Marked as answer by JohnDBCTX Sunday, October 30, 2016 6:22 AM
    Sunday, October 30, 2016 6:20 AM
  • Hi John,

    Thanks a lot for sharing, it will help others who run into the same issue.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 31, 2016 5:36 AM