By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,475 Members | 1,530 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,475 IT Pros & Developers. It's quick & easy.

removing incomplete records

P: n/a
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
Jan 22 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Jan 22, 2:22*pm, sparks <jstal...@swbell.netwrote:
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
Why not make the field rewuired in the underlying table then
'incomplete' records cannot be entered at all. No code required.
Jan 22 '08 #2

P: n/a
On Jan 22, 2:22*pm, sparks <jstal...@swbell.netwrote:
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
REQUIRED oops...
Jan 22 '08 #3

P: n/a
On Tue, 22 Jan 2008 19:22:22 GMT, sparks <js******@swbell.netwrote:
>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

Jan 22 '08 #4

P: n/a
On Jan 22, 3:05*pm, sparks <jstal...@swbell.netwrote:
On Tue, 22 Jan 2008 19:22:22 GMT, sparks <jstal...@swbell.netwrote:
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- 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.
Jan 28 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.