Access - Form Listbox select data for Unbound Combobox when selecting Listbox Item

I have a form which has a Listbox and an unbound combobox.  When a user selects an item on the Listbox I want that ClickEvent to Select an item in the combobox.  I'm not getting an error but nothing is happening.

The Listbox and the Combobox both refer to the same table.

My code I attempted is:
Private Sub ListCity_Click()
On Error GoTo ListCity_AfterUpdate_Err
    
    Me.cboSelectCity.SetFocus
    
    DoCmd.SearchForRecord , Me.cboSelectCity.Column(0), acFirst, "[CitiesID] = " & (Nz(Me.listCity.Column(0), 0))
    Me.cboSelectCity.Requery

ListCity_AfterUpdate_Exit:
    Exit Sub

ListCity_AfterUpdate_Err:
    MsgBox Error$
    Resume ListCity_AfterUpdate_Exit
    
End Sub

Open in new window

wlwebbAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
It seems to be the SetFocus line that is throwing the monkey wrench in the works.

Try placing those lines at the END of the code see if it behaves the way you want.
0
 
Dale FyeCommented:
Use the after update event of the list box, not the click event
0
 
peter57rCommented:
Are you trying to use DoCmd.SearchForRecord to search columns of a combobox?

I don't think that is possible.

If your combo rowsource is a named query or a table then the simplest way to find the value you want is using Dlookup().

cboSelectCity= Dlookup("FieldNameTHatIsInColumn0","TableOrQueryName", "[CitiesID] = " & Nz(Me.listCity.Column(0), 0))
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
mbizupCommented:
An alternative to Pete's suggestion if your combo's row source is more complex:

Dim intIndex As Integer
Dim blFound AS boolean

For intIndex = 0 To Me.cboSelectCity.ListCount-1
    If Me.cboSelectCity.Column(0, intIndex) = me.ListCity.Column(0) Then
        Me.cboSelectCity.Value = Me.cboSelectCity.ItemData(intIndex )
        blFound = true
        Exit For
    End If
Next intIndex 

    If blFound = False Then
        MsgBox "Item not found"
        Me.cboSelectCity = ""
    End If

Open in new window

0
 
wlwebbAuthor Commented:
Mbiz.........  When I click on an item in my list i get the "Item not found" msgbox.  Even though I checked to see if the city is in the listbox and the combobox before I clicked.

Peter.....  replaced my code with the Dlookup..........  Still that combobox is left blank after clicking on an item in the listbox.......  (PS.... I had used fyed's suggestion of moving it to the AfterUpdate before I used your suggestion.  Is the Afterupdate the right place?)

Fyed...  Tried that....  still same result... no error but no result in my combobox.
0
 
mbizupCommented:
It sounds like you might be getting your column numbers mixed up...

Can you post the rowsource properties of your combo and listboxes, and also a little sample data from viewing the rowsource query in datasheet view?
0
 
wlwebbAuthor Commented:
Yep ... Give me a few......
0
 
wlwebbAuthor Commented:
Rowsource for cboSelectCity
SELECT [sysdta_Cities].[CitiesID], [sysdta_Cities].[Cities] FROM sysdta_Cities;

Rowsource for ListCity
SELECT CitiesID, Cities FROM sysdta_Cities;
0
 
peter57rCommented:
I'm afraid your original code is wrong - so abandon that.

The code posted by Miriam (mbizup) is OK provided you get the correct columns sorted out.
The afterupdate event should be fine but I'm not sure you will see the result as soon as you click on an entry - you have to move on to the next control, I think.
0
 
wlwebbAuthor Commented:
Sample data of sysdta_Cities
CitiesID      Cities      Tmp      EnteredByEmpID      DateEntered
1      Aaronsburg      1      1      12/14/2012 3:47:07 PM
2      Abbeville      2      1      12/14/2012 3:47:07 PM
3      Abbot      3      1      12/14/2012 3:47:07 PM
4      Abbotsford      4      1      12/14/2012 3:47:07 PM
5      Abbott      5      1      12/14/2012 3:47:07 PM
0
 
mbizupConnect With a Mentor Commented:
The code I posted definitely works, given your table and control names.

Just to verify that we are on the same page, try this again (the click, double click, after update events of your listbox are all fine):

Dim intIndex As Integer
Dim blFound AS boolean

For intIndex = 0 To Me.cboSelectCity.ListCount-1
    If Me.cboSelectCity.Column(0, intIndex) = me.ListCity.Column(0) Then
        Me.cboSelectCity.Value = Me.cboSelectCity.ItemData(intIndex )
        blFound = true
        Exit For
    End If
Next intIndex 

    If blFound = False Then
        MsgBox "Item not found"
        Me.cboSelectCity = ""
    End If

Open in new window

0
 
mbizupCommented:
Some other things to check...

Add Option Explicit to the top of your form's module and compile to make sure that all variables are declared and that your control names are all correct.

Here is an ugly, but working sample (see Form1) ...  Ive added the table, with just two fields, but the principle is the same.

Compare it to your own, especially row sources, column widths, column counts, bound columns etc.
Database4.mdb
0
 
mbizupCommented:
By the way, if you don't need the not in list check that I have in my sample code (I had assumed the rowsources would be much less similar), you could probably get by with just a single line of code:

Me.myCombo = Me.MyListbox
0
 
wlwebbAuthor Commented:
Mbiz.........  That works UNTIL I add the "Visible" and "Setfocus" (Line 4 - 6) code at the top.  (Note I also have an unbound textbox that they will start inputting a city name that filters the listbox down as they type.... then they'll click on the city name if it exists in the list OR if it doesn't then they can click a command to add new city.


Dim intIndex As Integer
Dim blFound As Boolean

Me.cboSelectCity.Visible = True
Me.cboSelectCity.SetFocus
Me.txtCity.Visible = False

For intIndex = 0 To Me.cboSelectCity.ListCount - 1
    If Me.cboSelectCity.Column(0, intIndex) = Me.listCity.Column(0) Then
        Me.cboSelectCity.Value = Me.cboSelectCity.ItemData(intIndex)
        blFound = True
        Exit For
    End If
Next intIndex

    If blFound = False Then
        MsgBox "Item not found"
        Me.cboSelectCity = ""
    End If

Open in new window

0
 
wlwebbAuthor Commented:
Uggg.  You've got to be kidding.......  THANKS FOR THE HELP... !!!!!!!
0
 
mbizupCommented:
:-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.