Split Form that Filters

[QuantifyRisk]

I am trying to make a form that resembles a split form. However, I want the boxes at the top to be filters rather than boxes that edit the data (2 combo boxes, 3 type ins). I want the form to show about 5 or so filters and filter the table part as I type (or select). Meaning, I don't wanna type something, hit "Filter" then see if I need to filter some more. At the end, I want to create a button that takes the selcted record from the table and opens it in the original form for ntering data so it can be completed. Is this doable?



I've been trying to get it to work but I cannto seem to get rid of those text editing boxes and just make them filtering fields.

[ranman256]

You cant use form boxes in a query if there's nothing in them..so..
Test all controls for a possible filter then build the sql.
on btnFind_Click()...

dim sSql as string, sWhere as string

if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value
    'remove 1st And
sWhere= mid(sWhere,4)
sSql = "SELECT * FROM tblCompany WHERE " & sWhere
frmResults.Recordsource = sSql

[QuantifyRisk]

Sorry I'm on a 1 month delay but I haven't touched the database since. I've never done SQL before and, thus, your post might as well be in Russian.

[June7]

Do you mean you've never done VBA? VBA and SQL are not the same. VBA can run SQL statements. Access uses SQL to build query objects. You do build queries in Access? Switch the query builder to SQL View to see the statement. That's how I learned a lot of SQL.

Also, go to http://www.w3schools.com/sql/default.asp

Emulating a split form is not easy, that's why Access introduced split form with 2007.

Can have 'filter' boxes in the header of a split form.

[QuantifyRisk]

I've done some VBA, but it's not something I would say I could write on my own. SQL I have never worked with.
I'm trying to get this to be as simple as possible for the user where they can search for a record in a split form, select it, and load it in the main form to edit the data.

[June7]

Did you try this with the Split form object?

[QuantifyRisk]

I put in this:

Dim sSql As String, sWhere As String
If Not IsNull(Text0) Then sWhere = sWhere & " and [DateEntered]='" & Text0 & "'"
If Not IsNull(Text4) Then sWhere = sWhere & " and [AgencyName]='" & Text4 & "'"
If Not IsNull(Text12) Then sWhere = sWhere & " and [Contact]=" & chkContact.Value & "'"
If Not IsNull(Text20) Then sWhere = sWhere & " and [AgencyType]='" & Text20.Value & "'"
If Not IsNull(Text28) Then sWhere = sWhere & " and [LicensingRep]=" & Text28.Value
'remove 1st And
sWhere = Mid(sWhere, 4)
sSql = "SELECT * FROM NewAgentChecklist WHERE " & sWhere
frmResults.RecordSource = sSql

And it had a problem with the last line. "Object required" error.

[June7]

I prefer to set the form Filter and FilterOn properties. Review http://allenbrowne.com/ser-62code.html

[QuantifyRisk]

Would it be better to do something like this:
http://www.access-programmers.co.uk/...d.php?t=188663

I'm trying it now to see if I can do multiple text boxes to search for things.

[June7]

That approach shows records in a listbox. That is an option but if you just want to filter the form, look at the link I posted.

[QuantifyRisk]

Ok. I'm using that link you sent earlier. Sorry I missed it the first read through.

It works great... except for my number field

I changed it to:
If Not IsNull(Me.AgencyCode) Then
strWhere = strWhere & "([AgencyCode] = " & Me.AgencyCode & ") AND "
End If

But it never returns a value.

[June7]

It should.

Have you step debugged?

[QuantifyRisk]

I was looking at the wrong number... pathetic. Thanks everyone for your help.