Solved

Refresh a list box data on an Access form

Posted on 2016-08-06
10
52 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
 
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
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

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
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…

920 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

13 Experts available now in Live!

Get 1:1 Help Now