Solved

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

Posted on 2013-05-13
16
1,093 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 (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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 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