Help wanted with Visual Basic code

  Nosmas 21:04 08 Apr 03
Locked

I maintain the software for my bowls club membership database. From time to time I have discovered (accidentally) that the database has dropped a record from the main table, but haven't yet been able to find out why it happens. In order to draw our administrator's attention to the fact immediately it happens, I wrote the following piece of code: -

Public Function SBCStart()
RecordsCheck

End Function

Public Sub RecordsCheck()
'Routine to check whether total records in SBCMembers Table agrees with Serial number 'of last record and if not to issue a warning message to user.

Dim dbs As Database, rst As Recordset
Dim tblrecs As Integer, lastrec As Integer

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SBCMembers")
rst.MoveLast
tblrecs = rst.RecordCount
lastrec = rst.Fields("Serial")
If lastrec > tblrecs Then 'Records in table different from last record Serial number
Beep ' Sound a tone.
MsgBox "Total records in SBCMembers Table is " & tblrecs &Chr(13) & Chr(13) _
& "Last record Number is " & lastrec & Chr(13) & Chr(13) _
& "Please advise xxxxx xxxxx of this error and quote the above numbers", _
vbExclamation, "WARNING - Records Discrepancy!"
End If
rst.Close
Set dbs = Nothing

End Sub

The code is run on opening the database file (in MS Access) and again on closing the program, and I thought it was working properly. If a record has been dropped the expected error would be that the last record number is one MORE than the records in the table, but recently the error message reported that the last record number was one LESS than the records in the table. Upon inspection that was patently untrue - BOTH values were the same so the error message should not have been issued. This is really puzzling me as it is quite a simple piece of code and I cannot see any error in my logic. (For info the administrator tells me that she had amended two or three records and then added a new record, and then got the error message when she closed the program.)

The error message is now appearing every time the database is opened and closed even though there are no records missing from the table. Please does anyone have any thoughts or suggestions for a cure?

  Nosmas 21:10 08 Apr 03

Sorry - the above was pasted in from a Word document and the formatting has not been repeated. Hope the following code will be more intelligible!

Public Function SBCStart()

RecordsCheck

End Function

Public Sub RecordsCheck()

'Routine to check whether total records in SBCMembers Table agrees with

'Serial number of last record and if not to issue a warning message to user.

Dim dbs As Database, rst As Recordset

Dim tblrecs As Integer, lastrec As Integer

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("SBCMembers")

rst.MoveLast

tblrecs = rst.RecordCount

lastrec = rst.Fields("Serial")

If lastrec <> tblrecs Then 'Records in table different from last record Serial number
Beep ' Sound a tone.

MsgBox "Total records in SBCMembers Table is " & tblrecs &Chr(13) & Chr(13) _
& "Last record Number is " & lastrec & Chr(13) & Chr(13) _
& "Please advise xxxxx xxxxx of this error and quote the above numbers", _
vbExclamation, "WARNING - Records Discrepancy!"

End If
rst.Close

Set dbs = Nothing

End Sub

  Nosmas 02:20 09 Apr 03

Refresh

Anyone there!!

  Nosmas 12:56 09 Apr 03

Refresh

Help urgently needed please!

  Cooco 14:02 09 Apr 03

In a moment of madness the other day I bought a book by Greg Perry entitled, "Absolute Beginner's Guide" - "No prior progamming necessary". I am now on page 78. He goes on to demonstrate how to use, "Your first Language: Visual Basic". The assumption is that I have VB on the machine and of course I do not. So go out and buy it he says. Well I have had a look at what is available and once again, as usual, I am confused. There are things like, VB Studio; VB.net, VB Upgrades and, VB Service Packs none of which look like the thing I need. I do not feel too bad about it because I took my problem to PC World and the only thing they had was something called VB.net but they did not know what it was.

I wonder if you could be so kind as to tell me what I need and, where I could get it?

  Peverelli 14:45 09 Apr 03

Cooco,

Best to start your own thread so that you will emails when there's a reply.

Nosmas,
Sorry I can't help but I beleive VOG could be useful here, so I hope he reads your thread.

  Kegser 15:51 09 Apr 03

I've not used Access or Visual Basic but it seems to my non-expert eyes that you are comparing the count of records with the serial number field in the last record of your recordset. As I recollect DBMS generally - unless you specify the order of records in a cursor/collection with either an ORDER BY clause or the SORT property then the order of records will be undefined. Hence you have no guarantee that the record with the highest serial number will be the last one in yout recordset.

  Nosmas 17:40 09 Apr 03

Your suggestion is certainly food for thought - thanks very much. I am at home now but will certainly look into it when I next go up to my club.

The field "Serial" is a copy of the number allocated to field "MshipNo" which is automatically generated by Access because its Data Type is "AutoNumber". "MshipNo" is also the Primary Key of the table, so when records are displayed they are in numerical order. However if Primary Key is removed and the table displayed, the records are in apparently random order. It therefore seems a possibility that the last physical record in the table may not be the highest numbered record.

  VoG™ 18:27 09 Apr 03

Thanks for your e-mail.

Unfortunately I use VBA in Excel (and a little in Word) but I don't use Access at all, I'm afraid.

Good luck!

  Kegser 13:06 10 Apr 03

Until I read your reply, I hadn't realised that you were using an automatic numbering system to generate your Serial Number. Again, I am extrapolating from my experience with other DBMS, and don't know if this also applies to ACCESS but generally - automatic numbering systems do not fill in vacated gaps in the sequence. What this means is that if you create 10 records they will be numbered 1 to 10 automatically. If you then erase 2 records from your database - when you go on to create another new record it will be numbered as 11 even though you are creating the 9th record in the database. If this is the case and you do permit deleton of records then I don't think your check algorithm will work even if you do ID the highest serial number correctly.

This gap in a numbering sequence can also be created by cancelling the Create New Record operation. What happens is you start to fill in a new record, find you're missing some details and press ESC to cancel. This aborts the new record but some DBMS mark the number as "issued" and next time round present a serial number +1 higher than expected. Again I don't know if MS-Access works this way or not but you could check and see.

  Nosmas 18:23 11 Apr 03

Thanks for your further thoughts. The present problem is that my code is reporting an error when none exists! I know from inspecting the table that there are 2087 records and the highest numbered record is 2087, but my code is reporting it as 2086. My code is correctly counting 2087 records. What puzzles me is that field "MshipNo" is the Primary Key and therefore should be able to point to the correct record even if Access has physically placed it "out of sequence" - therefore "rst.MoveLast" I would expect to make record 2087 the "active" record which has 2087 in field "Serial".

I have received a suggestion from another source for amending my code which I am trying to get to work on the copy of the database I have on my home system before transferring it to the club system. Having problems with the syntax!!

This thread is now locked and can not be replied to.

What is Amazon Go and will it come to the UK? The store without checkouts or queues

1995-2015: How technology has changed the world in 20 years

Why ecommerce hasn't taken off on social media

New MacBook Pro 2016 review | MacBook Pro with Touch Bar review: Apple's expensive and powerful…