please help (ms access) programmers!

[d_airlie]

hi all, i really need some help with this one!

i have a database that i'm trying to create in MS Access 97, and i want to be able to get a GOTO function on a form to seek for the next available record with a NULL value in a specific field under a table.

recno |Column1 Column2 Column3 Column4 Column5 Column6
------------------------------------------------------
1 |abdcefg abdcefg abdcefg abdcefg abdcefg abdcefg
2 |abdcefg abdcefg abdcefg abdcefg abdcefg abdcefg
3 |abdcefg abdcefg abdcefg abdcefg abdcefg abdcefg
4 |abdcefg abdcefg
5 |abdcefg abdcefg
6 |abdcefg abdcefg
7 |abdcefg abdcefg
8 |abdcefg abdcefg
9 |abdcefg abdcefg

for example, above is the database table
i want the goto button to look for the next record with a NULL/BLANK value in Column3, in this case it will be record number 4

apparently this needs to be written in VBA code so that it works, does anyone have any advice or code that i can use?

Many thanks to all that try!

[Edited by d_airlie on 01-10-2001 at 09:27 AM]

[paulw]

Why? As a matter of course, you should avoid Nulls if at all possible. It makes coding easier and you do not have to deal with tri-state logic.

Poor understanding of, and dealing with Nulls causes more problems than anything else. You might be better re-defining the question...

If you really need to do this then you could easily create a recordset and loop through until the field is null using Is Null. You could probably also do this with an agggregate query using First but you ould have to be careful about order in that case and you might need a sub-query.

Cheers,

P.

okay try RS.MoveNext

Dim x As String, y As Integer
Dim DB As DataBase
Dim RS As RecordSet

'Set your database and recordsets...
Set DB = OpenDatabase("Path")
Set RS = DB.OpenRecordSet("Table")

RS.MoveLast
RS.MoveFirst 'Needed for record count

Do Until x = "NULL" or y = RS!RecordCount
    x = RS!FieldName
    RS.MoveNext
    y = y + 1
Loop
If y = RS!RecordCount Then
    MsgBox "End of file reached!"
Else
    MsgBox RS!FieldName
End If

hope this helps

~~~~~~~~~~
~~Chenko~~
~~~~~~~~~~