?
Solved

Refresh a list box data on an Access form

Posted on 2016-08-06
10
Medium Priority
?
91 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
[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
  • 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
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.

 
LVL 2

Assisted Solution

by:Antonio Salva Ripoll
Antonio Salva Ripoll earned 1000 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 51

Accepted Solution

by:
Gustav Brock earned 1000 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 51

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

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 …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
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…
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…
Suggested Courses

770 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