Learning Code

[puforee]

I have a Form that has a Sub-Form. That Sub-Form has several Sub-Forms of its own. I want the setting of a control on the Form to decide if the Sub-Form is enabled. And if it is, which of it's Sub-Forms are enabled. Here is what I have been working on.

Private Sub Form_Current()
If Me.Status = 1 Then
Me.Accesses_Subform.Enabled = True
Employee.[Form_Employee Code Frm].Form![Accesses Subform].Form![APC Subform].Enabled = True
Else: Me.Accesses_Subform.Enabled = False
End If
End Sub


I think I got the first two statements right. I check Status to see if it is a 1. If it is I enable the Accesses_Subform.

But, I also want to Enable the APC Sub-form that is on the Accesses Sub-Form. I don't think this code is correct. There are more items to add to the Then and the Else statements but I need help with the syntax when talking about a Sub-Form on a Sub-Form on a Form.

I hope you can understand my giberish...I am trying to stop using Macros and learn code.

Thanks,

 

[TheAceMan1]

How are ya puforee . . .

Always a pleasure to help someone willing to learn.

But first, you need to provide all the form names in this secnario and the logic that will determine which [blue]level2 subform[/blue] to enable!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[puforee]

AceMan1,

Thanks for your reply. Here is the information.

Main Form: Employee Frm
Sub-Form: Accesses Subform
Sub-Form: APC Subform
Sub-Form: CGS Subform
Sub-Form: DocuRolls Subform
Sub-Form: Military Subform
Sub-Form: Server Share Subform
Sub-Form: Share Point Selections

As you can see, the Main Form has one direct subform....Accesses Subform. All of the rest of the subforms are located on the Accesses Subform.

I want a control on the Main form to control all of the Sub-Forms Properities ie. Enabled, Visable, Locked etc. I will pick the correct one for the correct instance of the control on the Main form.

With this information, can you help me complete the code I showed in my first posting?

Thanks,

 

[fneily]

Since someone is approaching it your way, have you considered a tab control? See:
Using the tab control on a form

http://office.microsoft.com/en-us/access/HA012300491033.aspx

From the above, you can then control each tab with code:
Private Sub Form_Open(Cancel As Integer)

If Me.OpenArgs = "Hide It" Then
Me!pgeThree.Visible = False
Me!pgeTwo.Enabled = False
End If

End Sub

 

[puforee]

Fneily,

Good thought and I have use TABs before. Unfortunately this is a mature DB and I am just trying to see if I can speed things up. I do not have the budget for a redesign.

Thanks for your input,

 

[TheAceMan1]

[blue]I am just trying to see if I can speed things up.[/blue]

So its speed your really after!

For starters you can setup subform references when you open the form and use the references instead of spelling it all out. To do this ...
[ol][li]In the declaration section of the mainform, copy/paste the following:

[blue]Private Accesses As Control, APC As Control, CGS As Control, DocuRolls As Control
Private Military As Control, Server As Control, Share As Control[/blue]

These are the reference names you'll be using.[/li]
[li]In the mainforms [blue]Load[/blue] event, copy/paste the following:

[blue]   Set Accesses = [Accesses Subform]
   Set APC = Accesses.Form![APC Subform]
   Set CGS = Accesses.Form![CGS Subform]
   Set DocuRolls = Accesses.Form![DocuRolls Subform]
   Set Military = Accesses.Form![Military Subform]
   Set Server = Accesses.Form![Server Share Subform]
   Set Share = Accesses.Form![Share Point Selections][/blue]

The references are now set and will remain as long as the form is open.[/li]
[li]As an example:

[blue][purple][b]Your Code:[/b][/purple]
    If Me.Status = 1 Then
      Me.Accesses_Subform.Enabled = True
      Employee.[Form_Employee Code Frm].Form![Accesses Subform].Form![APC Subform].Enabled = True
    Else
      Me.Accesses_Subform.Enabled = False
    End If

[purple][b]Converts to:[/b][/purple]
   If Me.Status = 1 Then
      Accesses.Enabled = True
      APC.Enabled = True
   Else
      Accesses.Enabled = False
   End If[/blue]

[/li]
[li]Finally, to release memory properly, in the mainforms [blue]UnLoad[/blue] event, copy/paste the following:

[blue]   Set Accesses = Nothing
   Set APC = Nothing
   Set CGS = Nothing
   Set DocuRolls = Nothing
   Set Military = Nothing
   Set Server = Nothing
   Set Share = Nothing[/blue]

[/li]
[li]Thats it, perform your testing![/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[puforee]

TheAceMan1,

Thank you very much. I will be applying this code in a test arena over the next few days.

Can you take a few minutes to explain exactly what is going on with your suggestion....especiall, what will make it faster and how.

It looks like you are setting alises for the subforms. I know that in its current configuration when I run the database remotely from the work sereve it is on, it always shows "Calculating...." when changing records. How does your code make this faster.

Is there any good reference material I could refer to to learn these processes?

Again...I really appreciate the help.
:-D :-D :-D

 

[TheAceMan1]

puforee . . .

Is this a [blue]split[/blue] Db?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[puforee]

Yes this is a split DB. The front end...Forms, Quries, Reports, and linked tables are in one DB. The actual tables are in the other DB one folder deeper on my server.

Thanks,

 

[puforee]

TheAceMan1,

Loaded your code as described. When opening the form I get and error and this is the debug point.

First a Visual Basic Run-time error "'424" Object Required" dialog box shows and when I click Debug, I get:

See the Else statement for Accesses.

Private Sub Form_Current()
If Me.Status = 1 Then
Accesses.Enabled = True
APC.Enabled = True
CGS.Enabled = True
DocuRolls.Enabled = True
Military.Enabled = True
Server.Enabled = True
Share.Enabled = True
Else
Accesses.Enabled = False (This line item is highlighted yellow)
APC.Enabled = False
CGS.Enabled = False
DocuRolls.Enabled = False
Military.Enabled = False
Server.Enabled = False
Share.Enabled = False
End If
End Sub

I rearange the statements so APC.Enabled.False was first and I received the same error, only it was for APC instead of Accesses.

This also happens to the Enabled = True statements if I open the form and the Status = 1. Of course it stops at the Enabled = True statement.

Did I misss something in your instructions? I put all the code exactly where you indicated it should go.

Thanks.

 

[TheAceMan1]

puforee ...

Scratch that code since the Db is split. I'll get back later . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[puforee]

TheAceman1,

OK...I will await your magic.

As a reminder. The main form has one sub-form. Accesses Subform.

All the other Sub-Forms are on the Accessess Subform.

Thanks for your continuing help.

 

[TheAceMan1]

puforee . . .

Sorry to get back so late. I made a simulation of what you have (as far as subforms are concerned) and had trouble accessing subform level two properties. Level one ([blue]Accesses Subform[/blue]) was just fine.

I found if I installed the subforms in proper subform level order, the code finally works. I initially made all the subforms then installed level 2 subforms on [blue]Accesses Subform[/blue], finally installing [blue]Accesses Subform[/blue] on the mainform. subform installation should be:
[blue]Accesses Subform[/blue]
then
[blue]level 2 subforms[/blue].

Remove all subforms then re-install in the order above.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[puforee]

I will give this a try.

Originally I created the Main form and then used the subform tool to create the Accesses subform. I then used the the subform tool again to create the rest of the subforms on the Accesses subform.

I too cannot access the properties of the multiple subforms from the code. Is this because on the way I built the originals?

I will get back to you after I try your idea above?

 

[puforee]

TheAceMan1,

I tried removing the accesses subform from the main form. I then opened the accesses subform and removed all the other subforms from it. I closed and saved the accesses subform. At that time I just had a bunch of forms in the DB.

I then opened the Accesses Subform again. I did a drag and drop of all the other subforms into the accesses subform. I ensured the Child Parent relationships were created correctly. I closed and saved the Accesses Subform.

I then opened the Main Form and did a drag and drop of the now completed Accesses Subform into it. I again verified the Child Parent links. I closed the form and saved.

When I opened the form the code still did not work. It failed three of the subforms on the Load event. I removed them from the Load Event code and ran the form again. This time I got past the load event (with the remainin subform Load codes working) and went to the On Current event and failed because it could not find the Accesses Subform as an object.

Should I just delete everything and recreate it from scratch?

Or am I still missing something?

Thanks,

 

[TheAceMan1]

[blue]I too cannot access the properties of the multiple subforms from the code. Is this because on the way I built the originals?[/blue]

I can't say at this time. Its really a paradox for me as proper subform references failed!

I currently have this thread flagged for research. I'd really like to know the mechanism of why.

Anyway . . . I await your return . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[puforee]

TheAceMan1,

As another tact. I created a new Accesses Subform1. I created it as a Form from scratch...not by usint the SubForm tool while on the Main Form.

I then opened the main form and used the Tool to attache the Subform. I modified the code to remove all items except the Accesses Subform code. It still fails on the Accessess.Enable = True saying it expected an object.

I then looked atthe Access Subform Properties from within the Main Form.

When I double click the upper left area on the Access Subform it show the FORM properties and there is NO Enabled on the Data tab. But, if I double click the border of the Access Subform I get a Properties box that show the title of "Subform/Subreport: Access Subform1. This properties screen....Data tab...has the Enable propertie.

Could this have something to do with this problem?

Thanks, and I will continue to play with the code. Please let me know what you find out.

 

[TheAceMan1]

puforee . . .

Your adding complication to solving the problem. We need to stick with the current set of subforms (nothing wrong with them). Just perform the following:
[ol][li][blue]Remove all subforms[/blue] from the mainform.[/li]
[li]Using the subform wizard toolbar button

, insert the [blue]Accesses Subform[/blue].[/li]
[li]Ony by one insert the level2 subforms onto the [blue]Accesses Subform[/blue].[/li]
[li]Perform your testing.[/li][/ol]
When you return provide the form names again so were both on the same page.

Be careful with names as you've shown [blue]Accesses[COLOR=blue yellow]_[/color]Subform[/blue], as well as [blue][Accesses[COLOR=blue yellow] [/color]Subform][/blue]. [blue]Two different names![/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

puforee . . .

Woops! ... Also postback the form references used during your testing.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[puforee]

TheAceMan1,

I will be out of town until tomorrow (SAT) evening. I will try to work some them...stand by....I will get back to you.

Thansk,