create smart "find" button

[avivit]

How can I create a button to point at empty (certain) field in each record (micosoft access 2000)?
I'd like it to point the record in which a specific field is empty, and than by clicking "next" or something, to move on and show the next empty.
It's like using the "find" button, but to know in advance the field to search in (I guess I can set the focus once clicking that button), and the value "empty".
btw - I can't search for empty, in the regular "find" box. "" is not working there.

Thanks

 

[RickSpr]

If you're using a form, there's a simple built-in way to do this. Choose Records>Filter>Filter By Form from the menu, and then type the word null in the field that has empty values. Next choose Records>Apply Filter/Sort from the menu, and your form now shows you all the records that have that field empty (and none of the other records). When you're done browsing, choose Records>Remove Filter/Sort from the menu, and you're back to the full set of records.

If you're using a table, you can still use filters, but the menus might be laid out a little bit different. I don't have Access 2000 where I am right now, so I can't check.

Note that "" is not the same thing as Null. So-called empty fields almost always are Null, rather than having empty string values. Rick Sprague

 

[Aivars]

Hi!

Try it way:

dim blnFirstSearch as boolean
dim rst as recordset
dim strCriteria as string

private sub Form_Load()
set rst=me.recordsetclone
strCriteria = "CheckField is null or trim(CheckField)=''"
end sub

private sub cmdFind_Click()
if blnFirstSearch =false then
rst.findfirst strCriteria
blnFirstSearch =true
else
rst.findnext strCriteria
end if
if not rst.nomatch then
me.bookmark=rst.bookmark
else
msgbox "No empty .... more..."
end if
end sub

Aivars

 

[avivit]

Thanks RickSpr. I tried it and it's great to know that (Eexcept the "RECORDS->Apply Filter/Sort should be: FILTER->Apply Filter/Sort. No doubt it was a typing mistake only.)

Is it possible to "record" a macro? Like in excel? Instead of writing code etc.?

Aivars thanks much, I'll check it out now.

 

[avivit]

Aivars, When pressing the button that supposes to activate the "find", I get an error msg: "Run-time error: 424".
It points to the line: "rst.findfirst strCriteria".
What shell I do?

 

[Aivars]

Avivit!

I don't understand why codes don't work. But - anew and in succession:

1. Check References! It's necessary that you have reference to DAO library.
2. Check your form's data source. It's correct?
3. Paste on top of form's module declare strings

Option Compare Database

Dim blnFirstSearch As Boolean
Dim rst As Recordset
Dim strCriteria As String

4. Paste On procedure Sub Form_Load() strings.

Set rst = Me.RecordsetClone
strCriteria = "CheckField is null or trim(CheckField)=''" 'if field's type is Text or
strCriteria = "CheckField is null" 'if other field's type

5. Replace word CheckField to field's name what you want to find.

6. Paste on procedure On Click of your command button for finding empty fields:

If blnFirstSearch = False Then
rst.MoveFirst 'You can create command button for new search. In such case write string blnFirstSearch = False on procedure On Click of this cmd button.
rst.FindFirst strCriteria
blnFirstSearch = True
Else
rst.FindNext strCriteria
End If
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Else
MsgBox "No empty .... more..." 'Your message if it's needed
End If

7. Open form and try to push command button! Verify result!

Aivars

 

[avivit]

Thanks. I'm still having problem. Not sure u still have the patience to help, but..I'll try anyway...:

I have no idea what u mean by "reference to DAO library". How do I make that?

Now I get the msg: "Method or data member not found", pointing to "rst.FindFirst strCriteria" line.

I checked the error meaning and found the following but do not underatnd what to do with it:
"Use one of the following two methods to work around this behavior.

Method 1
Store the procedure in a standard module rather than in a class module. This makes the procedure directly accessible from other databases but requires that you convert your class module procedures to standard module procedures.
Method 2
Create a function in a standard module in the database where the class module is defined. This function calls the procedure from the class module. Then, you call this function from a standard module of another database. This allows you to leave your class module procedures as they are, but requires that you write a standard procedure for each class module procedure that you want to run. "

 

[avivit]

I think I have problem with that "DAO" because I get the msg:
"activeX component can't create object" when I try to use the wizard, wjile creating a button through the toolbox in access 2000. How can I fix it?
My access is a mess lately. The help file also causes probelm.

 

[Aivars]

Hi again!

Open VBA window, push <Tools>;<References...> on menu bar, find <Microsoft DAO 3.6 Object library>, select check box before text True, change position with arrays to 3rd or 4th (after <Ole Automation> (if it exist on list), push OK.
If <Microsoft DAO 3.6 Object library> don't exist on list then you may find file dao360.dll and make reference (<Browse>).

Good luck!
Aivars

 

[RickSpr]

You're right, it's Filter>Apply Filter in Access 2000. That's one of the few differences from Access 97 that comes up on Tek-Tips.

If you like the way the filter works, but you need to do it in code, you should look at the form .Filter and .FilterOn properties, and the .ApplyFilter and .RemoveFilter methods. You can control the whole process of creating, applying, and removing filters from code. Rick Sprague