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.

[hyperpau]

You would have to do rigorous coding to do your customized text Auto Increment field. Then you would have to use the DMax function to auto populate it.

I dont' fully understand your concept but here's how i understand it.
Let's say you have 2 combo boxes:
1 cboMusicCategoryID
2 cboArtistID
and 1 textbox for your Auto Increment Serial Number.
1 txtSerialNo

Do an After Update event on your cboMusicCategoryID field:

Private Sub cboMusicCategoryID_AfterUpdate (Cancel as Integer)
Me!txtSerialNo = Me!cboMusicCategoryID & "-"
End Sub

Supposing users will only update the ArtistID after the CategoryID has already been selected, do an After Update event on your cboArtistID as well:

Private Sub cboArtistID_AfterUpdate (Cancel as Integer)
Me!txtSerialNo = Me!txtSerial & Me!cboArtistID & "-"
End Sub

Following the above codes, then as soon as you select JZ from your CategoryID dropdown combo box, then the txtbox for the Serial Number will show JZ-. Then after you update the Artist ID let's say to JDA, then your textbox will now show JZ-JDA-.

But what if you updated the ArtistID first without selecting the category ID? then you would have the incorrect serial number format so you have to foresee that and control it using this code.

Private Sub cboArtistID_AfterUpdate (Cancel as Integer)
If IsNull(Me!cboMusicCategoryID) Then
     MsgBox "You need to select the Category ID first", vbInformation, "Selection Required"
Else
Me!txtSerialNo = Me!txtSerial & Me!cboArtistID & "-"
End If
End Sub

Now we need to take care of the auto increment on the number parts.

Artist (00)
We need to find the highest number which containst the same ArtistID and add 1 to it, right?

Use

Dim lngAritst as Long
 
lngArtist = DMax(Mid(Nz(txtSerialNo, 0), 8, 2), "tblYourTable", Mid(txtSerialNo, 4, 3) = Me!cboArtistID) + 1

Category (000)

Dim lngCategory as Long
 
lngCategory = DMax(Mid(Nz(txtSerialNo, 0), 11, 3), "tblYourTable", Mid(txtSerialNo, 11, 3) = Me!cboMusicCategoryID) + 1

And Lastly, Entire Collection Number (0000)
Dim lngCollection as Long

lngCollection = DMax(Right(Nz(txtSerialNo, 0), 4), "tblYourTable") + 1

Your Done!
I will not explain each code or function but you can look it up using your VBA offline or online help so you could understand each of these. (Mid, Right, Nz)

All you need now is to assign the
lngArtist, lngCategory, and lngCollection variables to your new txtSerialNo field and concatenate it to the JZ-JDA- which is already in there.

I would suggest then to put or add these to the After Update event of you cboArtistID. Therefore your final code for this combo box will be like:

Private Sub cboArtistID_AfterUpdate (Cancel as Integer)
Dim lngArtist as Long
Dim lngCategory as Long
Dim lngCollection as Long
 
If IsNull(Me!cboMusicCategoryID) Then
     MsgBox "You need to select the Category ID first", vbInformation, "Selection Required"
Else
     lngArtist = DMax(Mid(Nz(txtSerialNo, 0), 8, 2), "tblYourTable", Mid(txtSerialNo, 4, 3) = Me!cboArtistID) + 1
     lngCategory = DMax(Mid(Nz(txtSerialNo, 0), 11, 3), "tblYourTable", Mid(txtSerialNo, 11, 3) = Me!cboMusicCategoryID) + 1
     lngCollection = DMax(Right(Nz(txtSerialNo, 0), 4), "tblYourTable") + 1
 
 
     Me!txtSerialNo = Me!txtSerial & Me!cboArtistID & "-" & Format(lngArtist, "00") & "." & Format(lngCategory, "000") & "." & Format(lngCollection, "0000")
End If
End Sub

You can again use you offline/online help to understand the Format function I have used. Hope this helps.

[MNNovice]

Thank you so much for taking the trouble in helping me.

As you said, it's going to be rigoruous but I will definitely give it a try. By no means I understand VBA thoroughly but I will make an effort. With my limited knowledge of VBA I can tell you showed me the correct way to coding. Let's see.

Shall keep you posted.

Many thanks.

[hyperpau]

No problem. I hope you find the answer.
I started like you are and I learned most of what I know now through self study using the MS Access Help, and OF COURSE, www.bytes.com

Welcome to the forum.

[MNNovice]

Hi. It's me again.

Before I describe what happened when I followed the instructions, let me tell you about the table structures.

tblCategories
MusicCategoryID: Auto
MusicCategory: Text
MusicCategoryAbbv:Text

tblArtist
ArtistID: Auto
ArtistName: Text
ArtistAbbv: Text

These two tables are connected to tblCDDetails where I used MusicCategoryID and ArtistID as numbers with a look up Combo box, cboMusicCategoryID and cboArtistID.

The form I am using to enter the CD data is called frmCDDetails which is based on tblCDDetails. So basically when I click on Music Category I get to see the list of category and choose a category as needed. Same with the artist name.

When I added the following:

Private Sub cboMusicCategoryID_AfterUpdate (Cancel as Integer)
Me!txtSerialNo = Me!cboMusicCategoryID & "-"
End Sub 

The Code works but the txtSerialNo updates and reads as a number. e.g., 1 for CO (country) or 2 for JZ (Jazz). This is perhaps nowhere in tblCDDetails I have a field that pulls JZ or CO etc. Instead, it pulls the cateogry name such as Jazz or Country. How do I accomplish this?

When I entered the codes you sent to include as an AfterUpdate for the cboArtistID it didn't work. I got the following error messages:

"The expressiono n Curent you entered as the event property setting produced the following error. Procedure declaration dosw not match description of event or procedure having the same name.

The expression may not result in the name of a macro, the name of a user-defined function, or (Event Procedure], There may have been an error evaluating the function, event or macro"


I didn't put the SQL you sent on "Current" but AfterUPdate, don't know why I got the message. I only replaced the word "tblyourTable" with "tblCDDetails".

What am I doing wrong?

Please help. As always, thanks in advance.
I appreciate.

[hyperpau]

I see, i overlooked that one, this is because the bound column of your combo boxes are bound to the 1st column which is the Primary key.


Just modify the code to tell Access to look up the 2nd column instead.

Private Sub cboMusicCategoryID_AfterUpdate (Cancel as Integer)
Me!txtSerialNo = Me!cboMusicCategoryID.Columns(2) & "-"
End Sub 

Do this as well to the ArtistID.

[MNNovice]

Thanks for your guidence.

I modified the SQL to read as

Private Sub cboMusicCategoryID_AfterUpdate (Cancel as Integer)
Me!txtSerialNo = Me!cboMusicCategoryID.Column(3) & "-"
End Sub 

But I ran into new problem. The result was only the hyphen got displayed. Is it because my column 3 exists on tblMusicCategory and NOT in tblCDDetails?

Also, can you please help me understand this lingo
Dim lngArtist = as Long

What does it mean?

I tried to understand NZ function but didn't quite understand it. Perhaps I am not as smart as I should be.

Lastly, I would like to add a SEARCH form on frmCDDetails. This should help me locate if a CD is already entered into the database or not. How do I do this?

Thanks for your help.

[MNNovice]

Hi, It's me again.

On this site I found detailed Codes to create a Search Form. I modified it to my database. When I click on the Search button (after typing an existing CD tile as RecordingTitle), I get the seven columns I designed but I don't get the result to display. What is wrong with my coding?

 
Private Sub SearchBtn_Click()
    Dim strSQL As String
    Dim Criteria As String
 
    strSQL = "SELECT RecordingID as ID, RecordingTitle, CategoryID, TypeID, RecordingArtistID" & _
             "FROM tblCDDetails WHERE "
    If IsNull(Me.RecordingTitle) And _
       IsNull(Me.MusicCategoryID) And _
       IsNull(Me.TypeID) And _
       IsNull(Me.RecordingArtistID) Then
            MsgBox "Must Enter at least one value in " & _
                   "order to search database.", vbOKOnly
    Else
        If Not IsNull(Me.RecordingTitle) Then
            If Len(Criteria) > 0 Then
                Criteria = Criteria & " AND RecordingTitle = '" & Me.RecordingTitle & "'"
            Else
                Criteria = Criteria & "RecordingTitle = '" & Me.RecordingTitle & "'"
            End If
        End If
        If Not IsNull(Me.MusicCategoryID) Then
            If Len(Criteria) > 0 Then
                Criteria = Criteria & " AND MusicCategoryID = '" & Me.MusicCategoryID & "'"
            Else
                Criteria = Criteria & "MusicCategoryID = '" & Me.MusicCategoryID & "'"
            End If
        End If
        If Not IsNull(Me.TypeID) Then
            If Len(Criteria) > 0 Then
                Criteria = Criteria & " AND TypeID = '" & Me.TypeID & "'"
            Else
                Criteria = Criteria & "TypeID = '" & Me.TypeID & "'"
            End If
        End If
        If Not IsNull(Me.RecordingArtistID) Then
            If Len(Criteria) > 0 Then
                Criteria = Criteria & " AND RecordingArtistID = '" & Me.RecordingArtistID & "'"
            Else
                Criteria = Criteria & "RecordingArtistID = '" & Me.RecordingArtistID & "'"
            End If
        End If
 
        strSQL = strSQL & Criteria
        Me.ResultList.ColumnCount = 7
        Me.ResultList.BoundColumn = 1
        Me.ResultList.ColumnHeads = True
        Me.ResultList.ColumnWidths = "720;1440;1440;720;720;720;720"
        Me.ResultList.RowSourceType = "Table/Query"
        Me.ResultList.RowSource = strSQL
        Me.ResultList.Requery
    End If
End Sub
 

Any help you can offer will be much appreciated. Thanks.

[NeoPa]

Right. I'm coming to this late, in response to an invitation from the OP in another thread.

I have no intention of stepping on any toes, and I have not read through everything that has progressed to date. That conversation is too complicated for me to get involved in and I don't wish to muddy any waters.

I will approach this from scratch. Possibly clarifying, but possibly confusing still further. If the latter, then please simply allow this to drop and I won't feel insulted.

Anyway, on to the question.

...
I want the serial number to read as XX.YYY.00.000.0000

1. XX = 2 letter from the category
2. YYY = 3 letter from the artist
3. 00 = running number of CD for that artist
4. 000 = running number for that category
5. 0000 = running number for the entire collection.

Example: CO.JDA.10.030.0275
...

I have slightly adjusted the quoted excerpt to add codes to identify each element.
As far as I can tell (guess) the form where the data is added to the database already has ComboBoxes where items A and B are selected.

I assume that the values for C, D & E should reflect the values current at the time the item is actually added in. IE. If John Denver had a record that was classified as country and this were entered first, then it would be CO.JDA.01.001.0001. If the next two CDs to be added are an Easy Listening by Frank Sinatra followed by another Easy Listening by John Denver then these would be added as EL.FSC.01.001.0002 and EL.JDA.02.002.003 respectively. Just to finish, another Easy Listening by Frank Sinatra would be EL.FSC.02.003.0004.

Now, filling a TextBox with the proposed index value at this time is a problem (in a multi-user environment) as two users may be entering CDs at the same time. If they both work out what the index ought to be before either has submitted the CD for addition, then all of the last three elements (C, D & E) may be saved with incorrect values. E will be in all situations (for the second of the two to be submitted). It may be possible to get around this by simply displaying a (non-confirmed) value which is worked out again at the actual time of submitting the CD.

I propose a function (GetCDKey()) which is called in the AfterUpdate() event procedures of both [cboMusicCategoryID] and [cboArtistID], as well as during the procedure where the record is added. GetCDKey() would use both items to work out the correct current value for the index. I considered using DCount() for this (which would be simpler) but in case of retrospective deletes (not all possible numbers still in use), I decided that a more complicated version of DMax() was required. I am assuming the field that stores this index value in tblCDDetails is named [CDID].

Private Function GetCDKey() As String
  Dim strCat As String, strArt As String, strVal As String
  Dim intVal As Integer
 
  strCat = Me.cboMusicCategoryID
  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([CDID],8,2)", _
                       Domain:="[tblCDDetails]", _
                       Criteria:="[CDID] Like '*." & strArt & ".*'"), "0"))
  GetCDKey = Replace(GetCDKey, "%2", Format(intVal + 1, "00"))
  intVal = Val(Nz(DMax(Expr:="Mid([CDID],11,3)", _
                       Domain:="[tblCDDetails]", _
                       Criteria:="[CDID] Like strCat & ".*'"), "0"))
  GetCDKey = Replace(GetCDKey, "%3", Format(intVal + 1, "000"))
  intVal = Val(Nz(DMax(Expr:="Mid([CDID],15,4)", _
                       Domain:="[tblCDDetails]"), "0"))
  GetCDKey = Replace(GetCDKey, "%4", Format(intVal + 1, "0000"))
End Function

[NeoPa]

I don't have a rig to test this on so this is all untested code.

Let me know if this is any help.

[NeoPa]

You could even replace lines #7 through #9 with :

GetCDKey = Join(Array(strCat, strArt, "%2", "%3", "%4"), ".")

Depending on your preference.

Have fun :)

[MNNovice]

I shall give it a try and shall keep you posted. Many thanks.

[NeoPa]

Please do. It's always good to hear how something turns out :)

[MNNovice]

Hi NeoPa:

A. “I am assuming the field that stores this index value in tblCDDetails is named [CDID]”.

No. ItblCDDetails contains a field called txtSerialNumber which is a text field. I would like to replace it with CDID as you suggested. Before I do, my questions are:

1. What should be the properties for these field?
a. Number, or Text?
b. Indexed “YES” (No Duplication)?

2. Can I add this field at this juncture of my database? What goes in this field, how do I populate it with in the table? or Does it get populated as data are entered into the form?

B. “I propose a function (GetCDKey()) which is called in the AfterUpdate() event procedures of both [cboMusicCategoryID] and [cboArtistID], as well as during the procedure where the record is added. GetCDKey() would use both items to work out the correct current value for the index. I considered using DCount() for this (which would be simpler) but in case of retrospective deletes (not all possible numbers still in use), I decided that a more complicated version of DMax() was required.”

How do I write the codes to call in GetCDKey in the AfterUpdate of MusicCategoryID & ArtistID?

1. Private Sub MusicCategoryID_AfterUpdate()
2. WHAT GOES HERE?
3. End Sub

Forgive me but I am just starting out and don’t know much about SQL commands or Access formulas.


C. “As far as I can tell (guess) the form where the data is added to the database already has ComboBoxes where items A and B are selected.”

No I don’t. I created a text box for the Music Cuategory and in its control source I used a Choose function to pull in the two letters for each category. So when I select “Country” as a category for the MusicCategoryID combo box, the textbox called txtCategory gets updated as “CO”. This text box is working okay.

However, I couldn’t do this for the artist abbreviation. Here is, I think, why. frmCDDetails has a subform called sfrmCDDetails. This is where I have the ArtistID field. Because some of the CDs has more than one singer/artist. The sub form is designed to record, 1)track no. 2) song title, 3) artist’s name.

I created a combo box cboArtistAbbv and am trying to populate this with the three letters abbreviation for an aritist’s name for CDs with Solo singer. Now, I don’t know how to do that.

Lastly, The codes you sent me, Where do these go? I mean where do I enter them? Do I enter them in GetCDKey_AfterUpdate()? Sorry if I sound stupid. I am really new at these things.

[NeoPa]

1. txt as a prefix to a name generally indicates a TextBox control on a form or report.
   1. The field should be a string field of length 18.
      Indexing should be irrelevant within the field as you should be setting the field to be the Primary Index (I assume). If not (some people prefer to use AutoNumbers in ALL cases) then yes, it should be Indexed without duplicates.
   2. You must add the design of the field in now. The data should always be added in via the form.
2. Assuming you will rename the [CDID] control on your form from [txtSerialNo] to [txtCDID], then your code would look something like :
   

   Private Sub cboMusicCategoryID_AfterUpdate(Cancel as Integer)
     Me.txtCDID = GetCDKey()
   End Sub
    
   Private Sub cboArtistID_AfterUpdate(Cancel as Integer)
     Me.txtCDID = GetCDKey()
   End Sub

   Nothing to forgive. We all started somewhere, and we're here to help you to advance further.
3. This one I may have to give some more consideration to as it may even be a show-stopper. I can't for now see how your idea can possibly work as is if there can be multiple artists assigned to a CD. Ask yourself what the [CDID] would be if there were no unique Artist ID.

...
Lastly, The codes you sent me, Where do these go? I mean where do I enter them? Do I enter them in GetCDKey_AfterUpdate()? Sorry if I sound stupid. I am really new at these things.

Your last question(s) were not tagged so I needed to quote them here.
"The codes you sent me"? This refers to the code at the bottom of my post #9?
I can say they will not be posted in GetCDKey_AfterUpdate(), as that can't exist. More than that I can't say without a better understanding of what you're asking.

[MNNovice]

Thanks again. I sincerely appreciate your effort in helping me out.


I believe I understood what you tried to explain in item A & B.

As for C: Let me explain what I did.

If it is a solo CD I would like the CDID to number it as :CO.JDA.00.000.0000

If it is a duet CD the ID should read as: CO.YYY.00.000.0000
The three Y’s will indicate it’s a duet CD in the category called Country. The first two zeros will be the running number for duets.

Similarly if a CD has more than 2 singers I would like the CDID to read as: CO.ZZZ.00.000.0000

As for Artist’s name for a duet, I left it at DUET and for a CD with various artists, I added artist’s name as VARIOUS

YYY (Artist Abbv): DUET (ArtistName)
ZZZ (Artist Abbv): VARIOUS (ArtistName)

Does this make sense? Do you still think it can be the show stopper? Then I am in big trouble.

Yes, my last question referred to the codes you sent in post #9. Where do these go? I am assuming it's an Event Procedure but where do I include these? Will it be behind a button called "UPDATE DATA" or something? I am lost. Sorry. I can understand the result will be that the CDID box will be filled in with eg., CO.JDA.01.123.1234.

Thanks.

[NeoPa]

I will get to C some other time. My brain is mashed at the moment after trying to deal with a poster who's both inexperienced at Access as well as quite unable or unwilling to do any work for himself. Not ultimately a very pleasant experience at this time of night, so I'm running a little out of steam.

...
Yes, my last question referred to the codes you sent in post #9. Where do these go? I am assuming it's an Event Procedure but where do I include these? Will it be behind a button called "UPDATE DATA" or something? I am lost. Sorry. I can understand the result will be that the CDID box will be filled in with eg., CO.JDA.01.123.1234.

Thanks.

It's not exactly an event procedure, as it is not triggered by any defined event. It is actually procedural code. It runs whenever some other code requests it to.

It will be stored in the same module as the event procedures though, so it can be local to them. After all, they are where it will be called from.

Never forget that this code should be copied to your module via the clipboard though. It's not a question of whether or not an error will get in otherwise, but more how many errors will be. The clipboard is your friend.

[NeoPa]

As far as point C goes ...

I think the sensible thing for both [MusicCategoryID] and [ArtistID], is that they are determined before this process is used.

I would certainly suggest a ComboBox for the [MusicCategoryID] ([cboMusicCategoryID]) to replace your existing setup. Using a Choose() function can work, but within a databse it's like standing by your door barking at strangers when you have a dog. That's the dog's job. Let HIM do that. Let Access handle database work like matching codes with the names, and selecting only valid codes from your table. The ComboBox is designed for just that purpose.

For the ArtistID it will be a bit more complex I can see, but still you can update a TextBox ([txtArtistID]) when you know which, and how many artists are involved in the CD.

In this code then, we just need to trust that these two controls (now [cboMusicCategoryID] and [txtArtistID]) reflect the values you want included in the Index string ([CDID]).

The effect on the code in post #9 is simply that line #6 is changed to :

strArt = Me.txtArtistID

[MNNovice]

NuPa:

Here is how far I went.

1. I named it cboCategoryID. Here is what I have:

A. DATA:
Control Source: MusicCategoryID

Row Source Type: Table/Query

Row Source:
SELECT DISTINCTROW tblCategories.*, tblCategories.MusicCategory, tblCategories.MusicCategoryAbbr FROM tblCategories;

Bound Column: 3


B. EVENT
After Update:
a. Private Sub cboCategoryID_AfterUpdate(Cancel as Integer)
b. Me.txtCDID = GetCDKey()
c. End Sub

PROBLEM I encountered.

If I select 1 for Bound Column, I get the Category Name, eg., COUNTRY. If I select 2 for Bound Coulumn I get the actual category ID (which is 1 for COUNTRY). But when I select 3 and expect it to pick up the two letter abbreviation for this category, I get nothing.


I did the same thing for artist and encountered the same problem. For whatever reasons, I couldn’t get it to display the 3 letter abbreviation for an artist name.

What could be the possible error on my part? How can I solve it?

Here are my Table Structures:

tblMusicCategories: 1) MusicCategoryID, 2)MusicCategory & 3)CategoryAbbv
tblArtists: 1)ArtistID, 2)ArtistName, & 3)ArtistAbbv

2. I created an UNBOUND text box called txtCDID.


3. I added the codes you sent in Object Modules under Global Code. Didn’t get any error message. Thank God for that clipboard.


The text box called txtCDID still not displaying anything.

Many thanks.

[MNNovice]

NuPa:

I solved the problem on Item 1. I am referring to my post #17 or is it #18? - anyway, it was my last posting. I tried Bound column zero (0) and that did it. Now it's displaying the 2 letters for Category and 3 letters for artist. Wow!! feels good.

Now if I can make that txtCDID to display the numbers automatically, I should be in heaven. So I still need your help. Thanks.

[NeoPa]

No worries. I'll have a proper look through your post later when I have more time.

NB NeoPa is not NuPa. Curious as to why you keep using NuPa?

[MNNovice]

NeoPa
Sorry about misspelling your name. I am perhaps more dyslexic than I thought I am (smile).

Just wanted you to know where I put those procedure in my global modules. Here is the entire info.

Function IsLoaded(ByVal strFormName As String) As Integer
 ' Returns True if the specified form is open in Form view or Datasheet view.
 
    Const conObjStateClosed = 0
    Const conDesignView = 0
 
    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
        If Forms(strFormName).CurrentView <> conDesignView Then
            IsLoaded = True
        End If
    End If
 
 
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([CDID],8,2)", _
                       Domain:="[tblCDDetails]", _
                       Criteria:="[CDID] Like '*." & strArt & ".*'"), "0"))
  GetCDKey = Replace(GetCDKey, "%2", Format(intVal + 1, "00"))
  intVal = Val(Nz(DMax(Expression:="Mid([CDID],11,3)", _
                        Domain:="[tblCDDetails]", _
                       Criteria:="[CDID] Like '*." & strArt & ".*'"), "0"))
  GetCDKey = Replace(GetCDKey, "%3", Format(intVal + 1, "000"))
  intVal = Val(Nz(DMax(Expression:="Mid([CDID],15,4)", _
                       Domain:="[tblCDDetails]"), "0"))
  GetCDKey = Replace(GetCDKey, "%4", Format(intVal + 1, "0000"))
End Function
 
 
End Function

[NeoPa]

Right, let's look at a couple of problems that need fixing in your code.

1. Line #37 should be moved after line #11. That is the close of the routine (procedure).
2. Line #19 should be changed to refer to Me.txtArtistID as per the instructions in post #18.

[NeoPa]

For the RowSource of cboCategoryID try :

SELECT DISTINCTROW * FROM [tblCategories]

I think adding the extra stuff in (that you had) may just cause confusion.

The column numbers for your three columns are then 0, 1 and 2.

[NeoPa]

...
The text box called txtCDID still not displaying anything.
...

Have you added the code I suggested in post #15. This should put the code into txtCDID. It depends on cboCategoryID & txtArtistID, but should show something at least, even if they are not yet set perfectly.

[MNNovice]

Right, let's look at a couple of problems that need fixing in your code.
1. Line #37 should be moved after line #11. That is the close of the routine (procedure).

I made this change.

2. Line #19 should be changed to refer to Me.txtArtistID as per the instructions in post #18.

Since I was able to solve the problem by setting the bound column to zero, I changed the name to cboArtistID and therefore kept the code in its original form instead.

For the RowSource of cboCategoryID try :

Code: ( text )
1. SELECT DISTINCTROW * FROM [tblCategories]
I think adding the extra stuff in (that you had) may just cause confusion.

The column numbers for your three columns are then 0, 1 and 2.

I had both the combo boxes (cboCategoryID and cboArtistID) worked out. These are displaying the 2 letter for category and 3-letter for artist.

Have you added the code I suggested in post #15. This should put the code into txtCDID. It depends on cboCategoryID & txtArtistID, but should show something at least, even if they are not yet set perfectly.

Yes, I have. Please note I changed the name from txtArtistID to cboArtistID. The following codes are there.

1.    Private Sub cboMusicCategoryID_AfterUpdate(Cancel as Integer)
2.      Me.txtCDID = GetCDKey()
3.    End Sub
4.     
5.    Private Sub cboArtistID_AfterUpdate(Cancel as Integer)
6.      Me.txtCDID = GetCDKey()
7.    End Sub

Yet, nothing is displayed in txtCDID. Just to confirm I am on the right path. txtCDID is an UNBOUND text box on the form called frmCDDetails. There is a field called CDID in tblCDDetails.

With reference to the codes in posting #22:

Do think I need to change [CDID] to [txtCDID] in lines 23, 25, 27, 29, & 31? Since I don't quite understand how it works, didn't want to try it and make a bigger mess.

I am looking forward to your next set of instruction. Thanks for taking the time to teach the tricks. I appreciate.

[NeoPa]

Right, let's look at a couple of problems that need fixing in your code.
1. Line #37 should be moved after line #11. That is the close of the routine (procedure).

I made this change.

That's good

2. Line #19 should be changed to refer to Me.txtArtistID as per the instructions in post #18.

Since I was able to solve the problem by setting the bound column to zero, I changed the name to cboArtistID and therefore kept the code in its original form instead.

That's also good. You have this understood and made the sensible change.

For the RowSource of cboCategoryID try :

Code: ( text )
1. SELECT DISTINCTROW * FROM [tblCategories]
I think adding the extra stuff in (that you had) may just cause confusion.

The column numbers for your three columns are then 0, 1 and 2.

I had both the combo boxes (cboCategoryID and cboArtistID) worked out. These are displaying the 2 letter for category and 3-letter for artist.

The RowSource of [cboCategoryID] is probably working for you ok. I just felt you were probably repeating some columns unnecessarily. This shouldn't cause you any problems, but I couldn't see why anything more than the simple table was needed. In fact, possibly just selecting the table rather than putting SQL in there at all would work. That doesn't matter as what you have works.

Have you added the code I suggested in post #15. This should put the code into txtCDID. It depends on cboCategoryID & txtArtistID, but should show something at least, even if they are not yet set perfectly.

Yes, I have. Please note I changed the name from txtArtistID to cboArtistID. The following codes are there.

Private Sub cboMusicCategoryID_AfterUpdate(Cancel as Integer)
  Me.txtCDID = GetCDKey()
End Sub
 
Private Sub cboArtistID_AfterUpdate(Cancel as Integer)
  Me.txtCDID = GetCDKey()
End Sub

Yet, nothing is displayed in txtCDID. Just to confirm I am on the right path. txtCDID is an UNBOUND text box on the form called frmCDDetails. There is a field called CDID in tblCDDetails.

You may have missed one minor detail :-
cboMusicCategoryID_AfterUpdate() should now be cboCategoryID_AfterUpdate(), as you've used a different name for the control.

With reference to the codes in posting #22:

Do think I need to change [CDID] to [txtCDID] in lines 23, 25, 27, 29, & 31? Since I don't quite understand how it works, didn't want to try it and make a bigger mess.

Sensible question, but the answer is no.
Domain Aggregat functions deal with elements related to your record source. In this case a table. [CDID] is a field in the table. Me.txtCDID is a control on your form. We are asking it to produce results from your table (related to field [CDID]).

Another point worth making about the code in your post #22 :-
This should be in the same module as the AfterUpdate code above. That is, the form's module.

I am looking forward to your next set of instruction. Thanks for taking the time to teach the tricks. I appreciate.

I hope this makes things a little clearer. Stick with it as it's all good progress for you :)

[MNNovice]

NeuPa

Sensible question, but the answer is no.
Domain Aggregat functions deal with elements related to your record source. In this case a table. [CDID] is a field in the table. Me.txtCDID is a control on your form. We are asking it to produce results from your table (related to field [CDID]).

As I read these comments, I am a little confused. Let me explain. Quoting from an earlier posting Post #15

A. txt as a prefix to a name generally indicates a TextBox control on a form or report.
1. The field should be a string field of length 18.
Indexing should be irrelevant within the field as you should be setting the field to be the Primary Index (I assume). If not (some people prefer to use AutoNumbers in ALL cases) then yes, it should be Indexed without duplicates.
2. You must add the design of the field in now. The data should always be added in via the form.

Right now I have a field called [RecordingTitle] but I don’t have to put any data on the table. As I enter a CD title using txtCDTitle on the frmCDDetails, this field, ie, [RecordingTitle] automatically gets populated/updated.

When I read Item A. 2 above, I thought similar thing was going to happen to [CDID]. My understanding was that the two combo boxes we created cboCategoryID and cboArtistID will call in a procedure called GetCDKey. Then as I enter a category, the txtCDID will be automatically generate the 2-letter identifying the category followed by the 3-letter for the artist. This information then will be automatically fill in the field [CDID] in tblCDDetails.

Am I mistaken?

You may have missed one minor detail :-
cboMusicCategoryID_AfterUpdate() should now be cboCategoryID_AfterUpdate(), as you've used a different name for the control.

Sorry for this inadvertent typo. I actually have the correct name for this control.

Another point worth making about the code in your post #22 :-
This should be in the same module as the AfterUpdate code above. That is, the form's module.

I selected the Form_AfterUpdate for frmCDDetails. Is this where these codes need to go?
1.

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

Still nothing displays in txtCDID. Ooooooh! I wish I were smarter.

Thanks.

[NeoPa]

...

As I read these comments, I am a little confused. Let me explain. Quoting from an earlier posting Post #15

A. txt as a prefix to a name generally indicates a TextBox control on a form or report.

1. The field should be a string field of length 18.
Indexing should be irrelevant within the field as you should be setting the field to be the Primary Index (I assume). If not (some people prefer to use AutoNumbers in ALL cases) then yes, it should be Indexed without duplicates.
2. You must add the design of the field in now. The data should always be added in via the form.

Right now I have a field called [RecordingTitle] but I don’t have to put any data on the table. As I enter a CD title using txtCDTitle on the frmCDDetails, this field, ie, [RecordingTitle] automatically gets populated/updated.

When I read Item A. 2 above, I thought similar thing was going to happen to [CDID]. My understanding was that the two combo boxes we created cboCategoryID and cboArtistID will call in a procedure called GetCDKey. Then as I enter a category, the txtCDID will be automatically generate the 2-letter identifying the category followed by the 3-letter for the artist. This information then will be automatically fill in the field [CDID] in tblCDDetails.

Am I mistaken?
...

Not exactly. That is the plan.

However, this does depend on txtCDID being a BOUND control on your form.

NB. When this control is updated, it treats the record as changed, even if strictly, you haven't entered anything into any of the bound controls. Does that make sense?

...
I selected the Form_AfterUpdate for frmCDDetails. Is this where these codes need to go?

Private Sub Form_AfterUpdate()
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
 
End Sub

Still nothing displays in txtCDID. Ooooooh! I wish I were smarter.

Thanks.

Two points here :

1. You have added the GetCDKey() function within the Form_AfterUpdate() subroutine. This is not right. They are both at the same level. Try adding it after the End Sub line.
2. As a general rule, it is always expected that you try to compile (Alt-D {Debug} L {Compile Project Name}) the project before posting the code you're using. This will avoid your wasting time asking us for simple things which are easily resolved by compiling. If at that point you can't get it to compile then post. At least we will also know that the code doesn't compile. Important information.

[MNNovice]

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

1. Not exactly. That is the plan.

However, this does depend on txtCDID being a BOUND control on your form.

NB. When this control is updated, it treats the record as changed, even if strictly, you haven't entered anything into any of the bound controls. Does that make sense?

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.

2. You have added the GetCDKey() function within the Form_AfterUpdate() subroutine. This is not right. They are both at the same level. Try adding it after the End Sub line.

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.

3. As a general rule, it is always expected that you try to compile (Alt-D {Debug} L {Compile Project Name}) the project before posting the code you're using. This will avoid your wasting time asking us for simple things which are easily resolved by compiling. If at that point you can't get it to compile then post. At least we will also know that the code doesn't compile. Important information.

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.

[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_AfterUpdate() 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.0000 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]

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.

[NeoPa]

Did you run a Compact & Repair on the db before zipping it. This can also save much space.

If the size is small enough you can attach it to a post (assuming it's not sensitive). If it is let me know. I have been trying to arrange an email address that I can give out more freely, but it's not arranged yet.

I'll get back to you when I can, if you are unable or unwilling to leave a copy in the thread.

[NeoPa]

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.

Forget my last post - I hadn't seen this.

I will get that downloaded and shipped off home (I don't ever look at member DBs at work).

Let me know if you would like the attachment removed after I've downloaded it.

PS. Well done for attaching the db :)

[MNNovice]

Yes, please remove the attachment after you have downloaded it.

May I know where you are located? The reason I am asking you this is because if I respond to your feedback after 10am (CST, USA), I don't get the responses until the next day.

Take your time. I am avidly looking forward to your responses and comments on how did I do on my first Database job. Thanks.

[NeoPa]

This would make sense. Click on my avatar to be directed to my profile.

I will probably get a quick look tonight. Very busy evening with TWO UEFA matches including English clubs live on the TV.

Before I get to that, can you post some quick instructions as to what I need to do to get to the relevant part of your database when I open it?

[NeoPa]

May I know where you are located? The reason I am asking you this is because if I respond to your feedback after 10am (CST, USA), I don't get the responses until the next day.

Reading that again, that seems unusual. I'm GMT (or +1 ATM = CST+7) and I do some posting while at work (takes me up to 18:00 typically), particularly during my lunch, but I'm also frequently at my PC until 01:00 / 02:00 of an evening.

That's not true of every day certainly, but more often than not. Last night was a Wednesday so I was out most of the evening mind you.