scrollable and Selectable records from ComboBox

[Tupacmoche]

Hi Form Masters,

I have a combo box that displays 5 columns. It is populated by a userid field that filters the number of rows none, one or more. Functioning in the usual way when you click on the down arrow of the combo box it will show the rows associated with the userid. Now, to the issue. I have added a procedure to double click event. It displays a msgbox with information about the matching row. Please see attached file. What, I want to do when there are more than one is have the ability to scroll through the list of records and select the one I want. This is essentially the same functionality that is provided by the combo box. But, the display is different. Any ideas how this can be done? Here is the code for the data, I just don't know how to display it as described.:banghead:

Private Sub ProposalNum_DblClick(Cancel As Integer)
Dim MsgStr1 As String
Dim MsgStr2 As String
Dim MsgStr3 As String
Dim MsgStr4 As String
Dim MsgStr5 As String
MsgStr1 = Nz(DLookup("Proposal_id", "dbo_dm_proposal", "id_Number= '" & [Forms]![Donor_Intake_Form]![DonorAdvanceID] & "' and Proposal_Is_Active = '" & "Y" & " '"))
MsgStr2 = Nz(DLookup("Proposal_Title", "dbo_dm_proposal", "id_Number= '" & [Forms]![Donor_Intake_Form]![DonorAdvanceID] & "' and Proposal_Is_Active = '" & "Y" & " '"))
MsgStr3 = Nz(DLookup("Target_Amt", "dbo_dm_proposal", "id_Number= '" & [Forms]![Donor_Intake_Form]![DonorAdvanceID] & "' and Proposal_Is_Active = '" & "Y" & " '"))
MsgStr4 = Nz(DLookup("Start_Date", "dbo_dm_proposal", "id_Number= '" & [Forms]![Donor_Intake_Form]![DonorAdvanceID] & "' and Proposal_Is_Active = '" & "Y" & " '"))
MsgStr5 = Nz(DLookup("Staff_Report_Name", "dbo_dm_proposal", "id_Number= '" & [Forms]![Donor_Intake_Form]![DonorAdvanceID] & "' and Proposal_Is_Active = '" & "Y" & " '"))

MsgBox ("Proposal Number: " & MsgStr1 & vbCrLf & "Proposal Title: " & MsgStr2 & vbCrLf & "Target_Amt: " & Format(MsgStr3, "Currency") _
& vbCrLf & "Start Date: " & MsgStr4 & vbCrLf & "Staff Responsible: " & MsgStr5)
End Sub

 

[June7]

Duplicate thread https://www.access-programmers.co.uk/forums/showthread.php?t=300241

 

[MajP]

If I understand correctly based on your other thread. Maybe something like.

Private Sub ProposalNum_DblClick(Cancel As Integer)
  Dim MSg as string
  dim rs as dao.recordset
  dim strSql as string
  
  dim ID as string

  id = [Forms]![Donor_Intake_Form]![DonorAdvanceID]  
  strSql = "Select * from dbo_dm_proposal where ID_number = '" & ID & "'"
  set rs = currentdb.openrecordset(strSql)
  do while not rs.EOF
    if strMsg <> "" then strMsg = strMsg & vbcrlf & vbcrlf
    strMsg = "Proposal Number: " & rs!Proposal_id
    strMsg = strMsg & vbcrlf &  "Proposal Title: " & rs!proposalTitle
    strMsg = strMSG & vbcrlf & " Target Amt: " & rs!target_Amt
    strMsg = strMsg & vbcrlf & " Start Date: " & rs!StartDate
    ... 
    rs.moveNext
  loop

  msgbox strMsg
End Sub

 

[June7]

Should your thread title have been "from MsgBox"?

As I tried to advise in other thread, listed items are not selectable by click from MsgBox. Nor even from InputBox. And cannot scroll either.

Examples of InputBox code:

            strInput = "?"
            While strInput = "?"
                strInput = InputBox("Select Flat-Elongated ratios to test." & vbCrLf & vbCrLf & _
                "     3     1:3" & vbCrLf & vbCrLf & _
                "     5     1:5" & vbCrLf & vbCrLf & _
                "     B     Both")
                If strInput = "3" Or strInput = "5" Or strInput = "B" Then
                    Condition = UCase(strInput)
                Else
                    MsgBox "Not an appropriate entry.", vbApplicationModal, "Entry Error"
                    strInput = "?"
                End If
            Wend

        strTest = "?"
        While strTest = "?"
            strTest = InputBox("Select Test Method (Enter 1 thru 5): " & vbCrLf & _
                "1. T84" & vbCrLf & "2. T100" & vbCrLf & "3. T209" & vbCrLf & "4. T228" & vbCrLf & "5. T304", "Edit Calibrations", "?")
            If strTest Like "[1-5]" Then
                If strTest = 4 Then
                    DoCmd.OpenForm "PycCal"
                Else
                    DoCmd.OpenTable "PycCal" & Choose(strTest, "T84", "T100", "T209", "T228", "T304")
                End If
            ElseIf strTest <> "" Then
                MsgBox "Not a valid entry. Select 1 through 5 or Cancel."
                strTest = "?"
            End If
        Wend

These examples build a message string with literal text. Substitute with MajP recordset code. In your case, users would input desired Proposal_ID. Then need code to handle the user input. If the concatenated list string is too long for InputBox message display, it will likely truncate. IIRC, a MsgBox will display sequential 'pages'. I do have MsgBox code that dynamically builds message from recordset but it has been a long time since I've run it.

Again, a popup form may be more appropriate. Or perhaps a cascading (dependent) combobox. A common topic.