Formulate Index Value

[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.0 000
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.0 000

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.1 234.

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.1 234.

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] ([cboMusicCategor yID]) 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 [cboMusicCategor yID] 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.M usicCategory, tblCategories.M usicCategoryAbb r FROM tblCategories;

Bound Column: 3


B. EVENT
After Update:
a. Private Sub cboCategoryID_A fterUpdate(Canc el 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:

tblMusicCategor ies: 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 :-
cboMusicCategor yID_AfterUpdate () should now be cboCategoryID_A fterUpdate(), 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 :-
cboMusicCategor yID_AfterUpdate () should now be cboCategoryID_A fterUpdate(), 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_AfterUpdat e 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_AfterUpdat e 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_AfterUpdat e() 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_AfterUpdat e() 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.