Solved

Refresh a list box data on an Access form

Posted on 2016-08-06
10
62 Views
Last Modified: 2016-08-09
Hi Experts,

I have a form that contains a combobox containing a list of departments.

When a department is selected I would like a listbox to display info. from a db table that belongs to the dept selected.

Here is the Row Source for the listbox.

SELECT NRN FROM tblRegion WHERE Depts =[Forms]![Form1]![ComboDebts];

When the form first loads the list box contains the info. relating to the combobox selected Dept. What I would like is when a new dept. is selected in the combobox the list box refreshes the data.

At the moment nothing happens unless I refresh the form from the refresh button on the home ribbon.
0
Comment
Question by:DColin
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 41745725
Try:
In combo box after update event:
ListBox0.Requery

Open in new window

0
 

Author Comment

by:DColin
ID: 41745961
hnasr,

This has not worked. The listbox always seems to be one update behind. As I add another entry with the combobox the previous one appears.

I am wondering is the selected combobox entry is not getting added to the database quickly enough for the listbox requery to see it.

I am using the below code to add the combobox entry to the db table.

Private sub cboDepts_Change()

   Dim dbs as Database

   Set dbs = openDatabase("Manpower.accdb")

   dbs.Execute "INSERT INTO tblRegion (Depts, NRN) VALUES ("Me.txtDeptID", '" & Me.cboDepts.text & "');"

   dbs.close

End Sub
0
 
LVL 30

Expert Comment

by:hnasr
ID: 41745965
Try to upload a simple demo database demonstrating the issue.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 2

Assisted Solution

by:Antonio Salva Ripoll
Antonio Salva Ripoll earned 250 total points
ID: 41746170
Hi.

The way to load a listbox from a table based on another listbox-combobox is very simple.

You must create a Click event for the combo, and in that event you must put the code for refill the listbox.

For example:

Private Sub ComboDebts_Click
    'This reloads a recordsource for the listbox
    Me.ListDepts.RowSource = "SELECT NRN FROM tblRegion WHERE Depts = " & Me.ComboDebts
    Me.ListDepts.Requery

   'Now, we clear the previous entry
    Me.ListDepts = ""

Open in new window


DColin, If you can attach a sample database, Icould help you better, or, if you prefer it, I can post a sample database and later you can modify it to adapt to your needs.

Best regards

Antonio (Barcelona, Spain).
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 41746258
Try this:
Private sub cboDepts_Change()

    CurrentDb.Execute "INSERT INTO tblRegion (Depts, NRN) VALUES (" & Me!txtDeptID.Value & ", '" & Me!cboDepts.Value & "');"

    Me!YourListbox.Requery

End Sub 

Open in new window

/gustav
0
 
LVL 2

Expert Comment

by:Antonio Salva Ripoll
ID: 41746307
Hi DColin.

I have a question for you, the source table for the listbox, is a local /linked table in the same database file or is a table in an external database?.

Thanks.
Best regards.

Antonio (Barcelona, Spain).
0
 

Author Comment

by:DColin
ID: 41746310
Same database
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41746322
That's what I guessed.

/gustav
0
 
LVL 2

Expert Comment

by:Antonio Salva Ripoll
ID: 41746325
DColin, can you attach a copy of your database?. Really the solution is very easy.

If you post the database, I can see the form, the involved tables, the fields, relations, queries... and then help you better. Think that I don't know the table names or the field names, and is very difficult to give help.

I can prepare a sample database, with a solution, but you must modify  my sample to adapt it to your database, and I know nothing about your VBA programming knowledge.

Best regards.

Antonio (Barcelona, Spain).
0
 
LVL 2

Expert Comment

by:Antonio Salva Ripoll
ID: 41746355
Hi DColin.

I prepared a very small sample. It has two forms, one uses relationed tables and uses only one vba instruction in the click event of the combo.

The other form uses full VBA code for filling the combo and the listbox in runtime.

I hope this sample be useful for you.


Best regards.

Antonio (Barcelona, Spain).

P.D. I'm sorry, the tables and fields names are in Spanish because they are used in my DataBase applications.
codigos_postales.accdb
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)

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

840 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