Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

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?

  • 4
  • 2
  • 2
  • +1
2 Solutions
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.


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

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.

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now