UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Cascading Combo On Continuous Subform, Any Version    
 
   
damian.green
post Feb 15 2020, 10:25 PM
Post#1



Posts: 171
Joined: 24-October 18



I'm having issues applying other people's solutions to my database. How the heck do you get the combo box to reflect the filtered values of the other combo box? I've spent too many hours trying others solutions and can't make this work.

In the attached open the Contacts form and look at the subform results. As an example the PMP certification shouldn't have Green Belt in the 2nd drop down. You can see how I have the certifications and different levels in the Certifications form.

Please help.
Attached File(s)
Attached File  Cascade_Combo_Issue.zip ( 360.68K )Number of downloads: 7
 
Go to the top of the page
 
BentBrain
post Feb 16 2020, 01:56 AM
Post#2



Posts: 566
Joined: 10-February 03
From: Thailand


HI Damian,
With cbo linked to fields, its gets complicated doing filtered values,

I have edited your form and made a cbo out put to text box to show you an easier way and just hide the dropdown arrow.

Take a look and let me know if that helped.

Regards
BentBrain
This post has been edited by BentBrain: Feb 16 2020, 01:56 AM
Attached File(s)
Attached File  Cascade_Combo_Issue.zip ( 382.56K )Number of downloads: 14
 

--------------------
"There is no excellent beauty that hath not some strangeness in the proportion" Francis Bacon
Go to the top of the page
 
damian.green
post Feb 16 2020, 04:32 PM
Post#3



Posts: 171
Joined: 24-October 18



I see what you did and it does work but I'm still interested in a solution that works with the combo boxes and doesn't use boxes and displaying values in other text boxes.

I'm playing around with https://www.UtterAccess.com/wiki/Cascading_Combo_Boxes, which I came across late last night. I've gotten the cascading results to work but the issue now is having the results above the current record display when you're making a change or adding a new record.

After you leave the row, the data is displaying correctly. I'm closer to a solution.
Go to the top of the page
 
damian.green
post Feb 16 2020, 06:01 PM
Post#4



Posts: 171
Joined: 24-October 18



The remaining issue is to figure out how to have the rows above and below the current record display their values, when the combo box for the current record is filtered for the user to select from a specific field listing.

Once they make their selection on me.Certification_Level, the Certication_Level_ID_LostFocus() code fixes the visual issue.

Edited with a visual of the issue.

Thoughts?

CODE
Option Compare Database
Option Explicit

Private Sub Certication_Level_ID_LostFocus()
    'Show the values in the Certification Level above and below the current record.
    Me.Certication_Level_ID.RowSource = "SELECT t_Certification_Level.Certificate_Level_ID, t_Certification_Level.Certificate_Level FROM t_Certification_Level"
    Me.Certication_Level_ID.Requery
End Sub

Private Sub Certification_ID_AfterUpdate()
    Me.Certication_Level_ID = Null 'clear the cell
    
    'this code works in returning the filtered results that the user should select BUT ....
    'for the time that the user is figuring out what to select for this specific record....
    'the values turn blank, if they don't have a matching certification_ID.

    Me.Certication_Level_ID.RowSource = "SELECT t_Certification_Level.Certificate_Level_ID, t_Certification_Level.Certificate_Level, t_Certification_Level.Certification_ID FROM t_Certification_Level WHERE (((t_Certification_Level.Certification_ID)=" & Me.Certification_ID.value & "))"
    Me.Certication_Level_ID.Requery 'requery the dropdown list for the updated values
    Me.Certication_Level_ID.Enabled = True
    Me.Certication_Level_ID.SetFocus 'put the cursor in the certification level field
    Me.Certication_Level_ID.Dropdown 'show the dropdown list
End Sub

Private Sub Form_Current()

    If Me.NewRecord Then
        Me.Certification_ID.RowSource = "SELECT t_Certification.Certification_ID, t_Certification.Certification FROM t_Certification ORDER BY t_Certification.Certification;"
        Me.Certification_ID.Requery
    Else
        Me.Certication_Level_ID.RowSource = "SELECT t_Certification_Level.Certificate_Level_ID, t_Certification_Level.Certificate_Level, t_Certification_Level.Certification_ID FROM t_Certification_Level"
        Me.Certication_Level_ID.Requery
        Me.Certication_Level_ID.Enabled = True
    End If
End Sub
[attachment=93179:Picture1.png]
This post has been edited by damian.green: Feb 16 2020, 06:21 PM
Attached File(s)
Attached File  Picture1.png ( 366.19K )Number of downloads: 10
 
Go to the top of the page
 
fkegley
post Feb 17 2020, 07:40 PM
Post#5


UtterAccess VIP
Posts: 23,791
Joined: 13-January 05
From: Mississippi


Because of the resources that a combo box uses, each row of a continuous form, appearances not withstanding, contains an occurrence of the SAME combo box. So changing the combo box on one row causes all rows to take on the same value. Therefore, the cascading combo box will also take on the same row.

I do this with an UNBOUND combo box that changes the value of a BOUND text box. A text box does not take up as many resources as a combo box, so this works out just fine. I place the text box over the text box portion of the combo box, leaving just the arrow showing. The user clicks the arrow on the combo box. In the AfterUpdate event of the combo box I place the value I want from the combo box into the text box.

The combo boxes on the other rows also take on the same value, the same row source change (if any), etc. The user doesn't know the difference.

--------------------
Frank Kegley, Microsoft Access 2010 MVP
Go to the top of the page
 
damian.green
post Feb 17 2020, 09:15 PM
Post#6



Posts: 171
Joined: 24-October 18



Ok. 2 people with the same answer. [censored]... Thanks for the responses.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    7th July 2020 - 07:58 AM