Use table to set command button and textbox properties

Posted on 2004-11-10
Last Modified: 2008-03-06
Hello All,

I have a form that has 10 command buttons on it.  Each button has a textbox under it.
I would like to ".enable = True/False" and ".visible = True/False" the command buttons and ".visible = True/False" the textboxes based on the table's recordset.


If Status = "In for the day"

cmdClockIN.Enabled = False
cmdClockIN.Visible = True
txtClockIN.Visible = True

cmdClockOUT.Enabled = True
cmdClockOUT.Visible = True
txtClockOUT.Visible = False

Each record in the table will have a strStatus field and will contain something like the following:
In before shift
Out before shift
In for the day
Out to lunch
In from lunch
Out during shift
In during shift before lunch
In during shift after lunch
Out for the day
In after shift
Out after shift

So the table will pretty much only be 11 records total.
Each record should have data type Yes/No fields (format True/False) for all the command buttons and textboxes that need to be set.
So if the employee's current status is "Out to lunch" the only buttons that should be enabled would the the cmdLunchIN button, etc.

Can this be done?
Is there an easy way to do it?

Question by:Die-Tech
    LVL 10

    Expert Comment

    In your On Current Event of your form put something like

    If me.Status = "In for the day" then
         me.cmdClockIN.Enabled = False
         me.cmdClockIN.Visible = True
         me.txtClockIN.Visible = True
    ElseIf me.Status = "Whatever Else" The
         me.cmdClockIN.Enabled = False
         me.cmdClockIN.Visible = True
         me.txtClockIN.Visible = True
         me.cmdClockIN.Enabled = true
         me.cmdClockIN.Visible = false
         me.txtClockIN.Visible = false
    End If

    Continue with the If statements like above until you have covered all the status statements.  Make sure that your last statement (Else) is what you want the buttons to be like if status is something other than what you have determined.

    You can put the above code in the after update event of that Status field if they use it to update their status...then your buttons will change based on the new value they change status too.

    LVL 4

    Author Comment


    Thanks for the comment.

    I had something like that when I first started this.  I ended up with about 30 lines under each of the If and ElseIf statements (10 buttons with 3 settings to set)

    I changed it to use a Select Case statement.... but I still have about 30 lines for each case.
    I was to use a table that would hold all the settings, then I could look up a record where Status = "In for the day" and use the data from that recordset to set all the command button settings.  I thinking my code would only have to specify the 30 lines one time..... instead of one time for each of the Status'

    LVL 10

    Expert Comment

    If the buttons are not stacked on top of each other you could always put rectangles of the same color as behing the buttons and make it visible and invisible.

    You could create the code as a module and call it from the after update and on current event of the form.  That would allow the code to be entered one time.  Still all the lines of code, but would be in a module once instead of in your form 2 or 3 times.

    Wish I had a better answer for you.  Good luck on your project.

    LVL 9

    Accepted Solution

    I think that for this I would be inclined to make a lookup table
    that held your strStatus field plus a field for each button
    Doing it this way means that if you add another status then you just edit the table rather than rewritng all those ifs!

    I would add the new table to the query behind the form joined by the status field and add the 10 Status fields on the form as hidden textboxes and just refer to them in code.

    Me.cmdClockIN.Visible = Me.cmdClockINStatus

    LVL 4

    Author Comment


    Thanks for the comment.

    That sounds like what I am wanting to do.
    Can you elaborate on the table design a little bit?

    I have 10 buttons, each with 3 settings:
      cmdClockIN.Visible = True/False
      cmdClockIN.Enabled = True/False
      txtClockIN.Visible = True/False

    Depending on what strStatus is, the 3 settings could be either True or False for each one.

    Plus, they will change dynamically after the employee chooses one of the cmd buttons and clicks it.

    LVL 9

    Assisted Solution

    You will probably need to create all 30 fields then, doing it this way would really tie it to the value of the status field in the query, if you edit the record and save changes it should update OK.
    What happens when he clicks a button? is the record saved automaticaly?

    LVL 4

    Author Comment


    Q: What happens when he clicks a button? is the record saved automaticaly?
    A: The OnClick code for that button either adds a new record, or edits an existing record in the tblClockTimes table and then updates the strStatus field.  (e.g. strStatus was "In for the day"... employee clicks the cmdLunchOUT button, the tblClockTimes record is found and edited, the strStatus is edited to now say "Out to lunch")

    LVL 58

    Expert Comment

    > "Is there an easy way to do it?"

    Easy? well, you seem to be doing fine. The problem is rather the readability of the code... I think I would start with basic boolean calculations to get the logic right.

        Dim fClockIn As Boolean
        Dim fLunchIn As Boolean
        Dim fDuringShiftOut As Boolean
        ' etc...

        ' Rules here:

        fClockIn = (Status = "Not In")
        fLunchIn = (Status = "Out for Lunch" Or Status = "Out before Lunch" And Time() > #12:15 PM#)
        fDurintShiftOut = (Status = "In before Lunch" Or Status = "In after Lunch")
        ' etc...

        ' Adjust display:
        cmdClockIn.Enabled = fClockIn:     txtClockIn.Visible = fClockIn
        cmdLunchIn.Enabled = fLunchIn:    txtLunchIn.Visible = fLunchIn

    This way, you have one section where you concentrate on *logic*, and one where concenntract on *GUI*...
    I always find it difficult to mix both.

    Good Luck!
    LVL 58

    Expert Comment

    I also like Bat17's idea (give him the points if you use it). If it was a little more complex that that, it would be the only way to go. But I wouldn't bother with hidden fields:

        With CurrentDb.OpenRecordset("tlkpStatusFlags", dbOpenTable)
            .Seek "=", strCurrentStatus
            If .NoMatch Then .MoveFirst   ' Should never happen, but record 0 is "unknown"...

            cmdClockIn.Enabled = !ysnClockIn:     txtClockIn.Visible = !ysnClockIn
        End With

    The table will contain a bunch of check boxes, where you decide what to do when. Easy to maintain and to understand if someone else has to take over :)

    You could still use some VB for special cases (e.g. test agains time of day) by mixing both methods...

    LVL 4

    Author Comment

    Thanks Bat17,

    I ended up going with the lookup table and the status field.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Sql code problem 6 12
    Greater Than Current Month 2 26
    Passing a TempVar to a Combo box form 3 24
    Open Args 16 21
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now