Log in

View Full Version : I get compile errors on a command buttons when I click on each one. Any reasons why?



wedd
12-22-2011, 04:24 AM
Hi experts, experienced users, whizz kids! I created 3 command buttons on a form, however when I click on each button it hightlights the click command subroutine in yellao and displays an compile error message. I have published the functions behind each button...is there any code errors that need to be amended? Can this be resolved, if so how can this be done? I'm a beginner/intermediate user in visual basic...so debugging and learning how to troubleshoot these sort of issues will aide my learning. Thanks for your contributions:friends:


Command Search Button
Private Sub cmdSearch_Click() Highlighted in yellow
On Error Resume Next
Dim sSql As String
Dim sCriteria As String
sCriteria = "WHERE 1=1 "
'tblSurname qrySearchCriteriaSub
If Me![Venue] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.venue = """ & Venue & """"
End If

If Me![Title] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Title like """ & Title & "*"""
End If

If Me![FirstName] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.FirstName = """ & FirstName & """"
End If

If Me![Location] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Location = """ & Location & """"
End If

If Me![StartingScheduleDate] <> "" And EndingScheduleDate <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Starting Schedule Date between #" & Format(StartingScheduleDate, "dd-mmm-yyyy") & "# and #" & Format(EndingScheduleDate, "dd-mmm-yyyy") & "#"
End If

If Me![Surname] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Surname like """ & Surname & "*"""
End If

sSql = "SELECT DISTINCT [LocationID], [venue],[Title],[FirstName],[Location],[Starting Schedule Date],[Surname] from qrySearchCriteriaSub " & sCriteria
Forms![frmSearchCriteriaMain]![frmSearchCriteriaSub].Form.RecordSource = sSql
Forms![frmSearchCriteriaMain]![frmSearchCriteriaSub].Form.Requery
',[Title],[FirstName],[Location],[StartingScheduleDate],[EndingScheduleDate],[Surname]

'frmSearchCriteriaSub.Requery
End Sub

CmdPreviewReportCommandButton
Private Sub cmdPreviewReport_Click() Highlighted in yellow
On Error Resume Next
Dim sCriteria As String 'tblSurname qrySearchCriteriaSub
sCriteria = " 1 = 1 "

'This code is for a specific search where you will need to enter the exact string
'The source for this code can either be from a table or query
If Venue <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.venue = """ & Venue & """"
End If

'This code is for a Like search where can enter part of a string
'The source for this code can either be from a table or query
If Title <> "" Then 'on your form
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Title like """ & Title & "*"""
End If

If FirstName <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.FirstName = """ & FirstName & """"
End If

If Location <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Location = """ & Location & """"
End If

If StartingScheduleDate <> "" And EndingScheduleDate <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Starting Schedule Date between #" & Format(StartingScheduleDate, "dd-mmm-yyyy") & "# and #" & Format(EndingScheduleDate, "dd-mmm-yyyy") & "#"
End If

If Surname <> "" Then 'on your form
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Surname like """ & Surname & "*"""
End If
DoCmd.OpenReport "rptSearchCrietria", acPreview, , sCriteria
End Sub


CmdClear Button
Private Sub cmdClear_Click() Highligted in yellow

Title = ""
Venue = ""
FirstName = ""
Location = ""
StartingScheduleDate = ""
EndingScheduleDate = ""
Surname = ""
End Sub