Balloon tooltip popup when new record is added

Hi there,

I have managed to get code to utilise the balloon tooltip function from MS Access which works great with no problems. What I am struggling with is making the pop up appear on all the front end applications when a user creates a new record. It works only on the users local machine and would like the other 4 users to see it on theirs.

I am aware that in order to do this I have to somehow find a way to detect a newrow entry from the table directly whilst using the timer event. I presume detecting the new record via query is a start??

Could someone help me with some advice?
databarracksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
Try adding a module level variable at the top of your form's code:

Option Compare Database
Dim lngRecordCount as Long


Initialize it in the Open Event of your form:

lngRecordCount = DCount("*", "YourTable")


And check against the current record count through your timer event:

If DCount("*", "YourTable") > lngRecordCount Then
       ' Your code to display the tooltip goes here
        lngRecordCount = DCount("*", "YourTable")  '<-- Save the latest count
End IF
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbizupCommented:
The above code could be placed in a data entry form if you only need it while the user is entering data in a specific location.

Alternatively, if you need the tooltip to popup regardless of where the user is at in the database, it could be added to a "splash screen" which is opened automatically when the database starts up, and kept open (hidden if desired) as long as the database is open.
0
databarracksAuthor Commented:
Hi I am trying your first method at the moment. The thing with your second suggestion is that I am using system tray balloon notification instead so I would rather try your first suggestion as that ties in with what I want to accomplish.

Please give me a sec whilst I try it out?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

databarracksAuthor Commented:
Ok I tried it but no pop up? This is my current code for the timer event
Private Sub Form_Open(Cancel As Integer)
lngRecordCount = DCount("*", "tblCustomerContactsAdmin")
End Sub

Private Sub Form_Timer()
If DCount("*", "tblCustomerContactsAdmin") > lngRecordCount Then
       ' Your code to display the tooltip goes here
       ShowBalloonTooltip "New Contact Added ....", "A new Admin Contact has been added by " & vbCrLf & Environ("username") & " for " & [Forms]![frmMain]![sfrCustomersMain].[Form]![sfrCustomerList].[Form].[company_name] & vbCrLf & "", btInformation
        lngRecorcount = DCount("*", "tblCustomerContactsAdmin")  '<-- Save the latest count
End If
End Sub

Open in new window


Do you know why id didn't recognise the new record?
0
databarracksAuthor Commented:
Is it because I don't have a Me.TimerInterval = 10000 set?
0
mbizupCommented:
Have you set the timer interval property so that it fires at the desired interval?

Timer Interval is in milliseconds, so to fire it every 5 seconds, your timer interval needs to be set to 5000.
0
mbizupCommented:
<<Is it because I don't have a Me.TimerInterval = 10000 set?>>

Exactly...
0
databarracksAuthor Commented:
Ok it worked however brilliant! But how can I make it stop after one instance. I set it to one second but it stays there and keeps flashing due to it being set to 1 second.
0
databarracksAuthor Commented:
I have this
Private Sub Form_Open(Cancel As Integer)
lngRecordCount = DCount("*", "tblCustomerContactsAdmin")
Me.TimerInterval = 1000
End Sub

Open in new window


It is working as I said but I just need it to show up a second after insert and only once after the new record has been inserted and dissappear after that? Sorry to trouble you but you have been most helpful already?
0
mbizupCommented:
>>         lngRecorcount = DCount("*", "tblCustomerContactsAdmin")

This line should do it if we correct the typo:


        lngRecordCount = DCount("*", "tblCustomerContactsAdmin")
0
mbizupCommented:
That refers to code in your timer event, btw.  With the typo corrected it should be:

Private Sub Form_Timer()
If DCount("*", "tblCustomerContactsAdmin") > lngRecordCount Then
       ' Your code to display the tooltip goes here
       ShowBalloonTooltip "New Contact Added ....", "A new Admin Contact has been added by " & vbCrLf & Environ("username") & " for " & [Forms]![frmMain]![sfrCustomersMain].[Form]![sfrCustomerList].[Form].[company_name] & vbCrLf & "", btInformation
        lngRecordCount = DCount("*", "tblCustomerContactsAdmin")  '<-- Save the latest count
End If
End Sub

Open in new window

0
databarracksAuthor Commented:
I don't believe it, it worked with typo correction:) You are a legend thank you so much for that, I cannot thank you enough for your help. I have been struggling to do this for a while and finally success.

A quick question though, will this method work when all the users are logged into the database and when one user creates a new record, would all the others see the balloon notification in their trays?

Or does this mean I have to create a hidden/ghost form i the background for all users?
0
mbizupCommented:
And as an aside, it is always a good idea to have option explicit set at the top of your code to trap typos.  With Option Explicit set, your code will not compile unless all variables, objects, etc are defined (Dim statements required for variables) - so any typos will cause compilation errors:

Option Compare Database
Option Explicit
Dim lngRecordCount as Long
0
mbizupCommented:
If all users have a copy of the Front End that you have placed the code in, it should work as long as the form you have placed this code behind is open.

A 'ghost' form (I like that term) is a really, really good idea for this though - as it can stay open all the time.  With that set up, the user can be anywhere in the database - not just on the specific data entry form for this to work. That is what I was trying to describe in my second suggestion at the beginning of the thread.
0
databarracksAuthor Commented:
Alas your second solution makes complete sense now. Ok I will give thatapproach a go as I would prefer the users to see the message regardless of where they are in the form. I think I can manage from here but once again you have been absolutely brilliant and cannot thank you enough for your input.

Have a good day:)
0
databarracksAuthor Commented:
Brilliant expert, very responsive, concise and accurate to the 'T'. Highly recommended
0
mbizupCommented:
Glad to help out :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.