Solved

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

Posted on 2013-05-13
16
1,065 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)
Comment Utility
Use the after update event of the list box, not the click event
0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
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
Comment Utility
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
 

Author Comment

by:wlwebb
Comment Utility
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
Comment Utility
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
Comment Utility
Yep ... Give me a few......
0
 

Author Comment

by:wlwebb
Comment Utility
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
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:wlwebb
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Uggg.  You've got to be kidding.......  THANKS FOR THE HELP... !!!!!!!
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
:-)
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

744 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now