Goto Last Record that is not "None"

[infinitx]

Hi,

In the Forms OnOpen Event, I want a textbox (txtICL) to display the last record that is not "None".

How would I go about doing this?

Thanks a lot!

 

[WayneRyan]

In,

Text boxes don't display records. Need more info.

It sounds like you need to use the DLookUp function to retrieve a value
from a record that meets certain criteria.

Wayne

 

[infinitx]

I created a form using Form Wizard, and the form contained one field on it which is a textbox field. This field is linked to a field in the table. I want to filter that textbox to never display "none" records.

Do you need more information?

Thanks!

 

[Mike375]

I created a form using Form Wizard, and the form contained one field on it which is a textbox field. This field is linked to a field in the table. I want to filter that textbox to never display "none" records.

Do you need more information?

Thanks!

By "none" records do you me that the field is empty. If so you can make a very simply query off your table and in the criteria row enter Is Not Null. Then use this query as the data source for your record.

If you mean that you don't want records to display that have the word "none" entered then in the criteria row of the query enter Not Like "none" and again, use the query for your form's data source.

Mike

 

[infinitx]

Mike375,

Thank you! That got the ball rolling.

However, I have another textbox filtering question. Keep in mind these records are not Null. They have "none" as the word in the record.

Here is the code that I am using to filter blank, null, and "none" records in a textbox but for some reason the "none" records still appear.

Not IsNull([ICL 2]) or [ICL 2]="" or [ICL 2] Not Like "None"

Is there something wrong with the code?

 

[Mile-O]

1. Forms don't have fields. They have controls which can be bound to field's in the form's underlying recordset (i.e. its RecordSource)
2. As has been said, a textbox is incapable of displaying a record. They can display the value of one field in one record only.
3. Use the form's OnLoad event. The OnOpen event occurs when the form opens and before the RecordSource is loaded into the form.

Now, that said, have you bound a query the form?

If so:

• Is there any special criteria to this query?
• Have you ensured that it is sorted by a relevant field?

If not:

• Why not?

 

[Mile-O]

Also, just reread. You don't filter from a textbox. You filter in the query that's bound to the form.


For the field (no doubt called ICL 2 )

the criteria should be:

Is Null or "None"

 

[infinitx]

I just created one with the following criteria:

ItemID4
ICL 1 (Not Like "None")


My only concern with this is: Would I be able to disable the criteria through code? Some of the time, I do not want the query to use the criteria.

Is this possible?

Thanks!

P.S. I modified the code but "none" records still appear! What is wrong with it?

Not IsNull([ICL 2]) or [ICL 2]="" or [ICL 2] Like "None"

 

[infinitx]

So instead of 'Not Like "None"' it should be 'Is "None"'?

Thanks!

 

[Mile-O]

Can you strip it down and upload the relevant parts?


Also, the Like operator is useless here - it's used for pattern matching in strings with the use of a wildcard:

i.e Like "No*" Or Like "No?e"

Like "None" has no wildcard, so you'd be best to use the operator specifically for it: = (equals)

= is explicit
Like is not

 

[Mike375]

I don't know how you go with ""

Are you using that for blank fields.

But whatever, you need And Not Like "None"

If you enter in query criteria

"This" Or "That" And Not Like "None" when you reopen the saved query in design view you will find the following:

"This" will be on the first row and

"That" And Not Like "None" will be on the second row

When something is entered on different rows then those entries become an OR

 

[Mike375]

My only concern with this is: Would I be able to disable the criteria through code? Some of the time, I do not want the query to use the criteria.

Is this possible?

A simple way is to have different queries and then change the record source.

With a macro you use the SetValue action

Item [Forms]![MasterForm].[RecordSource]
Expression "MFormPreC"

MForPreC is a query name. I am not sure if in later version of Access the inverted commas are put around thr query name.

Mike

 

[infinitx]

Mile-O-Phile,

Here's the stripped-down version:

http://www.metriks.com/DB.zip

It was too big to upload it to this website.

 

[Mile-O]

A simple way is to have different queries and then change the record source.

With a macro you use the SetValue action

Item [Forms]![MasterForm].[RecordSource]
Expression "MFormPreC"

Macros are evil. Awful, etc.

To change a form's recordsource you just put the one line

Me.RecordSource = "MyQuery"

in whatever event (Code Builder) you wish to change the form's recordsource.

 

[Mile-O]

Now I'm lost...

No offense, but what is that meant to be/do?

It's just four tables (a repeating group at that) with 12,000 records each and one empty field other than an Autonumber field.

What are you trying to do? This is NOT a database.

 

[Mike375]

Macros are evil. Awful, etc.

I like their irritation factor

 

[infinitx]

This is used for the user to enter the Item Classifications that they later will use when they are entering the items to choose the Item Classifications for the items.

P.S. Macros are fun!

 

[Mile-O]

Please, explain

To me, you need a proper structure.

 

[infinitx]

Here is a sample of the classifications:

Level 1: Math

Level 2: Addition Level 3: Simple, Hard

Level 2: Division Level 3: Simple, Hard

There is another form (not included in DB.zip) where the user selects item classifications from 4 combo boxes. These combo boxes are dependent on one another.

If they select Math from Combo Box 1, then only those items that are related to math will appear in Combo Box 2 such as Addition and Division.

So basically, what DB.zip is is a collection of forms that are used to populate the data that will appear in the Combo Boxes.

If you look at Table ICL 2, you will see that this is the table that tells the program what to show in Combo Box 2 depending on what the user chooses from Combo Box 1.

The user can enter a maximum of 30 level 1's.

For each level 1, they can enter 20 level 2's

For each level 2, they can enter 10 level 3's

For each level 3, they can enter 2 level 4's

30 * 20 * 10 *2 = 12,000 and that is what the last number is in each table.


Did I confuse you even more?

 

[Mile-O]

Did I confuse you even more?

Bewildered me even more with your illogical thinking.


I'm finishing work for the day but I'm sure someone will continue on from here.