Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Balloon tooltip popup when new record is added

Posted on 2012-03-21
17
Medium Priority
?
1,228 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 1640 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

783 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