Populate Unbound Combo box from Another Unbound Combo box (1 Viewer)

Steve G

Registered User.
Local time
Today, 22:46
Joined
Aug 16, 2019
Messages
24
Hi guys,

I'm hoping some of you might be able to assist with this one:
I have one Unbound combo box on Main Menu Form (Search box if you like).

- User selects a participant from the combo box and a message box appears to advise the user in which form they will find the most recently completed transaction for this participant
- User clicks OK on the message box and that new form opens with the curser focused on the Unbound search combo box (where they will find that participant's name) in that form

My question is:
- How do I get the selected participant's name in the Unbound search combo box (Main Menu Form) to automatically populate into the Unbound search combo box when the new form opens? The primary key for participants (ParticipantID) is not in the same column in each of the other forms' Unbound combo box?

Thanks guys,
Kind regards Steve
 

June7

AWF VIP
Local time
Today, 03:46
Joined
Mar 9, 2014
Messages
5,425
Why would there be more than one form for displaying transactions?
Why does user even need to be annoyed by a message box telling them which form is opening?
Why would an unbound combobox need to be populated? Isn't there a bound control that shows the record data?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:46
Joined
May 7, 2009
Messages
19,175
add vba to it
the code already has comment what to change:
Code:
    Const myCombo       As String = "Combo0"    'the name of your search combobox
    Const OtherForm     As String = "Combo2"    'the name of the other form to open
    Const OtherCombo    As String = "Combo0"    'the name of the other combobox on another form
    
    Dim strSource       As String
    Dim ipos            As Integer
    Dim ips             As Integer

If msgbox(".......",vbYesNo + vbQuestion) = vbyes
    If Me.Controls(myCombo).ListIndex <> -1 Then
        DoCmd.OpenForm OtherForm
        
        With Forms(OtherForm)
            strSource = .Controls(OtherCombo).RowSource
            ipos = InStr(1, strSource, Me.Controls(myCombo).value & "")
            strSource = Left(strSource, ipos - 1)
            ips = fncCountDelimiters(strSource, ";")
            ips = (ips \ .Controls(OtherCombo).ColumnCount) + Abs(.Controls(OtherCombo).ColumnHeads)
            .Controls(OtherCombo).value = .Controls(OtherCombo).ItemData(ips)
        End With
    End If
End If
...
...


Private Function fncCountDelimiters(ByVal s As String, ByVal d As String) As Integer
    Dim i As Integer
    Dim c As Integer
    i = InStr(1, s, d)
    While i > 0
        c = c + 1
        i = InStr(i + 1, s, d)
    Wend
    fncCountDelimiters = c
End Function
 

Steve G

Registered User.
Local time
Today, 22:46
Joined
Aug 16, 2019
Messages
24
Hi June, Thanks for the reply. Each combo box has it's own imbedded query. The purpose of having a search box in the Main Menu Form is to enable users to quickly identify where participants' completed transactions are at....either in:
- QA (form 1)
- Assessment (form 2)
- Completed (form 3)
 

Steve G

Registered User.
Local time
Today, 22:46
Joined
Aug 16, 2019
Messages
24
add vba to it
the code already has comment what to change:
Code:
    Const myCombo       As String = "Combo0"    'the name of your search combobox
    Const OtherForm     As String = "Combo2"    'the name of the other form to open
    Const OtherCombo    As String = "Combo0"    'the name of the other combobox on another form
    
    Dim strSource       As String
    Dim ipos            As Integer
    Dim ips             As Integer

If msgbox(".......",vbYesNo + vbQuestion) = vbyes
    If Me.Controls(myCombo).ListIndex <> -1 Then
        DoCmd.OpenForm OtherForm
        
        With Forms(OtherForm)
            strSource = .Controls(OtherCombo).RowSource
            ipos = InStr(1, strSource, Me.Controls(myCombo).value & "")
            strSource = Left(strSource, ipos - 1)
            ips = fncCountDelimiters(strSource, ";")
            ips = (ips \ .Controls(OtherCombo).ColumnCount) + Abs(.Controls(OtherCombo).ColumnHeads)
            .Controls(OtherCombo).value = .Controls(OtherCombo).ItemData(ips)
        End With
    End If
End If
...
...


Private Function fncCountDelimiters(ByVal s As String, ByVal d As String) As Integer
    Dim i As Integer
    Dim c As Integer
    i = InStr(1, s, d)
    While i > 0
        c = c + 1
        i = InStr(i + 1, s, d)
    Wend
    fncCountDelimiters = c
End Function
Brilliant again....thanks Arnel https://accessworld-accessworld.netdna-ssl.com/forums/images/icons/icon14.gif
 

Users who are viewing this thread

Top Bottom