No wizard. using code. Please excuse the mess, i was still trying to get things working and not worried to much about how it looks yet
Code:
Private Sub cboloc_AfterUpdate()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strloc As String
Dim strdept As String
Dim strtemp As String
Dim strrs2 As String
Dim strrow As String
strloc = Me.cboloc
strdept = ""
strrow = ""
Me.cbodept.RowSource = "value list"
Me.cbodept.RowSource = ""
Me.cbodept.ColumnCount =2
Set db = CurrentDb
Set rs1 = db.OpenRecordset("LibraryT", dbOpenDynaset, dbSeeChanges)
With rs1
If Not .EOF And Not .BOF Then
.MoveLast
.MoveFirst
While (Not .EOF)
If Left(rs1!libraryid, 3) = strloc Then
strtemp = Mid(rs1!libraryid, 4, 3)
If strtemp <> strdept Then
strdept = strtemp
strrs2 = "SELECT DeptID, Department " & _
"FROM 4DepartmentT " & _
"WHERE DeptID='" & strdept & "'" ' & strdept
Set rs2 = db.OpenRecordset(strrs2, dbOpenDynaset, dbSeeChanges)
' Debug.Print (rs2!deptid)
Dim str1 As String
Dim str2 As String
str1 = rs2!deptid
str2 = rs2!department
strrow = strrow & str2 & ";" & str1 & ";"
End If
End If
.MoveNext
Wend
End If
End With
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing
' Debug.Print (strrow)
strrow = Left(strrow, Len(strrow) - 1)
' Debug.Print (strrow)
Me.cbodept.AddItem (strrow)
Me.cbodept.Visible = True
End Sub
I'm not sure why its not working, when debugging my rowsource string(Del;001;Vise;002;Lifting;003;General;004;Ut ilities;005) prints the full string but i'm only getting the first item.