Solved

Balloon tooltip popup when new record is added

Posted on 2012-03-21
17
1,180 Views
Last Modified: 2012-03-21
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?
0
Comment
Question by:databarracks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
17 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 410 total points
ID: 37747481
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37747508
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
 

Author Comment

by:databarracks
ID: 37747530
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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

Author Comment

by:databarracks
ID: 37747549
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
 

Author Comment

by:databarracks
ID: 37747569
Is it because I don't have a Me.TimerInterval = 10000 set?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37747575
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37747578
<<Is it because I don't have a Me.TimerInterval = 10000 set?>>

Exactly...
0
 

Author Comment

by:databarracks
ID: 37747595
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
 

Author Comment

by:databarracks
ID: 37747607
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37747609
>>         lngRecorcount = DCount("*", "tblCustomerContactsAdmin")

This line should do it if we correct the typo:


        lngRecordCount = DCount("*", "tblCustomerContactsAdmin")
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37747616
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
 

Author Comment

by:databarracks
ID: 37747636
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37747638
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37747657
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
 

Author Comment

by:databarracks
ID: 37747691
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
 

Author Closing Comment

by:databarracks
ID: 37747699
Brilliant expert, very responsive, concise and accurate to the 'T'. Highly recommended
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37747704
Glad to help out :)
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question