"All" included in combobox.

[PierreR]

This code comes out of an example in one of my books, of a combobox with ALL at the top of the drop-down list. SelectRecords refers to a public procedure. Can someone tell me what is wrong with the code? Please.

Private Sub cboActivityType_AfterUpdate()
On Error GoTo Error_Handler

    SelectRecords
    cboActivityType.Requery
    
Exit_Procedure:
    On Error Resume Next
    Exit Sub
Error_Handler:
    MsgBox "An error has occurred in this application. " _
    & "Please contact your technical support person " _
    & "and tell them this information:" _
    & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " &
'Something is incomplete at the end of the previous line
Err.Description , _
    Buttons:=vbCritical, Title:="TestWorklog"
    Resume Exit_Procedure
    Resume
    
End Sub

 

[KenHigg]

What error message do you get and/or what line does it exit on when you debug?

???
ken

 

[PierreR]

It says compile error, syntax error, and the whole block under "Error Handler" is highlighted. What I did then, was to make a continuance character after that block, and it worked, but there is now another problem. see next post.

Error_Handler:
    MsgBox "An error has occurred in this application. " _
    & "Please contact your technical support person " _
    & "and tell them this information:" _
    & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " & _
Err.Description, _
    Buttons:=vbCritical, Title:="TestWorklog"
    Resume Exit_Procedure
    Resume

 

[PierreR]

After adding the continuance character, that part seems to work, but now the SelectRecords() Public Sub referred to in the combo's AfterUpdate event, seems to have a bad section. It refers to a ReplaceWhereClause function, which does not seem to exist in the book, which is highlighted and the error reads ... Compile Error: Sub or function not defined. Do you know what is wrong?

Public Sub SelectRecords()
On Error GoTo Error_Handler
    
    Dim varWhereClause As Variant
    Dim strAND As String
    
    varWhereClause = Null
    strAND = " AND "
    
    If cboActivityType & "" <> "<all>" Then
        varWhereClause = (varWhereClause + strAND) & _
            "tblActivities.ActivityTypeID = """ & _
            cboActivityType & """"
    End If
    
    varWhereClause = " WHERE " + varWhereClause
    
    Me.RecordSource = ReplaceWhereClause(Me.RecordSource, varWhereClause)
    Me.Requery
    
    EnableDisableControls
    
Exit_Procedure:
    On Error Resume Next
    Exit Sub
Error_Handler:
    MsgBox "An error has occurred in this application. " _
    & "Please contact your technical support person " _
    & "and tell them this information:" _
    & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " & _
Err.Description, _
    Buttons:=vbCritical, Title:="TestWorklog"
    Resume Exit_Procedure
    Resume
    
End Sub

 

[Uncle Gizmo]

It’s very difficult (for me anyway) to work out exactly what’s happening. But from what I can see you are trying to change the record source of your combo box. Interestingly it appears to be using the selection in the combo box to determine the structure of the new record source.

I find that it is a very difficult to get the SQL strings correct, the syntax is difficult even when you have had experience. So I find the best approach is to construct a basic string, that is remove all the variables and replace them with actual values.

So to do that with your string I would start by getting rid of the If statement, you can do this as long as you don’t select <all> when you are testing.

Reduce This:
If cboActivityType & "" <> "<all>" Then
varWhereClause = (varWhereClause + strAND) & _
"tblActivities.ActivityTypeID = """ & _
cboActivityType & """"
End If

To This:

WHERE AND tblActivities.ActivityTypeID = “cboActivityType”

You can use the above string for your varWhereClause

varWhereClause = WHERE AND tblActivities.ActivityTypeID = “cboActivityType”

Now I can see something wrong straightaway, you shouldn’t have a WHERE and an AND together like that. (if I’ve converted the string correctly)

The other thing is, I don’t know what your “ ReplaceWhereClause function Does to The SQL string.

 

[WayneRyan]

Pierre,

Error_Handler:
    MsgBox "An error has occurred in this application. " _
    & "Please contact your technical support person " _
    & "and tell them this information:" _
    & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " & _ <-- It expects a continuation!!!!
Err.Description, _ <-- This is a label ( Err.Description: ), not the continuation!!!

Wayne

 

[PierreR]

Okay, but I am confused to why there is an error message without the continuation, and not with it?

AND

What could be wrong in this code when the continuation is not there?

 

[WayneRyan]

Pierre,

OK, I read a little slower this time.

The way it was formatted, I took the Err.Description as a label! Oops.

Syntax-wise, this should fix you up:

Private Sub cboActivityType_AfterUpdate()
On Error GoTo Error_Handler

    SelectRecords
    cboActivityType.Requery
    
Exit_Procedure:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "An error has occurred in this application. " & _
           "Please contact your technical support person " & _
           "and tell them this information:" & vbCrLf & vbCrLf & _
           "Error Number " & Err.Number & ", " & Err.Description, _
           Buttons:=vbCritical, Title:="TestWorklog"
    Resume Exit_Procedure
End Sub

Wayne