Re: message show in popup box when record inserted in database

ypma · Jun 13, 2017

Re: message show in popup box when record inserted in database

Good evening fellow access users. I would like to know if it is possible to create a pop up messgage when a new record is inserted into the backend of the database . If so a few suggestions would be appreciated .

This access 2010 database is linked to a backend with six users . So I would like, a pop up message giving basic info for finding the said record to pop up on each front end copy . I have tried to google but to no avail.

Regards Ypma

CJ_London · Jun 13, 2017

Re: message show in popup box when record inserted in database

you would need some code in a place frequented by your users - perhaps a navigation form. It would call a function in the open? load? current? event to check the recordcount of the table(s). All depends on how your application works.

Code could be something like

Code:

Function anyInserted(tname as string)
static lastcount as long

if currentdb.tabledefs(tname).recordcount<>lastcount then
    if lastcount<>0 then msgbox "a record has been added to " & tname
    lastcount=currentdb.tabledefs(tname).recordcount
end if

end function

If you want to know what record was inserted you would need a means of identifying it - perhaps a timestamp field, but if two users each added a record then this could go wrong and the first one added would be missed. Again, depends on what you want to tell the users about the record.

Alternative would be a hidden form and put the code on a timer event - but that will slow your application down and unless you are checking every millisecond almost certainly records would be missed.

You might want to consider a listbox to list records added today which users can reference at their convenience

ypma · Jun 13, 2017

Re: message show in popup box when record inserted in database

Thank you CJ London for you most welcome suggestions, I will experiment and report back. For your information the records are imported remotely from the same source .
Thanks again .

Ypma

ypma · Jun 14, 2017

Re: message show in popup box when record inserted in database

CJ London, I have been playing around with the function you kindly supplied, but does not seem to work correctly, I have a list box with a query record source to the subject table and criteria Max.which provides me this the record count "List372" I created a command button to call the function . on click informs me a record has been added even when has not. Am I missing something very basic "

#Public Function anyInserted() As String
Dim tname As String
Static lastcount As Long
tname = "personal Detaill new"
If CurrentDb.TableDefs(tname).RecordCount <> List372 Then
If List372 <> 0 Then MsgBox "a record has been added to " & tname
lastcount = CurrentDb.TableDefs(tname).RecordCount
End If
List372.Requery
End Function #

I would appreciate if you could look at my attempt . Note the table name is spelt correctly

CJ_London · Jun 14, 2017

Re: message show in popup box when record inserted in database

suggest check your code v mine - not sure where list372 is coming from - the recordcount is checked against lastcount.

ypma · Jun 14, 2017

Re: message show in popup box when record inserted in database

CJ London, thank for staying with me. I was not sure how the record count was achieved, hence I created the list box Max to get the last id number. I am confused dot.com as to where the last count is calculated . Sorry to be a pain and hope you can enlighten me.

Regards Ypma

NauticalGent · Jun 14, 2017

Re: message show in popup box when record inserted in database

CJ, would this one of the few uses for a table macro perhaps?

CJ_London · Jun 14, 2017

Re: message show in popup box when record inserted in database

I was not sure how the record count was achieved,

here:

lastcount=currentdb.tabledefs(tname).recordcount

first time of running through the code, lastcount would default to 0, hence the

if lastcount<>0 then ....

to prevent a message appearing first time round

Google the Static variable type to understand what it does.

CJ, would this one of the few uses for a table macro perhaps?

possibly - I believe it could send an email to everyone, but don't think it can pop a message box on everyones screen - unless you have checking code along the lines I've suggested

ypma · Jun 15, 2017

Re: message show in popup box when record inserted in database

CJ I failed to make you code work , I even tried to put it in the control source of a text box #=currentdb.tabledefs(tname).recordcount# No Joy. I have taken up too much of your time and will have to admit I am struggling .
Thank again
Regards Ypma

CJ_London · Jun 15, 2017

Re: message show in popup box when record inserted in database

well it was just a suggestion. I know the code works because I tested it before posting it, but I guess it doesn't work as you require.

isladogs · Jun 15, 2017

Re: message show in popup box when record inserted in database

I haven't really been following this thread so apologies if I'm off-beam ...

See this link for an instant messenger popup in case its what you want ....

http://www.utteraccess.com/forum/index.php?showtopic=1169381

I've not tried it myself but its had over 1600 downloads over the past 10 years or so...

Cronk · Jun 16, 2017

Re: message show in popup box when record inserted in database

CJ

Recordcount property does not give the recordcount for linked tables, as in this case. You must have tested with an unlinked table.

CJ_London · Jun 16, 2017

Re: message show in popup box when record inserted in database

You must have tested with an unlinked table.

Err, yes I did

I was using recordcount because it should be faster than dcount. Guess I'm trying to be too clever for my own good.

So would need for all instances of
currentdb.tabledefs(tname).recordcount

substitute with

DCount("*",tname)

Not tested

static · Jun 16, 2017

Re: message show in popup box when record inserted in database

Another option

Create a table with a number field called lastrec

Create a pop-up form.
Link the form to your new table.
Add a subform control called 'subfrm'.
In the main form's module paste

Code:

Private Sub Form_Load()

    Const LINK_TABLE As String = "[table1]" '<-- change to name of table you want to monitor

    subfrm.SourceObject = "table." & LINK_TABLE
    subfrm.Form.Filter = "id>" & IIf("" & lastrec = "", 0, lastrec)
    subfrm.Form.FilterOn = True
    
    'record last record
    Me.lastrec = CurrentDb.OpenRecordset("select top 1 id from " & LINK_TABLE & " order by id desc")(0)
    Me.Dirty = False
    Me.AllowAdditions = False
End Sub

When the form is opened the last record id is recorded to the lastrec field.
id in this case would be a standard autonum index. You could change this to timestamp.
Refreshing the form displays new records.
Closing and reopening the form filters out records before lastrec.

You could change table.[tablename] to a form or query.

Add a timer for automatic refresh.

NauticalGent · Jun 16, 2017

Re: message show in popup box when record inserted in database

Cronk said:
Recordcount property does not give the recordcount for linked tables, as in this case. You must have tested with an unlinked table.

Good to know...explains why I have had problems in the past. One more mystery solved!

Re: message show in popup box when record inserted in database

ypma

Registered User.

CJ_London

Super Moderator

ypma

Registered User.

ypma

Registered User.

CJ_London

Super Moderator

ypma

Registered User.

NauticalGent

Ignore List Poster Boy

CJ_London

Super Moderator

ypma

Registered User.

CJ_London

Super Moderator

isladogs

MVP / VIP

Cronk

Registered User.

CJ_London

Super Moderator

static

Registered User.

NauticalGent

Ignore List Poster Boy

Similar threads

Users who are viewing this thread