Drill down using DAO

[Guest]

Hi, I hope you have done this before.

I have one table, tblAllStockAlignedColumns, (32,000 records).

On a continuous form I use combo boxes in the form header linked back to
fields in the table to run a SQL filter using DAO. This works fine.

What I would like to do is set the combo boxes to drop down only records
from the resulting recordset, and then, drill down further within that
resulting recordset by using the after update of the combo to restrict the
number of records further. Here is the code I use, any help appreciated:-

Private Sub ComboReference_AfterUpdate()
Dim dbs As Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strSQLWhere As String
Dim frm As Access.Form


strSQL = "SELECT * FROM tblAllStockAlignedColumns"
strSQL = strSQL & " WHERE Reference LIKE'" & ComboReference & "*'" &
" AND"
strSQL = strSQL & " tblAllStockAlignedColumns.Deleted = No ORDER BY
Reference;"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
DoCmd.OpenForm "frmAllStockAlignedColumns"
Set frm = Forms![frmAllStockAlignedColumns]
Set frm.Recordset = rst
Me.ComboReference.Value = Null
Me.ComboReference.SetFocus


End Sub

Thanks,

John

 

[Allen Browne]

This kind of thing builds the WHERE clause based on the values in the
combos. It then assigns the SQL statement to the RecordSource of the form,
and to the RowSource of each combo:

Dim strWhere As String
Dim strSql As String
Dim lngLen As Long
Const strcStub = "SELECT MyField, Field2, SomeField, AnotherField " & _
vbCrLf & "FROM tblAllStockAlignedColumns " & vbCrLf
Const strcTail = "OrderBy SomeField, SeconarySortField;" & vbCrLf

If Not IsNull(Me.MyCombo) Then
strWhere = strWhere & "([MyField] = """ & Me.MyCombo & """) AND "
End If
If Not IsNull(Me.Combo2) Then
strWhere = strWhere & "([Field2] = " & Me.Combo2 & ") AND "
End If
'etc for other combos.

'Remove the trailing " AND ", and build the SQL statement.
lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen) & vbCrLf
strSql = strcStub & " WHERE " & strWhere & strcTail
Else
strSql = strcStub & strcTail
End If
Me.RecordSource = strSql

With Me.MyCombo
.Value = Null
.RowSource = strSql
End With
With Me.Combo2
.Value = Null
.RowSource = strSql
End With
'etc for other combos

 

[JohnW]

Thanks, Allen. Job Done.
--
John Whyte

This kind of thing builds the WHERE clause based on the values in the
combos. It then assigns the SQL statement to the RecordSource of the form,
and to the RowSource of each combo:

Dim strWhere As String
Dim strSql As String
Dim lngLen As Long
Const strcStub = "SELECT MyField, Field2, SomeField, AnotherField " & _
vbCrLf & "FROM tblAllStockAlignedColumns " & vbCrLf
Const strcTail = "OrderBy SomeField, SeconarySortField;" & vbCrLf

If Not IsNull(Me.MyCombo) Then
strWhere = strWhere & "([MyField] = """ & Me.MyCombo & """) AND "
End If
If Not IsNull(Me.Combo2) Then
strWhere = strWhere & "([Field2] = " & Me.Combo2 & ") AND "
End If
'etc for other combos.

'Remove the trailing " AND ", and build the SQL statement.
lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen) & vbCrLf
strSql = strcStub & " WHERE " & strWhere & strcTail
Else
strSql = strcStub & strcTail
End If
Me.RecordSource = strSql

With Me.MyCombo
.Value = Null
.RowSource = strSql
End With
With Me.Combo2
.Value = Null
.RowSource = strSql
End With
'etc for other combos

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi, I hope you have done this before.

I have one table, tblAllStockAlignedColumns, (32,000 records).

On a continuous form I use combo boxes in the form header linked back to
fields in the table to run a SQL filter using DAO. This works fine.

What I would like to do is set the combo boxes to drop down only records
from the resulting recordset, and then, drill down further within that
resulting recordset by using the after update of the combo to restrict the
number of records further. Here is the code I use, any help appreciated:-

Private Sub ComboReference_AfterUpdate()
Dim dbs As Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strSQLWhere As String
Dim frm As Access.Form


strSQL = "SELECT * FROM tblAllStockAlignedColumns"
strSQL = strSQL & " WHERE Reference LIKE'" & ComboReference & "*'"
&
" AND"
strSQL = strSQL & " tblAllStockAlignedColumns.Deleted = No ORDER BY
Reference;"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
DoCmd.OpenForm "frmAllStockAlignedColumns"
Set frm = Forms![frmAllStockAlignedColumns]
Set frm.Recordset = rst
Me.ComboReference.Value = Null
Me.ComboReference.SetFocus


End Sub

Thanks,

John