Button Automation

[TBotNik]

All,

I'm writing a script/function to automated putting buttons on the screen.

I'm running into a problem with var substitutions in my strings, which is necessary to make it run.

Here is my code:

Function Get_Btn(MyForm)
    Dim Wspace As DAO.Workspace, dbs As DAO.Database, rsDEST As DAO.Recordset
    Dim SQLstmt As String, SrcIdx As Variant, frmName, btnName, btnLabl, N
    Set Wspace = DBEngine.Workspaces(0)
    Set dbs = CurrentDb
    DoCmd.Hourglass True
    frmName = SourceForm.Name
    SQLstmt = "SELECT * FROM qryBTNjoin WHERE ([btn_fnm]='" & frmName & "');"
    Set rsDEST = dbs.OpenRecordset(SQLstmt, dbOpenDynaset)
    With rsDEST
        .MoveLast
        .MoveFirst
        For N = 1 To .RecordCount
            btnName = ![btn_nam]
'            btnLabl = Eval("SourceForm![" & btnName & "].Caption = ![btn_lab]")
'            SourceForm![ & btnName & ].Caption = ![btn_lab]
            SourceForm![![btn_nam]].Caption = ![btn_lab]
            Eval("SourceForm![btnName].Visible") = ![btn_vis]
            Eval("SourceForm![btnName].Enabled") = ![btn_enb]
            .MoveNext
        Next N
        .Close
    End With
    DoEvents
    DoCmd.Hourglass False
End Function

The lines:

'            btnLabl = Eval("SourceForm![" & btnName & "].Caption = ![btn_lab]")
'            SourceForm![ & btnName & ].Caption = ![btn_lab]
            SourceForm![![btn_nam]].Caption = ![btn_lab]
            Eval("SourceForm![btnName].Visible") = ![btn_vis]
            Eval("SourceForm![btnName].Enabled") = ![btn_enb]

Are my attempts to get this right and I've done this sort of substitution before, but right now my mind is blank.

The query code is:

SELECT tblButtons.btn_id, tblButtons.btn_fnm, tblButtons.btn_nam, tblButtons.btn_lab, tblButtons.btn_sfm, tblButtons.btn_act, tblButtons.btn_vis, tblButtons.btn_enb
FROM tblButtons LEFT JOIN MSysObjects ON tblButtons.btn_fid = MSysObjects.Id
WHERE (((tblButtons.btn_nam)<>"") AND ((tblButtons.btn_vis)=Yes) AND ((tblButtons.btn_enb)=Yes))
ORDER BY tblButtons.btn_fnm, tblButtons.btn_nam;

The buttons table "tblButtons" is:

Table: tblButtons
Columns:
Name      Type           Size        Attributes
btn_id    Long Integer   4           Fixed Size, Auto-Increment
btn_fid   Long Integer   4           Fixed Size
btn_nam   Text           20          Variable Length
btn_fnm   Text           50          Variable Length
btn_lab   Text           80          Variable Length
btn_sfm   Text           50          Variable Length
btn_act   Text           50          Variable Length
btn_vis   Yes/No         1           Fixed Size
btn_enb   Yes/No         1           Fixed Size
  
Table Indexes
Name        Number of Fields   Order by       Order
btn_fid     1                  Self           Ascending
btn_id      1                  Self           Ascending
PrimaryKey  1                  btn_id         Ascending

Field descriptions are:

btn_fid  Xref to ID in MSysObjects table
btn_nam  button name/identifier as used in form
btn_fnm  form name where button resides
btn_lab  button label/caption property
btn_sfm  subform that button calls, if any
btn_act  subroutine that button calls, if any
btn_vis  button visible property
btn_enb  button enabled property

Some help here would be appreciated.

OldManRiver