Web Development

Ask something
Discussion Locked

List Box Synchronization using Access

0 helpful
2 replies

by donq

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am working on a geography database with Access 2K (DAO). It has a Form named ?GSAmaint? where in-country separately governed areas are defined and/or maintained (States, Territories, Provinces, etc.). Data is linked to a table named ?GSA? (for generic secondary areas) with ?saPK? (state abbreviation as its 4-character text Primary key).

The Forms data source is a query named ?qfSAmaint? and the Form (GSAmaint) uses fields from qfSAmaint to drive a list box named ?SelList?(for Selection List) used for record navigation in place of the data control. The ?saPK? field actually concatenates a 2-character country ID in front of a 2-character internal area ID to form its value easily allowing every country in the world to be added. The result produces 3885 separately administered areas within 264 countries (or territories) in the world.

Using the list box ?SelList? is a little slow (because there are 3885 records) unless I first use FindFirst to locate the first in-country area belonging to each country. To do that I placed a Combo box named ?QuickFind? that locates the first record containing the 2-character country ID and indexes focus to that record.

Private Sub QuickFind_AfterUpdate()
?INDEX TO THE SELECTED RECORD ANDRESET CURSOR FOCUS.
Dim rs As Object

Set rs = Me.Recordset.Clone
Rs.FindFirst ?[saFKca] = ?? & Me![QuickFind] & ???
Me.Bookmark = rs.Bookmark
saFKca.SetFocus

End Sub

saFKca is the Foreign Key to the Country ID (field within the GSAtable and qfSAmaint query) and the above code executes to set the Forms focus perfectly however the SelList selection list (also on my GSAmaint Form) does not respond. How do I synchronize SelList to reset focus based on the record being displayed after QuickFind_AfterUpdate() executes? The problem grows significantly once Secondary County, Parish City and Town areas are added at the next level.

2 replies

by bob sellman

OK, I'm going to make a few suggestions, but still follow the basis of my answer to your previous question.

From your explanation I can't figure out why you are using a list box for SelList. Why not use another combo box? Here's what I would do(to whatever level of country/state/county/city/etc is needed):

GSA can have two columns as the primary key, one the country ID, the second the internal area ID. (You've combined them together.) Things are simpler if you keep them separate, buteither way will work.

QuickFind is where the user selects the country.

Make SelList a combo box based on a query with criteria (as I suggested before) that country ID = QuickFind, and requery SelList after updates of QuickFind.

If you want the combined saPK field, the query for SelList can return saPK and the name of the area, but use the individual country ID only in its where clause.

Follow the same concept to go to each of the next levels. The lower level queries would include the criterias where country ID = QuickFind, area ID = SelList, and so on as needed.

Your table doesn't have many records, so you could run the queries right from the table. The trick is to index every field you will be using (country ID, area ID, etc.), so the requeries will work quickly.

by donq

I'm using a List Box for record navigation because it is MUCH more User Friendly then the numbered data control. Sorry Bob - I asked for the (one) line of code to add to "QuickFind_AfterUpdate()". Try again please.

This discussion is closed to new replies.