Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-27-2011, 04:48 AM   #1
z0001130
Newly Registered User
 
Join Date: May 2011
Posts: 8
Thanks: 6
Thanked 0 Times in 0 Posts
z0001130 is on a distinguished road
Question Enable/disable Button depending on recordcount

Hi,

I has the On Open action for my Form set to:

Private Sub Form_Open(Cancel As Integer)
If Me.Recordset.RecordCount < 1 Then
Me.Command33.Enabled = False
Else
Me.Command33.Enabled = True
End If
End Sub

which works fine, unless I delete all the records shown on the form. Why, because the button sends and email based on the data on the form and it cannot be allowed to send an empty email. I can add code to the on_click action that checks and displays a msgbox if there are no records but that looked a bit untidy, far better to disable the button if the are no records.
So I added and after_update event similar to the code shown above.
All works fine as I said unless I delete all the records (the button does disable) and then when I click on my close button :

Private Sub Close_Button_Click()
On Error GoTo Err_Close_Button_Click

DoCmd.Close
DoCmd.OpenForm "Master"
Exit_Close_Button_Click:
Exit Sub
Err_Close_Button_Click:
MsgBox Err.Description
Resume Exit_Close_Button_Click

End Sub

I get a 'No Current Record' error box, when I hit OK the form closes. If I remove the On_Open event it doesn't error when closing.

Where am I going wrong.
Thanks,
John

z0001130 is offline   Reply With Quote
Old 05-28-2011, 02:11 AM   #2
stopher
AWF VIP
 
stopher's Avatar
 
Join Date: Feb 2006
Location: Southampton, UK
Posts: 2,396
Thanks: 20
Thanked 311 Times in 292 Posts
stopher is a jewel in the rough stopher is a jewel in the rough stopher is a jewel in the rough stopher is a jewel in the rough
Re: Enable/disable Button depending on recordcount

I think the code only needs to go in the On Current event of the form. Then it is not needed in the On Open or After Update events.

hth
Chris
stopher is offline   Reply With Quote
The Following User Says Thank You to stopher For This Useful Post:
z0001130 (05-29-2011)
Old 05-28-2011, 02:52 AM   #3
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,769
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Enable/disable Button depending on recordcount

but all you need to do is error trap the email button anyway

Code:
sub email_button_click
dim id as long  'used to detect an error below

on error goto fail

'if there is no current record this next line will error
'it doesn't have to be this - if you are call the email function with an argument , you don't need this. 'you just need something that will detect a blank form and not call the email 

id = recordid  
 
send email
exit sub

fail:
msgbox("no active record", & vbcrlf & vbcrlf & _
"error: " & err & "  Desc: " & err.description)
'I always like to show the error code
end sub

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 05-29-2011, 08:12 AM   #4
z0001130
Newly Registered User
 
Join Date: May 2011
Posts: 8
Thanks: 6
Thanked 0 Times in 0 Posts
z0001130 is on a distinguished road
Re: Enable/disable Button depending on recordcount

Quote:
Originally Posted by stopher View Post
I think the code only needs to go in the On Current event of the form. Then it is not needed in the On Open or After Update events.

hth
Chris
Thanks for that. Whilst this wasn't the soluction, it still errored on closing the form, it was the start of the solution. It made me rethink the logic for enabling the button.

I have now disabled the button by default, and if there are any records I activate it, rather than the other way round.

Thanks again,
John
z0001130 is offline   Reply With Quote
Old 05-29-2011, 08:13 AM   #5
z0001130
Newly Registered User
 
Join Date: May 2011
Posts: 8
Thanks: 6
Thanked 0 Times in 0 Posts
z0001130 is on a distinguished road
Re: Enable/disable Button depending on recordcount

Quote:
Originally Posted by gemma-the-husky View Post
but all you need to do is error trap the email button anyway

Code:
sub email_button_click
dim id as long 'used to detect an error below
 
on error goto fail
 
'if there is no current record this next line will error
'it doesn't have to be this - if you are call the email function with an argument , you don't need this. 'you just need something that will detect a blank form and not call the email 
 
id = recordid  
 
send email
exit sub
 
fail:
msgbox("no active record", & vbcrlf & vbcrlf & _
"error: " & err & "  Desc: " & err.description)
'I always like to show the error code
end sub
Thanks for the code, I had already done code to error trap the button, but I prefered to grey out button if there were no records.

Thanks
John

z0001130 is offline   Reply With Quote
Reply

Tags
button , disable , enable

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Enable/Disable buttons (Add, Delete) depending on criteria brunces General 4 08-11-2015 09:48 PM
If...then for cmd button enable/disable Johnny Drama Modules & VBA 2 01-28-2009 08:51 AM
Enable/Disable button eugz Modules & VBA 2 12-28-2006 03:36 AM
Enable/Disable button ekta Forms 7 12-31-2003 07:57 AM
disable/enable button deepbreath Forms 2 08-01-2001 03:23 PM




All times are GMT -8. The time now is 12:17 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World