I have inherited a Legacy Database. This db has one table that is used to track sales. It contains one field that is sort of a yearly serial number. the program basically queries the table for the last number used then inserts the next numerical number into the new record. Unfortunately, if a record is deleted, that yearly serial number is no re-used. I want to be abel to query this table in order to find these missing yearly serial numbers. In order to find these records I created a table that contains numbers from 1 to 5000 and I use an join this table to the sales table but there has to be a better way.
querying a database for data that isn't there - cute!
private sub findGaps()
dim dabs as dao.database
dim reci as dao.recordset
dim reco as dao.recordset
dim seqn as long
dim ssql as string
ssql = "SELECT theSeqWithGaps FROM theTable ORDER BY theSeqWithGaps;"
set reci = dabs.openrecordset(ssql)
seqn = 0
do while not .EOF
if !theSeqWithGaps > seqn + 1 then 'we have a gap
reco!fieldLong = seqn+1
seqn = seqn+1
I tried your code but I was not able to run your code. THe name of the table is called tblSales and the field name is CaseNum. The format of the Case Number is A00-0000 (i.e. C99-1234, M03-4321). The letter indicates the type of sale (C = Cash) the next two numbers indicate the last two digits of the sale year and the remaining four numbers are a sequential number that begins at 1 for each year. Can you resubmit your code using my values? Thanks!!!!
ooops again - there were still errors in my code, but it would not have worked on your case anyway due to your compound CaseNum.
i know you inherited the db, but those compound CaseNum are a pain - they are so easy to construct when you need them from simple fileds like [SaleType], [Year], [Serial]
here's corected code. (also a working .mdb since there is some other stuff to do - make a table to receive the output, make a delete query).
one poss other change:
gotn = !NumNum 'text to long
gotn = clng(!NumNum) 'text to long
Private Sub Gaps()
On Error GoTo err_Gaps
Dim dabs As DAO.database
Dim reci As DAO.Recordset 'input recordset from tblSales
Dim reco As DAO.Recordset 'output recordset to tblGaps
Dim seqn As Long 'var will be forced to increment sequentially
Dim gotn As Long 'sequence number part of tblSales.CaseNum
Dim maxn As Long 'largest sequence number part in tblSales.CaseNum
Dim ssql As String 'SQL to extract sequence number part of CaseNum
ssql = "SELECT mid$(CaseNum,5) as NumNum FROM tblSales ORDER BY mid$(CaseNum,5);"
Set dabs = CurrentDb()
Set reci = dabs.OpenRecordset(ssql)
Set reco = dabs.OpenRecordset("tblGaps")
maxn = CLng(!NumNum) 'get max sequence number used
seqn = 0 'seed the sequence
Do While Not .EOF 'intil you run out of input
gotn = !NumNum 'text to long
If Not gotn < maxn Then Exit Do 'all done
If gotn > seqn + 1 Then 'here is a gap
reco.AddNew 'add a gap record
reco!gap = seqn + 1 'record the number
reco.Update 'save the edit
Else 'there is no gap
.MoveNext 'get next input record
seqn = seqn + 1 'increment sequential pointer
MsgBox "All done - check tblGaps!", vbInformation, "Done"
Set reci = Nothing 'tidy up
Set reco = Nothing
Set dabs = Nothing
Exit Sub 'and quit
MsgBox Err.Description, vbCritical, "Unanticipated Error in Gaps()!"
Izy, I ran your code on one computer and it runs great!!!! excellent idea, I never would have thought of doing that way. However, the code will not run on another machine. I believe that a DAO active X driver is not loaded for example in the top of the Gaps() Proc when I attempt to dim the DAO objects (i.e. dabs, reci, and reco) the way you wrote it I get the following error message:
User Defined Type not defined
and the DIM dabs AS DAO.database statement is highlighted
I figured out a work around regarding the compound case number problem. I am separated the case number into parts: Type, year, serial number then I search for the number and then before it is appended into the table I combine the type, year and serail number. It appears to work fine.