CODEWITHSUNDEEP
vbams-accessms-access-2007
user3241316
user3241316

Reputation: 167

Cascading combo boxes

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.

Upvotes: 0

Views: 441

Answers (1)

Rich
Rich

Reputation: 4170

Few items of change.

  1. You don't have to reference the table in every element of the query. FinalTable only needs to be included after the "From" Statement.
  2. 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.
  3. 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

Upvotes: 3

Related Questions