Looking for List Box VBA help after click update (1 Viewer)

mchr5900

New member
Local time
Yesterday, 22:37
Joined
Dec 12, 2018
Messages
9
Hi All

New to the forum with no VBA experience. Looking for some VBA help to have a 2nd listbox update based on selections in the first list box

Background - I am trying to create a "helper query builder" page for managers in our area with no Access experience but would like to create custom queries on the fly. The idea is that the user can first select the name of whatever table they would like to see data from and then once a table has been selected they can then select all of the available column headers from that table. Once the column headers have been selected they can query results based on the 2nd listbox selections.

First uploaded image shows the two list boxes
Second image shows the field, table and sort for the first listbox - currently showing the results I would expect to see - which is all tables
Third image shows the row source data for the 2nd listbox - however in regular form view the 2nd listbox is blank - hoping to get help populating that 2nd list box

Happy to answer any questions in order to make the process easier. Thank you for your time
 

Attachments

  • 2018-12-12_9-19-46.jpg
    2018-12-12_9-19-46.jpg
    40.6 KB · Views: 29
  • 2018-12-12_9-22-56.jpg
    2018-12-12_9-22-56.jpg
    41.4 KB · Views: 29
  • 2018-12-12_9-25-47.jpg
    2018-12-12_9-25-47.jpg
    62.9 KB · Views: 35

isladogs

MVP / VIP
Local time
Today, 05:37
Joined
Jan 14, 2017
Messages
18,186
By column headers, do you mean field names?
Why do you have two copies of your table in the query in the third screenshot?
Furthermore, as they aren't joined, you will get each repeated many times.
If your table has 200 records the query will have 200*200=40000 records!

The first listbox should just have the table names
The second listbox should use both fields as its record source with the table name column hidden and filtered to the first combo selection

Forgot to say, welcome to AWF.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:37
Joined
May 21, 2018
Messages
8,463
This works for me if I set the listbox rowsource type to Field List and then change the rowsource.
Code:
  Me.LstFields.RowSourceType = "Field List"
  Me.LstFields.RowSource = Me.txtname
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:37
Joined
May 21, 2018
Messages
8,463
Sorry that would give you the field names and not the column headers.
 

mchr5900

New member
Local time
Yesterday, 22:37
Joined
Dec 12, 2018
Messages
9
Thank you both for the super fast replies

-Removed the 2nd table from the first listbox (oversight on my part)
First image shows one table with the field Table name displayed and the result as expected (2nd image)

The third image will show column headers (fields) from the helper sheet table with the criteria as the Listbox from the query builder page (table names hidden)

However the 2nd listbox when I open the Form is blank. I am guessing VBA would have to go somewhere after the user selects one or more table names in the first box and then re query based on their selection < I am failing on this last part
 

Attachments

  • 2018-12-12_10-18-40.jpg
    2018-12-12_10-18-40.jpg
    51.8 KB · Views: 26
  • 2018-12-12_10-19-10.jpg
    2018-12-12_10-19-10.jpg
    48.7 KB · Views: 24
  • 2018-12-12_10-22-08.jpg
    2018-12-12_10-22-08.jpg
    53.9 KB · Views: 23

isladogs

MVP / VIP
Local time
Today, 05:37
Joined
Jan 14, 2017
Messages
18,186
Just to repeat my previous comments

The first listbox should just have the table names
The second listbox should use both fields as its record source with the table name column hidden and filtered to the first combo selection

Include the table name field but untick it in the Show checkbox (or set its width to zero in the listbox property sheet)
Move your filter criteria to the Table name field
 

mchr5900

New member
Local time
Yesterday, 22:37
Joined
Dec 12, 2018
Messages
9
I put both headers back into the "field" names and unchecked table name and moved the criteria over to that column as well

However, I still don't have the 2nd listbox updating with anything once an item from the first listbox is selected -2nd image
 

Attachments

  • 2018-12-12_10-37-09.jpg
    2018-12-12_10-37-09.jpg
    57.4 KB · Views: 23
  • 2018-12-12_10-39-33.jpg
    2018-12-12_10-39-33.jpg
    52.5 KB · Views: 24

isladogs

MVP / VIP
Local time
Today, 05:37
Joined
Jan 14, 2017
Messages
18,186
I'm still unclear what the Column Headings refer to - see post 2

The phrase Column Headings as used in listboxes refers to the top line shown in your listboxes : Table Name select & (confusingly) Column Heading in Table

You seem to have selected all items in the first listbox
Is this a multiselect listbox? If so that method won't work.
Change it to a Simple listbox (single select)

Then select one item in first listbox - does the 2nd listbox populate?
If not, add the following line in the after update event for the first listbox
Code:
Me.[COLOR="Red"]secondlistboxname[/COLOR].Requery
replacing the part in RED with the actual name
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:37
Joined
May 21, 2018
Messages
8,463
This seems overly complicated to me and a lot of work. You have to maintain tables of table names and tables of field names. Do this in code.
The following are two listboxes on a form. The first shows all tables in the database, the second shows all visible column names for the selected table name. No need for any tables or queries Click on the first table and see all captions/column names in the next list box.

Code:
Private Sub Form_Load()
  Dim tdf As TableDef
  Dim strList As String
  For Each tdf In CurrentDb.TableDefs
    If Not Left(tdf.Name, 4) = "MSYS" Then
        If strList = "" Then
          strList = tdf.Name
        Else
          strList = strList & ";" & tdf.Name
        End If
    End If
  Next tdf
  Me.lstTables.RowSourceType = "value list"
  Me.lstTables.RowSource = strList
End Sub

Private Sub lstTables_AfterUpdate()
  If Not IsNull(Me.lstTables) Then
    LoadFieldList Me.lstTables.Value
  End If
End Sub
Public Sub LoadFieldList(tableName As String)
  Dim tdf As TableDef
  Dim db As DAO.Database
  Dim fld As DAO.Field
  Dim VisibleName As String
  Dim strFields As String
  Set db = CurrentDb
  Set tdf = db.TableDefs(tableName)
  On Error GoTo errlbl
  For Each fld In tdf.Fields
    VisibleName = fld.Name
    'now check for a description property if it fails then take the field nam
    VisibleName = fld.Properties("Caption")
    If strFields = "" Then
      strFields = VisibleName
    Else
      strFields = strFields & ";" & VisibleName
    End If
  Next fld
  Me.lstFields.RowSourceType = "value list"
  Me.lstFields.RowSource = strFields
  Exit Sub
errlbl:
  Resume Next
End Sub
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:37
Joined
May 21, 2018
Messages
8,463
demo attached
 

Attachments

  • Show Fields.accdb
    1.3 MB · Views: 26

Users who are viewing this thread

Top Bottom