Trigger event on Access table

[Eradic8or]

Is there anyway I can trigger an event whenever a record is added to a Access table?.
All I want is a msgbox that says a new record has been entered.

 

[ZmrAbdulla]

How do you enter the new record? through form or direct to table?

________________________________________________________
Zameer Abdulla
Help to find Missing people
Even a thief takes ten years to learn his trade.

 

[RoyVidar]

Access does not have any triggers on tables, so the closest you get (I think), is using form events. You're only allowing the user to enter information through forms, right?

Then - the before update event of the form happens immediately before the save, and you can cancel it, and the after update happens immediately after a save (usable as event for your messagebox?)

Roy-Vidar

 

[Eradic8or]

I don't have any forms at all.

What I have a is a web based front end that I have written which directly adds a record to the dataabase table.

What I am trying to is inform the user when a new record has been written via the web.

I expect if I can't do triggers, I am going to have to convert it to SQL Server.

 

[JoeAtWork]

I don't see how you can use a trigger in SQL Server to send a message back to your user. Triggers are meant to do something internal in the SQL Server, like update an associated record.

It will need to be on your web app that you display the message that the record was added sucessfully to the database. Generally, you would do that after whatever code adds the record, you can assume if there wasn't an error then it was successful.

 

[RoyVidar]

What you are after, doesn't seem to relate much to triggers, and is probably how to find out the result of your action. JoeAtWork has one suggestion, then both DAO and ADO have methods to get the number of records affected by an action query (I suppose such exist for ADO.Net too), and using Jet 4.0, you can do "@@select identity" to retrieve the last identity/autonumber. All these questions, however, are probably better answered in a forum dedicated to the technology you're using.

Roy-Vidar

 

[fneily]

I'm assuming your creating DAP's. Hopefully you have a command button to save the record. If you do, right click the button, select Microsoft Script editor. On the left under Script Outline, scroll down to your button's name. Expand the branch and double click on OnClick. You will write some VB script to open another form with your message. See the following two web sites for help.
The first shows you how to Position your web page in a new window with the message.

http://support.microsoft.com/?kbid=234253

http://www.kayodeok.btinternet.co.uk/favorites/kbofficeaccesshowto.htm

 

[fneily]

Oops. I referenced some interesting info but it doesn't show you how to open another window. The following site does:

http://msdn.microsoft.com/library/d...hop/author/dhtml/reference/methods/open_0.asp

This example uses the open method to create a new window that contains Sample.htm. The new window is 200 pixels by 400 pixels and has a status bar, but it does not have a toolbar, menu bar, or address field.

window.open("Sample.htm",null,
"height=200,width=400,status=yes,toolbar=no,menubar=no,location=no");

 

[Eradic8or]

Just to clarify here exactly what I want, although, I think I have found a solution.

I have an assistant in Edinburgh that has the access database. What I am trying to do is that if a person (in Inverness for example) opens up the web front end and raises a work request, that entry is written into the database in Edinburgh.

What I want to achieve then is that the access database recognises that a new entry has been written and informs ths assistant in Edinburgh.

So, I need some sort of trigger that fires when a new record is written. I thought about using a form with a timer checking the number of records in the table every so often but I wondered if there was a better way.

 

[JoeAtWork]

The only way I can think of is for the HTML page to periodically refresh, checking each time for any new records in the database.

One possibility, create a new table that relates users to work requests, to track which work requests the user has acknowledged. On the web page, you could list all requests not yet Acknowledged as links. If the user clicks on the link (presumably sending him to a page with the details of the work request), that would set the Acknowledged flag to True, and thus removing the request from the "new" list the next time the user goes to the home page.

 

[fneily]

Oh. I get it now. I don't know about a message, but I created one where when a table was updated it also created a log to another table of which record was changed or ADDED, what was change, and at what time. Maybe you could do this and that way the Edinburgh person could then create reports of any changes or additions. Plus you'll have a history table.

 

[Eradic8or]

fneily,
Can I ask you what you used to check when a new entry was added to the table.

Did you have to use a form?

 

[fneily]

I recorded the date and time the record was added to the history (or log) table. Then you can just run a query with date parameters for a daily, weekly, whatever report to see what was added or changed. I don't have an "instant message" appear.