Formulate Index Value

[NeoPa]

In general, when posting a reply, only include the quoted portion if it is not the previous post, and also if it is relevant to what you are posting. Otherwise the whole thread is so much harder to read.

Quoting from a previous post (sometimes only partially) can be very useful, but if not necessary, merely gets in the way.

[NeoPa]

In your database (on this form [frmCDDetails] particularly, there are a number of event procedures which are not linked.

That is to say that the code exists, with the correct name, but the control event itself (EG. RecordingArtist ID_AfterUpdate exists, but the After Update property of RecordingArtist ID is not set to [Event Procedure] as it must be.) For these to work as expected, all the event procedures must be linked in this way.

[NeoPa]

Finally for tonight, check out the following code for the GetCDKey() function and let me know what results you get :

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", Format(intVal + 1, "00"))
  intVal = Val(Nz(DMax(Expr:="Mid([SerialNumber],11,3)", _
                        Domain:="[tblCDDetails]", _
                       Criteria:="[SerialNumber] Like " & _
                                 "'*." & strArt & ".*'"), "0"))
  GetCDKey = Replace(GetCDKey, "%3", Format(intVal + 1, "000"))
  intVal = Val(Nz(DMax(Expr:="Mid([SerialNumber],15,4)", _
                       Domain:="[tblCDDetails]"), "0"))
  GetCDKey = Replace(GetCDKey, "%4", Format(intVal + 1, "0000"))
End Function

[MNNovice]

The top of your form's module, should now look like :

Option Compare Database
Option Explicit

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

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

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

This will handle locking and unlocking the three controls (remember, they should be locked in the design of the form).

PS. Until the CODE tags are sorted out properly, copy the code from the reply window only (Click Reply then select and copy the code from there).

I followed these instructions, I beleive. The file is attached to thread #78. No error message when compiled.

[MNNovice]

In your database (on this form [frmCDDetails] particularly, there are a number of event procedures which are not linked.

That is to say that the code exists, with the correct name, but the control event itself (EG. RecordingArtist ID_AfterUpdate exists, but the After Update property of RecordingArtist ID is not set to [Event Procedure] as it must be.) For these to work as expected, all the event procedures must be linked in this way.

I was going to ask you about this issue. But thought I better wait because I am already asking too much.

What happened is that I wanted to add categories and/or songs as needed. But I couldn't get the "not in list" to work out okay. The [MusicCategoryID] works okay but the [SongTitleID] does not. When I double click I get this error message:

You tried to assign the Null Value to a variable that is not a variant data type. Only God knows what that means. So I gave up on that one and created a command button cmdAddSong. It opens the form to add songs, however, I must close frmCDDetails first and then get back in to get the updated song on the drop down list. It's frustrating! But I don't know how to fix the problem.

I also have another problem with my frmFIND - but that has to wait for future discussion. I don't want to burn my welcome mat.

Thanks.

[MNNovice]

Finally for tonight, check out the following code for the GetCDKey() function and let me know what results you get :

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", Format(intVal + 1, "00"))
  intVal = Val(Nz(DMax(Expr:="Mid([SerialNumber],11,3)", _
                        Domain:="[tblCDDetails]", _
                       Criteria:="[SerialNumber] Like " & _
                                 "'*." & strArt & ".*'"), "0"))
  GetCDKey = Replace(GetCDKey, "%3", Format(intVal + 1, "000"))
  intVal = Val(Nz(DMax(Expr:="Mid([SerialNumber],15,4)", _
                       Domain:="[tblCDDetails]"), "0"))
  GetCDKey = Replace(GetCDKey, "%4", Format(intVal + 1, "0000"))
End Function

This didn't work. It compiles okay but then I get this error message: Run Type Error 13 Type mismatch and the following line gets highlighted in yellow.

GetCDKey = Replace(GetCDKe y, "%2", Format(intVal + 1, "00"))

Here is my DB. Many many thanks.

[NeoPa]

Ah,

That's what I've been getting since I can't remember how far back ;) Now I know you're up with me I can start to look at this in more detail. This is a nasty one (at least until I can find the cause).

Still at work, so unable to download (or remove) db as yet. Will get to it later though. Will also deal with all other posts then.

[NeoPa]

I followed these instructions, I beleive. The file is attached to thread #78. No error message when compiled.

That seems good as far as it goes.

We are getting to the stage where we lose duplicate controls in the form for items which only really need one when don correctly :)

[NeoPa]

I was going to ask you about this issue. But thought I better wait because I am already asking too much.

What happened is that I wanted to add categories and/or songs as needed. But I couldn't get the "not in list" to work out okay. The [MusicCategoryID] works okay but the [SongTitleID] does not.

As a general rule, it is better to focus on one problem at a time.

As far as your [SongTitleID] control goes though, I don't see it anywhere on the form (Found it later within sfrmCDDetails - see below) :S I would recommend a separate form for adding these items though. That way there is no possible confusion for the operator as to exactly what they are doing.

When I double click I get this error message:

You tried to assign the Null Value to a variable that is not a variant data type. Only God knows what that means. So I gave up on that one and created a command button cmdAddSong. It opens the form to add songs, however, I must close frmCDDetails first and then get back in to get the updated song on the drop down list. It's frustrating! But I don't know how to fix the problem.

That is fairly simple. You type in a song that is not in your list of songs. The ID returned as the value of the ComboBox must then be Null. Adding songs on the fly can work. It needs to be treated logically that's all. Let's get to that later.

I also have another problem with my frmFIND - but that has to wait for future discussion. I don't want to burn my welcome mat.

Thanks.

For ease of following, this issue should be tackled later. Too much new stuff can get confusing, and the communication could suffer from that.

[NeoPa]

This didn't work. It compiles okay but then I get this error message: Run Type Error 13 Type mismatch and the following line gets highlighted in yellow.

GetCDKey = Replace(GetCDKe y, "%2", Format(intVal + 1, "00"))

Here is my DB. Many many thanks.

Now we're getting to the nitty-gritty of the problem.

I may (or may not) be able to look at this in the detail it requires tonight. I hope so as I have a fairly busy weekend approaching. My son leaves for university, so my time might be restricted somewhat.

Anyway, the issue here as I understand it is that the Format() function is, only in certain circumstances, behaving oddly. It's almost as if there is some other Format() function which it is getting confused with. Needless to say I haven't got to the bottom of it yet, though I am determined to if humanly possible.

This problem is mine, and I don't intend to let it get away.

[NeoPa]

I've just noticed your Form_Current() procedure.

Private Sub Form_Current()
  If IsNull(Me![RecordingID]) Then
    DoCmd.GoToControl "RecordingTitle"
  End If

  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

Lines #2 through #4 are entirely unnecessary.

Line #6 does the same thing, but in a more direct way. That's why I explained in post #73 the Me.NewRecord is what you use to determine if you are at the New Record position.

The procedure should really be simply :

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

[NeoPa]

For now, replace your GetCDKey() function with the following (including the extra Fmt() function).

Remember to copy the code from within the reply.

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 " & _
                                 "'*." & strArt & ".*'"), "0"))
  GetCDKey = Replace(GetCDKey, "%3", Fmt(intVal + 1, 3))
  intVal = Val(Nz(DMax(Expr:="Mid([SerialNumber],15,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

[MNNovice]

Well - I have good news and bad news.

Good News: As soon as I entered an Artist ID, txtSerialNumber got populated automatically. The 2-letter for the category was correct, followed by 3-letter artist ID and the last four digits came out right.

Bad News: For artist it picked up a serial number for the entire collection and not for that specific category. For example, let's say I have total 3 CDs for John Denver, 2 country and 1 easy listening. Now I am entering a 4th CD in the category called Country. The Correct Number should be CO.JDA.03.....
But it returned CO.JDA.04 which is incorrect.

Secondly, I couldn't tell how it picked a number for the Category itself - it didn't make any sense and so I cannot explain.

Lastly, I was going to further investigate for other categories but it wouldn't let me overwrite my current selection. Basically it locked both the artist ID and Category ID and didn't allow me to change these data. Neither will it allow me to delete this info. I am in trouble...:)

That's all for now. Thanks.

[NeoPa]

Well - I have good news and bad news.

Good News: As soon as I entered an Artist ID, txtSerialNumber got populated automatically. The 2-letter for the category was correct, followed by 3-letter artist ID and the last four digits came out right.

That's a good start I reckon.

Visible progress is always best :)

[NeoPa]

Bad News: For artist it picked up a serial number for the entire collection and not for that specific category. For example, let's say I have total 3 CDs for John Denver, 2 country and 1 easy listening. Now I am entering a 4th CD in the category called Country. The Correct Number should be CO.JDA.03.....
But it returned CO.JDA.04 which is incorrect.

I'm very pleased to say that this is your fault rather than mine :D (phew!) I just checked out where I posted this code first (post #9) and the error was not there then. It was first introduced in post #22 (Yay - your post - hee hee).

The problem is that the second "intVal = Val(Nz(DMax(" line also uses strArt, when it should be using strCat.
I will include the whole procedure again (which now works fine by the way), but don't forget to copy from the Reply as before :

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