Need to enable command button if subform has no records when main form loads.


I have a main form with 1 sub form. When the main form opens the user types in a txt box and when the exit the txt box a search is performed to return any records for that search. The subform is linked on this field. If their is data for the subform then it renders and the user has command buttons to update the data in the subform. However if their is no data to populate the subform it is blank. I have created a command button to launch a pop-up form and the user enters a new record when they save and close the pop-up the form and subform do a requery and display that data. The problem I have is my users continue to use this command button even though the data comes up on their search and they should use the controls in the subform.

What I would like to do is set the default value of the command button for the pop-up to disabled. If the seacrh on the main form for the subform finds no records I would llike to then enable the command button for the pop-up on the main for to be enabled. Then when the user enters the data on the pop-up form and saves and closes, and the main for and subform requeries then disable it again.
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Move your focus to another control before running my code:

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can determine if the subform contains any records like this:


So to enable/disable your button:

Me.MyButton.Enabled = (Me.NameOfYourSubformCONTROL.Form.RecordsetClone.RecordCount=0)

Note the "NameOfYourSubformCONTROL" ... this is the name of the Subform control being used on the main form, and may or may not be the name of the actual form being used as a subform.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Sorry, that should be:

Dim rst As DAO.Recordset
Set rst = Me.NameOfYourSubformCONTROL.Form.RecordsetClone
Me.MyButton.Enabled = rst.Recordcount = 0

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

woodjeAuthor Commented:

Thanks that works for enableing the button. Now my question is when I press the save and close command button on the pop-up form . How can i have it disable the command button on the main form?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Like this:

Forms("NameOfYourMainForm").NameOfYourCommandButton.Enabled = False
hey all,
shortcuts rock

Me.Button.Enabled = Me.subformname.Form.RecordsetClone.RecordCount

you've been busy here on EE this year. Nice!
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
J:  Yeah, mostly just bored here at home/work ... lots to do, but it's one of those "bang it out for 2 - 3 days and then send it to the client and wait" type of jobs ...
woodjeAuthor Commented:

When I place the code you have shown above. I get an run-time error '2164':
You can't disable a control while it has the focus.

And when I try to add :

I get an error that this function is not available at this time.
how can i disable command button of one form in another form?
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.

All Courses

From novice to tech pro — start learning today.