[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Refresh a list box data on an Access form

Posted on 2016-08-06
10
Medium Priority
?
130 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 31

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 31

Expert Comment

by:hnasr
ID: 41745965
Try to upload a simple demo database demonstrating the issue.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 52

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 52

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

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

656 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