Not in List (1 Viewer)

[Matt Greatorex]

I have a form that gets populated after the user selects a value (a person's surname) from a combo box. This works fine if the name is present or if the name is missing entirely. Where I'm having problems is if there are two people in a team e.g. the last name could be 'Davies/Smith'. I want the user to be able to search for 'Smith'.

What I would like to do is add code such that, if the entered name isn't in the list, a second combo box gets enabled and has it's source set to just those people who could possibly be the one that the user is searching for e.g. 'Davies/Smith', 'Philips/Smith', etc.

The source for this second combo box is being set correctly. However, the focus keeps returning to the first as the code loops. Consequently, the second never appears to be enabled (I'm unsure if it gets enabled, then disabled, or if it never gets enabled in the first place).

Does anyone know:
1) How I can ensure the second box gets enabled and stays that way?
2) Where I should put a SetFocus command so that, if the second combo box is enabled, focus moves to it?

The code for OnNotInList event of the first combo box is as follows:

    Dim li_Count As Integer
    Dim str_Source As String
    Dim str_Criteria As String
    
    On Error GoTo End_Sub
    
    str_Criteria = "Instr(IALastname,'" & NewData & "') <> 0"
    
    Response = acDataErrContinue
    
    li_Count = DCount("IACode", "tbl_iainfo", str_Criteria)

    If li_Count > 0 Then
        str_Source = "SELECT DISTINCT tbl_iainfo.IACode, " & _
                     "[IALastName] & ', ' & [IAfirstname] AS Fullname, " & _
                     "tbl_iainfo.IALastname, " & _
                     "tbl_iainfo.IAFirstName " & _
                     "FROM tbl_iainfo " & _
                     "WHERE " & str_Criteria & " " & _
                     "ORDER BY tbl_iainfo.IALastname, tbl_iainfo.IAFirstName;"
        [Forms]![frm_NBDash - IA]![cbo_Possibilities].RowSource = str_Source
        [Forms]![frm_NBDash - IA]![cbo_Possibilities].Requery
        [Forms]![frm_NBDash - IA]![cbo_Possibilities].Enabled = True
    Else
        [Forms]![frm_NBDash - IA]![cbo_Possibilities].Enabled = False
    End If

    Exit Sub
End_Sub:
    MsgBox Err.Description

The code for the AfterUpdate event of the first combo box is as follows:

    Dim str_IAcode As String
    str_IAcode = [Forms]![frm_NBDash - IA]![ianame]
    Call IA_Name_Change(str_IAcode) ' -- THIS FUNCTION WORKS CORRECTLY
    DoCmd.Maximize

As always, any help is gratefully appreciated.

 

[grnzbra]

What do you expect str_Criteria to be after

str_Criteria = "Instr(IALastname,'" & NewData & "') <> 0"

?

It looks like it should be part of an IF statement.

 

[Matt Greatorex]

str_Criteria is "The NewData value should be somewhere in the field IALastName".

This part is working, the source for the second combo box is correct every time. It's shifting the focus that I'm having the real problems with.

 

[Bodisathva]

normally, I would say that you need to set combo1.Enabled = False and combo2.setFocus commands directly following the enable of the 2nd combo box. I don't think it's going to be that easy though...something here feels like an odd interplay from the on_update of the combo box...are you repainting or requerying the form sometime after enabling the second combo box?

 

[Matt Greatorex]

Not the entire form, no.

I've been able to work around the problem by replacing 'enabled' with 'visible', for the second combo, and blanking the first combo box if the second becomes visible. This inidicates to the user to use the second one, and they seem happy enough with it.

It's not ideal, though, as the drop-down list for the first still pops up if the entered item isn't in the list.

If you know how to prevent the drop-down list being shown, that would get me what I need to do.

 

[Steve R.]

If you know how to prevent the drop-down list being shown, that would get me what I need to do.

If I understand your question correctly, this may help:

Solution, of sorts, found. It finally dawned on me that there was no point in staying in the combobox after accepting the new data. In the Limit to List subroutine, I added a line of code that moves you to the next control requiring data entry after accepting the new data. Consequently, there is no residual drop-down list obscuring the fields below it.

See my post of 10/15/2006.

 

[Matt Greatorex]

Thanks for the response.

My problem is that I don't want to enter any new data (as the person's name is already present, albeit in a format that the drop-down list doesn't recognize). I've tried using SetFocus to move to the newly populated combo box, but something is causing the focus to return to the first.

Guess I'll have to keep hacking away at it.

 

[Matt Greatorex]

Okay, I got to the clutching at straws stage and tried compiling the database. Once I'd done that, it allowed me to use the SetFocus expression to move to the next combo box.

All is now well with the world and thanks to those to helped.