Formulate Index Value

[MNNovice]

I have developed a database for my CD collections. I am trying to develop serial number of each CD which will populate based on data entered in a form called frmCDDetails. This form is based on a table called tblCDDetails which combines data from various tables.

Table 1 tblMusicCategory
Field 1: MusicCategoryID - Auto Number (Primary)
Field 2: MusicCategory - text
Field 3: MusicCategoryAbbv - text

Each category is uniquely given 2-letter abbreviation. Examples, Easy Listening = EL, Jazz = JZ, Classical = CL, Country = CO

Table 2 tblArtists
Field 1 ArtistID - Auto Number (Primary)
Field 2 ArtistName - text
Field 3 ArtistAbbv - text

Each artist is given a 3-letter abbreviation to identify them. for example, John Denver is JDA, Julio Iglesias is JIB, Fran Sinatra FSC etc.

I want the serial number to read as XX.YYY.00.000.0000
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.

Example: CO.JDA.10.030.0275

I
This means this CD is the 10th CD by John Denver. There are 30 CDs in Country Category and the Total CD collection is 275

I am trying to get this Serial number to be automatically populated when I select 1)MusicCategoryID and 2)ArtistID. In other words, as I select Country in the Category the text box for the Serial number will popualate as CO. and as I enter John Denver's name, the Serail number will populate as CO.JDA.10.030.0275

This is my goal.

So far I succeeded in creating the 2-letter Category part by using Choose function in a text box on the form frmCDDetails.

The rest I am struggling. For example, I couldn't get the Artist Abbreviation to display (instead the Artist ID number is displayed). Perhaps because ArtistID is not a part of the tblCDDetails but of a different table called tblArtists which is used for a subform and added to the frmCDDetails.

Can someone please help me with this?

Please remember I am a Novice and don't have much knowledge of VBA or specific functions unless it's explained with an example.

Many thanks to all the gurus out there. I am hoping one of you can help.

[MNNovice]

I will fix up the data and upload a modified version of the DB. We can proceed with our Automated Serial Number at that time.

Thank you for your wishes. Cheers.

[NeoPa]

Sounds good. I look forward to it :)

[MNNovice]

Sounds good. I look forward to it :)

Hi, I am back from vacation. Hope all is well with you and your family.

I was able to read up on your comments and incorporated the instructions you sent me. Thank you.

Here is the updated Database. I corrected some bad data that existed. The new codes that you sent were applied. The serial number is getting updated automatically but there is a problem. Please look at records 584 – 589. I entered these as a sample data. When I entered the category and the artist’s name (record ID #588), the serial number automatically pops up as, UH.MHA.09.032.0588. Except for the first two digits everything else is correct. Same with record ID #589

Before entering record # 584, in my database I had 8 CDs for MHA in Category GH and 3 CDs in Category UH. When I entered 584 – 587 I didn’t get the correct numbers for MHA’s CD number in category UH. So I went to the tblCDDetails and manually corrected these numbers. Finally I entered record #588 & 589. See below for further details on the problem.

Record ID       What’s popping up  Correct Number should be
588             UH.MHA.09.032.0588    UH.MHA.08.032.0588
589             GH.MHA.10.023.0589    GH.MHA.09.023.0589

Apparently, it’s calculating MHA’s CDs for one category and not the other. So it’s mixing up UH with GH and giving me 09 followed by 10 and keeping it all under one category GH. How do we fix it? Did I explain it okay?

Thanks for your help.

[MNNovice]

I couldn't upload the zipped file. Network problem. I shall try in a few minutes. Thanks.

[NeoPa]

As I look now, there is no attachment added :(

If you can get it added shortly I can look at it this evening when I go home :)

[NeoPa]

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]

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

[NeoPa]

Great!

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

[NeoPa]

Attachment downloaded and removed.

Now to get to the fun part :D

[NeoPa]

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]

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.0584. 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]

I did notice that the new record was added as soon as I left the [RecordingArtistID] 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.

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]

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 :

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

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

[MNNovice]

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.0584. 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.0586 the correct serial number should be UH.MHA.04.028.0586.

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

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]

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 :

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

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]

I did notice that the new record was added as soon as I left the [RecordingArtistID] 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.

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]

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]

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]

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]

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.

[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.0586 the correct serial number should be UH.MHA.04.028.0586.

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.0000
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.0586 the correct serial number should be UH.MHA.04.028.0586.

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}.ReQuery.

[MNNovice]

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

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.

[NeoPa]

The combo box is called [SongTitleID] and yes, it's line #18.

It is now clear that the problem is not the code as such - but the order (or timing) of when the code is run.

When you open a new form, the code after the OpenForm runs immediately afterwards. It is not synchronous so it doesn't wait for the form to close before continuing. This means that the requery is done before any changes are made via the form.

What you need to do is to call the Requery from the code in the opened form, as it closes. You can pass the name of the form and / or control to the new form using OpenArgs if necessary.

Does that make sense?

[MNNovice]

What you need to do is to call the Requery from the code in the opened form, as it closes. You can pass the name of the form and / or control to the new form using OpenArgs if necessary.

Does that make sense?

Sorry but this is way over my expertise/understanding of SQL. I applied similar codes (in the same order) to update Artists and Categories and those two combo boxes are working just fine namely, [RecordingArtistID] and [MusicCategoryID]. Why it does not work for [SongTitleID] - that I don't understand.

Thanks.

[NeoPa]

To answer that I would need to see the other code. I suspect it's different in some way that's not obvious to you. Maybe doesn't use a separate form to enter the new item. I don't know until I get to see the database with the problem in it. Certainly, your code for this I would NOT expect to work for the reasons outlined.

To explain again, more simply if I can, the requery, if it's to have the desired effect, needs to execute after the item is added on the form.

As the requery code is immediately after the code which opens the form, it is run immediately after the form is opened, and not after the item has been added on the form.

Let's have another copy of the database and we'll see what we can discover.

[NeoPa]

I should say I'm not in Wednesday evening and tonight might also be a little tight. I will do what I can if you post the database but it may not get done until Thursday. Obviously I'll do what I can though :)

[MNNovice]

To answer that I would need to see the other code. I suspect it's different in some way that's not obvious to you. Maybe doesn't use a separate form to enter the new item. I don't know until I get to see the database with the problem in it. Certainly, your code for this I would NOT expect to work for the reasons outlined.

To explain again, more simply if I can, the requery, if it's to have the desired effect, needs to execute after the item is added on the form.

As the requery code is immediately after the code which opens the form, it is run immediately after the form is opened, and not after the item has been added on the form.

Let's have another copy of the database and we'll see what we can discover.

Here is what I am trying to achieve.

I opened the form frmCDDetails to add a new CD. The page for Record 584 opened. I entered a CD titled "The Gold Collection Vol 1" by Karen Carpenters. Now I would like to enter the track info for this CD. For example, a track titled: "Close to you" But this song is not listed in tblSongs and therefore does not show up in the drop down list. So in order to enter the track info, I would like to click on [SongTitleID] (located in the subform sfrmCDDetails). But when I double click on this combo box [SongTitleID], I get the error message I explained earlier.

** Edit **

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.

** /Edit **


However, on the same page of this form frmCDDetails, I can add a new Music Category or a new Artist's name by double clicking the combo boxes, [MusicCategoryID] and [RecordingArtistID] respectively.

Hope this explains my problem. Looking forward to your solutions, whenever you have time.

As always, thank you very much.

[NeoPa]

Database downloaded.

Will update when I get time to look at it.

[NeoPa]

I leave this for two days and then I have to search pages of threads to find it again.

Just to let you know I'm looking at it now. Hope to post something soon.

[MNNovice]

No hurry. Take your time. Let me know if you have any questions.

I will be leaving for Iowa for the weekend. Shall return Sunday. And thank you for your help. Hope you get some time off to enjoy with family and friends.

Take care.

[NeoPa]

Please check your PMs and let me know your response.

[NeoPa]

I've just noticed (after working through some of the code) that line #17 of post #130 includes a parameter (acDialog) which means that my post #136 is not correct. Please disregard it.

[NeoPa]

If IsNull(Me![SongTitleID]) Then
    Me![SongTitleID].Text = ""
Else
    lngSongTitleID = Me![SongTitleID]
    Me![SongTitleID] = Null
End If

This is the code in your procedure where the error occurs (Line #5). I did have a fair look to see if I could see why this crashes when the same (similar) code for Artist doesn't have the same problem, but I couldn't find it easily, and I didn't want to spend too much more time on it as removing line #5 (Me![SongTitleID] = Null) seems to fix the problem. I can't see any reason for that line, but if there is one just tell me. If not, simply lose it. It does work without it as far as I can see.

[NeoPa]

I've found out why you can't delete line #5 :(

I'm struggling to understand why the control [SongTitleID] behaves so differently from the others. I think I will need to get back to this at some other time.

I've checked through the way the different (one working & one not) controls are set up and even back to the queries and tables they come from. While there are some minor differences, I can't see anything which points to why.

[MNNovice]

I've found out why you can't delete line #5 :(

I'm struggling to understand why the control [SongTitleID] behaves so differently from the others. I think I will need to get back to this at some other time.

I've checked through the way the different (one working & one not) controls are set up and even back to the queries and tables they come from. While there are some minor differences, I can't see anything which points to why.

I was able to sort out this problem. I just marked that line (#5 in your posting, #9 in this posting) a comment instead. It seems that took care of the problem. Let me know if this is not the right way to fixing this issue. Here are the revised codes and it's working now. Lots of thanks for your help.

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

[NeoPa]

I tried that first. Unfortunately it doesn't really solve the problem. It works sometimes, but not in other circumstances :(

I would say it's a worthwhile compromise for the moment though. It's better than not working at all :)

[NeoPa]

Please check your PMs and let me know your response.

Did you manage to check your PMs yet?