Formulate Index Value

[NeoPa]

I find I'm too tired to work tonight. I will have a more in-depth look at exactly what's happening with the database tomorrow or over the weekend when I can. Sorry for the extra delay.

[NeoPa]

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.

This seems wrong.

.04. would imply that there were only three previous CDs by Mehdi Hassan. This can't be true as you have just added the tenth in record number 585.

PS. I will investigate further - but I think this is a fundamental problem.

[NeoPa]

I tried to use the SQL I posted in post #115 - BUT THEY BOTH FAILED!!

Sorry that was so poor. I've fixed them both now and 'now' they both work fine.

PS. You may get a better idea of how they can help, now you can see them working correctly.

[NeoPa]

I want the serial number to read as XX.YYY.00.000.0 000
XX = 2 letter from the category
YYY = 3 letter from the artist
00 = running number of CD for that artist
000 = running number for that category
0000 = running number for the entire collection.

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.

From your post #114 (the part quoted in post #122), and from looking at the data you're referring to, it seems to me that maybe you don't want the third part of the Serial Number to be "00 = running number of CD for that artist", but instead you want it to be "00 = running number of CD for that artist WITHIN THE SELECTED CATEGORY". Is that right?

[MNNovice]

From your post #114 (the part quoted in post #122), and from looking at the data you're referring to, it seems to me that maybe you don't want the third part of the Serial Number to be "00 = running number of CD for that artist", but instead you want it to be "00 = running number of CD for that artist WITHIN THE SELECTED CATEGORY". Is that right?

Sorry I was too busy this weekend to read through all your comments. I will shortly do so.

You are too cool. You are absolutely correct. I want "00 = running number of CD for a given artist within the selected category".

[NeoPa]

No worries about busy-ness :)

I will look at the code in this fresh light. It might be a little more complicated, but it can certainly be done.

I will post further when I've had a chance to look into it.

[NeoPa]

Try the following version of the function :

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)
  strArt = strCat & "." & 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

[MNNovice]

[QUOTE]

Try the following version of the function :

This is just too good to be true. You are a genius! I guess you already know that. How can I ever put into words how thankful I am for your generous helping hands! Please accept my sincere gratitudes for your time and effort. I am grateful.

Can we talk about my next problem? It's rather a simple one.

On frmCDDetails, when I double click on [SongTitleID] on sfrmCDDetails to add a new song, I get this error message: "You tried to assign the Null Value to a variable that is not a variant data type". I don't understand what does this mean.

So I added a command button: ADD NEW Song which opens the form to add a new song. But I need to close the form frmCDDetails in order for the new song to show up on the dropdown list. This is so inefficient.

How can I solve this problem?

Once again many thanks for your help. It's much appreciated.

[NeoPa]

Instead of closing and re-opening the form, try a call of {form reference}.ReQu ery.

[MNNovice]

Instead of closing and re-opening the form, try a call of {form reference}.ReQu ery.

Well, I thought that's what I did. But it does not work. Here are my codes:

Private Sub ArtistID_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 SongTitleID_DblClick(Cancel As Integer)
On Error GoTo Err_SongTitleID_DblClick
    Dim lngSongID As Long

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

Exit_SongTitleID_DblClick:
    Exit Sub

Err_SongTitleID_DblClick:
    MsgBox Err.Description
    Resume Exit_SongTitleID_DblClick
End Sub

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

It's in a sub form (sfrmCDDetails) within the the frmCDDetails. Field name: [SongTitleID].

I have exactly the same codes for updating MusicCategoryID and it works just fine. What am I doing wrong here?

Thanks.

[NeoPa]

Let me throw the question back at you.

Is it line #18 you're talking about?

Exactly what are you trying to update on the screen? Which object? Is it a control - or a form?

[MNNovice]

May be I didn't quite explain my problem clearly. Sorry about that.

frmCDDetails is used to enter/update CDs. Within this Form I have a subform called sfrmCDDetails. When I add/update a CD, I use this subform to enter Track No., Song Title, Artists etc.

There is another form called frmSongs based on a table called tblSongs that lists songs of all kinds. When I am using sfrm to enter/update tracks of a CD, if a song does not exist in my list (ie., tblSongs), I would like to call/open frmSongs, add the song to the list and continue with my data entry on frmCDDetails and/or sfrmCDDetails. My objective is to update tblSongs so that the new song now appears in the dropdown list on the sfrmCDDetails without my exiting the frmCDDetails.

Does this make sense? Thanks.

[NeoPa]

It does make sense, but it is also harder for me to work with than simple answers to the questions. Let me explain as I'm not trying to be snappy.

A textual description / explanation is all very well, but to process it I have to read it all and try and fit the information into my mental picture. As it covers such a large area, my picture has to be large too, so I have to consider many angles, even though a lot of it is irrelevant to the problem.

Knowing which object (form or control) is the one that needs to be updated and the current attempt is at line #18 allows me to focus on a much smaller set of information, so I have a much smaller, more focused picture. That makes finding an answer so much simpler as I have much less elements to worry about fitting together.

Anyway, if you could confirm the piece of code you you are talking about is around line #18 and what the name of the control is (I now know it's a ComboBox control but I still don't have the name of it), then I can see what I can do to help.

[MNNovice]

It does make sense, but it is also harder for me to work with than simple answers to the questions. Let me explain as I'm not trying to be snappy.

Anyway, if you could confirm the piece of code you you are talking about is around line #18 and what the name of the control is (I now know it's a ComboBox control but I still don't have the name of it), then I can see what I can do to help.

The combo box is called [SongTitleID] and yes, it's line #18. Sorry for not knowing what's being asked. I shall be more succinct next time.

Also, I should probably open a new thread for this question, should I? Thanks.

[NeoPa]

The combo box is called [SongTitleID] and yes, it's line #18. Sorry for not knowing what's being asked. I shall be more succinct next time.

No worries. It wasn't obvious.

Also, I should probably open a new thread for this question, should I? Thanks.

You can, but I'm happy to deal with this here if you are. It's already nearly 140 posts long so as far as Googling is concerned - I doubt anyone will want to wade through it all even if they were to find it.

Besides, it's not a typical question that others are likely to be asking. It's quite specific to your particular database.