Can't figure out multiple checkboxes...might be the wine :)

[ML!]

I have a client table with a one to many relationship to a client project table. Each project can require multiple analyses.

tblClient
ClientID (pk)
ClientFirst
ClientLast
...etc

tblProject
ProjectID (pk)
ClientID (fk)
ProjectType
...etc

tblProjectAnalysis
ProjectAnalysisID (pk)
ProjectID (fk)
AnalysisTypeID (fk)

tblAnalysis
AnalysisTypeID (pk)
AnalysisDescription

I want to create a form with a project subform that then has a related subform on which I can identify the multiple analyses required. Ideally, the analysis type descriptions defined in tblAnalysis will be presented with checkboxes that the user can select.

I'd like some advice on how to set that up.

TIA
ML!

 

[Simon_MT]

I use lookup Functions:

Function LookupClient()

Dim MyControl As Control
        Set MyControl = Screen.ActiveControl

    With CodeContextObject
        MyControl.RowSource = "SELECT qryClients.ClientID, qryClients.ClientsFirst, qryClients.ClientsLast FROM qryClients;"
        Call ListDisplay
    End With

End Function

Function LookupProject()

Dim MyControl As Control
        Set MyControl = Screen.ActiveControl

    With CodeContextObject
        MyControl.RowSource = "SELECT qryProjects.ProjectID, qryProjects.ProjectName, qryClients.ProjectType FROM qryProjects WHERE qryProject.[ClientID]= " & .[Client] & ";"
        Call ListDisplay
    End With
End Function

etc ...

You need to requery the next combi afterUpdate to refresh the content.

Simon

 

[ML!]

Thanks for the reply Simon. I'm not sure I understand...

A checkbox has no RowSource which is why I'm having trouble with the 'multiple' idea.

Are you suggesting the CodeContextObject is the subform or the control is the subform?

What does the ListDisplay function do?

 

[Simon_MT]

I'm suggesting combiboxes with SQL and then using Functions.

CodeContextObject simply means that this code can be used on any Form and not tied down to a particular Form i.e. reusable.

Function ListDisplay()
    Dim MyControl As Control
    Set MyControl = Screen.ActiveControl
        If IsNull(MyControl) Then
            MyControl.Dropdown
        End If
End Function

 

[ML!]

OK, that makes sense to me now Simon, thanks.

I am using combo boxes for a similar item (Project Outcomes) and will absolutely incorporate your code into that process.

However, with the analyses, from a usability perspective, I'd really like the user to be able to select mulitple items from a group of items presented to them.

For each new project, outcomes are identified by the client. Some are generic and others are custom - generally speaking reports and recommendations. Once they are identified, the staff need to determine the pre-defined analyses required to deliver on the outcomes.

Ultimately, there could be as many as 10 analyses. I'd like to show them to the user to make sure the correct ones get selected (and make it easy for them to choose them). The problem with dropdowns is that you have to scroll through the same list several times to select and with that many, you might miss some.

I've attached a sample form image. I've made the possible outcomes and selectable analyses names generic. You can see that with the outcomes dropdowns make sense but I want a selectable list of analyses to be more obvious (and I don't want to use a list box).

My problem is that if I use a frame, only 1 items can be selected. I want the user selections to write the multiple items selected to the tblAnalysis for each ProjectID as in the second image. See that ProjectID 5 has multiple analyses to be done.

Any thoughts?