VB CODE HELP ON RECORDSET 1

[Gazer44]

I'm trying to display a mssg 'Record Added' when a NEW record is added to a table. I've been given the following info as part of the solution to the problem but I'm struggling with the VB code. Can anyone help!

'Open a recordset for a table that you want to modify, store a variable for the Recordset.RecordCount. Then refresh the recordset from the table based on a timer interval you set to compare the PreviousRecordCount to the CurrentRecordCount.'

Any help appreciated.

 

[jgoodman00]

It seems a little ambiguous who will be receiving this message. Is the purpose of this to tell the user they have successfully added a record, or is it to tell another user somebody has added a record? James Goodman

 

[Gazer44]

It's to tell another user that someone else has added a record.

 

[tunsarod]

Here's two ways of getting a count of records in a table:

1. Dcount("RecID","MyTable&quot

Where RecID is any field that is consistently filled with data. I.e. Never Null

2. Dim rst As Recordset, dbs As Database, intCount as integer

Set dbs = currentDb
Set rst = dbs.OpenRecordset("MyTable&quot
intCount = rst.RecordCount

If you do either of these at the time the form opens and store that value in a variable (intInitialCount) you can repeat the count anytime with (intNewCount) and report any change with a message box:

MsgBox intNewCount-intInitialCount & " Records have been added!",vbOkOnly,"News-Flash! Read All About It: Records Added"


Regards
Rod

 

[tunsarod]

Further to my last post you could if you want create a small pop-up form. Make it hidden by default and insert the previously described procedure into its OnTimer event.

Then set the Timer event to say 5000 so the procedure tests the table every five seconds and makes the Form visible if there is a difference between the last count and the previous count. Have a button on the form which makes the form invisible again. that way regardless which forms your user is working with or has open he will get a message whenever the count changes.

Just remember that the hidden form needs to initialise and open in hidden mode every time the user starts the application. You should also remove the control box to prevent the user from closing the form accidentally.

Hope this helps.

Rod

 

[scriverb]

Dimension a variable at the Form level:
Dim vTableRecordCount as long

Upon opening the form each users copy would perform the following during the OnLoad event procedure:

dim db as database
dim rs as recordset
set db = currentdb
set rs = db.openrecordset("tablename", dbOpenDynaset)
vTableRecordCount = rs.recordcount
rs.close
db.close

Set the TimerInterval to some let's say one minutes: 60000
This can be adjusted to whatever however frequent that you want the recordcount to be checked. Remember this is in milliseconds. 1000 ms per second

Set the OnTimer event procedure to the following:

dim db as database
dim rs as recordset
set db = currentdb
set rs = db.openrecordset("tablename", dbOpenDynaset)
If vTableRecordCount <> rs.recordcount then
   vTableRecordCount = rs.recordcount
   MsgBox(&quot;A new record has been added to the table.&quot;)
end if
rs.close
db.close

This event procedure will constantly monitor the starting recordcount to the recordcount of the table each times the event is processed. When it is different the message is displayed to the user. You may want to incorporate a me.requery to repopulate the form so that the new record is part of the RecordSource.

Hope this helps you out.

Bob Scriver

 

[Gazer44]

Thanks Rod & Bob, I'll give these a try & let you Know.
Cheers

 

[Gazer44]

Sorry Guys, still having probs, can’t get either of these to work. I’ll explain exactly what I’m trying to do.

Users have FormA constantly open (which looks at TableA).

If a user opens FormB, (connected to TableB) and adds a record then I want other users screens (who only have FormA open) to pop up a mssg saying “New record added to FormB”.

They will then open FormB and see it !!

(p.s. sometimes records get deleted as well as the day goes on !)

 

[scriverb]

Are you saying that you want two tables monitored for Adds and Deletes? If that is the case then you would have to doubleup on the code in FormA. Have two variables to store record counts for each table (i.e. vTableARecordCount and vTableBRecordCount) You would set the startup value in the OnLoad Procedure of FormaA.

Then in the OnTimer Event you would run through double the code I gave you making the comparisons of the recrdcounts for tableA to the current TableA recordset count and the same for TableB. Each would have it's own message specific to its own table.

Is this what you need?

Bob Scriver

 

[tunsarod]

Hi Guys,

Here's another approach since it seems that if there is the potential for users to be adding AND deleting records simultaneously then there may not be a count difference even though record(s) were added.

Let each user's App create a copy of the target table on start-up.

e.g. DoCmd.CopyObject, "TargetTable", acTable, "SampleTable"

Then use a query to list those record in the target table which are not found in the sample table.

A continous form could be used that is normally hidden, has the query as its Record Source and has code in its OnTimer event to:
1. run the query to refresh its data
2. make the form visible if the count of displayed records is changed
3. have a button that opens a form to view the new record.

You might also want to delete the sample table and create a new copy ready for the next comaprison. Alternatively leave the original copy intact so that you have an ongoing log of new records added during the period the user's App is running.

You will find that Access offers a Wizard that will create exactly such a query to find records in one table that are not matched in another table.

Here's SQL from a query to examine two tables:


SELECT DISTINCTROW T_Target.RecID, T_Target.AnotherField
FROM T_Target LEFT JOIN T_Sample ON T_Target.RecID = T_Sample.RecID
WHERE (((T_Sample.RecID) Is Null));


This shows the a list of (NEW) records T_Target that do not have any matching records in table T_Sample.

Rod

 

[Gazer44]

Hi Guys,
basically - users constantly monitor FormA (which auto refreshes every 10 seconds).
Upon each refresh I want it to check TableB for any additional records added, if so inform the user to open FormB to see them.

(TableA does not need to be checked because it requery's every 10 secs but TableB does because it's an additional Table used for other purposes which the user only needs when told to (via the new record mssg)!)

I'm not bothered about the odd record being deleted as this is rare and unlikely to happen within the 10 second timer interval !

Cheers

 

[scriverb]

If that is the case then the code I provided earlier should work just fine. Just identify tableB as the table in the OpenRecordset statements and that should work for you.

Bob Scriver

 

[Gazer44]

Hi Bob, (I'ts still not working !)

I've put 'Dim vTableRecordCount as long' at the top of FormA's event procedure (directly after lines 'Option Compare Database' & 'Option Explicit').

In FormA's On Load procedure i've got:-

dim db as database
dim rs as recordset
set db = currentdb
set rs = db.openrecordset("TableB", dbOpenDynaset)
vTableRecordCount = rs.recordcount
rs.close
db.close

In FormA's On Timer procedure i've got:-

DoCmd.Requery
dim db as database
dim rs as recordset
set db = currentdb
set rs = db.openrecordset("TableB", dbOpenDynaset)
If vTableRecordCount <> rs.recordcount then
vTableRecordCount = rs.recordcount
MsgBox(&quot;A new record has been added to the table.&quot
end if
rs.close
db.close

I've also tried replacing the Msgbox with a Docmd.openform option (to open a form with a mssg on it) but still not working.

Can you spot anything ??

 

[scriverb]

It looks good as is but remember you have to have a new record added to TableB for the MsgBox to trigger. Are you doing that?

Open another copy of the database and open the form for tableB and add a record and see if the message box in the first copy of the program triggers the MsgBox.

If that doesn't work put a stop in the OnTimer event just after the Dim statements and use you F8 key to walk through each statement checking the values of rs.recordcount and the vTableRecordCount to see what there values are each time you pass through the event procedure. I would set your timerinterval to let's say 30000 so you have time to add the new TableB record before you check it again.

Good luck. Heading out to work and will monitor from there.

Bob Scriver

 

[Gazer44]

Nope, not working !!

What do you mean by 'put a stop in the OnTimer event just after the Dim statements and use you F8 key to walk through each statement checking the values of rs.recordcount and the vTableRecordCount to see what there values are each time you pass through the event procedure'.

I've not done this before !

 

[scriverb]

In design mode you can put a stop command in the code like I said and when the code gets to that point you can then move one line of code at a time through the code and check out the values of everything as it is happening.

The F8 key will step you through the code one line at a time. After you move to a line you can check the values by going to the DeBugger window and typing in ? name of variable, object, etc and hit return to look at the data. This will help you to find out what is not working correctly.

Give it a try. It won't hurt anything. Just remember you have to remove it later when the code goes live. I mentioned about entering a larger number for the TimerInterval so that it doesn't keep triggering every second or so.

I am here for the rest of the evening so I can help you to walk through it.

Bob Scriver

 

[scriverb]

Okay, my mistake. There is a missing statement in both the OnLoad procedure and the OnTimer procedure. To assess the recordset record count you must assess the last record of the recordset. rs.movelast must be added to each. Also, the variable vTableRecordCount has to be dimensioned at the:

Declaration:
Option Compare Database
Option Explicit
Dim vTableRecordCount As Long

OnLoad:
dim db as database
dim rs as recordset
set db = currentdb
set rs = db.openrecordset(&quot;TableB&quot;, dbOpenDynaset)
rs.movelast
vTableRecordCount = rs.recordcount
rs.close
db.close

OnTimer:
dim db as database
dim rs as recordset
set db = currentdb
set rs = db.openrecordset(&quot;TableB&quot;, dbOpenDynaset)
rs.movelast
If vTableRecordCount <> rs.recordcount then
vTableRecordCount = rs.recordcount
MsgBox(&quot;A new record has been added to the table.&quot
end if
rs.close
db.close

I have tested this out and it works. The tables TableA and TableB are on a shared back-end and the multiple apps front-ends all have the TableA form open always which is where the code is and whenever anyone adds a record to TableB the message pops up on the front-ends all over your office.

This works now. Sorry about the oversite. That's the problem with pseudo-code that isn't actually tested.

Bob Scriver

 

[Gazer44]

Thanks VERY VERY much, this works great.

 

[scriverb]

Glad I could help.

Bob Scriver