-----Original Message-----
I know this can be very frustrating at times and I do
apologize as well for thinking the worse because I had not
gotten a response from previous post of the same issue.
Here is the code that I have provided w/revisions:
Comments inline.
Private Function FilterData() As DAO.Recordset
On Error GoTo err_routine
Dim ErrorValue As String
Dim APPSYSID As String
Dim SalesID As String
Dim FilterValue As String
Dim SQL As String
Dim rs As DAO.Recordset
Dim myDB As DAO.Database
' get the value of the Error from the combo box
Me.cboError.SetFocus
ErrorValue = Me.cboError.Text
You can also determine the contents of a control by examining its
Value property. It's not necessary to setfocus to it in that case. If
you Dim ErrorValue as a Variant instead of a String it will accept
NULL - see below.
' get the value of the AppSysID from the combo box
Me.cboAppID.SetFocus
APPSYSID = Me.cboAppID.Text
' get the value of the SalesID from the combo box
'Me.cboSalesID.SetFocus
'SalesID = Me.cboSalesID.Text
Commented out intentionally?
' get an instance of the database
Set myDB = CurrentDb
' set up the filter
SQL = "SELECT * FROM [tblExclude] "
If Len(Trim$(ErrorValue & "")) > 0 Then
'we have an error value
If Len(Trim$(APPSYSID & "")) > 0 Then
'we have a app sys id
SQL = SQL & "where [Error]=" & ErrorValue & "
and [APP SYS ID]=" & APPSYSID & ""
If Error is a Text datatype field you'll need the syntactically
required quote marks:
"where [Error] = '" & [ErrorValue] & "' and...
If it's numeric your code should work as written.
Else
'we do not have an app sys id
SQL = SQL & "where [Error]=" & ErrorValue
'we do not have a error value
End If
Else
If Len(Trim$(APPSYSID & "")) > 0 Then
'we have a app sys id
SQL = SQL & "where [APP SYS ID]=" & APPSYSID
Same concern about Text fields.
Else
'we do not have an app sys id
'do not change the SQL
End If
End If
Set rs = myDB.OpenRecordset(SQL)
Set FilterData = rs
Exit Function
err_routine:
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbOKOnly + vbInformation, "Error"
Set FilterData = Nothing
Exit Function
End Function
Private Sub cboAPPID_AfterUpdate()
Set Me.Recordset = FilterData
End Sub
Private Sub cboError_AfterUpdate()
Set Me.Recordset = FilterData
End Sub
... <code snipped, irrelevant to this problem>
Private Sub Description_AfterUpdate()
Me!tblExclude_Error!Description = Me!Description
End Sub
Private Sub Form_Load()
'clear screen
cboError = ""
cboAppID = ""
'cboSalesID=""
Set these to NULL rather than to a zero length string.
cboError.SetFocus
End Sub
So... a couple of questions:
- What's happening when you select a value from the combo box?
- What do you WANT to happen?
If you just want to display records from tblExclude which match the
combo box criteria, there are two perhaps easier ways to do so. First,
build your SQL and set the RecordSource property of the Query to that
string (since the Recordsource is a string property, not a Recordset
property); second, use *no code at all*, and instead put a Subform on
your form based on tblExclude. Set its Master Link Field property to
[cboError];[cboAppID];[cboSalesID]
and its Child Link Field to
[ErrorID];[AppID];[SalesID]
It should filter the data on the basis of these fields. You'll
probably need to Requery the subform in the AfterUpdate event of each
combo box.
John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
.
