Stop Blank Entries 1

[trystanhuwwilliams]

Hi,
I wonder if anyone can help me? I've got a Mail Logging System running over a network. Mail is logged by admin then
technical staff are required to access the system via a password which opens a form filtered so that only mailed addressed specifically to them is displayed. They can't change what's been logged only update a field to say that they've dealt with the letter. However if a person tries to access their mail & there isn't anything on the system for them on that day, an appropriate message is displayed and the form is closed, BUT a blank entry is created. I've tried various approaches, but nothing has worked so far,
Thank you in anticipation,
Trystan

 

[pgk]

Hi,
Just before you open the form and display the mail to the users, open the database and check if there are any mails for the user. If not, just display a message saying 'No Mail' and close the recordset. Otherwise, open this filtered form and then close the recordset.

Eg. Assuming an user with id fb123 is attempting to view his mails for the day. Then you could run a check using that id and current date. If recordset record count>0 then, open form. Else message.

I hope I ahve got your question right. If not, please post a more elaborate message so that soemone will be able to help.

Let me know what happens.
With regards,
PGK

 

[trystanhuwwilliams]

Hi,
Yes, this sounds like the appropriate route to take. What I've been doing is opening the form, then testing whether or there's mail on the system, using code in the ON LOAD Event of the form. However I'm not sure how to write the code which will test the criteria before the form is opened.
I would be grateful if you could show me the procedure:
I want 2 criteria filtered - the Name of the person & All Mail for that person which hasn't been actioned. The 2 fields are Name (Text)
Actioned?(Y/N)
Thank you for your assistance,
Trystan

 

[pgk]

Hi,
I am using DAO to access the database. I further assume that there is some button ( called GetMail ) on the form, clicking on which gets the user his mail. This button is clicked after the user has entered his id in a text box called txtLoginId and password in a box called txtPassword.

Private Sub GetMail_Click()

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = CurrentDB
Set rs= db.OpenRecordset(&quot;Select * from mails where name='&quot; & Me.txtLoginId & &quot;' and action <>-1&quot;,dbOpenDynaset)

If rs.RecordCount > 0 then
'Code for opening the filtered you are currently using
else
MsgBox &quot;Sorry. No new mails for you.&quot;
End If

rs.Close
db.Close
Set rs=Nothing
Set db=Nothing

End Sub

Of course, this won't suit you as such. But you will get the idea and modify it to suit your needs. Also check the
true condition for the Yes/No field. I have assumed it to be -1 for true.

Post again if you need any assistance.
With regards,
PGK

 

[trystanhuwwilliams]

PGK,
Thank u ever so much for all your help I do appreciate it. I've had little experience of working with the Recordset, so u'll have to forgive my ignorance.
I'm still encountering problems... My security form containing the staff names & password text boxes has a button which they press to access their mail - this button opens another form which is attached to a parameter query. The criteria for the query is the name on the security form.
Ok, so my problem is that I can't seem to open the recordset for this query, only the table. However I am making progress learning about the recordset, but I would appreciate any pointers,
Thank you very much.

 

[pgk]

Hi,

You are using the name on the security form as the user id and I assume the button on the security form that the user clicks to get his mail is called GetMail. I furhter assume that you are storing the current use name in a variable called loginName.

Put the following in the click event of the GetMail button.

Private Sub GetMail_Click()

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = CurrentDB
Set rs= db.OpenRecordset(&quot;Select * from mails where name='&quot; & loginName & &quot;' and action <>-1&quot;,dbOpenDynaset)

If rs.RecordCount > 0 then
'Code for opening the filtered form you are currently using
else
MsgBox &quot;Sorry. No new mails for you.&quot;
End If

rs.Close
db.Close
Set rs=Nothing
Set db=Nothing

End Sub

Executing this code will open the recordset ( i.e. all the records satisfying the conditions - user name and action = No ). Now you use the RecordCount property of the recordset to determine if any records satisifed our conditions. If recordCount > 0, then we open the filtered form that you are currently using. Otherwise we just display a message.

Always be sure to close the recordset as well as the database.

Very Important : Set a refernce to the Microsoft DAO 3.6 Object Library or 3.51 Object Library.

The refernce can be set by clicking on the Tools button in the VB Code Editor and clicking on References in the drop down box.

Post if you want something else.
Have a nice day.
With regards,
PGK