Preventing Unwanted Records

[trystanhuwwilliams]

HI,
This is related to a previous thread posted last week - Stop Blank Entries & I would like to thank PGK for all the help & I did finally get it to work & I'm now happier working with the recordset.

A new problem has now arisen. I've got a mail system which staff log in to in order to check the mail which has been forwarded to them. The form calculates whether there are any mail items on the system for each person who logs in. If there is no mail, a message is displayed & the main form is not opened. However if there is mail, the form (based on a parameter query) is opened & displays only the mail for that person. The problem is that when the form is opened it will create an additional record for that person.
I think i'm missing something fairly basic here & I would appreciate any advice,

Thank you,
Trystan

 

[pgk]

Hi,
Where is this extra record created? Is is created in the table that stores the mails? Is there some field that can uniquely identify the extra record created like an AutoNumber field?

Send us more details. Someone will be able to help you. Hope it helps. Let me know what happens.
With regards,
PGK

 

[trystanhuwwilliams]

Hello,
Yes, the record is created in the main table called MAIL LOG & does have a unique autonumber field called LETTER NUMBER

The field for the name of the person is ADDRESSEE & the parameter query that the Mail form is based on, looks for this value in the logging in form. The Mail form opens & displays all the records for that person - but also creates an additional record which contains only the name of that person i.e it fills the ADDRESSEE value with the name of the current user in a new record.

This occurs despite the form being opened in Edit Mode with Allowadditions property set to false.
The Form cannot be read only because the user has to update the COMPLETED field where appropriate.

I'd welcome any suggestions,
Thanks,
Trystan

 

[pgk]

Hi,
Why don't you try this:

Once the user is completed with his mail checking, you can locate the record for that particular user and with all other fields empty. Then delete this record.

Actually I want you to check that the other fields are empty because it is possible that when we are about to run the code to delete the record ( assuming we select record using only user anem and highest AutoNumber), someone may send this user a mail and it will get deleted ( since it will have the highest AutoNumber)

So if we check and ensure that we select the record with the entries balnk for all the other fields, we can delete it safely. Hope it helps. Let me know what happens.
With regards,
PGK

 

[trystanhuwwilliams]

HI,
This is my procedure to delete any 'empty' records which is triggered when the user clicks the MAIN MENU button on the form to exit.
My problem is that is that i can count every record in the recordset but I don't know how to count the amount records which have been filtered, so my TOT1 variable in incorrect.
For example if I have a total of 7 records & 5 of which are addressed to JOHN, then TOT1 should=5 not 7.


Private Sub Main_Menu_Click()
On Error GoTo Err_Main_Menu_Click

Dim stDocName As String
Dim stLinkCriteria As String


Dim dbs As Database
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Mail Log&quot
TOT1 = rst.RecordCount
With rst
.MoveFirst
End With



DoCmd.GoToRecord , , acFirst
For x = 1 To TOT1
S = [Sender].Value

If IsNull(S) Then DoCmd.SetWarnings False: DoCmd.RunCommand acCmdDeleteRecord Else
DoCmd.GoToRecord , , acNext

Next x


dbs.Close
Set dbs = Nothing
Set rst = Nothing

DoCmd.Close

stDocName = "Switchboard"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Main_Menu_Click:
Exit Sub

Err_Main_Menu_Click:
MsgBox Err.Description
Resume Exit_Main_Menu_Click

End Sub


Cheers,
Trystan

 

[pgk]

Hi,
Hwy don't you use a recordset and open that recordset using the filtering that you apply in your filtered form? This will give you the same records that are visible to the user say John. The recordCount property will give you the total no. of records returned. But make sure that you use the rs.MoveLast and then the rs.MoveFirst commands in sequence because if you don't use this sometimes you will get a wrong recor count.

Alternatley, you can use the Do While Not EOF technique to process all records.

rst.MoveFirst
Do While Not rst.EOF
'Code to process deletion of invalid records

rst.MoveNext

Loop


2) You can also try opening a recordset with all invalid records and then delete them.

Set rst=db.OpenRecordSet("Select * from MailLog where sender=' & "" & "' and user='" & "John" & "'",dbOpenDynaset)

'Code to delete all records returned.


Just a few suggestions. Post back if you want something else. Hope it helps. Let me know what happens.
With regards,
PGK