running query

Question

I am working with Access 2016, and as part of a form I have a listbox where the "row source" is  a query ie  SELECT tblFullName.tblsurname, tblFullName.tblforename FROM tblFullName;  I wish to run the query from an Event to populate the list box. The command I have used is listbox.requery. This does not seem to generate any data.   I just cannot seem to be able to run the query.  Any thughts. Many thanks.

Answer 1

Which event are you using to run the Requery? Is it on the same form as the listbox? If so, have you tried it this way?

Me.ListboxName.Requery

Answer 2

The query does not appear to reference any parameters, so there should be no need to requery the list box; the control should show all rows from the table when the form is opened.  There is no need to 'run' the query.

Are you sure that the tblFullName column contains data?  What happens if you open the query independently of the list box?  Are its columns really named tblsurname and tblforename?  The 'tbl' tag, where used, is normally in a table name, not a column name.

One point I would make is that, as personal names can legitimately be duplicated, any table of people's names should have a distinct numeric column as its primary key, usually an autonumber for convenience.  The BoundColumn property of the list box would then reference a hidden column containing the numeric key.  The column is hidden by setting the first dimension of the ColumnWidths property to zero, e.g. 0cm;3cm;3cm where the RowSource property returns three columns such as ContactID, FirstName, LastName.  The value of the control when a row is selected would be the distinct numeric ContactID value for the person selected, of if the control is a multi-select list box its ItemsSelected collection would contain one or more ContactID values.

You might like to take a look at DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In this little demo file, in the section on 'Retrieving data from the database', one of the forms contains a list box in which one or more contacts can be selected by name.  Its RowSource property is:

SELECT ContactID, FirstName & " " & LastName
FROM Contacts
ORDER BY LastName, FirstName;

In this case the first and last name columns' values are concatenated into a single column.  Its ColumnCount property is 2 and its ColumnWidths property is 0cm;8cm to hide the first column.

---

Ken Sheridan, Stafford, England