Finding first Null Record...
in [Forms]
|
Prev: dlookup
Next: ListBox Question
From: John on 5 Jul 2008 14:31 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. -- Thanks - John
From: Marshall Barton on 5 Jul 2008 14:56 John wrote: >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) 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 -- Marsh MVP [MS Access]
From: John on 5 Jul 2008 15:55 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? -- Thanks - John "Marshall Barton" wrote: > John wrote: > > >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) > > > 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 > > -- > Marsh > MVP [MS Access] >
From: Damon Heron on 5 Jul 2008 18:29 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 "John" <John(a)discussions.microsoft.com> wrote in message news:ABEFA649-BE51-4189-A6B1-0538EBB08C6F(a)microsoft.com... > 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? > > > -- > Thanks - John > > > "Marshall Barton" wrote: > >> John wrote: >> >> >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) >> >> >> 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 >> >> -- >> Marsh >> MVP [MS Access] >>
From: John on 5 Jul 2008 20:23
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? -- Thanks - John "Damon Heron" wrote: > 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 > > > "John" <John(a)discussions.microsoft.com> wrote in message > news:ABEFA649-BE51-4189-A6B1-0538EBB08C6F(a)microsoft.com... > > 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? > > > > > > -- > > Thanks - John > > > > > > "Marshall Barton" wrote: > > > >> John wrote: > >> > >> >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) > >> > >> > >> 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 > >> > >> -- > >> Marsh > >> MVP [MS Access] > >> > > > |