MS Access Caption Assigned Button

Question

I am trying to write a code in the " Code Builder " section of MS Access, that will pull the caption from a button from a record. I have a table named Main Menu that includes fields " RecordID, Caption, and Report to Run. I need this button to assign it's caption based on the caption name in the MainMenu table.

Ex. Button A (Record ID = 1 , Caption = Borrower , Report to Run = rptCurrentBorrowers).

Upon clicking I need for this button's caption to = Borrower, by reading the caption from Button A's Record. I cannot specifically write the caption in the button it needs to be pulled from the MainMenu table.

Answer 1

Gord Thompson pointed me in the right location. So after doing a lot of research I was able to discover not only how to implement this type of code, but why it is useful. DLookup simply search a record or query within the database and pull information into its' scripted location dependent upon its criteria. The reason why I would need to implement this for a button caption, is so that my end user has the ability to change the caption of the button from a table, rather than manipulating the actual code. Here is the VBA code that I completed, with working buttons.

Private Sub Form_Open(Cancel As Integer)
    rptBorrower.Caption = DLookup("Caption", "MainMenu", "ReportID = 1")
    rptMediaList.Caption = DLookup("Caption", "MainMenu", "ReportID = 2")
    rptPastDue.Caption = DLookup("Caption", "MainMenu", "ReportID = 3")
End Sub


'' This code defines the Borrower Button
Private Sub rptBorrower_Click()
    rptBorrower.Caption = DLookup("Caption", "MainMenu", "ReportID = 1")

    Dim varBorrower As Variant
    varBorrower = DLookup("[ReportToRun]", "MainMenu", "[ReportID] = 1")
    DoCmd.OpenReport varBorrower, acViewReport

    rptBorrower_Click_Exit:
    Exit Sub
End Sub

'' This code is defines the Media List Button

Private Sub rptMediaList_Click()
    rptMediaList.Caption = DLookup("Caption", "MainMenu", "ReportID = 2")

    Dim varMediaList As Variant
    varMediaList = DLookup("[ReportToRun]", "MainMenu", "[ReportID] = 2")
    DoCmd.OpenReport varMediaList, acViewReport

    rptMediaList_Click_Exit:
    Exit Sub
End Sub

'' This code defines the Past Due Report Button

Private Sub rptPastDue_Click()
    rptPastDue.Caption = DLookup("Caption", "MainMenu", "ReportID = 3")

    Dim varPastDue As Variant
    varPastDue = DLookup("[ReportToRun]", "MainMenu", "[ReportID] = 3")
    DoCmd.OpenReport varPastDue, acViewReport

    rptPastDue_Click_Exit:
    Exit Sub
End Sub