Combo Box Query Lookup ListIndex Woes

[Jon-G]

HI guys, Im having a problem with my Contacts lookup combo box.

What I am trying to achieve is to have the combo jump to the new contact after it has been entered into the table.

The combo is on a form that is linked to the Plots table, and its control source is the ClienteID field (the correct relationships are in place). The Row Source populates the list via the following query:

SELECT [qryClienteInfo].FullName, [qryClienteInfo].Saludo, [qryClienteInfo].Apellido, [qryClienteInfo].Nombre, [qryClienteInfo].Telèfono, [qryClienteInfo].Email, [qryClienteInfo].ClienteID FROM qryClienteInfo ORDER BY [Apellido], [Nombre];

This is working correctly, and I have the column widths set just to show the FullName field (which is the first name and surname concatenated together in the qryClienteInfo.

The new user is added via a pop-up form, and I have the following DLookup in the verify/append routine which finds the ClienteID value which has been assigned to the new record. This is also returning the correct ClienteID

Result = DLookup("[ClienteID]", "tblClientes", "[Nombre] = '" & ebNombre.Value & "' And [Apellido] = '" & ebApellido.Value & "' And [Dirreción] = '" & ebDirreción.Value)

What I would then like to do is find this record in the combos list and have the combo jump to it, but I dont know how I can get back the position of the record with this ClienteID from the query to set the ListIndex option correctly. Note that the order of items returned from the query is sorted alphabetically on surname, so I cant just jump to the last record, as the new item is sorted into the results after I requery the combo (and thus re-run the query)

Any advice would be greatly appreciated

[June7]

There is no need to 'jump to' the item in the combobox list. The combobox should be requeried so that the new ClientID is in the list then simply set the combobox value to that ClientID.

Is this combobox set as multi-column? Is the ClientID field hidden by setting width to 0?

[Jon-G]

There is no need to 'jump to' the item in the combobox list. The combobox should be requeried so that the new ClientID is in the list then simply set the combobox value to that ClientID.

Is this combobox set as multi-column? Is the ClientID field hidden by setting width to 0?

Yes this is exactly what I need. By jumped I meant set to. Yes the cb is multi column and the ClienteID field is persent but just not displayed because I've hidden it with the columnwidths settings.

How do I set the cb to the row I need?

[Jon-G]

bummmmmmmmp

[Jon-G]

Can anybody else shed any light? Ideally a code sample would be most beneficial

TIA

[June7]

Frequent bumping in a short period is considered bad form.

Couple ways you can do this. Where is the code that requeries the combobox? Is the combobox LimitToList property set to Yes? This is one way I pass value from a popup.

DoCmd.OpenForm "DialogGetDate", , , , , acDialog, "Report" 
If CurrentProject.AllForms("DialogGetDate").IsLoaded Then
    Me.tbxDate = Form_DialogGetDate.tbxDate
    DoCmd.Close acForm, "DialogGetDate"
End If

Here is another:

Private Sub cbxStateNum_NotInList(NewData As String, Response As Integer)
If MsgBox("State Number not in database.  Add new project record?", vbYesNo + vbQuestion, "NoRecord") = vbYes Then
    If MsgBox("Do you really want to add a new project record?", vbYesNo, "ConfirmAddNewProject") = vbYes Then
        DoCmd.OpenForm "AddEditProject", acNormal, , , , acDialog, NewData
        Me.cbxStateNum = NewData
        Me.lbxProjects.Requery
        Me.tbxDateSampled.SetFocus
    Else
        Me.cbxStateNum = Null
        Me.cbxStateNum.SetFocus
    End If
Else
    Me.cbxStateNum = Null
    Me.cbxStateNum.SetFocus
End If
Response = acDataErrContinue
Me.cbxStateNum.Requery
End Sub

[Jon-G]

ok point taken, but I was stuck at this point!

Here is what I have now come up with which is working as expected. This is in the verify/exit function of the new client entry form. Any comments on the efficiency of doing it like this well received:

'Get the details of our new client
            Dim lngClienteID As Long
            Dim sQuery As String
            
            'set up the query for dlookup
            sQuery = "[Nombre] = '" & ebNombre.Value & "' And [Apellido] = '" & ebApellido.Value & "' And [Dirreción] = '" & ebDirreción.Value & "'"
            
            'Close the form and save the new contact
            DoCmd.Close acForm, "frmClientes", acSaveYes
            
            'Get the ID of the new contact
            lngClienteID = Nz(DLookup("[ClienteID]", "tblClientes", sQuery))
            
            'Set the Clientes cb to our new client
            Forms!frmLote.cbClienteNombre.Requery
            
            Dim intX As Integer
            
            If lngClienteID < 1 Then Exit Sub
            
        
                For intX = 0 To Forms!frmLote.cbClienteNombre.ListCount - 1
                
                If Forms!frmLote.cbClienteNombre.Column(5, intX) = lngClienteID Then
                
                    'If entry found, set combobox to new entry
                    Forms!frmLote.cbClienteNombre.SetFocus
                    Forms!frmLote.cbClienteNombre.ListIndex = intX
                Exit For
            End If
            Next

[June7]

I believe the acSaveYes argument pertains to saving an edited design, not with saving a record, so it doesn't do anything (I have never used it). A record is automatically committed to table when form closes.

Otherwise, code seems as good as any. Congratulations!