Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old September 4th, 2007, 09:47 PM
Authorized User
 
Join Date: Sep 2007
Location: , , .
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default Different subforms depending on combo box choice

Is it possible to have a different subform appear when a value is chosen on a combo box. Let's say we have these values in a field called Type: Application, Evaluation, Complaint. When I select Application, I would like to see the application form fields in the subform. If I select Evaluation, I want it to load a different form in the subform. Is this possible? If so, then how?

  #2 (permalink)  
Old September 5th, 2007, 09:50 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Why not just use tabbed subforms. The user can select the tab they want instead of using the other selection method. Put a different subform on each tab.

Did that help?

mmcdonal
  #3 (permalink)  
Old September 5th, 2007, 10:42 AM
Authorized User
 
Join Date: Sep 2007
Location: , , .
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I tried the tabbed forms. When I put the entry in the form I placed on one tab, it also tried to put an entry on the other form that I had on the other tab. I guess that's because both forms are joined to the same mother table. Is there a way I can tell Access to put the entry only into the one form and not the other?

Just to be clear, I do need the form/subform structure. Simply putting forms on different tabs will not work.
  #4 (permalink)  
Old September 5th, 2007, 11:20 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

This sounds like an underlying table structure issue. Can you post the table structures, and the record source for the main and sub forms?

mmcdonal
  #5 (permalink)  
Old September 5th, 2007, 12:49 PM
Authorized User
 
Join Date: Sep 2007
Location: , , .
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

On the previous messages, I had used other field names to try to simplify things. Here are the actual field names and forms I am using.

[u]This is in my main form.</u>
Record Source - TblTrainingNew
Name of Table: TblTrainingNew
TrainingNumber - PK - Autonumber - Long integer
Type - Text

[u]This is in my first subform.</u>
Record Source - tblZone
Name of Table: TblZone
ZoneNumber - PK - Number - Long integer
Zone - Text
TrainingNumber - FK - Number - Long integer

[u]This is my second subform with the two tabs.</u>
On the first tab is this subform.
RecordSource of subform - tblInstitutional
Name of Table: TblInstitutional
ZoneNumber - FK - Number - Long integer
Field 1
Field 2, etc.
On the second tab is this subform.
Record Source of subform- tblInstitutional
Name of Table: TblRisk Inventory
ZoneNumber - FK - Number - Long integer
ID - Number
Zone - Text
CreationDate - Date, etc.

Just thinking out loud, but should I be going to each individual field and giving it the correct [u]control </u>source? I can't figure out how I can give the subform two different [u]record </u>sources, one for each tab.

  #6 (permalink)  
Old October 3rd, 2007, 08:54 PM
Authorized User
 
Join Date: May 2007
Location: , , .
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

From what you've posted above the simplest solution i can think of is an unbound subform with an option group as the source. The main form will load alot faster, the most important part is to ensure that the master and child fields are going to be the same for each; other wise it gets a little bit more fun to correct.

The form which the copied code below belongs to (experts feel free to suggest improvements to make it run faster) there is a tab control and a subform sized to exactly the same size and position on the form. As the user presses on of the buttons (toggle, option, check) the subform changes to the relevant one. Each of the tables running the sources has adviserid as a link to the adviser table and adviserid is set as the child and master link fields in the unbound subform. The tab control simply contains information from the adviser table itself.

as you can see option 2 hasn't been written yet

Private Sub optadvisers_AfterUpdate()

    On Error GoTo errorhandler

    Select Case Me.optadvisers

    Case 1
        'hide the tabcontrol
        Me.tbadvisers.Visible = False
        'make the subform visible
        Me.subarform.Visible = True
        'switch to the ar history form
        If Me.subarform.SourceObject <> "fsubadviserfirmhistory" Then
            Me.subarform.SourceObject = "fsubadviserfirmhistory"
        End If
        Me.subarform.Requery

    Case 2
        'hide the tabcontrol
        Me.tbadvisers.Visible = False
        'make the subform visible
        Me.subarform.Visible = True

    Case 3
        'hide the tabcontrol
        Me.tbadvisers.Visible = False
        'make the subform visible
        Me.subarform.Visible = True
        'switch to the ar assessments form
        If Me.subarform.SourceObject <> "fsubarassessments" Then
            Me.subarform.SourceObject = "fsubarassessments"
        End If
        Me.subarform.Requery

    Case 4
        'hide the tabcontrol
        Me.tbadvisers.Visible = False
        'make the subform visible
        Me.subarform.Visible = True
        If Me.subarform.SourceObject <> "fsubadvisercaseslist" Then
            Me.subarform.SourceObject = "fsubadvisercaseslist"
        End If
        Me.subarform.Requery

    Case 5
        'hide the tabcontrol
        Me.tbadvisers.Visible = False
        'make the subform visible
        Me.subarform.Visible = True

    Case 6
        'hide the tabcontrol
        Me.tbadvisers.Visible = False
        'make the subform visible
        Me.subarform.Visible = True

    Case 7
        'hide the tabcontrol
        Me.tbadvisers.Visible = False
        'make the subform visible
        Me.subarform.Visible = True
        'switch to development plans
        If Me.subarform.SourceObject <> "fsubadviserdevelopmentplans" Then
            Me.subarform.SourceObject = "fsubadviserdevelopmentplans"
        End If
        Me.subarform.Requery

    Case 8
        'make the tab control visible
        Me.tbadvisers.Visible = True
        'empty the subform source
        Me.subarform.SourceObject = ""
        'hide the subform
        Me.subarform.Visible = False

    End Select

exitopt:
    Exit Sub

errorhandler:
    generalerrorhandler Err.Number, Err.Description, fadv, "optadvisers_afterupdate"
    Resume exitopt
    Resume

End Sub



Similar Threads
Thread Thread Starter Forum Replies Last Post
Combo box to display items from parent combo box Gini Visual Studio 2008 0 June 18th, 2008 12:30 AM
Combo box choice creating filtered combo box stevensj5 Access 11 September 13th, 2007 11:33 AM
changing content depending on combo box value bewise PHP How-To 1 September 13th, 2006 11:57 AM
Find Record Combo Box Wizard Won't Show 3rd Choice HenryE Access 0 February 2nd, 2004 11:52 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.