Syntax help

[matthewR]

I want to create cascading combo boxes similar to the function below: What would the syntax be if the first combo box is a numeric field and the second combo box is a date field? The example below is for two text fields.

Private Sub FilterMe()
Dim strFilter As String
strFilter = MakeFilter
Me.Filter = strFilter
Me.FilterOn = Not (strFilter = "")
End Sub

Function MakeFilter() As String
If Not IsNull(Me.cboconsultant) Then
strFilter = " AND [Mclname]=" & Chr(34) & Me.cboconsultant & Chr(34)
End If
If Not IsNull(Me.cbocm) Then
strFilter = strFilter & " AND [SFRep_Last]=" & Chr(34) & Me.cbocm & Chr(34)
End If
If Not strFilter = "" Then
' Omit first " AND "
strFilter = Mid(strFilter, 6)
End If
MakeFilter = strFilter
End Function

[HansV]

You don't need & Chr(34) for a number field, and you need to enclose a date field in # characters. So change MakeFilter as follows:

Code: Select all

Function MakeFilter() As String
    If Not IsNull(Me.cboconsultant) Then
        strFilter = " AND [Mclname]=" & Me.cboconsultant
    End If
    If Not IsNull(Me.cbocm) Then
        strFilter = strFilter & " AND [SFRep_Last]=#" & Me.cbocm & "#"
    End If
    If Not strFilter = "" Then
        ' Omit first " AND "
        strFilter = Mid(strFilter, 6)
    End If
    MakeFilter = strFilter
End Function

FilterMe can remain unchanged.

[matthewR]

I have another question about cascading combo boxes. The first combo box I just use the wizard to create. The second combo box, the row source looks like I didn't use the wizard:

SELECT tblSF.SFRep_Last FROM tblSF WHERE ((([forms]![frmSearchSic]![cboconsultant])=[mclname] Or ([forms]![frmSearchSic]![cboconsultant]) Is Null)) GROUP BY tblSF.SFRep_Last;

Am I correct in saying, I have to enter the SQL or go out to the query and enter it there something similar to the above.

[HansV]

You can use the builder button (the ... on the right hand side) in the Row Source property to start the Query Builder.

(You could use the Wizard first, then use the builder button to modify the query generated by the Wizard.)

[matthewR]

On the form (in the old database), I put a row of fields. When I go to form view, it shows multiple rows - one after the other. What property makes it do that? In the form in the database I am trying to emulate, it does what I want but for some reason I am missing something. I tried continuous form but that gives me separate pages for each record. I want all records to show - one after the other just like the form in my old database. What am I missing?

[HansV]

If you set the Default View property to Continuous Forms, Access will display as many records as fit within the form's window. Of course, if the Detail section is tall, only one record may fit. So for a continuous form to display multiple records, you need to keep the height of the Detail section down.
Another option is to set the Default View property to Datasheet. This will display the form the same way a table or query is displayed.

[matthewR]

Thank you again Hans. I had the detail section too big. I am not used to looking at forms in 2007. Once I reduced the size of the detail section everything showed. Thank you very very much.