Command button now causes parameter dialogue box when form loads

[humtake]

Hello all,



I'm making a very simple database that includes two tables and 1 form. The form has a search button at the top that I created following this tutorial: https://www.youtube.com/watch?v=wCPs4aE5I2w
The search button just returns the value of each field to the text box in the form. To sum up the video, a button is created and a macro is assigned to the OnClick event; the macro code is:

ApplyFilter
Filter Name 
Where Condition = [IPAddress] Like "*" & [Forms]![qry_findipaddress]![txt_findipaddress0] & "*"
Control Name

The query name is qry_findipaddress
The text box is named txt_findipaddress0

The problem is, when I put in a value in the search text box and click the search button, I get an "Enter Parameter Value" dialogue box that has the query in it "Forms!qry_findipaddress!txt_findipaddress0". I then have to enter in the value I just entered into the text box and it works. If I leave the dialogue box blank, it just loads the form with all data. Is there a way to stop that dialogue box from appearing and have the form do the search just by clicking the Search button?

Additionally, that same dialogue box appears when I load the form most of the time. There is nothing in the OnLoad events of the form so I am very confused.

Thanks all!

[ranman256]

you dont filter queries that way.
instead, open the form showing all records.
then filter the records using the txt_findipaddress0 ON THE FORM (not the query)

sub

txt_findipaddress0_afterupdate
if IsNull(me.txt_findipaddress0) then
   me.filterOn = false
else
  me.filter = "[IPAddress] Like "*" & [Forms]![frmMyForm]![txt_findipaddress0] & "*"
   me.filterOn = true
endif
end sub

[aytee111]

Access does not recognize the field name, that field does not exist. The form name you are using is the same name as the query - is this right?

[humtake]

ranman,
Ok, I kind of see what you are referring to. I changed the qry to the frm so it says [IPAddress] Like "*" & [Forms]![frm_findipaddress1]![txt_findipaddress0] & "*"

That got rid of the dialogue box coming up when I load the form. Where do I put the code you provided, is that in the OnLoad parameter of the form? I'm not sure where just 'sub' should go...

aytee,
The form name is frm_findipaddress and the query name is qry_findipaddress...so they are the same except for the prefix. The only field name in the macro is IPAddress, which is a field in the table (although I am confused as to why I don't put the table name in the query, but since I'm only using one table I figured it's not worth the trouble asking :-))

Thanks!!

[humtake]

I added your code to the Click so it looks like the below:

Private Sub cmd_findipaddress0_Click()
    txt_findipaddress0_afterupdate
    If IsNull(Me.txt_findipaddress0) Then
        Me.FilterOn = False
    Else
        Me.Filter = "[IPAddress] Like " * " & [Forms]![frm_findipaddress1]![txt_findipaddress0] & " * ""
        Me.FilterOn = True
    End If
End Sub

But it gives me a "Compile Error - Sub or Function not defined" and it highlights the Private Sub cmd_findipaddress0_Click() line. When I try to add this to the AfterUpdate event for the cmd button, it gives me a lot of errors and I'm sure that's not what I'm supposed to do anyways. I'm confused as to where this code goes...please forgive my terrible Access skills.

Also, just to give more details, the text box I use to search and the search button are both in the header of the form...not sure if that makes a difference. Let me know if it's easier to just put them into the main form body.

[ranman256]

is your command button named: cmd_findipaddress0?

normally I put it in the filter text box AFTERUPDATE event.,
yours is
txt_findipaddress0_afterupdate

[humtake]

Yes, the search button is named cmd_findipaddress0.

So, what I have now is a text box that has:

Private Sub txt_findipaddress0_AfterUpdate()
    If IsNull(Me.txt_findipaddress0) Then
        Me.FilterOn = False
    Else
        Me.Filter = "[IPAddress] Like " * " & [Forms]![frm_findipaddress1]![txt_findipaddress0] & " * ""
        Me.FilterOn = True
    End If
End Sub

and a search button that has (I converted the macro to code since I'm more familiar with code):

Private Sub cmd_findipaddress0_Click()
On Error GoTo cmd_findipaddress0_Click_Err

    DoCmd.ApplyFilter "", "[IPAddress] Like ""*"" & [Forms]![frm_findipaddress1]![txt_findipaddress0] & ""*""", ""

cmd_findipaddress0_Click_Exit:
    Exit Sub

cmd_findipaddress0_Click_Err:
    MsgBox Error$
    Resume cmd_findipaddress0_Click_Exit

End Sub

When I enter in a couple octets of an IP address in txt_findipaddress0 and click Search, I get an error that says "Run-time error '13': Type mismatch". When I click debug, it highlights the Me.Filter = "[IPAddress] Like "*"... line. From past errors, this always dealt with the way the field is defined, in this case the IPAddress field is Short Text. Not sure if that has anything to do with it but figured I'd put it here.

Thank you.

[aytee111]

You don't need two search fields - either the text box or the command button, they both accomplish the same thing. And the code would be exactly the same for both.

Me.Filter = "[IPAddress] Like '*" & Me![txt_findipaddress0] & "*'"

[ranman256]

use single quotes on the stars:
"[IPAddress] Like '*' & [Forms]![frm_findipaddress1]![txt_findipaddress0] & '*'"

[humtake]

Awesome, thanks. I did what aytee said and am just using the text box...I never knew it could perform a function like that; I've always depended on a button with a text box value so that's cool to learn.

I changed the quotes to single quotes and it's working...until I need a new search. I understand this is a filter so the form has to be refreshed to apply a new filter. I anticipated this and added a button to refresh the form, which works fine. Here is the code:

Private Sub cmd_newsearch_Click()
On Error GoTo cmd_newsearch_Click_Err

    DoCmd.RunCommand acCmdRemoveFilterSort
    
    ' Clear text box
    txt_findipaddress0.Value = ""

cmd_newsearch_Click_Exit:
    Exit Sub

cmd_newsearch_Click_Err:
    MsgBox Error$
    Resume cmd_newsearch_Click_Exit

End Sub

It blanks out txt_findipaddress0 (I will refer to this as "text box" now) that is doing the filtering and removes the filter sort. However, it never really seems to refresh the filter. Let me walk you through this the best I can:
Two entries in table: 10.0.16.0 and 10.0.17.0
I put in 17 in "text box" and hit enter, it then filters correctly and perfectly and shows the 10.0.17 record (thanks to you guys!)
I click the button to refresh the filter (code above). This brings back both records (using a split form) and populates the form with the first record in the list (as is expected).
I put in 16 in the "text box" and hit enter but it returns the same results as when I did my first search with 17. The number 17 doesn't appear in the 10.0.16 record anywhere but it still only shows the 10.0.17 record and removes the 10.0.16 record. It seems to always produce the results of the filter I used prior to clicking the button.

In troubleshooting, I added the txt_findipaddress0.Value="" to blank it out in case that was causing the issue but it wasn't. If I don't have that line, the "text box" just keeps whatever value I entered in before but it still acts the same way whether that line exists or not. I've also tried adding me.txt_findipaddress0 lines to the button and changing some of the values to see if that works but nothing does. I promise I do actually do some troubleshooting and testing prior to posting on here :-)

Any reason for this behavior? I can't figure out why the form/filter is keeping the old value...

[Micron]

research concatenation - it's the root of your issue. By the time I got to post 8 I wondered why that hadn't been noticed. There's a few basic rules which come into play for virtually every case:
- any time a variable or form/report control reference is involved, it must lie on either side of the beginning and ending parts
- each of the parts must begin and end with double quotes
- the variables often need to be surrounded by the proper delimiters, according to the data type they hold - ' for text, # for dates, nothing for numbers. The tricky part is understanding where to put them.
That's only the basics.

As for the latest problem, you are entering an empty string ("") into the control. That is not the same as null, which is "unknown". Depending on the design of the form, applying a filter based on a control that contains an empty string should load the first record in the table since the filter is, actually, invalid - unless you have fields that accept and contain empty strings.

[humtake]

Thank you Micron. I couldn't get anything to work. I only tried this filtering mechanism because of all the problems I had with Searches in another database I created. It seems MS will never make an easy way to handle easy logic like clear a filter value or clearing a form after a search to begin fresh. They sure like to have it work through ribbon commands but making it work with code is useless for people with simple database purposes. I just went ahead and coded the button to close the form and reopen it. It won't have more than a couple thousand records and I don't want to waste more time trying to figure out something that should be a lot more intuitive.

I appreciate everyone's help!

[Micron]

Working with form filters is fairly easy - but like anything else, when you know how. Do a compact/repair and post a zipped copy of your db if you want someone to take a look at it.