Use table to set command button and textbox properties

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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Die-TechAuthor Commented:

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'

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.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Die-TechAuthor Commented:

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.

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?

Die-TechAuthor Commented:

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")

> "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!
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...

Die-TechAuthor Commented:
Thanks Bat17,

I ended up going with the lookup table and the status field.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.