Drill down using DAO

J

JohnW

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
 
A

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
 
J

JohnW

Thanks, Allen. Job Done.
--
John Whyte


Allen Browne said:
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.

JohnW said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top