Filter Customer Details

[adam]

Hi,

I'm getting error message when I'm trying to clear the filter from a command button in my Customers List form.

This happens when I'm using the text box Search Customer.

How may I prevent this?

Any help on this would be kindly appreciated.

Thanks in advance.

[HansV]

You apparently copied a form from another database, but you didn't copy the module that contains the clearBox function that is called in the last step of the macro associated with the cmdShowAll button.

[adam]

Thanks for pointing out that Hans. When the clear Box function is removed from the macro, everything works fine.

By the way,I would like to point out that only the idea was copied from another database but not the form.

I've applied a formula to display the current date & time as data gets added to the tables and corresponding forms. But the date & time is also displayed in the "New" row. Could this be avoided as it does appear on the Customer List Form?

[HansV]

No, if you set the Default Value property of a field, it will be displayed in the "New" row too.

[adam]

So it means there's no alternative for that?

[HansV]

An alternative would be to clear the Default Value properties and to use the Before Insert event of the form to set the date and time.

[adam]

Do you mean that I should write the code as;

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.TimeReg = Now
End Sub

For the time & date

[HansV]

No, in the Before Insert event, unless you want the date and time to be updated each time the record is modified.

[adam]

In my previous reply I did point out that the macro does work better when the clear Box function is removed from the macro. But as you might had assumed the text from the text box search customer does not get cleared.

Following is the code that I'm using behind the module modMapping. But with several attemps the code is failing and I'm getting the previous error message.

What may be the solution for this?

Code: Select all

Option Compare Database
Option Explicit

Function OpenMap(Address, CustomerName, Comments, Country)

    Dim strAddress As String
    strAddress = Nz(Address)
    strAddress = strAddress & IIf(strAddress = "", "", ", ") & Nz(CustomerName)
    strAddress = strAddress & IIf(strAddress = "", "", ", ") & Nz(Comments)
    strAddress = strAddress & IIf(strAddress = "", "", ", ") & Nz(Zip)
    strAddress = strAddress & IIf(strAddress = "", "", ", ") & Nz(Country)
    
    If strAddress = "" Then
        MsgBox "There is no address to map."
    Else
    End If
End Function

[HansV]

Do you still get an error message about the clearBox function?

[adam]

Yeah. I do get error message. as shown in the image

[adam]

And when I click the Ok button I get the following message

[HansV]

So, apparently the macro for the On Click event of cmdShowAll still calls clearBox!

[adam]

Yeah Hans. How could I prevent this?

[HansV]

So you did copy the form without studying the design...
Open the form in design view.
Select the cmdShowAll command button.
Activate the Property Sheet in the Design tab of the ribbon.
Activate the Event tab.
Click the ... button to the right of the On Click event.
You should see 4 lines with actions.

x297.png

The last action is RunCode with argument clearBox().
Select this line and press Delete.
Click the Close button and confirm that you want to save the changes.
The error message should now be gone.

[adam]

I did the same before. it does prevent the error message but does not clear the text box Search Customer.

[HansV]

So why did you mention that you were still getting the error message?

[adam]

Oh! please don't be bored Hans. What I said was when the line you referred is removed I do not get the error message. Instead the text in the Search Customer text box doesn't get cleared when the filter is removed.

[HansV]

You should import the code module that contains the clearBox() function into your own database. The macro will then work with the RunCode line to clear the text box.

[adam]

Well for those of you who might be interested in this thread, here's the solution for what had been asked.

First follow the instructions as mentioned by Hans on this post.

Next add the following code to the "Customers List" form module.

This would finally clear the text box and then clear the data sheet simultaneously.

Code: Select all

Private Sub cmdShowAll_Click()
Me.SearchBox.Value = Null
Me.FilterOn = False
End Sub