Originally Posted by
Perito
Can you post the SQL string so I can take a peek at it?
I have pasted here the procedure that I am having the error with, you can see below the highlighted line of code that is giving me the problem. Is this what you are asking for?
Private Sub cboStatus_AfterUpdate()
Dim statusTemp As String
Dim fiscalYearTemp As String
Dim provinceTemp As String
Dim sql As String
'using temp variables to avoid a bug where MS Access loses
'the value in Me.cboStatus and Me.cboFiscalYear
statusTemp = Nz(Me.cboStatus, " ")
fiscalYearTemp = Nz(Me.cboFiscalYear, " ")
provinceTemp = Nz(Me.cboProvince, " ")
'changing AmountApproved column to fiscal year budget amount
If fiscalYearTemp = " " Then
lblAmount.Caption = "AmountApproved:"
Me.txtAmount.ControlSource = "AmountApproved"
Else
lblAmount.Caption = "Budget" & fiscalYearTemp & ":"
Me.txtAmount.ControlSource = "Budget"
End If
'initializing the filter
Me.Filter = ""
Me.Filter = "" 'filter needs to be cleared twice as first clear doesn't always work
Me.FilterOn = False
'MsgBox (fiscalYearTemp & " 'filter needs to be cleared twice as first clear doesn't always work
Me.FilterOn = False
'MsgBox (fiscalYearTemp & " & statusTemp)
'filtering on Status
If statusTemp = "Open" & statusTemp)
'filtering on Status
If statusTemp = "(Status <> 'Closed' and Status <> 'Rejected' and Status <> 'Withdrawn')" Then
Me.Filter = "CA Not Signed"
ElseIf statusTemp = "(Status = 'Project Received' or Status = 'Project Approved' " Then
Me.Filter = "or Status = 'CA Finalized' or Status = 'CA Signed By ED')" _
& "CA Signed"
ElseIf statusTemp = "Status = 'CA Signed By Proponent'" Then
Me.Filter = "Rejected"
ElseIf statusTemp = "Status = 'Rejected'" Then
Me.Filter = "Withdrawn"
ElseIf statusTemp = "Status = 'Withdrawn'"
End If
'filtering on FiscalYear
If (fiscalYearTemp <> " Then
Me.Filter = ") And (Me.Filter <> ""
End If
'filtering on FiscalYear
If (fiscalYearTemp <> " and (FiscalYear = '") And (Me.Filter <> "'") Then
Me.Filter = Me.Filter & " or Status = 'Project Received')"
ElseIf fiscalYearTemp <> " & fiscalYearTemp & " Then
Me.Filter = "(FiscalYear = '" _
& "' or Status = 'Project Received')"
End If
'filtering on Province
If (provinceTemp <> "
ElseIf fiscalYearTemp <> ") And (Me.Filter <> "" Then
Me.Filter = "ON, MB, SK, AB, BC, NT & YT" & fiscalYearTemp & " and (Province = 'ON' or Province = 'MB' or Province = 'SK' "
End If
'filtering on Province
If (provinceTemp <> "or Province = 'AB' or Province = 'BC' or Province = 'NT' or Province = 'YT')") And (Me.Filter <> "QC, NU, NB, NS, PE & NL") Then
If cboProvince = " and (Province = 'QC' or Province = 'NU' or Province = 'NB' " Then
Me.Filter = Me.Filter & "or Province = 'NS' or Province = 'PE' or Province = 'NL')"
End If
ElseIf provinceTemp <> " _
& " Then
If provinceTemp = "ON, MB, SK, AB, BC, NT & YT"
ElseIf cboProvince = "(Province = 'ON' or Province = 'MB' or Province = 'SK' " Then
Me.Filter = Me.Filter & "or Province = 'AB' or Province = 'BC' or Province = 'NT' or Province = 'YT')" _
& "QC, NU, NB, NS, PE & NL"
End If
ElseIf provinceTemp <> "(Province = 'QC' or Province = 'NU' or Province = 'NB' " Then
If provinceTemp = "or Province = 'NS' or Province = 'PE' or Province = 'NL')" Then
Me.Filter = "" _
& "SELECT tblMain.ProjectCode, tblMain.Province, tblMain.EventDate, "
ElseIf provinceTemp = "tblMain.AmountApproved, tblMain.Status, tblMain.StatusComment " Then
Me.Filter = "tblMain.ProjectTitle " _
& "FROM tblMain "
End If
End If
'MsgBox (Me.Filter)
If Me.Filter = "ORDER BY tblMain.ProjectCode;"
ElseIf ((fiscalYearTemp = " Then
sql = ") Or (fiscalYearTemp = "" _
& "SELECT tblMain.ProjectCode, tblMain.Province, tblMain.EventDate, " _
& "tblMain.AmountApproved, tblMain.Status, tblMain.StatusComment " _
& "tblMain.ProjectTitle " _
& "FROM tblMain "
ElseIf ((fiscalYearTemp = "WHERE " & Me.Filter & ") Or (fiscalYearTemp = " _
& "ORDER BY tblMain.ProjectCode;")) Then
sql = "SELECT tblMain.ProjectCode, tblMain.Province, tblMain.EventDate, " _
& "tblMain.AmountApproved, tblMain.Status, tblMain.StatusComment, " _
& "tblFiscalBudget.FiscalYear AS FiscalYear, tblFiscalBudget.Budget " _
& "tblMain.ProjectTitle " _
& "FROM tblMain " & Me.Filter & "LEFT JOIN tblFiscalBudget ON tblMain.ProjectCode = tblFiscalBudget.ProjectCode " _
& "WHERE " & Me.Filter & "
Else
sql = " _
& "ORDER BY tblMain.ProjectCode;"
End If
Me.RecordSource = sql -(highlighted code)
Me.cboStatus = statusTemp
Me.cboFiscalYear = fiscalYearTemp
Me.cboProvince = provinceTemp
End Sub