Rotating Subforms on a main form (1 Viewer)

[Mcgrco]

Hi,

I have a one main form in my database and several subforms, exactly the same size, that appear/dissapear depending on what user has selected from a tree view menu. The form works by hiding all the other subforms and only showing the subform of interest.
The code i use is

Function funSubformVisible(frm As Form, strRequestedSubform As String)
Dim ctl As Control


For Each ctl In frm.Controls
Debug.Print ctl.Name & " " & ctl.ControlType

If ctl.ControlType = 112 Then
If ctl.Name <> strRequestedSubform Then
ctl.Enabled = False
ctl.Visible = False
Else
ctl.Visible = True
ctl.Enabled = True

End If
End If

Next ctl
End Function


the trouble I'm having is that one of the sub forms contains a subdatasheet which locks a table that i need to run a process on. i dont want to mess about to much in code with the record sources as i am building this for someone else. It there anyway i can stop the subforms from loading untill I need them? I thought that the ctl.Enabled = false would be enough but its not.

Thanks in advance

 

[Pat Hartman]

Instead of using multiple subform controls and manipulating their visible property, use a single subform control and modify its ControlSource to reference the subform you want to show.

 

[Mcgrco]

Pat, thanks for your comments. Unfortunatly the subforms are all differnt. ie some are datasheet, others Single Form with controls. For what its worth, I manged to get the problem sorted and as ever learned something new about access. The reason whey the process would not run when the datasheet form was loaded was because the process I was trying to run started with a make table query. It would appear as if Access needs to fully lock on to a table to run a make table query. I changed this to a delete and append and it works fine now. It was probably something most access poeple knew anyway, but I certainly didnt so maybe this may help others


Thanks again for your help

 

[Pat Hartman]

Unfortunatly the subforms are all differnt. ie some are datasheet, others Single Form with controls

- that wouldn't make a difference. My suggestion was to change the form that the single subform control was bound to on the fly as you decide which form the subform control should show now. However, you would have had the same problem with that technique.

The locking issue could have been solved by "unbinding" the problem form. Once a form is created and all its columns are bound to the form's RecordSource, you can "unbind" it by blanking out the RecordSource and then giving it a value in the subform's Open event. You would run the code that made the table, then change the subform's Recordsource to point to that table.

Are you aware that queries can be used in place of most tables? You may not need to go through the process of rebuilding this temp table. You may be able to base the form directly on a query.

Deleting/repopulating tables adds considerable db bloat so don't do it unless you really need to.

 

[Mcgrco]

Thanks Pat. I did try to create the table before the recordsource was assigned to the problem subform. This worked fine except that it was only triggered when the main form loaded . I couldnt find an event that i could unload the record source and re trigger the the table creation.

Thanks for your advise, its much appreciated

 

[Mcgrco]

Pat,

Just for future reference, how can i add a control and change the controlsource to look at differnt subforms. Thanks in advance

 

[Pat Hartman]

Sorry, I know I said ControlSource but I ment SourceObject.

Me.YourSubformControlName.SourceObject = "name of subform"

 

[Mcgrco]

Pat ,

Many thanks for the much easier and more managble solution. It was my first approach at the type of database screen . A lesson well learnt

 

[mattP]

Pat,

I have tried using this option for my DB, I have a main form and several forms that I am trying to configure by using the "Selectsource" method, to open as subforms.

My main form (and Subform) lists escalation details, where the main form holds the owners name and the subform should list all escalations in an "Open" state for that owner.
This works fine if I have the one subform in my main form and never change this but if I use the method in this thread to open the form and load the main subform via the sourceobject route in VBA, then I only get the 1st record showing and cannot get anything else to show.

Is this because I have got this happening on the load event for the main form, do I need to use the selectsource option via button ?

Any help, as always, is greatly appreciated.

Regards

MattP

 

[mattP]

Sorry when I say "SelectSource" I mean "SourceObject"

 

[Mcgrco]

Hi, I used a tree view control to trigger the event. On load try this routine

Private Sub Form_Load()
Dim x

x = SetSubForm("frm_SUB_General_Instruction")

End Sub



Function SetSubForm(SubFormName As String)

On Error GoTo errez:
Me.Slideshow.Visible = False

If Me.Slideshow.SourceObject <> "" Then
Me.Slideshow.LinkChildFields = ""
Me.Slideshow.LinkMasterFields = ""
Me.Slideshow.SourceObject = ""
End If


Me.Slideshow.SourceObject = SubFormName


Me.Slideshow.Visible = True


errex:

Exit Function

errez:

If Err.Number = 3011 Then
Me.Slideshow.SourceObject = ""
Else
MsgBox Error

End If
Resume errex

End Function

 

[mattP]

Not sure on how I apply the code, is this using the option of having the Subforms SourceObject set when you select an option form a main form menu (in my case several labels), thus making that particular subform visible or are you laoding all subforms and hiding them or making them visible ?

I'm not too hot on VBA code

Thanks

MattP

 

[Mcgrco]

Loading all and hiding caused me no end of trouble because all the forms are bound when you load them which is why Pat was kind enough to set me straight. Im not sure how you do this without code but try opening the form in code mode and paste the entire contents of the code I supplied you with . Then change the x = SetSubForm("place your subform here")

 

[mattP]

Ok Not sure how you attach a picture to this thread, so I'll try to describe my form a bit

The main form has a hidden field called "Status", there are also two other fields (combo's) one called "Txtsearchcriteria" for the owners name the other is "status".
Additionally there is a Company Name field, which is normally blank but if you type a company name in here it will search the DB for any records of this company.
Also there are no parent-child links in the subform to the main form, but this format currently works fine.
Basically when you login into the DB your user name is captured and this form loads with that criteria, it then lists all the escalations you have in an open state.

The code I currently use when this form loads is :

Private Sub Form_Load()

Me.txtsearchCriteria = user.UserName (saving a user logon name)
Me.State = "Open"
Me.cmbStatus = ""
Me.CompanyName = ""
DoCmd.ShowAllRecords


End Sub
What I have tried is loading the form as it is now, then changing the source code to open a different subform, but when I go back to my summary subform it only shows the first record again ?

regards

MattP

 

[mattP]

ANy further idea's ?

thks

MattP

 

[crmarrio]

Problem changing linkchildfield and linkmasterfield

Hi

I have been trying to do something very similar to the examples above, but get a Runtime Error 2101 on the second attempt to change the subform and its related links.

The code is below - I am using a treeview Control called axtreeview to select a department, teaching team or course. Depending on what level node is clicked, I want a different subform, filtered by that node. What I can't seem to do is 'reset' or 'unbind' the linking fields. Any ideas? Probably something really simple I've missed. trying to do

me.chris99.linkchildfields = ""

definitely doesn't work! Any help appreciated - I feel like I've been going round in circles on this one!!!

Chris


Private Sub axtreeview_nodeclick(ByVal mnode As Object)
'set up all variables
Dim oTree As TreeView
Dim strRet As String
Set oTree = Me!axTreeView.Object
Dim RecordtoSelect As Integer
Dim testcase As String

Dim strLinkText As String
strLinkText = Trim(Mid$(mnode.Key, 2, 20))
Me.linkingbox = strLinkText
DoCmd.RunCommand acCmdSaveRecord

testcase = Left$(mnode.Key, 1)
If testcase = "1" Then
If Me.Chris99.SourceObject <> "" Then
Me.Chris99.SourceObject = ""
Me.Chris99.LinkChildFields = ""
Me.Chris99.LinkMasterFields = ""
End If

Me.Chris99.SourceObject = "area"
Me.Chris99.LinkChildFields = "AREA"
Me.Chris99.LinkMasterFields = "linkingbox"

End If

the code goes on in the different testcases - 2, 3 or 4, but I figure if I can get it to work, then it should just be a copy and paste job.