Populate MS Access List box from button onClick

Posted on 2014-10-28
Last Modified: 2014-11-02
I have forgotten how to do this.

Should the list box be defined with no control source?
What should I set as the list box data properties?

This is my button on click event:

Private Sub cmdTranslate_Click()

Me!lstTranslationResults.RowSource = "SELECT ln.Lang_Name, dc.Dict_Word FROM tblLanguage AS ln INNER JOIN tblDictionary AS dc ON ln.Lang_ID = dc.Lang_ID WHERE (((ln.Lang_ID) <> 1)) ORDER BY ln.Lang_Name "

End Sub

There is no error. The listbox does not populate.
The list box is set to display 2 columns. The SQL Statement returns 2 columns.

A quick example or reference will help.

Question by:Dovberman
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

Accepted Solution

slubek earned 400 total points
ID: 40409405
Try this:
Private Sub cmdTranslate_Click()

Me!lstTranslationResults.RowSource = "SELECT ln.Lang_Name, dc.Dict_Word FROM tblLanguage AS ln INNER JOIN tblDictionary AS dc ON ln.Lang_ID = dc.Lang_ID WHERE (((ln.Lang_ID) <> 1)) ORDER BY ln.Lang_Name "

End Sub

Open in new window


Author Comment

ID: 40409426

I had forgotten the need to Requery.

Oops, That did not fix it.

BTY, What is the VB line continuation marker?
LVL 84
ID: 40409444
You don't need to Requery if you set the RowSource.

Are you sure your SELECT statement actually returns records?
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!


Author Comment

ID: 40409551

Col1                 Col2
French              Alors
Hebrew              אז

ColumnWidths 1",1"

onClick is the only event procedure.

This is confusing.

Author Comment

ID: 40409591
I used this as a test:

Private Sub cmdTranslate_Click()

Me!txtTest.Text = "abc"

Nothing happens.

The event procedure is not called.
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 100 total points
ID: 40410095
It should raise an error, as the correct syntax is:

    Me!txtTest.Value = "abc"

But for testing it is better to pop a message:

    MsgBox "cmdTranslate clicked."


Assisted Solution

Dovberman earned 0 total points
ID: 40410108
I commented out the list box statements.

Then I got the Set Focus error.

Applied the SetFocus method.

Now it works.

LVL 84
ID: 40410435
Sorry, but I'm not clear how the SetFocus method helped you to populate the Listbox. Could you please explain what the actual solution was to the problem you stated (i.e. populating the Listbox).

Author Comment

ID: 40411103
Error message received.  Cannot set a value for the control unless the control has the focus.

Once I set the focus, it worked. I remember this from years ago.  Try setting the controlsource of a listbox without setting focus.
LVL 84
ID: 40411359
Error message received.  Cannot set a value for the control unless the control has the focus.
No, you can set the RowSource of a Listbox without the Listbox having focus. I do it all the time (and just tried it on a test database, to be sure).

As far as ControlSource ... that's not what you asked, but still you can do that without the Listbox having the focus.

Not trying to be difficult, but your accepted solutions don't match up with what you asked ...

Author Comment

ID: 40411548
OK I will not argue. SetFocus works for me.
LVL 84
ID: 40411635
As I said, not trying to argue, just making sure that others who see this understand that SetFocus is not the solution. Simply put, you don't have to use SetFocus to modify the Rowsource (or Controlsource, for that matter) of your Listbox.

Author Closing Comment

ID: 40417984

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

Suggested Solutions

Title # Comments Views Activity
Delete table fields 3 48
ms access - Helpdesk ticket system 5 52
How to use Recordset, returned from a SPROC as the recourdsource of a form. 24 52
Reoccurring Access Query 24 47
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

739 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