By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,654 Members | 1,840 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,654 IT Pros & Developers. It's quick & easy.

Disable button with Filter

P: 6
I have a form with a directory of names and a filter on the bottom of the form. The customer wants a button for each letter of the alphabet so the user can find a name quickly....example: click on the "S" button to display all last names that start with an S (Smith). I would like to be able to disable/gray out a button if there are no last names that begin with that letter. For example the "Q" button if there are no names that begin with Q.

Here is an example of the "Q" button

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdQ_Click()
  2.     On Error GoTo errorhandler
  4.     Me.FilterOn = True
  5.     Me.Filter = "Left(" & mstrFiltField & ", 1) = 'Q'"
  7. ExitHere:
  8.     Exit Sub
  10. errorhandler:
  11.     Call LogError(Err.Number, Err.Description, "Form_frmContratorDir", "cmdQ_Click", , , True)
  12.     Resume ExitHere
  13. End Sub
I know I need an IfElse and place a cmdQ_Click.enable = False, but I'm not sure how to do it.

Can anyone help me with this?
Thank you!
Apr 26 '10 #1
Share this Question
Share on Google+
7 Replies

Expert 100+
P: 931
There are a few ways to do this. Maybe the most straightforward is this:

Expand|Select|Wrap|Line Numbers
  1. If IsNull(DLookup("mstrFiltField", "tbl", " Like 'Q%' ")) Then
  2.      cmdQ.Enabled = False
  3. End If

Here I called your table "tbl". You really want to disable the appropriate buttons at the point where the form opens though. That presents the prospect of doing this for 26 command buttons, which would be tedious, time-consuming, and (in my opinion) poor programming style. So, in the Form Open event, you might want to set up a loop where you check each button's letter using the test above, and enable/disable the button appropriately.

Apr 26 '10 #2

P: 6
I will try to create the loop like you suggest. That makes much more sense.
Thank you!
Apr 26 '10 #3

Expert 100+
P: 931
FYI...I tested this out and had to modify the DLookup criteria:

Expand|Select|Wrap|Line Numbers
  1. ctl.Enabled = Not (IsNull(DLookup("last_name", "tbl", "Left(last_name,1) = '" & Right(ctl.Name, 1) & "'")))

Basically, if the letter is not found, DLookup returns null, the IsNull function returns true, and we want not true = false to disable the command button. Let me know if you need help with the loop.

Apr 26 '10 #4

P: 6
Yes, I am having trouble with this. That would be wonderful if you could help me with the loop.
Thank you!
Apr 26 '10 #5

Expert 100+
P: 931
I found this to work nicely in the mock database I made:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  3. Dim ctl As Control
  5. For Each ctl In Me.Controls
  6.     If ctl.ControlType = acCommandButton And ctl.Name Like "cmd?" Then
  7.         ctl.Enabled = Not (IsNull(DLookup("last_name", "tbl", "Left(last_name,1) = '" & Right(ctl.Name, 1) & "'")))
  8.     End If
  9. Next
  11. End Sub

This loops through every control on the form. If the control is a command button having a name of the form "cmd*" then it will run the test and enable/disable the button correspondingly.

Apr 26 '10 #6

P: 6
It worked! Thank you very much!
Apr 26 '10 #7

Jim Doherty
Expert 100+
P: 897
In addition to domain aggregate functions you might want to consider how other SQL methods could also handle the dataset.

ie: you could prepare an SQL statement to retrieve unique instances of the first letter of the surname as an independant recordset in code on opening your form. (You should get a potential maximum return dataset of 26 records or less)

This unique result set (26 records maximum) could be traversed 'once' and the contents assign to a string variable. Testing the contents of the string in a loop against the logic of whether or not to enable/disable your command buttons.

There are many ways to skin a cat and I mention this in essence as it is worthy to consider the alternatives especially if it helps optimise performance where large datasets are encountered.

Have a look at the attached sample to give you a flavour of what I am talking about in terms of a different approach that has essentially the same result


Attached Files
File Type: zip (18.9 KB, 61 views)
Apr 27 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.