Button Automation

OldManRiver · Jan 29, 2009

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:

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:

Code:

'            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:

Code:

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:

Code:

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:

Code:

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

gemma-the-husky · Jan 30, 2009

without being silly - why do you want to add buttons to a screen

much easier to either

a) have the buttons already there, hidden or inactive
b) use a shortcut menu

OldManRiver · Jan 30, 2009

gemma-the-husky said:
without being silly - why do you want to add buttons to a screen

much easier to either

a) have the buttons already there, hidden or inactive
b) use a shortcut menu

Gemma,

Actually, when you have it automated correctly they auto-gen out of the DB itself onto the screen. Did not set up the example table to put in height, width and other properties, which if you set in the DB, set aside an area in your form for them, you can then calc their placement and auto-gen them.

So then when developing, instead of spending all the time in def'ing and messing with buttons, you just open table and define for a screen and they write themselves into the form.

Sending you thumbnails of this so you will understand. They are:

1. Form dev mode,
2. Form output mode,
3. Form display in production mode with other screens.

Using the method shown, with category tabs and subcategory or processing feature buttons, allowing for full use of subforms, you can pre-design full libraries of forms to use for any application, so you do not want to spend time making buttons for them, just importing them and calling them.

Furthermore in automating the gen of apps, if correctly designed, you will have one .mdb file as a library of forms, modules, etc. and you add a form with buttons that you select the form, you want into new .mdb file "X" and it get the form, the class module, the tables, etc the pushes them into new .mdb.

But back to the problem. You help me solve this and I'll show you a few things about how to automate in Access.

Thanks!

OMR

HiTechCoach · Jan 30, 2009

OldManRiver said:

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:

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:

Code:

'            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:

Code:

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:

Code:

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:

Code:

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

OldManRiver,

In

Function Get_Btn(MyForm)

you are passing using MyForm but in our code you use SourceForm. Which is correct?

Try:

Code:

            SourceForm.(![btn_nam]).Caption = ![btn_lab]
            SourceForm.(![btnName]).Visible = ![btn_vis]
            SourceForm.(![btnName]).Enabled = ![btn_enb]

or should it be:

Code:

            MyForm.(![btn_nam]).Caption = ![btn_lab]
            MyForm.(![btnName]).Visible = ![btn_vis]
            MyForm.(![btnName]).Enabled = ![btn_enb]

OldManRiver · Jan 30, 2009

HiTechCoach,

Done it both ways, either works. Orignally had it as:

Function Get_Btn(MyForm as Form)

but assignment was not right so changed to variant, then worked.

When using the SourceForm the Globals declares

Public SourceForm as Form

and the calling routine has:

Set SourceForm = Me

Before invoking the function.

But I'm just have a "DUH!!" moment as I forgot how I do var substitutions. Been working PHP web dev, too long and forgot how this is done here.

OMR

HiTechCoach · Jan 30, 2009

OldManRiver said:
HiTechCoach,

Done it both ways, either works. Orignally had it as:

Function Get_Btn(MyForm as Form)

but assignment was not right so changed to variant, then worked.

When using the SourceForm the Globals declares

Public SourceForm as Form

and the calling routine has:

Set SourceForm = Me

Before invoking the function.

But I'm just have a "DUH!!" moment as I forgot how I do var substitutions. Been working PHP web dev, too long and forgot how this is done here.

OMR

OMR,

You said:

You help me solve this and I'll show you a few things about how to automate in Access.

Are you still willing to share?

OldManRiver · Feb 15, 2009

Sure!!

Sorry for long delay. Production Run deadline and all.

OMR

OldManRiver · Mar 18, 2009

Following

HiTechCoach,

Are you following?

Also have the thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=167351

Which is an automation script that will allow save of any form to any table.

OMR

DCrake · Mar 18, 2009

Three questions

How would work if it was an MDE?

How do you add VBA code to the button events?

How would you tell one button that another button is there?

OldManRiver · Mar 21, 2009

DCrake said:
Three questions

How would work if it was an MDE?

How do you add VBA code to the button events?

How would you tell one button that another button is there?

DC,

In reading you can use the following:

foreach ctl in form.control
....
next

and read the buttons this way without having to declare hard code, especially if you put the button name and the function the button would call into a table and call the function via a query.

OMR

DCrake · Mar 23, 2009

What I don't understand is the need or the inclination to attempt to create controls, code, events, etc,, dynamically. Storing the code in a database is all good and well but what hapopens if the database becomes corrupt or compromised. You loose everything. I have a paper on a system that was written 100% based on information held in a table. The forms, reports, code, the lot. Unfortunately its a hard copy but if I can I will scan it and let you have a look at it.

David

OldManRiver · Mar 26, 2009

DCrake said:
What I don't understand is the need or the inclination to attempt to create controls, code, events, etc,, dynamically. Storing the code in a database is all good and well but what hapopens if the database becomes corrupt or compromised. You loose everything. I have a paper on a system that was written 100% based on information held in a table. The forms, reports, code, the lot. Unfortunately its a hard copy but if I can I will scan it and let you have a look at it.

David

D,

Kewl, would love to see it. I've already gotten things pretty automated and can deliver almost anything to a customer based on pre-built subforms, but the better I get at this the better I like it and the faster it goes.

OMR

Banana · Mar 26, 2009

Just want to mention that I'd imagine it far more reasonable to design a custom wizard to help automate building the subform, which would be more flexible than having code to build button dynamically. Just a thought.

Button Automation

OldManRiver

Registered User.

gemma-the-husky

Super Moderator

OldManRiver

Registered User.

Attachments

HiTechCoach

Well-known member

OldManRiver

Registered User.

HiTechCoach

Well-known member

OldManRiver

Registered User.

OldManRiver

Registered User.

DCrake

Remembered

OldManRiver

Registered User.

DCrake

Remembered

OldManRiver

Registered User.

Banana

split with a cherry atop.

Users who are viewing this thread