Solved

Balloon tooltip popup when new record is added

Posted on 2012-03-21
17
1,104 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
  • 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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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 up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
This collection of functions covers all the normal rounding methods of just about any numeric value.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now