Display All Records

I

Incognito

I have (2)cbo's to filter a subform. One works fine, but
when i select the second for filter as well - nothing
happens. I also want the form to populate ALL records, if
nothing is selected, but doesn't seem to like my code.
Maybe I just need another pair of eyes to look at it.
Can't find the error. Thanks a million!!!
Here is what I have:
This is the code for the cbo:
Set Me.Recordset = FilterData

This is the code to pull ALL records:
Dim Update As String
Dim tblExclude_Error As subform
If Toggle45 = Update Then
tblExclude_Error.Visible = True
End If

This is the code for filtering:
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
' 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

' 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

'If Len(Trim$(SalesID & "")) > 0 Then
' we have a sales id

SQL = SQL & "where [Error]=" & ErrorValue & "
and [APP SYS ID]=" & APPSYSID & " "

' we do not have an app sys id
Else

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

'If Len(Trim$(SalesID & "")) > 0 Then
'we have a sales id
'SQL = SQL & "where [Sales ID]=" & SalesID
Else
' we do not have an app sys id
' we do not have a sales 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
 
G

Guest

Mail not delivered - no account
-----Original Message-----
could you plz send ur code to this e-mail?
(e-mail address removed)

hi
Duane
-----Original Message-----
I have (2)cbo's to filter a subform. One works fine, but
when i select the second for filter as well - nothing
happens. I also want the form to populate ALL records, if
nothing is selected, but doesn't seem to like my code.
Maybe I just need another pair of eyes to look at it.
Can't find the error. Thanks a million!!!
Here is what I have:
This is the code for the cbo:
Set Me.Recordset = FilterData

This is the code to pull ALL records:
Dim Update As String
Dim tblExclude_Error As subform
If Toggle45 = Update Then
tblExclude_Error.Visible = True
End If

This is the code for filtering:
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
' 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

' 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

'If Len(Trim$(SalesID & "")) > 0 Then
' we have a sales id

SQL = SQL & "where [Error]=" & ErrorValue & "
and [APP SYS ID]=" & APPSYSID & " "

' we do not have an app sys id
Else

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

'If Len(Trim$(SalesID & "")) > 0 Then
'we have a sales id
'SQL = SQL & "where [Sales ID]=" & SalesID
Else
' we do not have an app sys id
' we do not have a sales 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
.
Click to expand...
.
Click to expand...
 
D

Duane

ok
sent to
(e-mail address removed)

and also try
(e-mail address removed)
-----Original Message-----
Mail not delivered - no account
-----Original Message-----
could you plz send ur code to this e-mail?
(e-mail address removed)

hi
Duane
-----Original Message-----
I have (2)cbo's to filter a subform. One works fine, but
when i select the second for filter as well - nothing
happens. I also want the form to populate ALL records, if
nothing is selected, but doesn't seem to like my code.
Maybe I just need another pair of eyes to look at it.
Can't find the error. Thanks a million!!!
Here is what I have:
This is the code for the cbo:
Set Me.Recordset = FilterData

This is the code to pull ALL records:
Dim Update As String
Dim tblExclude_Error As subform
If Toggle45 = Update Then
tblExclude_Error.Visible = True
End If

This is the code for filtering:
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
' 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

' 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

'If Len(Trim$(SalesID & "")) > 0 Then
' we have a sales id

SQL = SQL & "where [Error]=" & ErrorValue & "
and [APP SYS ID]=" & APPSYSID & " "

' we do not have an app sys id
Else

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

'If Len(Trim$(SalesID & "")) > 0 Then
'we have a sales id
'SQL = SQL & "where [Sales ID]=" & SalesID
Else
' we do not have an app sys id
' we do not have a sales 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
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
I

Incognito

Option Compare Database
Option Explicit

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

' 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


' 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

'If Len(Trim$(SalesID & "")) > 0 Then
' we have a sales id

SQL = SQL & "where [Error]=" & ErrorValue & "
and [APP SYS ID]=" & APPSYSID & " "
'and [Sales ID]=" & SalesID & ""

' we do not have an app sys id
' we do not hae a sales id

Else

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

'If Len(Trim$(SalesID & "")) > 0 Then
'we have a sales id
'SQL = SQL & "where [Sales ID]=" & SalesID
Else
' we do not have an app sys id
' we do not have a sales 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

Private Sub cboSalesID_AfterUpdate()
Set Me.Recordset = FilterData
End Sub

Private Sub cmdSwitchboard_Click()
On Error GoTo Err_cmdSwitchboard_Click

Dim stDocName As String

stDocName = "mrcSwitchBoard"
DoCmd.RunMacro stDocName

Exit_cmdSwitchboard_Click:
Exit Sub

Err_cmdSwitchboard_Click:
MsgBox Err.Description
Resume Exit_cmdSwitchboard_Click

End Sub

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click


DoCmd.Close

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub

Private Sub cmdEmail_Click()
On Error GoTo Err_cmdEmail_Click

Dim stDocName As String

stDocName = "mcrEmail"
DoCmd.RunMacro stDocName

Exit_cmdEmail_Click:
Exit Sub

Err_cmdEmail_Click:
MsgBox Err.Description
Resume Exit_cmdEmail_Click

End Sub

Private Sub Form_Load()
'clear screen
cboError = ""
cboAppID = ""
'cboSalesID=""

cboError.SetFocus

End Sub

Private Sub Toggle45_Click()
Dim Update As String
Dim tblExclude_Error As SubForm

'clear screen
cboError = ""
cboAppID = ""
'cboSalesID=""

cboError.SetFocus

If Toggle45 = Update Then
tblExclude_Error.Visible = True

End If

End Sub
-----Original Message-----
ok
sent to
(e-mail address removed)

and also try
(e-mail address removed)
-----Original Message-----
Mail not delivered - no account
-----Original Message-----
could you plz send ur code to this e-mail?
(e-mail address removed)

hi
Duane

-----Original Message-----
I have (2)cbo's to filter a subform. One works fine, but
when i select the second for filter as well - nothing
happens. I also want the form to populate ALL records,
if
nothing is selected, but doesn't seem to like my code.
Maybe I just need another pair of eyes to look at it.
Can't find the error. Thanks a million!!!
Here is what I have:
This is the code for the cbo:
Set Me.Recordset = FilterData

This is the code to pull ALL records:
Dim Update As String
Dim tblExclude_Error As subform
If Toggle45 = Update Then
tblExclude_Error.Visible = True
End If

This is the code for filtering:
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
' 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

' 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

'If Len(Trim$(SalesID & "")) > 0 Then
' we have a sales id

SQL = SQL & "where [Error]=" & ErrorValue & "
and [APP SYS ID]=" & APPSYSID & " "

' we do not have an app sys id
Else

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

'If Len(Trim$(SalesID & "")) > 0 Then
'we have a sales id
'SQL = SQL & "where [Sales ID]=" & SalesID
Else
' we do not have an app sys id
' we do not have a sales 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
.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 

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

Similar Threads

Forms Programming 4
Multiple Combo Boxes 4
A Better Way 4
concatenating problem 2
Need help in printing vba code 0
Error in multiple select in a list box code 4
Concencate Emails, Error type declaration does not match 8
Help with VBA sql code 2

Top