Numbering Field

[rangersedge]

I have a TV Episode database. In the database I want to number each episode of a show. How do I autonumber a show's episodes when sorted by season then episode and have the autonumber restart at 1 when it hits a new show title?

 

[jdraw]

You do not want an autonumber for this. Autonumbers are unique only; not necessarily sequential; and not necessarily positive.

Assign your own numbers using your proposed query as a record set; process it and use your logic to assign numbers accordingly.

See this link to get a better understanding of autonumbers.

Good luck.

 

[rangersedge]

I know the differences and when to use the actual autonumber function. That was just a way to explain what I'm trying to do. I just dont want to site for hours numbering episode records in the hundred thousand range. I want Access to number these for me but I don't know how to make it do that.

 

[RainLover]

I know the differences and when to use the actual autonumber function. That was just a way to explain what I'm trying to do. I just dont want to site for hours numbering episode records in the hundred thousand range. I want Access to number these for me but I don't know how to make it do that.

What you are going to need is to use DMax to find the previous largest number then add one to that.

There is a sample of DMax plus One in the code archives.Have a look to see if it fits your situation.

Please post back with your outcome. Good or Bad.

 

[BigHappyDaddy]

A brute force method is below. Basically just a loop that resets a counter to 1 at every change in TV show name. If you want the episodes of each TV show in a specific order, include the secondary field in the ORDER BY statement (ie AirDate).

Sub NumberEpisodes()
DIM rs as DAO.Recordset
DIM lngCounter as Long
DIM strCurrentShowName as String
DIM strSQL as String
 
strSQL = "Select TVShowName, Episode " & _
         "FROM tblTVShows " & _
         "ORDER BY TVShowName;"
set rs = CurrentDb.OpenRecordset(strSQL,dbOpenDynaset)
lngCounter = 0
if rs.RecordCount > 0 Then
   strCurrentTVShowName = rs!TVShowName
Else
   Goto EmptyRecordset
End If
 
Do Until rs.EOF
   If rs!TVShowName = strCurrentShowName Then
      lngCounter = lngCounter + 1
   Else
      lngCounter = 1
      strCurrentShowName = rs!TVShowName
   End If
   rs.Edit
   rs!Episode = lngCounter
   rs.Update
   rs.MoveNext
Loop
 
rs.Close
Set rs = Nothing
 
Exit Sub
 
EmptyRecordset:
   MsgBox "Empty Recordset"
End Sub

 

[jdraw]

I agree with BigHappyDaddy's approach. That is what I was advising in post #2
with

Assign your own numbers using your proposed query as a record set; process it and use your logic to assign numbers accordingly.

No one is suggesting you sit for hours and manually assign numbers -- you analyze the problem, write some code, test it with your data; then, once working, you run the code.

Good luck with your project.

 

[rangersedge]

I understand what you guys are trying to say. I still concider myself a novice at coding although I have gotten some pretty intense codes to work. Like a button to import many xml files while replacing any instances of the & symbol. I just needed a point in the right direction of how to start this code. I wasn't sure what command would be used to even create a sequential numbering system. I'm going to try the VBA code above when I get a chance. I will let you know if it works. I think BigHappyDaddy is on the right track. I have the Show Title, Season Number, and Episode Number in individual fields so that should work.

 

[rangersedge]

I copied the code onto a button to test it and tried changing the lines to fit what I need.

Private Sub Command486_Click()
Dim rs As DAO.Recordset
Dim lngCounter As Long
Dim strCurrentShowTitle As String
Dim strSQL As String

strSQL = "Select ShowTitle, Season, Episode, EpisodeNumberTitle " & _
"FROM TV " & _
"ORDER BY ShowTitle, Season, Episode;"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
lngCounter = 0
If rs.RecordCount > 0 Then
strCurrentShowTitle = rs!ShowTitle
Else
GoTo EmptyRecordset
End If

Do Until rs.EOF
If rs!ShowTitle = strCurrentShowTitle Then
lngCounter = lngCounter + 1
Else
lngCounter = 1
strCurrentShowTitle = rs!ShowTitle
End If
rs.Edit
rs!EpisodeNumberTitle = lngCounter
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

Exit Sub

EmptyRecordset:
MsgBox "Empty Recordset"
End Sub

I keep getting an error on line...
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
The error says...
"Run-time error '3061':
Too few parameters. Expected 1.

 

[BigHappyDaddy]

My first guess is something in the SQL statement. Typo in the field name? Calling out a field name that doesn't exist?

 

[rangersedge]

Good catch... I had labeled "EpisodeNumberTitle" instead of "EpisodeNumberTotal". I corrected that and began getting a different error stating "File Sharing lock count exceeded. Increase MaxLocksPerFile registry entry." A quick google search gave me this line of code... "DAO.DBEngine.SetOption dbmaxlocksperfile,100000". All works perfectly and rather quickly I might add. Took less than 2 seconds to number all 35,000 episodes. I will include the code in case anyone would like to use it.

Private Sub Command486_Click()
Dim rs As DAO.Recordset
Dim lngCounter As Long
Dim strCurrentShowTitle As String
Dim strSQL As String
DAO.DBEngine.SetOption dbMaxLocksPerFile, 100000

strSQL = "Select ShowTitle, Season, Episode, EpisodeNumberTotal " & _
"FROM TV " & _
"ORDER BY ShowTitle, Season, Episode;"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
lngCounter = 0
If rs.RecordCount > 0 Then
strCurrentShowTitle = rs!ShowTitle
Else
GoTo EmptyRecordset
End If

Do Until rs.EOF
If rs!ShowTitle = strCurrentShowTitle Then
lngCounter = lngCounter + 1
Else
lngCounter = 1
strCurrentShowTitle = rs!ShowTitle
End If
rs.Edit
rs!EpisodeNumberTotal = lngCounter
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

Exit Sub

EmptyRecordset:
MsgBox "Empty Recordset"
End Sub

 

[BigHappyDaddy]

Cool.

For future reference - I was doing a small project with a recordset and got the same file sharing lock error. I think the error is a result of having a large number of recordset records open at a time. Increasing the MaxLocksPerFile obviously works, but changing up the size of the recordsets might help. One option would be to create a recordset of just the TV Show Titles. Then from that, loop through the entire table, but not a record at a time, but a related group of records. Then there are options, nested recordset, SQL statement with simple function imbedded, etc.