Formulate Index Value

[NeoPa]

Sorry to report that the codes didn't compile. I sincerely appreciate you taking the time and effort in helping me with this.
...

No worries. I'm not too surprised at this stage that the code doesn't compile. We will get it there yet though, I'm sure.

Okay. So I set the control for txtCDID BOUND to [CDID]. When you say, “When this control is updated”, I am assuming you refer to txtCDID. “It treats the record as changed”, I am assuming you are referring to [CDID]. Am I correct?

Sorry, I still don’t understand this item. Sometimes things become clearer once I see the results. But I am confident once it works, I will be able to understand the logic behind this. Until then, I will have to take your word for it.
...

When txtCDID is changed, it will be by your code, rather than the operator typing anything into it. It will treat it as if it had been typed into though. It will treat the underlying record (specifically the field [CDID]) as changed. Even though you have only selected items from UNBOUND ComboBoxes. From here it cannot move on until the change is either saved or discarded.

...
If I do what you said here (as I understand) it would look something like this:

Private Sub Form_AfterUpdate()
End Sub
Private Function GetCDKey() As String
  Dim strCat As String, strArt As String, strVal As String
  Dim intVal As Integer
 
  strCat = Me.cboCategoryID
  strArt = Me.cboArtistID
  GetCDKey = "%C.%A.%2.%3.%4"
  GetCDKey = Replace(GetCDKey, "%C", strCat)
  GetCDKey = Replace(GetCDKey, "%A", strArt)
  intVal = Val(Nz(DMax(Expression:="Mid([txtCDID],8,2)", _
                       Domain:="[tblCDDetails]", _
                       Criteria:="[txtCDID] Like '*." & strArt & ".*'"), "0"))
  GetCDKey = Replace(GetCDKey, "%2", Format(intVal + 1, "00"))
  intVal = Val(Nz(DMax(Expression:="Mid([txtCDID],11,3)", _
                       Domain:="[tblCDDetails]", _
                       Criteria:="[txtCDID] Like '*." & strArt & ".*'"), "0"))
  GetCDKey = Replace(GetCDKey, "%3", Format(intVal + 1, "000"))
  intVal = Val(Nz(DMax(Expression:="Mid([txtCDID],15,4)", _
                       Domain:="[tblCDDetails]"), "0"))
  GetCDKey = Replace(GetCDKey, "%4", Format(intVal + 1, "0000"))
End Function

What’s the purpose of line 1 & 2? There is nothing in between these two lines. Also, the codes didn’t compile. Please see below.
...

As it happens, you don't have any code in your Form_AfterUpdat e() procedure. If there is no need for it, it can simply be removed entirely (Delete lines 1 & 2).

...
I am sorry I didn’t know about this protocol. It didn’t compile and Line #7 (above) was highlighted. I got the same error message in both the places, i.e., 1)Global Codes within the modules, and 2) frmCDDetails.
Inside the help key the error message was described like this:

This error has the following causes and solutions:
• The Me keyword appeared in a standard module.
The Me keyword can't appear in a standard module because a standard module doesn't represent an object. If you copied the code in question from a class module, you have to replace the Me keyword with the specific object or form name to preserve the original reference.
• The Me keyword appeared on the left side of a Set assignment, for example:
• Set Me = MyObject ' Causes "Invalid use of Me keyword" message.
Remove the Set assignment.
Note The Me keyword can appear on the left side of a Let assignment, in which case the default property of the object represented by Me is set. For example:
Let Me = MyObject ' Valid assignment with explicit Let.
Me = MyObject ' Valid assignment with implicit Let.

I think I understand this problem, although I'm not sure how you got into this position (and I don't think you actually posted the error message - although the help info made it clear enough I think).

To confirm I understand correctly can you give me the exact name of the module the code is found in. To find this you can edit the code in the VBA Editor window (Alt-F11 from Access then F7 to select the Code Pane. You need to make sure you have THIS code visible. At this point the title of the Microsoft Visual Basic window should include the name of the module in square brackets at the end. Please post that (or the whole title) in here so that I can confirm I understand the situation aright.

[MNNovice]

NeoPa:

Here is the info you requested.

1. The error message: "Invalid use of Me Keyword".
2. Name of the module: [Global Code (Code)]

Sorry to have delayed in responding. I had a little accident when I slipped down the stairs and was confined to bed for 4 days.

Thanks.

[NeoPa]

I'm very sorry to hear that, and don't worry about it anyway. I have plenty of threads to keep me busy and all the information is in the thread anyway in case the gap is so long I need it.

Anyway, on to business. This confirms that the code has been added into a standard module, rather than the module associated with the form object.

To open the latter (so that you can move the code across with a copy/paste), simply select the [txtCDID] control from the form. Next, open the Properties pane if not already available and navigate to the Before Update property. Select [Event Procedure] from the drop-down and click on the little button to the right with the elipsis (...).

This will create some code automatically for you :

Private Sub txtCDID_BeforeUpdate(Cancel As Integer)

End Sub

Simply delete this new code and proceed to transfer the code from earlier into THIS module.

[MNNovice]

I did exactly how you wanted. I deleted the code from Modules / Global Code and added to the [txtCDID] in the form called frmCDDetails.

Private Function GetCDKey() As String
  Dim strCat As String, strArt As String, strVal As String
  Dim intVal As Integer
 
  strCat = Me.cboCategoryID
  strArt = Me.cboArtistID
  GetCDKey = "%C.%A.%2.%3.%4"
  GetCDKey = Replace(GetCDKey, "%C", strCat)
  GetCDKey = Replace(GetCDKey, "%A", strArt)
  intVal = Val(Nz(DMax(Expression:="Mid([txtCDID],8,2)", _
                       Domain:="[tblCDDetails]", _
                       Criteria:="[txtCDID] Like '*." & strArt & ".*'"), "0"))
  GetCDKey = Replace(GetCDKey, "%2", Format(intVal + 1, "00"))
  intVal = Val(Nz(DMax(Expression:="Mid([txtCDID],11,3)", _
                        Domain:="[tblCDDetails]", _
                       Criteria:="[txtCDID] Like '*." & strArt & ".*'"), "0"))
  GetCDKey = Replace(GetCDKey, "%3", Format(intVal + 1, "000"))
  intVal = Val(Nz(DMax(Expression:="Mid([txtCDID],15,4)", _
                       Domain:="[tblCDDetails]"), "0"))
  GetCDKey = Replace(GetCDKey, "%4", Format(intVal + 1, "0000"))
End Function

Now the word Expression:= is highlighted on Line # 10 and this error message is displayed: Named argument not found

Thanks a bunch.

[NeoPa]

That's my error :(

I used Expression:= instead of Expr:= as it SHOULD have been.

I have updated the code originally posted in post #9 to reflect this. If you have made no changes to that then use that, otherwise simply change those three occurrences in your module to fix the problem.

PS. Isn't the compiler helpful :)

[MNNovice]

This time it compiled okay. But still no data displays for txtCDID. I expected to display: XX.YYY.00.000.0 000 where,

XX = 2-letter abbv. for a category
YYY = 3-letter abbv. for an artist's name
00 = CD number for a given artist
000 = CD number for that category
0000 = CD number in my collection.

What's next? Yes, compilation is great.

Thanks for your help.

[NeoPa]

I suspect you may be missing the AfterUpdate routines from post #27. To help clarify, can you post in here the whole of your form's module (the one that now contains the GetCDKey() function).

[MNNovice]

Absolutely. Here it is and thanks.

Private Sub cboArtistID_AfterUpdate()
  Me.txtCDID = GetCDKey()
End Sub

Private Sub cboCategoryID_AfterUpdate()
  Me.txtCDID = GetCDKey()
End Sub

Private Sub Form_Current()
    If IsNull(Me![RecordingID]) Then
      DoCmd.GoToControl "RecordingTitle"
    End If
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_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


Private Function GetCDKey() As String
  Dim strCat As String, strArt As String, strVal As String
  Dim intVal As Integer
 
  strCat = Me.cboCategoryID
  strArt = Me.cboArtistID
  GetCDKey = "%C.%A.%2.%3.%4"
  GetCDKey = Replace(GetCDKey, "%C", strCat)
  GetCDKey = Replace(GetCDKey, "%A", strArt)
  intVal = Val(Nz(DMax(Expr:="Mid([txtCDID],8,2)", _
                       Domain:="[tblCDDetails]", _
                       Criteria:="[txtCDID] Like '*." & strArt & ".*'"), "0"))
  GetCDKey = Replace(GetCDKey, "%2", Format(intVal + 1, "00"))
  intVal = Val(Nz(DMax(Expr:="Mid([txtCDID],11,3)", _
                        Domain:="[tblCDDetails]", _
                       Criteria:="[txtCDID] Like '*." & strArt & ".*'"), "0"))
  GetCDKey = Replace(GetCDKey, "%3", Format(intVal + 1, "000"))
  intVal = Val(Nz(DMax(Expr:="Mid([txtCDID],15,4)", _
                       Domain:="[tblCDDetails]"), "0"))
  GetCDKey = Replace(GetCDKey, "%4", Format(intVal + 1, "0000"))
End Function

[NeoPa]

I can't say that I see anything wrong with this :S

Try adding the line :

Option Explicit

as the first line and see whether it compiles OK?

All modules should have this set really. It helps the compile to find dodgy code.

You should set this in all of your databases. From the VBA Window select Tools / Options and make sure Require Variable Declaration is checked in the Editor tab.

[MNNovice]

Last time I inadvertently left out the first two lines of my VBA Codes. So the first five lines are as follows:

Option Compare Database
Option Explicit
Private Sub cboArtistID_AfterUpdate()
  Me.txtCDID = GetCDKey()
End Sub....

It still didn't compile. So I followed your next instruction and when the Require Variable Declaration is checked in the Editor tab, it compiles. (What does it do? Just curious to know).

However, I still don't see anything is displayed in that text box (txtCDID). So what's next?

Thanks.

[NeoPa]

Last time I inadvertently left out the first two lines of my VBA Codes. So the first five lines are as follows:

Option Compare Database
Option Explicit
Private Sub cboArtistID_AfterUpdate()
  Me.txtCDID = GetCDKey()
End Sub....

It still didn't compile. ...

This time it compiled okay. But still no data displays for txtCDID.
...

From post #36 I thought it was already compiling ok :S

...
So I followed your next instruction and when the Require Variable Declaration is checked in the Editor tab, it compiles. (What does it do? Just curious to know).
...

Nothing extra :S I'm confused by this. What you say seems to contradict other things you say.
Setting Require Variable Declaration simply ensures that any new module created after that point is created with the Option Explicit line in it from scratch.

Option Explicit means that code is Less likely to compile, but that's what we want. We get better information to help us avoid the more basic errors.

...
However, I still don't see anything is displayed in that text box (txtCDID). So what's next?

That's a tricky question. How small is the file if you compact your database and Zip it up?

If too large we're probably looking at taking you through some debugging. Not fun from the other side of a web page :(

[MNNovice]

My database is 2,916KB. I don't know how to upload/attach a file here. Can you give me an e-mail address to reach you?

Thanks.

[NeoPa]

Have you compacted it and Zipped it?

[MNNovice]

Yes, I used WinZip and the size is now 1,067KB. How do I upload or send it to you?

Thanks.

[MNNovice]

Attachment CD Database

I found your instructions on how to post a reply with an attachment elsewhere. Let's see if I can follow these instructions accurately.

I did it. Here is a copy of my database.