R
ryguy7272
Yesterday I linked my Access DB to an Excel Workbook, and specifically to a
single sheet named ‘Main’. I was able to control the creation of a dynamic
Report through a Form. The Row Source references this:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;
The appropriate columns do exist in the Excel sheet that I am trying to
reference. The code under the Form looks like this:
Option Compare Database
Option Explicit
Private Sub cmdApplyFilter_Click()
Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String
' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If
' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If
' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on
DoCmd.OpenReport "SelReport", acPreview, , strFilter
With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub
Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub
I know this thing was working yesterday, but today it won’t do anything
(when I click in any of the ComboBoxes on the Form I can’t see any data at
all). I don’t know what changed between yesterday and today. What could
cause this? How can I reestablish my link between the Form and the Excel
sheet named ‘Main’?
Regards,
Ryan---
single sheet named ‘Main’. I was able to control the creation of a dynamic
Report through a Form. The Row Source references this:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;
The appropriate columns do exist in the Excel sheet that I am trying to
reference. The code under the Form looks like this:
Option Compare Database
Option Explicit
Private Sub cmdApplyFilter_Click()
Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String
' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If
' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If
' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on
DoCmd.OpenReport "SelReport", acPreview, , strFilter
With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub
Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub
I know this thing was working yesterday, but today it won’t do anything
(when I click in any of the ComboBoxes on the Form I can’t see any data at
all). I don’t know what changed between yesterday and today. What could
cause this? How can I reestablish my link between the Form and the Excel
sheet named ‘Main’?
Regards,
Ryan---