Refresh a list box data on an Access form

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.
DColinAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
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
 
hnasrCommented:
Try:
In combo box after update event:
ListBox0.Requery

Open in new window

0
 
DColinAuthor Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
hnasrCommented:
Try to upload a simple demo database demonstrating the issue.
0
 
Antonio Salva RipollConnect With a Mentor Commented:
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
 
Antonio Salva RipollCommented:
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
 
DColinAuthor Commented:
Same database
0
 
Gustav BrockCIOCommented:
That's what I guessed.

/gustav
0
 
Antonio Salva RipollCommented:
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
 
Antonio Salva RipollCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.