Finding first Null Record...

  • Thread starter Thread starter John
  • Start date Start date
J

John

I need help opening a form that is automaticall parked on the first null
value of a select group of fields. I have tried:

DoCmd.OpenForm "frmActMapUpDte", acNormal, , "IsNull(JVID)", acFormEdit,
acWindowNormal

This works, but it FILTERS the record set to just JVID is null. I would
like to just Find the first occurance of JVID that is null, allow the user to
update the value, use the next or previous control, then click a "Find Next"
command button on the form to go to the next null value.

To complicate matters, there are several null values I need to find. I need
to find records that are:
IsNull(JVID) or IsNull(OwnIni) or IsNull(Srt1) or IsNull(Srt2) or
IsNull(Srt3) or IsNull(Srt4)

Any help would be greatly appreciated.
 
John said:
I need help opening a form that is automaticall parked on the first null
value of a select group of fields. I have tried:

DoCmd.OpenForm "frmActMapUpDte", acNormal, , "IsNull(JVID)", acFormEdit,
acWindowNormal

This works, but it FILTERS the record set to just JVID is null. I would
like to just Find the first occurance of JVID that is null, allow the user to
update the value, use the next or previous control, then click a "Find Next"
command button on the form to go to the next null value.

To complicate matters, there are several null values I need to find. I need
to find records that are:
IsNull(JVID) or IsNull(OwnIni) or IsNull(Srt1) or IsNull(Srt2) or
IsNull(Srt3) or IsNull(Srt4)
Click to expand...


Remove the WhereCondition from the OpenForm line so you can
retrieve all the records.

Then add the FindNext button and use this kind of code in
it's Click event procedure:

With Me.RecordsetClone
.FindNext "JVID Is Null Or OwnIni Is Null " _
& "Or Srt1 Is Null Or Srt2 Is Null Or " _
& " Or Srt3 Is Null"
If Not .NoMatch Then
Me.Boolmark = .Bookmark
Else
Beep
End If
End With

If you always want the form to open to the first record with
Null in any of those fields, then call the procedure from
the form's Load event using either:

Call cmdFindNext()
or just:
cmdFindNext
 
Mashall,

I get the following error:

Run-time error '3070':

The Microsoft Jet database engine does not recognize 'CstOwnIni' as a
valid field name or expression.

The field name is correct (I abbreviated in my original post). When I
remove "... or Is Null CstOwnIni..." from the statement, I get the same error
only for the Srt1 field. It dosn't seem to have a problem with the first
field (JVID). When I reverse the order (put CstOwnIni first and JVID
second), I get the same error. If I take CstOwnIni out all together, it
gives me the same error on the Srt1...4 fields.

Any suggestions?
 
I caught that typo when I first entered the code. This is something else...
I even tried putting ' around the field names, but then it just beeps and
doesn't find anythng. Would putting [] around the field names help?
 
John said:
I caught that typo when I first entered the code. This is something else...
I even tried putting ' around the field names, but then it just beeps and
doesn't find anythng. Would putting [] around the field names help?
Click to expand...


THere's also an extra OR in what I posted.

Make sure you are using the names of the fields in the
form's record source table/query, not the names of their
bound controls.
 
I found the problem! In the query qryActMapUpdte I had renamed some of the
field names like - Craft Sort: [Srt1], Cost Owner's Initials: [CstOwnIni],
etc.

So technically I guess the fields were not there! Everything is working
good now. Thanks for the help Marshall..

--
Thanks - John


Marshall Barton said:
John said:
I caught that typo when I first entered the code. This is something else...
I even tried putting ' around the field names, but then it just beeps and
doesn't find anythng. Would putting [] around the field names help?
Click to expand...


THere's also an extra OR in what I posted.

Make sure you are using the names of the fields in the
form's record source table/query, not the names of their
bound controls.
Click to expand...
 
Damon said:
John,
sometimes you get an error msg that is lying to you about the reason
it stopped the code. If you copied Marshal's code as is,
then
Me.Boolmark = .Bookmark
has a spelling error. it should be me.bookmark= .bookmark

Damon
Click to expand...

I recently had coded things and queries stop working for me except in an
immediate window. The error message was no help.

The problem was in code that I no longer used but had failed to remove.
Recompiling brought up all the real errors and getting rid of the bad code
"over there" fixed everything.
Referred pain.
 
Back
Top