Setting "All" as a default value for a Text box

[mo9rissat]

Hello,

I put a text box in an Access form where the user must enter a name, and click on the Validate button. then it appears in a new form all the information corresponding to the record having that name. I want to put All as default value for the text box. So when the user clicks OK without entering a value, all names are displayed.

Can you help me please?

 

[pr2-eugin]

On Click of the button.. What is the code you have placed? Is it opening a Query as the recordset of the new Form or you opening a Form with a WHERE condition?

 

[mo9rissat]

On Click of the button.. What is the code you have placed? Is it opening a Query as the recordset of the new Form or you opening a Form with a WHERE condition?

Yes i'm opening a query where I have only the field of Names

 

[pr2-eugin]

Okay so use a

Like Nz([Forms]![FormName]![FieldName], "*")

in your Query's criteria. That way it will pull out all records if they do not enter a value.

A word of caution : using Like might not give you the desired result you are looking for. As far as I am aware like is the operator that is used to pull ALL records.

 

[vbaInet]

Another alternative:

[Forms]![FormName]![FieldName] Or [Forms]![FormName]![FieldName] Is Null

... all on one line.

Or do you mean when the textbox reads "All" then it should display all records? If that's the case, I would use "-All-" instead of "All", but that's up to you.

LIKE IIF([Forms]![FormName]![FieldName] = "-All-", "*", [Forms]![FormName]![FieldName])

 

[mo9rissat]

Okay so use a

Like Nz([Forms]![FormName]![FieldName], "*")

Thanks.

Yes it works, but i think that it eliminates Null records bcause of the Nz. I tried to use it without Nz, but it gives errors.

 

[mo9rissat]

Thank you very much.
This code

[Forms]![FormName]![FieldName] Or [Forms]![FormName]![FieldName] Is Null

Works perfectly. Thank you.

Or do you mean when the textbox reads "All" then it should display all records? If that's the case, I would use "-All-" instead of "All", but that's up to you.

LIKE IIF([Forms]![FormName]![FieldName] = "-All-", "*", [Forms]![FormName]![FieldName])

No, I just meant what you gave me in the first code. But it's good if i need to make my application more beautiful .

But I have another control on my form, listbox that allows me to choose records, I want to add in the top of the listbox the value "-all-" and when I click on I want it to show me all the records. Is this is based on the same principle of the second code???

 

[vbaInet]

In that case you will need the second piece of code.

Then you need to change the Row Source of the listbox using this:
http://access.mvps.org/access/forms/frm0043.htm

The idea is creating a dummy record and appending it to your original list of records using the UNION statement.

 

[mo9rissat]

Hello,

And if i want to show the "-All-" value in all fields using the union statement, what i'm going to add to this:

SELECT CustomerID, CompanyName FROM Customers UNION Select Null as AllChoice , "(All)" From Customers ORDER BY CustomerID;

??

 

[vbaInet]

If you're talking about the code for this, then remember my post above -->

Or do you mean when the textbox reads "All" then it should display all records? If that's the case, I would use "-All-" instead of "All", but that's up to you.

LIKE IIF([Forms]![FormName]![FieldName] = "-All-", "*", [Forms]![FormName]![FieldName])

Otherwise, please explain what your intentions are.

 

[mo9rissat]

Hello,
No, What I want now is to display the value-All- in the top of my columns.
I know how to do it when I have a single column in a table. This can be done using an "Union" query.
For example if i have this:
StudentName
-------------
John
Lisa
Zac

And I apply this quey:

SELECT StudentName FROM STUDENT
UNION SELECT "-All-" FROM STUDENT;

I'll have this:
StudentName
-------------
-All-
John
Lisa
Zac

But how do I make a request to add the value of several header fields?

 

[vbaInet]

You mean this:

SELECT StudentName, Age, Gender
FROM Student
UNION
SELECT "-All-", Null, Null
FROM Student;

StudentName, Age, Gender
----------------------------
-All-, ,
John, 12, M
Lisa, 15, F
Zaz, 13, M