?
Solved

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

Posted on 2013-05-13
16
Medium Priority
?
1,096 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 48

Expert Comment

by:Dale Fye
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 2000 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 2000 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

771 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