dependent combo box by using vba programming (1 Viewer)

postingart

New member
Local time
Today, 16:28
Joined
Jun 27, 2021
Messages
17
hello ,

i want to create dependent combo box by using vba programming , can any one help me for this


thanks
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:28
Joined
Feb 19, 2002
Messages
42,981
This is commonly called cascading combos. For two combos, you only need two lines of code.
In the BeforeUpdate event of the first combo:
Me.cbo2 = Null
Me.cbo2.Requery
If you had three combos, you would need two similar lines in cbo2 AND you would have to add them also to the BeforeUpdate event of cbo1 so they would be in two places.
Me.cbo3 = Null
Me.cbo3.Requery

What makes this work is using saved querydefs for the RowSources of the combos. In the where clause of the query, you reference the "parent" combo. So in the RowSource query for cbo2, you reference cbo1
Where SomeField = Forms!yourform!cbo1

In the RowSource query for cbo3, you reference cbo2
Where SomeField = Forms!yourform!cbo2

I'm sure others will post more code and embed SQL. But this is all you need.
 

postingart

New member
Local time
Today, 16:28
Joined
Jun 27, 2021
Messages
17
Option Compare Database
Option Explicit
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim ind As Integer
Dim selectID As String
Private Sub Form_Load()
On Error GoTo err_msg


StateList

Exit Sub

err_msg:
MsgBox Err.Source & " " & Err.Number & " - " & Err.Description, vbCritical + vbOKOnly, "Error"

End Sub
Private Sub StateList()
On Error GoTo err_msg

strSQL = "SELECT ID,State from tblState order by State"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)

' Check if not exist
If rs.NoMatch = True Then
Else

If cboState.ListCount <> 0 Then
' cboDistricts.RowSource = ""

End If

cboState.AddItem ("*;Select State")

For ind = 1 To rs.RecordCount
cboState.AddItem rs("ID") & ";" & rs("State")

rs.MoveNext
Next

Me.cboState = Me.cboState.ItemData(0)


End If

Exit Sub

err_msg:
MsgBox Err.Source & " " & Err.Number & " - " & Err.Description, vbCritical + vbOKOnly, "Error"

End Sub

Private Sub cboState_Click()
On Error GoTo err_msg




strSQL = "SELECT ID,cityName from tblCities WHERE stateID = " & Me.cboState.Value & " order by cityName"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)

' Check if not exist
If rs.NoMatch = True Then
Else

If cboCity.ListCount <> 0 Then

End If

cboCity.AddItem ("*;Select City")

For ind = 1 To rs.RecordCount
cboCity.AddItem rs("ID") & ";" & rs("cityName")

rs.MoveNext
Next

Me.cboCity = Me.cboCity.ItemData(0)


End If

Exit Sub

err_msg:
MsgBox Err.Source & " " & Err.Number & " - " & Err.Description, vbCritical + vbOKOnly, "Error"
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:28
Joined
Feb 19, 2002
Messages
42,981
I guess you get paid by the line of code you write:) There are simple solutions and there are complex solutions. Is one better than the other? I think there is a clear winner.

Here's the code from one of my forms
Code:
Private Sub cboStateID_AfterUpdate()
    Me.Cityid = Null
    Me.cboCityID.Requery
End Sub

Here's the query for the city combo
Code:
SELECT tblCities.cityID AS Expr1, tblCities.City, tblCities.Stateid
FROM tblCities
WHERE (((tblCities.Stateid)=[forms]![frmtestBrokenCombos]![cbostateID]))
ORDER BY tblCities.City;
 

postingart

New member
Local time
Today, 16:28
Joined
Jun 27, 2021
Messages
17
my issue is not resolved please resolved it
 

Attachments

  • dependent combo.accdb
    444 KB · Views: 88

mike60smart

Registered User.
Local time
Today, 11:28
Joined
Aug 6, 2017
Messages
1,899
I would imagine it is obvious that you would select a City ?
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:28
Joined
Sep 21, 2011
Messages
14,051
I would imagine it is obvious that you would select a City ?
That is grossly unfair Mike, considering what you continually ask on UA all the time? :(
Really disappointed with that response.? :(
 

mike60smart

Registered User.
Local time
Today, 11:28
Joined
Aug 6, 2017
Messages
1,899
where is default text in city drop down . like "select City" & "Select State"
Not knowing a lot of VBA the following may be a better way?
 

Attachments

  • dependent combo.zip
    27.9 KB · Views: 84

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:28
Joined
May 21, 2018
Messages
8,463
See solution for the default value
 

Attachments

  • MajP dependent combo.accdb
    440 KB · Views: 100

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:28
Joined
May 21, 2018
Messages
8,463
i want default text in city drop down . like "select City" & "Select State
see previous thread
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:28
Joined
May 21, 2018
Messages
8,463
Pretty old trick. Here is a detail explanation of two methods. The OP seemed to be doing the latter.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:28
Joined
May 21, 2018
Messages
8,463
Although not used here, I use this code to take a complex query and convert it into a value list. With a value list you can do things like move a value up and down in the list without having to know the sql of the listbox or add a value such as what the OP was trying. You can then add or remove items from the list. Multi column value lists are a pain to add records.
So if you have a need to convert a recordset into a value list this may be handy.

Code:
Private Sub convertToValueList()
    Dim rs              As dao.Recordset
    Dim strSql          As String
    Dim fldField        As dao.Field
    Dim fldCount        As Byte
    Dim strLstValue     As String
    Dim intColCount     As Integer
    Dim intRowCounter   As Integer
    Dim i               As Byte
   
    strSql = Me.ListBox.RowSource
    Me.ListBox.RowSource = ""
    Set rs = CurrentDb.OpenRecordset(strSql)
    Me.ListBox.RowSourceType = "Value List"
   
    'IF LBOX COLUMN COUNT PROPERTY > ROWSOURCE COLUMNS - ERROR
    fldCount = rs.Fields.Count
    If ListBox.ColumnCount > fldCount Then
        Me.ListBox.ColumnCount = fldCount
    End If
   
    intColCount = Me.ListBox.ColumnCount
    'Since converting to value list column heads do not work
    If Me.ListBox.ColumnHeads Then
       MsgBox "Unfortunately, this will not work with column heads. You will have to make your own.", vbInformation
       Me.ListBox.ColumnHeads = False
    End If
   
    Do While Not rs.EOF
       For i = 0 To intColCount - 1
          strLstValue = strLstValue & """" & CStr(Nz(rs.Fields(i), " ")) & """;"
       Next i
     
       intRowCounter = intRowCounter + 1
       rs.MoveNext
       strLstValue = Left(strLstValue, Len(strLstValue) - 1)
       Me.ListBox.addItem (strLstValue)
       strLstValue = ""
    Loop
End Sub

I use it in this class that turns any listbox into a sortable listbox where you can move things up and down. Really helpful for ordering items.
 

Attachments

  • ListBoxSorter V1.accdb
    1.3 MB · Views: 93

Users who are viewing this thread

Top Bottom