Cascading combo boxes

Question

First, let me preface this by saying I know next to nothing about Access, VBA, or SQL. But, when the boss asks you to do something, you do it. I apologize in advance for poor formatting, improper DB management, and my general lack of knowledge.

Anyways, I have a large table in a query that I need to use combo boxes to organize. The query's name is FinalTable, and the Fields that I would like to have the combo boxes named from are ID Maker.Billet Material, ID Maker.Billet Number, ID Maker.Test Type, and ID Maker.Axis. Once each box is updated, I want to display the rest of the fields for that row.

Here's what I have in the code for the form so far. It's ripped from a tutorial that I found while googling, but I am hopelessly lost and confused now and can't figure out what to do next.

Private Sub TestType_AfterUpdate()
    On Error Resume Next
   Dim query As String
   Dim Fields(3)
   Fields(0) = "[ID Maker.Billet Material]"
   Fields(1) = "[ID Maker.Billet Number]"
   Fields(2) = "[ID Maker.Test Type]"
   Fields(3) = "[ID Maker.Axis]"

   query = "Select DISTINCT {replace} " & _
           "FROM FinalTable " & _
           "WHERE [ID Maker.Test Type] = '" & TestType.Value & "' " & _
           "WHERE [ID Maker.Billet Number] = " & BilletNumber.Value & " " & _
           "WHERE [ID Maker.Billet Material] = '" & BilletMaterial.Value & "' " & _
           "ORDER BY {replace};"

   'Assign Queries
   '=========================
   Me.BilletMaterial.RowSource = Replace(query, "{replace}", Fields(0)) 'Billet Material Query
   Me.BilletNumber.RowSource = Replace(query, "{replace}", Fields(1)) 'Billet Number Query
   Me.TestType.RowSource = Replace(query, "{replace}", Fields(2)) 'Test Type Query
   Me.Axis.RowSource = Replace(query, "{replace}", Fields(3)) 'Axis Query

   'requery combobox's
   Me.BilletMaterial.Requery
   Me.BilletNumber.Requery
   Me.TestType.Requery
   Me.Axis.Requery
End Sub


Private Sub Axis_AfterUpdate()
On Error Resume Next
   Dim query As String
   Dim Fields(3)
   Fields(0) = "[ID Maker.Billet Material]"
   Fields(1) = "[ID Maker.Billet Number]"
   Fields(2) = "[ID Maker.Test Type]"
   Fields(3) = "[ID Maker.Axis]"

   query = "Select DISTINCT {replace} " & _
           "FROM FinalTable " & _
           "WHERE [ID Maker.Axis] = '" & Axis.Value & "' " & _
           "WHERE [ID Maker.Test Type] = '" & TestType.Value & "' " & _
           "WHERE [ID Maker.Billet Number] = " & BilletNumber.Value & " " & _
           "WHERE [ID Maker.Billet Material] = '" & BilletMaterial.Value & "' " & _
           "ORDER BY {replace};"

   'Assign Queries
   '=========================
   Me.BilletMaterial.RowSource = Replace(query, "{replace}", Fields(0)) 'Billet Material Query
   Me.BilletNumber.RowSource = Replace(query, "{replace}", Fields(1)) 'Billet Number Query
   Me.TestType.RowSource = Replace(query, "{replace}", Fields(2)) 'Test Type Query
   Me.Axis.RowSource = Replace(query, "{replace}", Fields(3)) 'Axis Query

   'requery combobox's
   Me.BilletMaterial.Requery
   Me.BilletNumber.Requery
   Me.TestType.Requery
   Me.Axis.Requery

The query = ... statement that I have there right now causes an error. There was just the first WHERE statement in there before I tried this.

score 3 · Accepted Answer · edited Dec 21 '19 at 12:58

3

Few items of change.

You don't have to reference the table in every element of the query. FinalTable only needs to be included after the "From" Statement.
Brackets are necessary for Column names that have spaces in their names, it's how the compiler differentiates what is and is not a query command.
You need to re-query the information so that it can actually populate into the combo box.

As properly stated in Query a table that has spaces in its name, MS Access C# VS2008

"Surround the spaced out item with square brackets:

[Common station]

Then slap the guy who designed the database."

So give this a shot and let me know how it goes - Note: cannot test it on my system.

Private Sub Material_AfterUpdate()
   On Error Resume Next
   Me.BilletNumber.RowSource = "Select [ID Maker.Billet Number] " & _
        "FROM FinalTable " & _
        "WHERE [ID Maker.Billet Material] = '" & Material.Value & "' " & _
        "ORDER BY [ID Maker.Billet Number];"
   Me.BilletNumber.Requery
End Sub

UPDATE

Sure, you get only unique records, you can utilize the DISTINCT command in access.

Secondly, to populate another combobox with the same information, you can stash the query in a string and requery both boxes.

Private Sub Material_AfterUpdate()
       On Error Resume Next
       Dim basequery As String
       Dim Fields(3)
       Fields(0) = "[ID Maker.Billet Material]"
       Fields(1) = "[ID Maker.Billet Number]"
       Fields(2) = "[ID Maker.Test Type]"
       Fields(3) = "[ID Maker.Axis]"

       basequery = "Select DISTINCT {replace} " & _
            "FROM FinalTable " & _
            "WHERE [ID Maker.Billet Material] = '" & Material.Value & "' " & _
            "ORDER BY {replace};"

       'Assign Queries
       '=========================
       'I do NOT know your combobox names.
       'Me.COMBOBOXNAME.RowSource - Change COMBOBOXNAME.
       Me.BilletMaterial.RowSource = Replace(query, "{replace}", Fields(0)) 'Billet Material Query
       Me.BilletNumber.RowSource = Replace(query, "{replace}", Fields(1)) 'Billet Number Query
       Me.TestType.RowSource = Replace(query, "{replace}", Fields(2)) 'Test Type Query
       Me.Axis.RowSource = Replace(query, "{replace}", Fields(3)) 'Axis Query

       'requery combobox's
       Me.BilletMaterial.Requery
       Me.BilletNumber.Requery
       Me.TestType.Requery
       Me.Axis.Requery
    End Sub

edited Dec 21 '19 at 12:58

June7

19,874
8
24
34

answered Mar 25 '14 at 20:38

Rich

4,134
3
26
45

Well no errors are popping up, so that's progress. However, the second combo box is not being populated. Also, is there any way to remove duplicates in the combo box? – user3241316 Mar 26 '14 at 14:00
I like this question/answer. I especially liked slapping the guy who put spaces in the names. – Smandoli Mar 26 '14 at 14:50
@user3241316 You can utilize the `DISTINCT` command to fix the duplicate issue. I don't know the other names of your combobox's, but all you have to do is a create a new string, copy and paste the query, and change the distinct value you want to return, assign it to the combobox and requery and you are done. Please view code update above. – Rich Mar 26 '14 at 15:03
The first combobox is Material, the second is BilletNumber, then Test Type, then Axis (all part of ID Maker). With your update, I'm, still not getting anything in the 2nd combobox. Also, if I want to also only have distinct items shown in the first combobox, where would I put that code? I think it would be something like this, SELECT DISTINCT [ID Maker.Material] FROM [ID Maker] ORDER BY [ID Maker.Material]; but I don't know the proper location – user3241316 Mar 26 '14 at 16:23
Close, your From Command needs to reference the table, not the column – Rich Mar 26 '14 at 16:42
So something like this, SELECT DISTINCT [ID Maker.Material] FROM FinalTable ORDER BY [ID Maker.Material]; ? – user3241316 Mar 26 '14 at 16:46
Alright, I got it to only take unique values in the first box. And your code works for the subsequent boxes. (I changed basequery to qury to match the rest of the code.) If I want the other comboboxes to update sequentially, all I have to do is copy that code into the After Update codebuilder and change a few names for each one, right? – user3241316 Mar 26 '14 at 17:58
Absolutely correct! Glad we worked this out. :) Don't forget to mark an answer and upvote – Rich Mar 26 '14 at 17:59
One last thing, if I want each combobox to update the entries in a table as well, is there some way I can say, "update all fields" as opposed to going through each? – user3241316 Mar 26 '14 at 18:05
It's under the "note" section of the updated answer :) – Rich Mar 26 '14 at 18:28
I think I may have been a little unclear with my last question. I want those 4 combo boxes to filter a table that has more fields than just those 4. The table also is already placed in the form, and I'm not entering anything new into it; this form is just for looking at data that's already there. Thanks again for all your help. – user3241316 Mar 26 '14 at 20:21
Thats a longer question than just a simple response here. You can either ask a new question or go here -> http://www.dbforums.com/microsoft-access/1641732-using-combo-box-filter-records-access-form.html and it describes how to filter form via combobox – Rich Mar 26 '14 at 20:39
One last thing (sorry). I just realized that each Combo box only updates based on the immediate previous box. ie box 4 does not care about box 2 or 1. I've updated my post with the code for the 4th box; if you could take a look at it I'd really appreciate it. – user3241316 Mar 27 '14 at 19:11
Please clarify. What was box 4 supposed to update with from box 1 or 2? – Rich Mar 27 '14 at 19:17
Box 1 is called BilletMaterial. Box 2 is BilletNumber. When an option is selected in 3, all of the options in 4 that correspond to 3 show up in 4, with the filtering that was generated by 1 and 2 being ignored. – user3241316 Mar 27 '14 at 19:22
That's most likely because your queries are messed up. you can only have 1 `WHERE` statement in a query. So instead of `WHERE x='x' WHERE y='y'` it should be `Select * From tbl WHERE x='x' AND y='y'` Does that make sense? – Rich Mar 27 '14 at 20:11
That fixed it. I figured that was where my problem was, I just didn't know the proper syntax. Thanks! – user3241316 Mar 27 '14 at 20:19

Cascading combo boxes

1 Answers1

Linked

Related