Forms Programming

  • Thread starter Thread starter Priss
  • Start date Start date
P

Priss

Can anyone tell me why I am getting error 3075? See code
below.


Option Compare Database

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 [tblLSBO_5/10] "

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 & " "
Else
' we do not have an app sys id

SQL = SQL & "where [Error]=" & ErrorValue
End If
' we do not have a error value

SQL = SQL & "where [Error]=" & ErrorValue & "
and [Sales ID] = " & SalesID & ""
End If
Else
'we do not have a sales id

If Len(Trim$(AppSysID & "")) > 0 Then
' we have a app sys id
SQL = SQL & "where [APP SYS ID]=" & AppSysID
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

Private Sub cboSalesID_AfterUpdate()
Set Me.Recordset = FilterData

' Find the record that matches the control.
'Dim rs As Object

'Set rs = Me.Recordset.Clone
'rs.FindFirst "[Sales ID] = '" & Me![cboSalesID] & "'"
'Me.Bookmark = rs.Bookmark

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 cmdRefresh_Click()
On Error GoTo Err_cmdRefresh_Click
'Clear screen
cboError = ""
cboAppID = ""
cboSalesID = ""

'tblLSBO_5/10 subform=""
cboError.SetFocus

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70

Exit_cmdRefresh_Click:
Exit Sub

Err_cmdRefresh_Click:
MsgBox Err.Description
Resume Exit_cmdRefresh_Click

End Sub

Private Sub txtDesc_AfterUpdate()
Me.txtDesc = Me![cboAppID]
End Sub
 
Priss said:
Can anyone tell me why I am getting error 3075? See code
below.


Option Compare Database

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 [tblLSBO_5/10] "

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 & " "
Else
' we do not have an app sys id

SQL = SQL & "where [Error]=" & ErrorValue
End If
' we do not have a error value

SQL = SQL & "where [Error]=" & ErrorValue & "
and [Sales ID] = " & SalesID & ""
End If
Else
'we do not have a sales id

If Len(Trim$(AppSysID & "")) > 0 Then
' we have a app sys id
SQL = SQL & "where [APP SYS ID]=" & AppSysID
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

Private Sub cboSalesID_AfterUpdate()
Set Me.Recordset = FilterData

' Find the record that matches the control.
'Dim rs As Object

'Set rs = Me.Recordset.Clone
'rs.FindFirst "[Sales ID] = '" & Me![cboSalesID] & "'"
'Me.Bookmark = rs.Bookmark

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 cmdRefresh_Click()
On Error GoTo Err_cmdRefresh_Click
'Clear screen
cboError = ""
cboAppID = ""
cboSalesID = ""

'tblLSBO_5/10 subform=""
cboError.SetFocus

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70

Exit_cmdRefresh_Click:
Exit Sub

Err_cmdRefresh_Click:
MsgBox Err.Description
Resume Exit_cmdRefresh_Click

End Sub

Private Sub txtDesc_AfterUpdate()
Me.txtDesc = Me![cboAppID]
End Sub
Click to expand...

Please post the actual error message (3075 being a multipurpose error
number) and indicate the line on which the error is raised. If it
happens on
Set rs = myDB.OpenRecordset(SQL)
Click to expand...

set a breakpoint there and determine the value of SQL at the point where
the error occurs.
 
The error is not coming up now. I just can't get the
Sales ID to respond to the subform or the text field under
APP SYS ID to display.
-----Original Message-----
Priss said:
Can anyone tell me why I am getting error 3075? See code
below.


Option Compare Database

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 [tblLSBO_5/10] "

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 & " "
Else
' we do not have an app sys id

SQL = SQL & "where [Error]=" & ErrorValue
End If
' we do not have a error value

SQL = SQL & "where [Error]=" & ErrorValue & "
and [Sales ID] = " & SalesID & ""
End If
Else
'we do not have a sales id

If Len(Trim$(AppSysID & "")) > 0 Then
' we have a app sys id
SQL = SQL & "where [APP SYS ID]=" & AppSysID
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

Private Sub cboSalesID_AfterUpdate()
Set Me.Recordset = FilterData

' Find the record that matches the control.
'Dim rs As Object

'Set rs = Me.Recordset.Clone
'rs.FindFirst "[Sales ID] = '" & Me![cboSalesID] & "'"
'Me.Bookmark = rs.Bookmark

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 cmdRefresh_Click()
On Error GoTo Err_cmdRefresh_Click
'Clear screen
cboError = ""
cboAppID = ""
cboSalesID = ""

'tblLSBO_5/10 subform=""
cboError.SetFocus

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70

Exit_cmdRefresh_Click:
Exit Sub

Err_cmdRefresh_Click:
MsgBox Err.Description
Resume Exit_cmdRefresh_Click

End Sub

Private Sub txtDesc_AfterUpdate()
Me.txtDesc = Me![cboAppID]
End Sub
Click to expand...

Please post the actual error message (3075 being a multipurpose error
number) and indicate the line on which the error is raised. If it
happens on
Set rs = myDB.OpenRecordset(SQL)
Click to expand...

set a breakpoint there and determine the value of SQL at the point where
the error occurs.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
The line below is what's causing the problem. Also the
error 3075, Syntax error (missing operator)in query
expression. Open for suggestions.

SQL = SQL & "where [Error]=" & ErrorValue & " and [Sales
ID] = " & SalesID & " "

-----Original Message-----
The error is not coming up now. I just can't get the
Sales ID to respond to the subform or the text field under
APP SYS ID to display.
-----Original Message-----
Priss said:
Can anyone tell me why I am getting error 3075? See code
below.


Option Compare Database

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 [tblLSBO_5/10] "

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 & " "
Else
' we do not have an app sys id

SQL = SQL & "where [Error]=" & ErrorValue
End If
' we do not have a error value

SQL = SQL & "where [Error]=" & ErrorValue & "
and [Sales ID] = " & SalesID & ""
End If
Else
'we do not have a sales id

If Len(Trim$(AppSysID & "")) > 0 Then
' we have a app sys id
SQL = SQL & "where [APP SYS ID]=" & AppSysID
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

Private Sub cboSalesID_AfterUpdate()
Set Me.Recordset = FilterData

' Find the record that matches the control.
'Dim rs As Object

'Set rs = Me.Recordset.Clone
'rs.FindFirst "[Sales ID] = '" & Me![cboSalesID] & "'"
'Me.Bookmark = rs.Bookmark

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 cmdRefresh_Click()
On Error GoTo Err_cmdRefresh_Click
'Clear screen
cboError = ""
cboAppID = ""
cboSalesID = ""

'tblLSBO_5/10 subform=""
cboError.SetFocus

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70

Exit_cmdRefresh_Click:
Exit Sub

Err_cmdRefresh_Click:
MsgBox Err.Description
Resume Exit_cmdRefresh_Click

End Sub

Private Sub txtDesc_AfterUpdate()
Me.txtDesc = Me![cboAppID]
End Sub
Click to expand...

Please post the actual error message (3075 being a multipurpose error
number) and indicate the line on which the error is raised. If it
happens on
Set rs = myDB.OpenRecordset(SQL)
Click to expand...

set a breakpoint there and determine the value of SQL at the point where
the error occurs.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
.
Click to expand...
 
Hi:

If the SalesID is a string then it will need to be
quoted, eg:

SQL = SQL & "where [Error]=" & ErrorValue & " and [Sales
ID] = '" & SalesID & "' "
Perry

-----Original Message-----
The line below is what's causing the problem. Also the
error 3075, Syntax error (missing operator)in query
expression. Open for suggestions.

SQL = SQL & "where [Error]=" & ErrorValue & " and [Sales
ID] = " & SalesID & " "

-----Original Message-----
The error is not coming up now. I just can't get the
Sales ID to respond to the subform or the text field under
APP SYS ID to display.
-----Original Message-----
Can anyone tell me why I am getting error 3075? See code
below.


Option Compare Database

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 [tblLSBO_5/10] "

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]=" &
Click to expand...
ErrorValue
& "
and [APP SYS ID]=" & AppSysID & " "
Else
' we do not have an app sys id

SQL = SQL & "where [Error]=" & ErrorValue
End If
' we do not have a error value

SQL = SQL & "where [Error]=" &
Click to expand...
ErrorValue
& "
and [Sales ID] = " & SalesID & ""
End If
Else
'we do not have a sales id

If Len(Trim$(AppSysID & "")) > 0 Then
' we have a app sys id
SQL = SQL & "where [APP SYS ID]=" & AppSysID
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

Private Sub cboSalesID_AfterUpdate()
Set Me.Recordset = FilterData

' Find the record that matches the control.
'Dim rs As Object

'Set rs = Me.Recordset.Clone
'rs.FindFirst "[Sales ID] = '" & Me![cboSalesID] & "'"
'Me.Bookmark = rs.Bookmark

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 cmdRefresh_Click()
On Error GoTo Err_cmdRefresh_Click
'Clear screen
cboError = ""
cboAppID = ""
cboSalesID = ""

'tblLSBO_5/10 subform=""
cboError.SetFocus

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70

Exit_cmdRefresh_Click:
Exit Sub

Err_cmdRefresh_Click:
MsgBox Err.Description
Resume Exit_cmdRefresh_Click

End Sub

Private Sub txtDesc_AfterUpdate()
Me.txtDesc = Me![cboAppID]
End Sub

Please post the actual error message (3075 being a multipurpose error
number) and indicate the line on which the error is raised. If it
happens on

Set rs = myDB.OpenRecordset(SQL)

set a breakpoint there and determine the value of SQL
Click to expand...
at
the point where
the error occurs.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top