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?
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.

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

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?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Move your focus to another control before running my code:


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
how can i disable command button of one form in another form?
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.