open subform with button
- Thread starter dscudder
- Start date
The subform is still embedded in your form, right? It almost sounds from your description that it isn't any more. If that's the case, it's not a subform.
Are you making the subform not visible when the main form opens and then opening it with code? Simply changing the visibility of a subform should not impact the master/child relationships. I have a number of forms with subforms that are hidden until something is selected from a combo box and there are not issues with the master/child relationships.
Are you making the subform not visible when the main form opens and then opening it with code? Simply changing the visibility of a subform should not impact the master/child relationships. I have a number of forms with subforms that are hidden until something is selected from a combo box and there are not issues with the master/child relationships.
Thanks for responding. I tried the button with the subform embedded but it opened another copy of the form. I took the subform out and lost the master child relationship.
Your surmise was right on. Some of the subforms are only required when a trigger event is entered in the main form. (it is a critical incident database for hospitals and community providers.) Not all incidents involve a death and those forms do not need to appear all the time.
I found some code on the internet but I do not see in the property tables--either for the button or the subform--where to put it?
I am using the 2007 version if that helps. I also just bought 3 books on access 2007.
I have been a researcher primarily. But my clients need information system integration more than just research so now I am developing applications. I have a server arriving next week and I will be running SQL server express. with a website.
Your surmise was right on. Some of the subforms are only required when a trigger event is entered in the main form. (it is a critical incident database for hospitals and community providers.) Not all incidents involve a death and those forms do not need to appear all the time.
I found some code on the internet but I do not see in the property tables--either for the button or the subform--where to put it?
I am using the 2007 version if that helps. I also just bought 3 books on access 2007.
I have been a researcher primarily. But my clients need information system integration more than just research so now I am developing applications. I have a server arriving next week and I will be running SQL server express. with a website.
If I'm understanding what you need, try this. Add all of the subforms you need to your main form and set Visible to No. Make sure the Master Child relationships look ok. If only one subform needs to be visible at a time, you can overlap them to save on screen real estate.
Add command buttons to show the various subforms. The On Click event for these buttons is something like:
Basically, they all start off invisible and then when you need one you make it visible, and hide everything else.
Add command buttons to show the various subforms. The On Click event for these buttons is something like:
Code:
Me.frmSomeForm_sub.Visible = True
Me.frmSomeOtherForm_sub.Visible = FalseBasically, they all start off invisible and then when you need one you make it visible, and hide everything else.
Greetings Brian:
The procedure you suggested is very close but I cannot quite make it work.
I think the reason is the differences between Access versions. I have used 2003, 2007 and 2010 extensively, and one earlier version too, but, quite frankly, I get confused between all the different versions.
I have attached 3 screen shots to help. The first is the error message I received when I clicked on the button in the form view, after entering code based on what you suggested in the VBA window to define the event of the button "On Click"
The second is the property sheet for the button, which shows the type of action for the "OnClick" and the "..." to open the VBA window.
The third is the VBA window which opens when you click "..."
Some things are already entered by default in that VBA window. What is n the screen shot attached is not how I tried it the first time. I also tried som other code I got off the interenet. The screen shot is where I stopped to write this.
What I need is assistance writing the exact code to go in the VBA window. The main form is called IncidentMainForm, and the subform is called SubformConsumerDetail2.
I told you before I bought three books yesterday; one is devoted to VBA. I do not want to learn just the code for this issue. I need to be able to do many things including having reports run automatically from outlook and emailed to staff. And I need to be able to train my client's staff to do this as well.
If you know good titles please tell me.
Thanks for all your help.
David
The procedure you suggested is very close but I cannot quite make it work.
I think the reason is the differences between Access versions. I have used 2003, 2007 and 2010 extensively, and one earlier version too, but, quite frankly, I get confused between all the different versions.
I have attached 3 screen shots to help. The first is the error message I received when I clicked on the button in the form view, after entering code based on what you suggested in the VBA window to define the event of the button "On Click"
The second is the property sheet for the button, which shows the type of action for the "OnClick" and the "..." to open the VBA window.
The third is the VBA window which opens when you click "..."
Some things are already entered by default in that VBA window. What is n the screen shot attached is not how I tried it the first time. I also tried som other code I got off the interenet. The screen shot is where I stopped to write this.
What I need is assistance writing the exact code to go in the VBA window. The main form is called IncidentMainForm, and the subform is called SubformConsumerDetail2.
I told you before I bought three books yesterday; one is devoted to VBA. I do not want to learn just the code for this issue. I need to be able to do many things including having reports run automatically from outlook and emailed to staff. And I need to be able to train my client's staff to do this as well.
If you know good titles please tell me.
Thanks for all your help.
David
Attachments
You are very close . . . there are just a couple things to clean up.
Let's start this button from the beginning just so you understand what's going on. Delete the code in screen shots 1 and 3 by highlighting those lines and hitting delete. Then delete your existing button.
As you type the code, Access should prompt you using Intellisense. Once you get used to it, it will help you get syntax and control names correct.
The syntax error you got in your screenshot is simply because the form name was not correct. In my example, I used "_sub" on the end because that is the naming convention I use for my subforms. Sorry for the confusion about that.
Naming conventions are important as your databases get more complex. Google "access naming conventions". They're not required, but it make it easier for others to understand what you're doing. (BTW, my "_sub" on the end of a subform name is NOT standard.)
Let's start this button from the beginning just so you understand what's going on. Delete the code in screen shots 1 and 3 by highlighting those lines and hitting delete. Then delete your existing button.
- Add a new command button. Don't go through the wizard . . . click cancel when it comes up.
- Highlight the button and look at the property sheet. Start with the "All" tab because all properties are there. You'll quickly learn to use the other tabs to focus upon what you are looking for.
- Change the name of the button to something meaningful. Part of your naming convention should indicate the type of control it is. So let's name this button "cmdOpenSubformConsumerDetail2" (don't put the parenthesis in the property sheet).
- Change the caption to what you want the button to read, for example "Open Consumer Detail" (same thing with parenthesis again).
- Now you need to code what you want the button to do. You'll do this in the "On Click" event, which you can find quickly on the "Event" tab.
- At this point, the "On Click" should not have anything listed next to it. Click on the ellipse, then "Code Builder". A code sheet for your form will open with the cursor between "Private Sub cmdOpenSubformConsumerDetail2_Click()" and "End Sub". Whatever you want the button to do goes here.
- To make your subform visible, you want:
Code:
Me.SubformConsumerDetail2.Visible = True
As you type the code, Access should prompt you using Intellisense. Once you get used to it, it will help you get syntax and control names correct.
The syntax error you got in your screenshot is simply because the form name was not correct. In my example, I used "_sub" on the end because that is the naming convention I use for my subforms. Sorry for the confusion about that.
Naming conventions are important as your databases get more complex. Google "access naming conventions". They're not required, but it make it easier for others to understand what you're doing. (BTW, my "_sub" on the end of a subform name is NOT standard.)
Thanks. That worked. I know so little about VBA that I did not grasp that when you wrote"_sub" it was part of your form name. I was also putting in too much code. I also did not grasp that the code had to go between the default lines, which are private sub and ant the end, end sub. This is a subroutine. I get that. In the window we are glimpsing a small subroutine of a larger program.
I do not yet grasp enough. I entered the one line of code you sent. It does open the form. But it does not close the subform. I tried the second line you had earlier with no success.
Me.SubformconsumerDetail2.Visible = True
Me.IncidentMainForm.Visible = False
If I stack up the subforms as you suggested earlier the user needs to close the subform when finished. Right?
I do not yet grasp enough. I entered the one line of code you sent. It does open the form. But it does not close the subform. I tried the second line you had earlier with no success.
Me.SubformconsumerDetail2.Visible = True
Me.IncidentMainForm.Visible = False
If I stack up the subforms as you suggested earlier the user needs to close the subform when finished. Right?
In the code you posted, you can't hide the Incident Main Form because it contains your subform. I envisioned that on the main form you have a series of buttons that each opens a separate subform. The code for each of them will make ONE subform visible and hide the rest.
Regarding the user closing the subform when completed, you could have a button on the main form to do that, but you don't have to. If they need to go to a separate subform, your code will make the currently open subform invisible and make the new one visible.
Regarding the user closing the subform when completed, you could have a button on the main form to do that, but you don't have to. If they need to go to a separate subform, your code will make the currently open subform invisible and make the new one visible.
OK I got that. SO, a button can only do one thing: either open the form or close it; Right? If I want the user to be able to close the subform so they could go back and edit a previous screen I should but another button inside the subform to close i; right? Perhaps in the upper right hand corner where everyone expects the close button to be. And the code would be, for example,
Me.subformconsumerdetail2.visible = False
Is that right?
How does the "Not Me." command work? In an online document with VBA tutorials it shows that the Me. command refers to this current form. So what does the Not Me. command do?
Is it not possible to make a single button open and close a form?
I also see that as you write more code, the code builder screen contains all the entries.
Thanks so much. I am getting the hang of it.
David
Me.subformconsumerdetail2.visible = False
Is that right?
How does the "Not Me." command work? In an online document with VBA tutorials it shows that the Me. command refers to this current form. So what does the Not Me. command do?
Is it not possible to make a single button open and close a form?
I also see that as you write more code, the code builder screen contains all the entries.
Thanks so much. I am getting the hang of it.
David
With regard to the subforms, you are making them invisible, not closing them.
A command button can do more than one thing. Just add multiple lines of code. Your command button to make a subform visible should also hide all the others:
A command button can do more than one thing. Just add multiple lines of code. Your command button to make a subform visible should also hide all the others:
Code:
Me.fsubSomeSubform.Visible=True
Me.fsubSomeOtherSubform.Visible=False
Me.fsubSomeOtherSubform2.Visible=FalseOK. Got it. I was having two unrelated issues.
1) somehow, the visibility setting for at least one subform kept getting set back to yes so one form opened either over or under the other. That is why I thought I needed another button to Hide them. I must have taken the subform out of the main table and then put it back in without resetting visibility to No.
2) a tiny typo in the subform name makes the button inoperable.
Now I have two buttons at the bottom of the main form with two lines of code associated with each:
Private Sub subform_Click()
Me.sub1.Visible = True
Me.sub2.Visible = False
Private Sub Subform2_Click()
Me.Sub2,Visible = True
Me.Sub1.Visible = False
If sub1 is visible and the user clicks on the sub2 button, Sub1 is hidden and sub2 becomes visible. The user can go back and forth between the subforms and edit responses until they are satisfied they have itall right. This is just what I wanted.
When I add a third button, then I need to add another line of code to these first two buttons saying:
Me.Sub3.Visible = False
If the next subform I add has a subform within it, then when insert the main subform in the main form the child subform should come with it: Right? Similarly, the button which opens the main subform will open he nested child subform too. Right?
I am very familiar wiith SPSS and SASS. Both are like this in that they are partially windows driven (SPSS more so than SASS). When you need to do something beyond the windows driven part you open up the code window and edit it to do more precisely what you want, except that SPSS and SASS show all the code generated by the windows driven part and you can save that code in a separate file. But VBA is foreign to me.
Thanks for you help.
1) somehow, the visibility setting for at least one subform kept getting set back to yes so one form opened either over or under the other. That is why I thought I needed another button to Hide them. I must have taken the subform out of the main table and then put it back in without resetting visibility to No.
2) a tiny typo in the subform name makes the button inoperable.
Now I have two buttons at the bottom of the main form with two lines of code associated with each:
Private Sub subform_Click()
Me.sub1.Visible = True
Me.sub2.Visible = False
Private Sub Subform2_Click()
Me.Sub2,Visible = True
Me.Sub1.Visible = False
If sub1 is visible and the user clicks on the sub2 button, Sub1 is hidden and sub2 becomes visible. The user can go back and forth between the subforms and edit responses until they are satisfied they have itall right. This is just what I wanted.
When I add a third button, then I need to add another line of code to these first two buttons saying:
Me.Sub3.Visible = False
If the next subform I add has a subform within it, then when insert the main subform in the main form the child subform should come with it: Right? Similarly, the button which opens the main subform will open he nested child subform too. Right?
I am very familiar wiith SPSS and SASS. Both are like this in that they are partially windows driven (SPSS more so than SASS). When you need to do something beyond the windows driven part you open up the code window and edit it to do more precisely what you want, except that SPSS and SASS show all the code generated by the windows driven part and you can save that code in a separate file. But VBA is foreign to me.
Thanks for you help.
Brian: I want to take this opportunity to thank you again for sticking with me.
I finally got it that when a button opens one subform, the rest of the lines of code for the button ensure that the other subforms are not visible. I now have four subforms opening off the main form. One of those subforms has a button which opens an additional subform, which itself has another table view subform nested within it.
The user can go back and forth between the subforms until they are certain they have the medical incident described correctly.
Many of the instructions I viewed on the internet, which are related to this issue, have to do with creating an entire application in visual basic. When the context is different, the code does not work in quite the same way.
When you are new to VBA you do not know this, and it makes you not trust the code. You just see that there are different ways of writing code in different settings to do the same thing. Accordingly, I had tiny errors in other places, and I thought it was another case of the code not working in my specific situation. I had a visibility setting that had become set to Yes again, which made one form open either under or over another. I had a typo in a subform name so small I could not see it, even when the debugger took me to the line with the error multiple times.
But, as I gained confidence in the code you provided I came to realize that if the code does not work, the error must be somewhere else.
You stayed with me when, at the beginning, I could not open any subform with a button. Now I have a complex web of subforms that all open and close correctly. I am sure I will learn more about how to make the entire application prettier but it now works the way it should.
I came across our thread on the internet while still searching for other answers to my questions. Anyone else who stumbles on this thread will receive all they need to create multiple buttons to open and close any number of subforms.
Note: I found one thread on the internet that was very similar. In it the event ID was not an autonumber. This means the user could skip that field. Then the entire rest of the data entered is not linked to anything. They had a complex web of code to prevent this, so the uer had to go back and enter an ID before opening a subform. You were the one who said to make the original event ID an autonumber. As soon as the user enters any information on the main form an event ID is created automatically and it is automatically contained in every subform and associated table with no user action required.
One final note: in the last nested subforms I had to create master/child relationships with more than one field. The tables had to be linked on the event ID and the patient ID. After I gained confidence in what you were saying, this too became matter of fact. So now the nested subforms retain both IDs. How cool is that!
Thanks again for sticking with me in this. This has been a giant leap forward for me. It is the best experience I ever had with any online service.
And I saw some great advertising too.
David
I finally got it that when a button opens one subform, the rest of the lines of code for the button ensure that the other subforms are not visible. I now have four subforms opening off the main form. One of those subforms has a button which opens an additional subform, which itself has another table view subform nested within it.
The user can go back and forth between the subforms until they are certain they have the medical incident described correctly.
Many of the instructions I viewed on the internet, which are related to this issue, have to do with creating an entire application in visual basic. When the context is different, the code does not work in quite the same way.
When you are new to VBA you do not know this, and it makes you not trust the code. You just see that there are different ways of writing code in different settings to do the same thing. Accordingly, I had tiny errors in other places, and I thought it was another case of the code not working in my specific situation. I had a visibility setting that had become set to Yes again, which made one form open either under or over another. I had a typo in a subform name so small I could not see it, even when the debugger took me to the line with the error multiple times.
But, as I gained confidence in the code you provided I came to realize that if the code does not work, the error must be somewhere else.
You stayed with me when, at the beginning, I could not open any subform with a button. Now I have a complex web of subforms that all open and close correctly. I am sure I will learn more about how to make the entire application prettier but it now works the way it should.
I came across our thread on the internet while still searching for other answers to my questions. Anyone else who stumbles on this thread will receive all they need to create multiple buttons to open and close any number of subforms.
Note: I found one thread on the internet that was very similar. In it the event ID was not an autonumber. This means the user could skip that field. Then the entire rest of the data entered is not linked to anything. They had a complex web of code to prevent this, so the uer had to go back and enter an ID before opening a subform. You were the one who said to make the original event ID an autonumber. As soon as the user enters any information on the main form an event ID is created automatically and it is automatically contained in every subform and associated table with no user action required.
One final note: in the last nested subforms I had to create master/child relationships with more than one field. The tables had to be linked on the event ID and the patient ID. After I gained confidence in what you were saying, this too became matter of fact. So now the nested subforms retain both IDs. How cool is that!
Thanks again for sticking with me in this. This has been a giant leap forward for me. It is the best experience I ever had with any online service.
And I saw some great advertising too.
David
