Solved

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

Posted on 2013-05-13
16
1,077 Views
Last Modified: 2013-05-14
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

0
Comment
Question by:wlwebb
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39163330
Use the after update event of the list box, not the click event
0
 
LVL 77

Expert Comment

by:peter57r
ID: 39163733
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
 
LVL 61

Expert Comment

by:mbizup
ID: 39164291
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:wlwebb
ID: 39164572
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
 
LVL 61

Expert Comment

by:mbizup
ID: 39164583
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
 

Author Comment

by:wlwebb
ID: 39164605
Yep ... Give me a few......
0
 

Author Comment

by:wlwebb
ID: 39164831
Rowsource for cboSelectCity
SELECT [sysdta_Cities].[CitiesID], [sysdta_Cities].[Cities] FROM sysdta_Cities;

Rowsource for ListCity
SELECT CitiesID, Cities FROM sysdta_Cities;
0
 
LVL 77

Expert Comment

by:peter57r
ID: 39164832
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
 

Author Comment

by:wlwebb
ID: 39164836
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
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 500 total points
ID: 39164900
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
 
LVL 61

Expert Comment

by:mbizup
ID: 39164934
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
 
LVL 61

Expert Comment

by:mbizup
ID: 39164951
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
 

Author Comment

by:wlwebb
ID: 39165100
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
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39165166
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
 

Author Closing Comment

by:wlwebb
ID: 39165212
Uggg.  You've got to be kidding.......  THANKS FOR THE HELP... !!!!!!!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39165223
:-)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question