Form Search query not limiting results due to null values in table?

[rpeare]

ok I didn't want to check through the properties of your fields but they are not getting evaluated as null so try this on your search button click:

Dim ctl As ControlDim sWhere As String
Dim sSQL As String


For Each ctl In Me.Controls
    If ctl.ControlType = 111 Or ctl.ControlType = 109 Then 'combo box or text box
        If Len(ctl) <> 0 Then
            Select Case ctl.Tag 'I use the TAG property here N = number, T = Text, D = Date
            Case "D"
                sWhere = sWhere & "[" & ctl.Name & "] = #" & ctl & "# AND "
            Case "T"
                sWhere = sWhere & "[" & ctl.Name & "] Like '*" & ctl & "*' AND "
            Case "N"
                sWhere = sWhere & "[" & ctl.Name & "] = " & ctl & " AND "
            End Select
        End If
    End If
Next ctl


'applies a filter if any criteria are filled out
If Len(sWhere) > 0 Then
    DoCmd.OpenReport "CLAcctInfoTableReport", acViewPreview, , Left(sWhere, Len(sWhere) - 4)
Else
    DoCmd.OpenReport "CLAcctInfoTableReport", acViewPreview
End If
Debug.Print sSQL

note I'm checking the length of each field now instead of whether or not it's null, len will give the length of the string minus any hidden characters (carriage returns, line feeds etc)

Secondly you will have to remove the sorting/grouping you have on your report because it is referencing a field that no longer exists on your table.

[THarrison]

Ok. Once I separated out the first line of the Dim statements in your code above like you had before, the search ran and it appears to run correctly. I guess I won't know how well it runs until a lot of data is added to the table. I can't tell you how much I appreciate all of your help with this, rpeare.

I also wasn't aware that I had any grouping/sorting set up on the report. I think I have removed it.

I apologize for my ineptitude with VBA, however, this is my first time working with it. As far as the "Clear Form" button, the code you posted earlier was returning a "Run-Time Error '424' Object Required". When working with VBA, do you always have to have a Dim statement? Is there anything else that should be added to that code to tell it what I am trying to do?

[rpeare]

oops that code should be

Dim ctl

For Each ctl In Me.Controls
    If ctl.Tag = "T" Or ctl.Tag = "N" Or ctl.Tag = "D" Then
        ctl.Value = Null
    End If
Next ctl

assuming you have the D, T and N in the tag property ONLY for fields you want to set to null. I changed to to check for a specific tag in case you use the tag on on any other item.

[THarrison]

That code works but after I clear the form, when I leave or close the form, it creates a blank entry in the table. Is there a way to stop it from creating a blank record?

[rpeare]

if your form is based on the table clacctinfotable, remove that, it should be an unbound form. And all of your fields should be unbound as well.

[THarrison]

Sorry rpeare, I should have been more specific. The clear form code works perfectly fine on my "Search Form". I also incorporated the code into my "Add Info Form", which is a bound form, and that is the form that adds the blank record. Is there not a way to have a bound form and use that code to clear the fields? I am unsure how to use VBA to tell Access how to update the table with new records...

[rpeare]

on a bound form you can't set the value to null or you'll do exactly what you're experiencing, create a record that's all null values. What behavior are you trying to accomplish?

basically undo any changes on a record you're modifying?
don't save the record unless certain fields are filled out?

you can visually 'blank' the records just by going to a new record

docmd.gotorecord ,,acnewrec

or something like that but as soon as you set a value you're going to force a record to be modified or created.

[THarrison]

Well on my "Add Record Form", I was just trying to create a form for users to only add a record. I created two forms, a "Search Form" and an "Add Form" to prevent people from accidentally adding duplicate records for the same company when they are actually trying to search for results.

The bound form was the only way I knew how to add a record to a table. I would ideally like for the add form to be able to add a record to the table or modify an existing record, but that was over my head so I created it the only way I knew how by using the bound form. But obviously that creates the issue of a blank record if someone accidentally enters incorrect information, clears the form and then leaves the form.

[rpeare]

I do not use bound forms so I do not know the best way to 'clear' a bound form other than going to a new record, I would think it's accompanied by some sort of undo command. I'm enclosing another sample of your database with how I use UNBOUND forms to do both data entry and lookups on the same form.

I have put some basic instructions on the form on how to set it up using the globalfunctions module.

Account Database.zip

[THarrison]

Thanks rpeare! I am trying to get more familiar with code and teaching myself the basics to VB. This database will be very helpful in comparing what I am reading vs. what an experienced user does. Thanks again for all of your help.