removing incomplete records

am 22.01.2008 20:22:22 von Sparks

Some of the people are requiring a personID to be in the records and
of course formatted as 001 002....etc so its a text field.
Now they are also skipping this field and putting in stuff and just
quitting out...leaving junk records in the tables.

at first I tried this

Private Sub Form_Unload(Cancel As Integer)

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("tblRegistrationMain",
dbOpenDynaset)
rst.FindFirst "[PrimarySector] = null"
Do While Not rst.NoMatch
rst.Delete
rst.FindNext "[PrimarySector] = null"
Loop

rst.close
End Sub

I thought this should work but again its a darn text field..

what do I need to do to find an empty text field?

thanks for any pointers on this

Re: removing incomplete records

am 22.01.2008 20:31:26 von frogsteaks

On Jan 22, 2:22=A0pm, sparks wrote:
> Some of the people are requiring a personID to be in the records and
> of course formatted as 001 002....etc so its a text field.
> Now they are also skipping this field and putting in stuff and just
> quitting out...leaving junk records in the tables.
>
> at first =A0I tried this
>
> Private Sub Form_Unload(Cancel As Integer)
>
> Dim rst As Recordset
> =A0Set rst =3D CurrentDb.OpenRecordset("tblRegistrationMain",
> dbOpenDynaset)
> =A0 =A0 =A0 =A0 rst.FindFirst "[PrimarySector] =3D null"
> =A0 =A0 =A0 =A0 Do While Not rst.NoMatch
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0rst.Delete
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0rst.FindNext "[PrimarySector] =3D null"
> =A0 =A0 =A0 =A0 Loop
>
> rst.close
> End Sub
>
> I thought this should work but again its a darn text field..
>
> what do I need to do to find an empty text field?
>
> thanks for any pointers on this

Why not make the field rewuired in the underlying table then
'incomplete' records cannot be entered at all. No code required.

Re: removing incomplete records

am 22.01.2008 20:31:51 von frogsteaks

Re: removing incomplete records

am 22.01.2008 21:05:47 von Sparks

On Tue, 22 Jan 2008 19:22:22 GMT, sparks wrote:

>Some of the people are requiring a personID to be in the records and
>of course formatted as 001 002....etc so its a text field.
>Now they are also skipping this field and putting in stuff and just
>quitting out...leaving junk records in the tables.
>
>at first I tried this
>
>
>Private Sub Form_Unload(Cancel As Integer)
>
>Dim rst As Recordset
> Set rst = CurrentDb.OpenRecordset("tblRegistrationMain",
>dbOpenDynaset)
> rst.FindFirst "[PrimarySector] = null"
> Do While Not rst.NoMatch
> rst.Delete
> rst.FindNext "[PrimarySector] = null"
> Loop
>
>rst.close
>End Sub
>
>I thought this should work but again its a darn text field..
>
>what do I need to do to find an empty text field?
>
>thanks for any pointers on this
================================================
I hope that something like this will work.
your idea is sound but they won't get the feedback on what and why.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim response As Integer
Dim rst As Recordset

SaveIt = True
If Dirty Then
Cancel = False
If IsNull(PrimarySector) Then
response = MsgBox("Cannot save a record with no PrimarySector" &
vbCr & _
"Do you want to delete this record?", vbYesNoCancel)
Select Case response
Case vbNo
Cancel = True
PrimarySector.SetFocus
Case vbYes
SaveIt = False
Case vbCancel
Cancel = True
PrimarySector.SetFocus
End Select
End If
End If
End Sub

I hope this does it.
please understand that these people will argue with the moon. IF
somehow their record is not there or they think they did it right and
we don't tell them in a polite way they might have made a mistake then
we are a holes..
========
========
"tell them in a polite way they might have made a mistake "
this is pushing it

Re: removing incomplete records

am 28.01.2008 17:52:47 von frogsteaks

On Jan 22, 3:05=A0pm, sparks wrote:
> On Tue, 22 Jan 2008 19:22:22 GMT, sparks wrote:
> >Some of the people are requiring a personID to be in the records and
> >of course formatted as 001 002....etc so its a text field.
> >Now they are also skipping this field and putting in stuff and just
> >quitting out...leaving junk records in the tables.
>
> >at first =A0I tried this
>
> >Private Sub Form_Unload(Cancel As Integer)
>
> >Dim rst As Recordset
> > Set rst =3D CurrentDb.OpenRecordset("tblRegistrationMain",
> >dbOpenDynaset)
> > =A0 =A0 =A0 =A0rst.FindFirst "[PrimarySector] =3D null"
> > =A0 =A0 =A0 =A0Do While Not rst.NoMatch
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 rst.Delete
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 rst.FindNext "[PrimarySector] =3D null"
> > =A0 =A0 =A0 =A0Loop
>
> >rst.close
> >End Sub
>
> >I thought this should work but again its a darn text field..
>
> >what do I need to do to find an empty text field?
>
> >thanks for any pointers on this
>
> ==================== =====
==================== ====
> I hope that something like this will work.
> your idea is sound but they won't get the feedback on what and why.
>
> Private Sub Form_BeforeUpdate(Cancel As Integer)
>
> =A0 Dim response As Integer
> =A0 Dim rst As Recordset
>
> =A0 SaveIt =3D True
> =A0 If Dirty Then
> =A0 =A0 Cancel =3D False
> =A0 =A0 If IsNull(PrimarySector) Then
> =A0 =A0 =A0 response =3D MsgBox("Cannot save a record with no PrimarySecto=
r" &
> vbCr & _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0"Do you want to delete this record?", v=
bYesNoCancel)
> =A0 =A0 =A0 Select Case response
> =A0 =A0 =A0 =A0 Case vbNo
> =A0 =A0 =A0 =A0 =A0 Cancel =3D True
> =A0 =A0 =A0 =A0 =A0 PrimarySector.SetFocus
> =A0 =A0 =A0 =A0 Case vbYes
> =A0 =A0 =A0 =A0 =A0 SaveIt =3D False
> =A0 =A0 =A0 =A0 Case vbCancel
> =A0 =A0 =A0 =A0 =A0 Cancel =3D True
> =A0 =A0 =A0 =A0 =A0 PrimarySector.SetFocus
> =A0 =A0 =A0 End Select
> =A0 =A0 End If
> =A0 End If
> End Sub
>
> I hope this does it.
> please understand that these people will argue with the moon. IF
> somehow their record is not there or they think they did it right and
> we don't tell them in a polite way they might have made a mistake then
> we are a holes..
> ========
> ========
> "tell them in a polite way they might have made a mistake "
> this is pushing it- Hide quoted text -
>
> - Show quoted text -

They will get feedback if you code correctly to give them feedback.
Many ways to do this as well such as on the before insert event in
your form that is used to populate the table.