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 tblMusicCategor y
Field 1: MusicCategoryID - Auto Number (Primary)
Field 2: MusicCategory - text
Field 3: MusicCategoryAb bv - 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.0 000
XX = 2 letter from the category
YYY = 3 letter from the artist
00 = running number of CD for that artist
000 = running number for that category
0000 = running number for the entire collection.

Example: CO.JDA.10.030.0 275

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)MusicCategory ID 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.0 275

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 cboMusicCategor yID
2 cboArtistID
and 1 textbox for your Auto Increment Serial Number.
1 txtSerialNo

Do an After Update event on your cboMusicCategor yID field:

[CODE=VB]Private Sub cboMusicCategor yID_AfterUpdate (Cancel as Integer)
Me!txtSerialNo = Me!cboMusicCate goryID & "-"
End Sub[/CODE]

Supposing users will only update the ArtistID after the CategoryID has already been selected, do an After Update event on your cboArtistID as well:
[CODE=VB]Private Sub cboArtistID_Aft erUpdate (Cancel as Integer)
Me!txtSerialNo = Me!txtSerial & Me!cboArtistID & "-"
End Sub[/CODE]

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.
[CODE=VB]Private Sub cboArtistID_Aft erUpdate (Cancel as Integer)
If IsNull(Me!cboMu sicCategoryID) Then
MsgBox "You need to select the Category ID first", vbInformation, "Selection Required"
Else
Me!txtSerialNo = Me!txtSerial & Me!cboArtistID & "-"
End If
End Sub[/CODE]

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(t xtSerialNo, 0), 4), "tblYourTab le") + 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:

[CODE=VB]Private Sub cboArtistID_Aft erUpdate (Cancel as Integer)
Dim lngArtist as Long
Dim lngCategory as Long
Dim lngCollection as Long

If IsNull(Me!cboMu sicCategoryID) Then
MsgBox "You need to select the Category ID first", vbInformation, "Selection Required"
Else
lngArtist = DMax(Mid(Nz(txt SerialNo, 0), 8, 2), "tblYourTab le", Mid(txtSerialNo , 4, 3) = Me!cboArtistID) + 1
lngCategory = DMax(Mid(Nz(txt SerialNo, 0), 11, 3), "tblYourTab le", Mid(txtSerialNo , 11, 3) = Me!cboMusicCate goryID) + 1
lngCollection = DMax(Right(Nz(t xtSerialNo, 0), 4), "tblYourTab le") + 1


Me!txtSerialNo = Me!txtSerial & Me!cboArtistID & "-" & Format(lngArtis t, "00") & "." & Format(lngCateg ory, "000") & "." & Format(lngColle ction, "0000")
End If
End Sub[/CODE]

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
MusicCategoryAb bv: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, cboMusicCategor yID 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 "tblyourTab le" with "tblCDDetai ls".

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 tblMusicCategor y 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.0 000

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.0 275
...

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.0 001. 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.0 002 and EL.JDA.02.002.0 03 respectively. Just to finish, another Easy Listening by Frank Sinatra would be EL.FSC.02.003.0 004.

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 [cboMusicCategor yID] 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 [cboMusicCategor yID] 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_AfterU pdate()? 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_AfterU pdate()? 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_AfterU pdate(), as that can't exist. More than that I can't say without a better understanding of what you're asking.