Formulate Index Value

**NeoPa** · Oct 8 '08, 03:58 PM

Originally posted by MNNovice

How do we fix it? Did I explain it okay?

I won't KNOW the answers to these until I see the details and the database together.

However, I think the explanation is probably fine and the fix will be quite straightforward once in possession of all the info I need.

PS. Looking at the detail you've provided, I think the explanation seems fine :)

**MNNovice** · Oct 8 '08, 04:15 PM

Please see post #104. I was able to upload the file. Many thanks.

**NeoPa** · Oct 8 '08, 04:47 PM

Great!

I'll download it this evening and delete it from the post when done.

**NeoPa** · Oct 8 '08, 06:36 PM

Attachment downloaded and removed.

Now to get to the fun part :D

**NeoPa** · Oct 8 '08, 07:37 PM

NB. When copying from the site at this time (it's a bit wrong with the code tags at the moment) remember to copy only from the source of a reply.

Otherwise, you get what is in your GetCDKey() function and that is a right royal mess :(

Don't worry for now, as I will probably be posting another version quite soon anyway, but when I do - be sure to include the indenting. It is very important if you ever want to work on it in future (and particularly if you want someone else to of course).

**NeoPa** · Oct 8 '08, 09:28 PM

You use the term Record ID in your post, yet there are two entities, a Record Number (Access has for all displayed recordsets) and a [Recording ID] which is the AutoNumber field in your table.

Anyway, I tried to follow your problem, but I found everything actually worked perfectly (according to the data that was there - I deleted some records at the end to match the recourd numbers you stated).

The largest value used for MHA was 08 and it produced UH.MHA.09.028.0 584. The largest value for UH was 027.

I think if you clear up your data you will see it start to work properly for you. The data had some repeats in it, so the count of items didn't match the items used, but from the items used, it selected the correct new values.

**NeoPa** · Oct 8 '08, 09:34 PM

I did notice that the new record was added as soon as I left the [RecordingArtist ID] control, so reordering the controls on the form wouldn't hurt (not very intuitive behaviour).

I will include in here the whole of the module as I have it (reformatted). It won't display well necessarily, but if you reply to the post then copy the code from within the quote, it should work for you.

Code:

Option Compare Database
Option Explicit

Private Sub Form_Current()
  With Me
    If .NewRecord Then Call .RecordingTitle.SetFocus
    .MusicCategoryID.Locked = Not .NewRecord
    .RecordingArtistID.Locked = Not .NewRecord
    .Duet_Trio.Locked = Not .NewRecord
  End With
      
End Sub

Private Function GetCDKey() As String
  Dim strCat As String, strArt As String, strVal As String
  Dim intVal As Integer

  GetCDKey = ""
  If IsNull(Me.MusicCategoryID) _
  Or IsNull(Me.RecordingArtistID) Then Exit Function

  strCat = Me.MusicCategoryID.Column(2)
  strArt = Me.RecordingArtistID.Column(2)
  GetCDKey = "%C.%A.%2.%3.%4"
  GetCDKey = Replace(GetCDKey, "%C", strCat)
  GetCDKey = Replace(GetCDKey, "%A", strArt)
  intVal = Val(Nz(DMax(Expr:="Mid([SerialNumber],8,2)", _
                       Domain:="[tblCDDetails]", _
                       Criteria:="[SerialNumber] Like " & _
                                 "'*." & strArt & ".*'"), "0"))
  GetCDKey = Replace(GetCDKey, "%2", Fmt(intVal + 1, 2))
  intVal = Val(Nz(DMax(Expr:="Mid([SerialNumber],11,3)", _
                       Domain:="[tblCDDetails]", _
                       Criteria:="[SerialNumber] Like " & _
                                 "'" & strCat & ".*'"), "0"))
  GetCDKey = Replace(GetCDKey, "%3", Fmt(intVal + 1, 3))
  intVal = Val(Nz(DMax(Expr:="Right([SerialNumber],4)", _
                       Domain:="[tblCDDetails]"), "0"))
  GetCDKey = Replace(GetCDKey, "%4", Fmt(intVal + 1, 4))
End Function

Private Function Fmt(intVal As Integer, intDigits As String) As String
  Fmt = Right(10000 + intVal, intDigits)
End Function

Private Sub RecordingArtistID_AfterUpdate()
  Me.TxtSerialNumber = GetCDKey()
End Sub

Private Sub RecordingArtistID_NotInList(NewData As String, Response As Integer)
    MsgBox "Double-click this field to add an entry to the list."
    Response = acDataErrContinue
End Sub

Private Sub RecordingArtistID_DblClick(Cancel As Integer)
On Error GoTo Err_RecordingArtistID_DblClick
    Dim lngRecordingArtistID As Long

    If IsNull(Me![RecordingArtistID]) Then
        Me![RecordingArtistID].Text = ""
    Else
        lngRecordingArtistID = Me![RecordingArtistID]
        Me![RecordingArtistID] = Null
    End If
    DoCmd.OpenForm "Artists", , , , , acDialog, "GotoNew"
    Me![RecordingArtistID].Requery
    If lngRecordingArtistID <> 0 Then Me![RecordingArtistID] = lngRecordingArtistID

Exit_RecordingArtistID_DblClick:
    Exit Sub

Err_RecordingArtistID_DblClick:
    MsgBox Err.Description
    Resume Exit_RecordingArtistID_DblClick
End Sub

Private Sub MusicCategoryID_AfterUpdate()
  Me.TxtSerialNumber = GetCDKey()
End Sub

Private Sub MusicCategoryID_NotInList(NewData As String, Response As Integer)
    MsgBox "Double-click this field to add an entry to the list."
    Response = acDataErrContinue
End Sub

Private Sub MusicCategoryID_DblClick(Cancel As Integer)
On Error GoTo Err_MusicCategoryID_DblClick
    Dim lngRecordingArtistID As Long

    If IsNull(Me![MusicCategoryID]) Then
        Me![MusicCategoryID].Text = ""
    Else
        lngRecordingArtistID = Me![MusicCategoryID]
        Me![MusicCategoryID] = Null
    End If
    DoCmd.OpenForm "frmCategories", , , , , acDialog, "GotoNew"
    Me![MusicCategoryID].Requery
    If lngRecordingArtistID <> 0 Then Me![MusicCategoryID] = lngRecordingArtistID

Exit_MusicCategoryID_DblClick:
    Exit Sub

Err_MusicCategoryID_DblClick:
    MsgBox Err.Description
    Resume Exit_MusicCategoryID_DblClick
End Sub

Private Sub cmdAddSong_Click()
On Error GoTo Err_cmdAddSong_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmSongs"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdAddSong_Click:
    Exit Sub

Err_cmdAddSong_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddSong_Click
    
End Sub

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdSave_Click:
    Exit Sub

Err_cmdSave_Click:
    MsgBox Err.Description
    Resume Exit_cmdSave_Click
    
End Sub

Private Sub cmdFindCD_Click()
On Error GoTo Err_cmdFindCD_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmFIND"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdFindCD_Click:
    Exit Sub

Err_cmdFindCD_Click:
    MsgBox Err.Description
    Resume Exit_cmdFindCD_Click
    
End Sub

PS. I find I can copy this code accurately on FireFox 3. It may well work for you but check it does first. Indenting IS important.

**NeoPa** · Oct 9 '08, 01:08 AM

Last post for the night (probably).

I have a couple of queries I use when checking your data. They show me what SHOULD be used in the next record created (by showing the related records that already exist). Actually, they're both relatively simple :

Code:

SELECT tblCDDetails.*
FROM tblCDDetails
WHERE Left([SerialNumber],2)=[Enter Music Category Code (2 chars)]

Code:

SELECT tblCDDetails.*
FROM tblCDDetails
WHERE Mid([SerialNumber],4,3)=[Enter Artist Code (3 chars)]

**MNNovice** · Oct 9 '08, 01:40 PM

Originally posted by NeoPa

You use the term Record ID in your post, yet there are two entities, a Record Number (Access has for all displayed recordsets) and a [Recording ID] which is the AutoNumber field in your table.

Anyway, I tried to follow your problem, but I found everything actually worked perfectly (according to the data that was there - I deleted some records at the end to match the recourd numbers you stated).

The largest value used for MHA was 08 and it produced UH.MHA.09.028.0 584. The largest value for UH was 027.

I think if you clear up your data you will see it start to work properly for you. The data had some repeats in it, so the count of items didn't match the items used, but from the items used, it selected the correct new values.

By Record ID I meant Record Number. Sorry for the confusion. I will be more careful in using correct term(s).

Well, I am sorry to report it didn't work. please see the attached DB.

Record Numbers: 584 & 585 worked okay (both of these records were from category Ghazal (GH) for artist Mehdi Hassan (MHA). But when I selected Urdu/Hindi for the same artist, that's when it goofed up. For Record Number: 586 it generated: UH.MHA.11.028.0 586 the correct serial number should be UH.MHA.04.028.0 586.

For the next record I selected MHA in category GH. It popped a serial number that reads: GH.MHA.12.025.0 587 when the correct number should be GH.MHA.11.025.0 587

Because it is the 11th CD of Mehhi Hassan in Category Ghazal. 10th CD was Record Number 585.

Do you see what I am trying to explain?

In addition to the database I am attaching an Excel file. For a better visibility, Mehdi Hassan's CDs in these two categories (GH & UH) are highlighted in blue in this file.

**MNNovice** · Oct 9 '08, 01:45 PM

Originally posted by NeoPa

Last post for the night (probably).

I have a couple of queries I use when checking your data. They show me what SHOULD be used in the next record created (by showing the related records that already exist). Actually, they're both relatively simple :

Code:

SELECT tblCDDetails.*
FROM tblCDDetails
WHERE Left([SerialNumber],2))=[Enter Music Category Code (2 chars)]

Code:

SELECT tblCDDetails.*
FROM tblCDDetails
WHERE Mid([SerialNumber],6,3))=[Enter Artist Code (3 chars)]

Perhaps it's stupid to ask this question, but where do these commands should be inserted? And also, I am not sure I quite understand the logic behind this change. Although I am positive and confident about your expertise. It's just for my own understanding :) Thanks.

**MNNovice** · Oct 9 '08, 01:46 PM

Originally posted by NeoPa

I did notice that the new record was added as soon as I left the [RecordingArtist ID] control, so reordering the controls on the form wouldn't hurt (not very intuitive behaviour).

I will include in here the whole of the module as I have it (reformatted). It won't display well necessarily, but if you reply to the post then copy the code from within the quote, it should work for you.

Code:

Option Compare Database
Option Explicit

Private Sub Form_Current()
  With Me
    If .NewRecord Then Call .RecordingTitle.SetFocus
    .MusicCategoryID.Locked = Not .NewRecord
    .RecordingArtistID.Locked = Not .NewRecord
    .Duet_Trio.Locked = Not .NewRecord
  End With
      
End Sub

Private Function GetCDKey() As String
  Dim strCat As String, strArt As String, strVal As String
  Dim intVal As Integer

  GetCDKey = ""
  If IsNull(Me.MusicCategoryID) _
  Or IsNull(Me.RecordingArtistID) Then Exit Function

  strCat = Me.MusicCategoryID.Column(2)
  strArt = Me.RecordingArtistID.Column(2)
  GetCDKey = "%C.%A.%2.%3.%4"
  GetCDKey = Replace(GetCDKey, "%C", strCat)
  GetCDKey = Replace(GetCDKey, "%A", strArt)
  intVal = Val(Nz(DMax(Expr:="Mid([SerialNumber],8,2)", _
                       Domain:="[tblCDDetails]", _
                       Criteria:="[SerialNumber] Like " & _
                                 "'*." & strArt & ".*'"), "0"))
  GetCDKey = Replace(GetCDKey, "%2", Fmt(intVal + 1, 2))
  intVal = Val(Nz(DMax(Expr:="Mid([SerialNumber],11,3)", _
                       Domain:="[tblCDDetails]", _
                       Criteria:="[SerialNumber] Like " & _
                                 "'" & strCat & ".*'"), "0"))
  GetCDKey = Replace(GetCDKey, "%3", Fmt(intVal + 1, 3))
  intVal = Val(Nz(DMax(Expr:="Right([SerialNumber],4)", _
                       Domain:="[tblCDDetails]"), "0"))
  GetCDKey = Replace(GetCDKey, "%4", Fmt(intVal + 1, 4))
End Function

Private Function Fmt(intVal As Integer, intDigits As String) As String
  Fmt = Right(10000 + intVal, intDigits)
End Function

Private Sub RecordingArtistID_AfterUpdate()
  Me.TxtSerialNumber = GetCDKey()
End Sub

Private Sub RecordingArtistID_NotInList(NewData As String, Response As Integer)
    MsgBox "Double-click this field to add an entry to the list."
    Response = acDataErrContinue
End Sub

Private Sub RecordingArtistID_DblClick(Cancel As Integer)
On Error GoTo Err_RecordingArtistID_DblClick
    Dim lngRecordingArtistID As Long

    If IsNull(Me![RecordingArtistID]) Then
        Me![RecordingArtistID].Text = ""
    Else
        lngRecordingArtistID = Me![RecordingArtistID]
        Me![RecordingArtistID] = Null
    End If
    DoCmd.OpenForm "Artists", , , , , acDialog, "GotoNew"
    Me![RecordingArtistID].Requery
    If lngRecordingArtistID <> 0 Then Me![RecordingArtistID] = lngRecordingArtistID

Exit_RecordingArtistID_DblClick:
    Exit Sub

Err_RecordingArtistID_DblClick:
    MsgBox Err.Description
    Resume Exit_RecordingArtistID_DblClick
End Sub

Private Sub MusicCategoryID_AfterUpdate()
  Me.TxtSerialNumber = GetCDKey()
End Sub

Private Sub MusicCategoryID_NotInList(NewData As String, Response As Integer)
    MsgBox "Double-click this field to add an entry to the list."
    Response = acDataErrContinue
End Sub

Private Sub MusicCategoryID_DblClick(Cancel As Integer)
On Error GoTo Err_MusicCategoryID_DblClick
    Dim lngRecordingArtistID As Long

    If IsNull(Me![MusicCategoryID]) Then
        Me![MusicCategoryID].Text = ""
    Else
        lngRecordingArtistID = Me![MusicCategoryID]
        Me![MusicCategoryID] = Null
    End If
    DoCmd.OpenForm "frmCategories", , , , , acDialog, "GotoNew"
    Me![MusicCategoryID].Requery
    If lngRecordingArtistID <> 0 Then Me![MusicCategoryID] = lngRecordingArtistID

Exit_MusicCategoryID_DblClick:
    Exit Sub

Err_MusicCategoryID_DblClick:
    MsgBox Err.Description
    Resume Exit_MusicCategoryID_DblClick
End Sub

Private Sub cmdAddSong_Click()
On Error GoTo Err_cmdAddSong_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmSongs"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdAddSong_Click:
    Exit Sub

Err_cmdAddSong_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddSong_Click
    
End Sub

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdSave_Click:
    Exit Sub

Err_cmdSave_Click:
    MsgBox Err.Description
    Resume Exit_cmdSave_Click
    
End Sub

Private Sub cmdFindCD_Click()
On Error GoTo Err_cmdFindCD_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmFIND"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdFindCD_Click:
    Exit Sub

Err_cmdFindCD_Click:
    MsgBox Err.Description
    Resume Exit_cmdFindCD_Click
    
End Sub

PS. I find I can copy this code accurately on FireFox 3. It may well work for you but check it does first. Indenting IS important.

Copied as instructed. Thanks.

**NeoPa** · Oct 9 '08, 03:18 PM

Originally posted by MNNovice

Perhaps it's stupid to ask this question, but where do these commands should be inserted? And also, I am not sure I quite understand the logic behind this change. Although I am positive and confident about your expertise. It's just for my own understanding :) Thanks.

Not at all.

This is fairly straightforward though. Once you've done it a couple of times you will easily be able to switch between Design View of an Access QueryDef and SQL view.

It seems that mostly, we have been looking at Access VBA issues in this thread, but it's common also to deal with queries (QueryDefs). An Access query, while it is generally displayed in Design View to modify, can also be shown in SQL View (Every QueryDef has a set of SQL behind the scenes).

When a QueryDef is shown in SQL View it is possible to copy and/or paste SQL code (as I posted in #113) into or from it. If you create a new query, then switch to SQL View (View / SQL) you can paste in the SQL posted. If you then change to Design View (View / Design) you will see it in Design View in the normal way.

Let me know how you get on with this.

**MNNovice** · Oct 9 '08, 05:49 PM

Well, this much I understood. That you created a couple of queries to look into my data. These queries are displayed in SQL view in post 113. I don't have to add these commands, queries anywhere. Right?

I am awaiting your response to my post #114. Thanks.

**NeoPa** · Oct 9 '08, 06:30 PM

Originally posted by MNNovice

Well, this much I understood. That you created a couple of queries to look into my data. These queries are displayed in SQL view in post 113. I don't have to add these commands, queries anywhere. Right?

I run these queries while I am looking at the values that are produced in the [SerialNumber] field. With all the matching records visible in the query, I can work out for myself what the code SHOULD be producing and compare that with what it does produce.

Remember, it is not the count but the Max() that we're looking for. In an ideal world (database) they would be the same, but while the data is not all properly cleaned up yet, there are discrepancies. These queries help to see what the code should be producing.

**NeoPa** · Oct 9 '08, 06:32 PM

Originally posted by MNNovice

I am awaiting your response to my post #114. Thanks.

I will get to this. As you know I can only look at your database at home. I have just got home but I have to go out again this evening (shortly), so I will attempt to make some time later. It's not always possible to catch up with all my threads every day I'm afraid, but I do try.

I certainly have the two latest attachments downloaded now at least.

Formulate Index Value

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment