By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 896 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Formulate Index Value

100+
P: 418
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.
Aug 14 '08 #1
Share this Question
Share on Google+
176 Replies


hyperpau
Expert 100+
P: 184
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboMusicCategoryID_AfterUpdate (Cancel as Integer)
  2. Me!txtSerialNo = Me!cboMusicCategoryID & "-"
  3. 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboArtistID_AfterUpdate (Cancel as Integer)
  2. Me!txtSerialNo = Me!txtSerial & Me!cboArtistID & "-"
  3. 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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboArtistID_AfterUpdate (Cancel as Integer)
  2. If IsNull(Me!cboMusicCategoryID) Then
  3.      MsgBox "You need to select the Category ID first", vbInformation, "Selection Required"
  4. Else
  5. Me!txtSerialNo = Me!txtSerial & Me!cboArtistID & "-"
  6. End If
  7. 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
Expand|Select|Wrap|Line Numbers
  1. Dim lngAritst as Long
  2.  
  3. lngArtist = DMax(Mid(Nz(txtSerialNo, 0), 8, 2), "tblYourTable", Mid(txtSerialNo, 4, 3) = Me!cboArtistID) + 1

Category (000)
Expand|Select|Wrap|Line Numbers
  1. Dim lngCategory as Long
  2.  
  3. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboArtistID_AfterUpdate (Cancel as Integer)
  2. Dim lngArtist as Long
  3. Dim lngCategory as Long
  4. Dim lngCollection as Long
  5.  
  6. If IsNull(Me!cboMusicCategoryID) Then
  7.      MsgBox "You need to select the Category ID first", vbInformation, "Selection Required"
  8. Else
  9.      lngArtist = DMax(Mid(Nz(txtSerialNo, 0), 8, 2), "tblYourTable", Mid(txtSerialNo, 4, 3) = Me!cboArtistID) + 1
  10.      lngCategory = DMax(Mid(Nz(txtSerialNo, 0), 11, 3), "tblYourTable", Mid(txtSerialNo, 11, 3) = Me!cboMusicCategoryID) + 1
  11.      lngCollection = DMax(Right(Nz(txtSerialNo, 0), 4), "tblYourTable") + 1
  12.  
  13.  
  14.      Me!txtSerialNo = Me!txtSerial & Me!cboArtistID & "-" & Format(lngArtist, "00") & "." & Format(lngCategory, "000") & "." & Format(lngCollection, "0000")
  15. End If
  16. End Sub
You can again use you offline/online help to understand the Format function I have used. Hope this helps.
Aug 18 '08 #2

100+
P: 418
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.
Aug 19 '08 #3

hyperpau
Expert 100+
P: 184
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.
Aug 19 '08 #4

100+
P: 418
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboMusicCategoryID_AfterUpdate (Cancel as Integer)
  2. Me!txtSerialNo = Me!cboMusicCategoryID & "-"
  3. 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.
Aug 22 '08 #5

hyperpau
Expert 100+
P: 184
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboMusicCategoryID_AfterUpdate (Cancel as Integer)
  2. Me!txtSerialNo = Me!cboMusicCategoryID.Columns(2) & "-"
  3. End Sub 
Do this as well to the ArtistID.
Aug 22 '08 #6

100+
P: 418
Thanks for your guidence.

I modified the SQL to read as

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboMusicCategoryID_AfterUpdate (Cancel as Integer)
  2. Me!txtSerialNo = Me!cboMusicCategoryID.Column(3) & "-"
  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.
Aug 25 '08 #7

100+
P: 418
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?

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub SearchBtn_Click()
  3.     Dim strSQL As String
  4.     Dim Criteria As String
  5.  
  6.     strSQL = "SELECT RecordingID as ID, RecordingTitle, CategoryID, TypeID, RecordingArtistID" & _
  7.              "FROM tblCDDetails WHERE "
  8.     If IsNull(Me.RecordingTitle) And _
  9.        IsNull(Me.MusicCategoryID) And _
  10.        IsNull(Me.TypeID) And _
  11.        IsNull(Me.RecordingArtistID) Then
  12.             MsgBox "Must Enter at least one value in " & _
  13.                    "order to search database.", vbOKOnly
  14.     Else
  15.         If Not IsNull(Me.RecordingTitle) Then
  16.             If Len(Criteria) > 0 Then
  17.                 Criteria = Criteria & " AND RecordingTitle = '" & Me.RecordingTitle & "'"
  18.             Else
  19.                 Criteria = Criteria & "RecordingTitle = '" & Me.RecordingTitle & "'"
  20.             End If
  21.         End If
  22.         If Not IsNull(Me.MusicCategoryID) Then
  23.             If Len(Criteria) > 0 Then
  24.                 Criteria = Criteria & " AND MusicCategoryID = '" & Me.MusicCategoryID & "'"
  25.             Else
  26.                 Criteria = Criteria & "MusicCategoryID = '" & Me.MusicCategoryID & "'"
  27.             End If
  28.         End If
  29.         If Not IsNull(Me.TypeID) Then
  30.             If Len(Criteria) > 0 Then
  31.                 Criteria = Criteria & " AND TypeID = '" & Me.TypeID & "'"
  32.             Else
  33.                 Criteria = Criteria & "TypeID = '" & Me.TypeID & "'"
  34.             End If
  35.         End If
  36.         If Not IsNull(Me.RecordingArtistID) Then
  37.             If Len(Criteria) > 0 Then
  38.                 Criteria = Criteria & " AND RecordingArtistID = '" & Me.RecordingArtistID & "'"
  39.             Else
  40.                 Criteria = Criteria & "RecordingArtistID = '" & Me.RecordingArtistID & "'"
  41.             End If
  42.         End If
  43.  
  44.         strSQL = strSQL & Criteria
  45.         Me.ResultList.ColumnCount = 7
  46.         Me.ResultList.BoundColumn = 1
  47.         Me.ResultList.ColumnHeads = True
  48.         Me.ResultList.ColumnWidths = "720;1440;1440;720;720;720;720"
  49.         Me.ResultList.RowSourceType = "Table/Query"
  50.         Me.ResultList.RowSource = strSQL
  51.         Me.ResultList.Requery
  52.     End If
  53. End Sub
  54.  
Any help you can offer will be much appreciated. Thanks.
Aug 26 '08 #8

NeoPa
Expert Mod 15k+
P: 31,186
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].
Expand|Select|Wrap|Line Numbers
  1. Private Function GetCDKey() As String
  2.   Dim strCat As String, strArt As String, strVal As String
  3.   Dim intVal As Integer
  4.  
  5.   strCat = Me.cboMusicCategoryID
  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(Expr:="Mid([CDID],8,2)", _
  11.                        Domain:="[tblCDDetails]", _
  12.                        Criteria:="[CDID] Like '*." & strArt & ".*'"), "0"))
  13.   GetCDKey = Replace(GetCDKey, "%2", Format(intVal + 1, "00"))
  14.   intVal = Val(Nz(DMax(Expr:="Mid([CDID],11,3)", _
  15.                        Domain:="[tblCDDetails]", _
  16.                        Criteria:="[CDID] Like strCat & ".*'"), "0"))
  17.   GetCDKey = Replace(GetCDKey, "%3", Format(intVal + 1, "000"))
  18.   intVal = Val(Nz(DMax(Expr:="Mid([CDID],15,4)", _
  19.                        Domain:="[tblCDDetails]"), "0"))
  20.   GetCDKey = Replace(GetCDKey, "%4", Format(intVal + 1, "0000"))
  21. End Function
Sep 4 '08 #9

NeoPa
Expert Mod 15k+
P: 31,186
I don't have a rig to test this on so this is all untested code.

Let me know if this is any help.
Sep 4 '08 #10

NeoPa
Expert Mod 15k+
P: 31,186
You could even replace lines #7 through #9 with :
Expand|Select|Wrap|Line Numbers
  1. GetCDKey = Join(Array(strCat, strArt, "%2", "%3", "%4"), ".")
Depending on your preference.

Have fun :)
Sep 4 '08 #11

100+
P: 418
I shall give it a try and shall keep you posted. Many thanks.
Sep 5 '08 #12

NeoPa
Expert Mod 15k+
P: 31,186
Please do. It's always good to hear how something turns out :)
Sep 5 '08 #13

100+
P: 418
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.
Sep 5 '08 #14

NeoPa
Expert Mod 15k+
P: 31,186
  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 :
    Expand|Select|Wrap|Line Numbers
    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
    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.
Sep 5 '08 #15

100+
P: 418
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.
Sep 5 '08 #16

NeoPa
Expert Mod 15k+
P: 31,186
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.
Sep 5 '08 #17

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. strArt = Me.txtArtistID
Sep 7 '08 #18

100+
P: 418
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.
Sep 8 '08 #19

100+
P: 418
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.
Sep 8 '08 #20

NeoPa
Expert Mod 15k+
P: 31,186
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?
Sep 8 '08 #21

100+
P: 418
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.

Expand|Select|Wrap|Line Numbers
  1. Function IsLoaded(ByVal strFormName As String) As Integer
  2.  ' Returns True if the specified form is open in Form view or Datasheet view.
  3.  
  4.     Const conObjStateClosed = 0
  5.     Const conDesignView = 0
  6.  
  7.     If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
  8.         If Forms(strFormName).CurrentView <> conDesignView Then
  9.             IsLoaded = True
  10.         End If
  11.     End If
  12.  
  13.  
  14. Private Function GetCDKey() As String
  15.   Dim strCat As String, strArt As String, strVal As String
  16.   Dim intVal As Integer
  17.  
  18.   strCat = Me.cboCategoryID
  19.   strArt = Me.cboArtistID
  20.   GetCDKey = "%C.%A.%2.%3.%4"
  21.   GetCDKey = Replace(GetCDKey, "%C", strCat)
  22.   GetCDKey = Replace(GetCDKey, "%A", strArt)
  23.   intVal = Val(Nz(DMax(Expression:="Mid([CDID],8,2)", _
  24.                        Domain:="[tblCDDetails]", _
  25.                        Criteria:="[CDID] Like '*." & strArt & ".*'"), "0"))
  26.   GetCDKey = Replace(GetCDKey, "%2", Format(intVal + 1, "00"))
  27.   intVal = Val(Nz(DMax(Expression:="Mid([CDID],11,3)", _
  28.                         Domain:="[tblCDDetails]", _
  29.                        Criteria:="[CDID] Like '*." & strArt & ".*'"), "0"))
  30.   GetCDKey = Replace(GetCDKey, "%3", Format(intVal + 1, "000"))
  31.   intVal = Val(Nz(DMax(Expression:="Mid([CDID],15,4)", _
  32.                        Domain:="[tblCDDetails]"), "0"))
  33.   GetCDKey = Replace(GetCDKey, "%4", Format(intVal + 1, "0000"))
  34. End Function
  35.  
  36.  
  37. End Function
Sep 8 '08 #22

NeoPa
Expert Mod 15k+
P: 31,186
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.
Sep 8 '08 #23

NeoPa
Expert Mod 15k+
P: 31,186
For the RowSource of cboCategoryID try :
Expand|Select|Wrap|Line Numbers
  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.
Sep 8 '08 #24

NeoPa
Expert Mod 15k+
P: 31,186
...
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.
Sep 8 '08 #25

100+
P: 418
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.
Expand|Select|Wrap|Line Numbers
  1. 1.    Private Sub cboMusicCategoryID_AfterUpdate(Cancel as Integer)
  2. 2.      Me.txtCDID = GetCDKey()
  3. 3.    End Sub
  4. 4.     
  5. 5.    Private Sub cboArtistID_AfterUpdate(Cancel as Integer)
  6. 6.      Me.txtCDID = GetCDKey()
  7. 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.
Sep 9 '08 #26

NeoPa
Expert Mod 15k+
P: 31,186
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.
Expand|Select|Wrap|Line Numbers
  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.
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 :)
Sep 10 '08 #27

100+
P: 418
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2. 2.    Private Function GetCDKey() As String
  3. 3.    Dim strCat As String, strArt As String, strVal As String
  4. 4.    Dim intVal As Integer
  5.  
  6. 5.    strCat = Me.cboCategoryID
  7. 6.    strArt = Me.cboArtistID
  8. 7.    GetCDKey = "%C.%A.%2.%3.%4"
  9. 8.    GetCDKey = Replace(GetCDKey, "%C", strCat)
  10. 9.    GetCDKey = Replace(GetCDKey, "%A", strArt)
  11. 10.    intVal = Val(Nz(DMax(Expression:="Mid([txtCDID],8,2)", _
  12. i.    Domain:="[tblCDDetails]", _
  13. ii.    Criteria:="[txtCDID] Like '*." & strArt & ".*'"), "0"))
  14. 11.    GetCDKey = Replace(GetCDKey, "%2", Format(intVal + 1, "00"))
  15. 12.    intVal = Val(Nz(DMax(Expression:="Mid([txtCDID],11,3)", _
  16. i.    Domain:="[tblCDDetails]", _
  17. ii.    Criteria:="[txtCDID] Like '*." & strArt & ".*'"), "0"))
  18. 13.    GetCDKey = Replace(GetCDKey, "%3", Format(intVal + 1, "000"))
  19. 14.    intVal = Val(Nz(DMax(Expression:="Mid([txtCDID],15,4)", _
  20. i.    Domain:="[tblCDDetails]"), "0"))
  21. 15.    GetCDKey = Replace(GetCDKey, "%4", Format(intVal + 1, "0000"))
  22. 16.    End Function
  23.  
  24. 17.    End Sub
Still nothing displays in txtCDID. Ooooooh! I wish I were smarter.

Thanks.
Sep 10 '08 #28

NeoPa
Expert Mod 15k+
P: 31,186
...
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?
Expand|Select|Wrap|Line Numbers
  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.  
  6.   strCat = Me.cboCategoryID
  7.   strArt = Me.cboArtistID
  8.   GetCDKey = "%C.%A.%2.%3.%4"
  9.   GetCDKey = Replace(GetCDKey, "%C", strCat)
  10.   GetCDKey = Replace(GetCDKey, "%A", strArt)
  11.   intVal = Val(Nz(DMax(Expression:="Mid([txtCDID],8,2)", _
  12.                        Domain:="[tblCDDetails]", _
  13.                        Criteria:="[txtCDID] Like '*." & strArt & ".*'"), "0"))
  14.   GetCDKey = Replace(GetCDKey, "%2", Format(intVal + 1, "00"))
  15.   intVal = Val(Nz(DMax(Expression:="Mid([txtCDID],11,3)", _
  16.                        Domain:="[tblCDDetails]", _
  17.                        Criteria:="[txtCDID] Like '*." & strArt & ".*'"), "0"))
  18.   GetCDKey = Replace(GetCDKey, "%3", Format(intVal + 1, "000"))
  19.   intVal = Val(Nz(DMax(Expression:="Mid([txtCDID],15,4)", _
  20.                        Domain:="[tblCDDetails]"), "0"))
  21.   GetCDKey = Replace(GetCDKey, "%4", Format(intVal + 1, "0000"))
  22. End Function
  23.  
  24. 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.
Sep 11 '08 #29

100+
P: 418
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2. End Sub
  3. Private Function GetCDKey() As String
  4.   Dim strCat As String, strArt As String, strVal As String
  5.   Dim intVal As Integer
  6.  
  7.   strCat = Me.cboCategoryID
  8.   strArt = Me.cboArtistID
  9.   GetCDKey = "%C.%A.%2.%3.%4"
  10.   GetCDKey = Replace(GetCDKey, "%C", strCat)
  11.   GetCDKey = Replace(GetCDKey, "%A", strArt)
  12.   intVal = Val(Nz(DMax(Expression:="Mid([txtCDID],8,2)", _
  13.                        Domain:="[tblCDDetails]", _
  14.                        Criteria:="[txtCDID] Like '*." & strArt & ".*'"), "0"))
  15.   GetCDKey = Replace(GetCDKey, "%2", Format(intVal + 1, "00"))
  16.   intVal = Val(Nz(DMax(Expression:="Mid([txtCDID],11,3)", _
  17.                        Domain:="[tblCDDetails]", _
  18.                        Criteria:="[txtCDID] Like '*." & strArt & ".*'"), "0"))
  19.   GetCDKey = Replace(GetCDKey, "%3", Format(intVal + 1, "000"))
  20.   intVal = Val(Nz(DMax(Expression:="Mid([txtCDID],15,4)", _
  21.                        Domain:="[tblCDDetails]"), "0"))
  22.   GetCDKey = Replace(GetCDKey, "%4", Format(intVal + 1, "0000"))
  23. 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.
Sep 12 '08 #30

NeoPa
Expert Mod 15k+
P: 31,186
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2. End Sub
  3. Private Function GetCDKey() As String
  4.   Dim strCat As String, strArt As String, strVal As String
  5.   Dim intVal As Integer
  6.  
  7.   strCat = Me.cboCategoryID
  8.   strArt = Me.cboArtistID
  9.   GetCDKey = "%C.%A.%2.%3.%4"
  10.   GetCDKey = Replace(GetCDKey, "%C", strCat)
  11.   GetCDKey = Replace(GetCDKey, "%A", strArt)
  12.   intVal = Val(Nz(DMax(Expression:="Mid([txtCDID],8,2)", _
  13.                        Domain:="[tblCDDetails]", _
  14.                        Criteria:="[txtCDID] Like '*." & strArt & ".*'"), "0"))
  15.   GetCDKey = Replace(GetCDKey, "%2", Format(intVal + 1, "00"))
  16.   intVal = Val(Nz(DMax(Expression:="Mid([txtCDID],11,3)", _
  17.                        Domain:="[tblCDDetails]", _
  18.                        Criteria:="[txtCDID] Like '*." & strArt & ".*'"), "0"))
  19.   GetCDKey = Replace(GetCDKey, "%3", Format(intVal + 1, "000"))
  20.   intVal = Val(Nz(DMax(Expression:="Mid([txtCDID],15,4)", _
  21.                        Domain:="[tblCDDetails]"), "0"))
  22.   GetCDKey = Replace(GetCDKey, "%4", Format(intVal + 1, "0000"))
  23. 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.
Sep 13 '08 #31

100+
P: 418
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.
Sep 15 '08 #32

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtCDID_BeforeUpdate(Cancel As Integer)
  2.  
  3. End Sub
Simply delete this new code and proceed to transfer the code from earlier into THIS module.
Sep 15 '08 #33

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

Expand|Select|Wrap|Line Numbers
  1. Private Function GetCDKey() As String
  2.   Dim strCat As String, strArt As String, strVal As String
  3.   Dim intVal As Integer
  4.  
  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)", _
  11.                        Domain:="[tblCDDetails]", _
  12.                        Criteria:="[txtCDID] Like '*." & strArt & ".*'"), "0"))
  13.   GetCDKey = Replace(GetCDKey, "%2", Format(intVal + 1, "00"))
  14.   intVal = Val(Nz(DMax(Expression:="Mid([txtCDID],11,3)", _
  15.                         Domain:="[tblCDDetails]", _
  16.                        Criteria:="[txtCDID] Like '*." & strArt & ".*'"), "0"))
  17.   GetCDKey = Replace(GetCDKey, "%3", Format(intVal + 1, "000"))
  18.   intVal = Val(Nz(DMax(Expression:="Mid([txtCDID],15,4)", _
  19.                        Domain:="[tblCDDetails]"), "0"))
  20.   GetCDKey = Replace(GetCDKey, "%4", Format(intVal + 1, "0000"))
  21. End Function
Now the word Expression:= is highlighted on Line # 10 and this error message is displayed: Named argument not found
Thanks a bunch.
Sep 15 '08 #34

NeoPa
Expert Mod 15k+
P: 31,186
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 :)
Sep 15 '08 #35

100+
P: 418
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.
Sep 16 '08 #36

NeoPa
Expert Mod 15k+
P: 31,186
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).
Sep 16 '08 #37

100+
P: 418
Absolutely. Here it is and thanks.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboArtistID_AfterUpdate()
  2.   Me.txtCDID = GetCDKey()
  3. End Sub
  4.  
  5. Private Sub cboCategoryID_AfterUpdate()
  6.   Me.txtCDID = GetCDKey()
  7. End Sub
  8.  
  9. Private Sub Form_Current()
  10.     If IsNull(Me![RecordingID]) Then
  11.       DoCmd.GoToControl "RecordingTitle"
  12.     End If
  13. End Sub
  14.  
  15.  
  16. Private Sub RecordingArtistID_NotInList(NewData As String, Response As Integer)
  17.     MsgBox "Double-click this field to add an entry to the list."
  18.     Response = acDataErrContinue
  19. End Sub
  20. Private Sub RecordingArtistID_DblClick(Cancel As Integer)
  21. On Error GoTo Err_RecordingArtistID_DblClick
  22.     Dim lngRecordingArtistID As Long
  23.  
  24.     If IsNull(Me![RecordingArtistID]) Then
  25.         Me![RecordingArtistID].Text = ""
  26.     Else
  27.         lngRecordingArtistID = Me![RecordingArtistID]
  28.         Me![RecordingArtistID] = Null
  29.     End If
  30.     DoCmd.OpenForm "Artists", , , , , acDialog, "GotoNew"
  31.     Me![RecordingArtistID].Requery
  32.     If lngRecordingArtistID <> 0 Then Me![RecordingArtistID] = lngRecordingArtistID
  33.  
  34. Exit_RecordingArtistID_DblClick:
  35.     Exit Sub
  36.  
  37. Err_RecordingArtistID_DblClick:
  38.     MsgBox Err.Description
  39.     Resume Exit_RecordingArtistID_DblClick
  40. End Sub
  41. Private Sub MusicCategoryID_NotInList(NewData As String, Response As Integer)
  42.     MsgBox "Double-click this field to add an entry to the list."
  43.     Response = acDataErrContinue
  44. End Sub
  45. Private Sub MusicCategoryID_DblClick(Cancel As Integer)
  46. On Error GoTo Err_MusicCategoryID_DblClick
  47.     Dim lngRecordingArtistID As Long
  48.  
  49.     If IsNull(Me![MusicCategoryID]) Then
  50.         Me![MusicCategoryID].Text = ""
  51.     Else
  52.         lngRecordingArtistID = Me![MusicCategoryID]
  53.         Me![MusicCategoryID] = Null
  54.     End If
  55.     DoCmd.OpenForm "frmCategories", , , , , acDialog, "GotoNew"
  56.     Me![MusicCategoryID].Requery
  57.     If lngRecordingArtistID <> 0 Then Me![MusicCategoryID] = lngRecordingArtistID
  58.  
  59. Exit_MusicCategoryID_DblClick:
  60.     Exit Sub
  61.  
  62. Err_MusicCategoryID_DblClick:
  63.     MsgBox Err.Description
  64.     Resume Exit_MusicCategoryID_DblClick
  65. End Sub
  66.  
  67. Private Sub cmdAddSong_Click()
  68. On Error GoTo Err_cmdAddSong_Click
  69.  
  70.     Dim stDocName As String
  71.     Dim stLinkCriteria As String
  72.  
  73.     stDocName = "frmSongs"
  74.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  75.  
  76. Exit_cmdAddSong_Click:
  77.     Exit Sub
  78.  
  79. Err_cmdAddSong_Click:
  80.     MsgBox Err.Description
  81.     Resume Exit_cmdAddSong_Click
  82.  
  83. End Sub
  84. Private Sub cmdSave_Click()
  85. On Error GoTo Err_cmdSave_Click
  86.  
  87.  
  88.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  89.  
  90. Exit_cmdSave_Click:
  91.     Exit Sub
  92.  
  93. Err_cmdSave_Click:
  94.     MsgBox Err.Description
  95.     Resume Exit_cmdSave_Click
  96.  
  97. End Sub
  98. Private Sub cmdFindCD_Click()
  99. On Error GoTo Err_cmdFindCD_Click
  100.  
  101.     Dim stDocName As String
  102.     Dim stLinkCriteria As String
  103.  
  104.     stDocName = "frmFIND"
  105.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  106.  
  107. Exit_cmdFindCD_Click:
  108.     Exit Sub
  109.  
  110. Err_cmdFindCD_Click:
  111.     MsgBox Err.Description
  112.     Resume Exit_cmdFindCD_Click
  113.  
  114. End Sub
  115.  
  116.  
  117. Private Function GetCDKey() As String
  118.   Dim strCat As String, strArt As String, strVal As String
  119.   Dim intVal As Integer
  120.  
  121.   strCat = Me.cboCategoryID
  122.   strArt = Me.cboArtistID
  123.   GetCDKey = "%C.%A.%2.%3.%4"
  124.   GetCDKey = Replace(GetCDKey, "%C", strCat)
  125.   GetCDKey = Replace(GetCDKey, "%A", strArt)
  126.   intVal = Val(Nz(DMax(Expr:="Mid([txtCDID],8,2)", _
  127.                        Domain:="[tblCDDetails]", _
  128.                        Criteria:="[txtCDID] Like '*." & strArt & ".*'"), "0"))
  129.   GetCDKey = Replace(GetCDKey, "%2", Format(intVal + 1, "00"))
  130.   intVal = Val(Nz(DMax(Expr:="Mid([txtCDID],11,3)", _
  131.                         Domain:="[tblCDDetails]", _
  132.                        Criteria:="[txtCDID] Like '*." & strArt & ".*'"), "0"))
  133.   GetCDKey = Replace(GetCDKey, "%3", Format(intVal + 1, "000"))
  134.   intVal = Val(Nz(DMax(Expr:="Mid([txtCDID],15,4)", _
  135.                        Domain:="[tblCDDetails]"), "0"))
  136.   GetCDKey = Replace(GetCDKey, "%4", Format(intVal + 1, "0000"))
  137. End Function
Sep 16 '08 #38

NeoPa
Expert Mod 15k+
P: 31,186
I can't say that I see anything wrong with this :S

Try adding the line :
Expand|Select|Wrap|Line Numbers
  1. 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.
Sep 17 '08 #39

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

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Private Sub cboArtistID_AfterUpdate()
  4.   Me.txtCDID = GetCDKey()
  5. 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.
Sep 17 '08 #40

NeoPa
Expert Mod 15k+
P: 31,186
Last time I inadvertently left out the first two lines of my VBA Codes. So the first five lines are as follows:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Private Sub cboArtistID_AfterUpdate()
  4.   Me.txtCDID = GetCDKey()
  5. 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 :(
Sep 17 '08 #41

100+
P: 418
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.
Sep 17 '08 #42

NeoPa
Expert Mod 15k+
P: 31,186
Have you compacted it and Zipped it?
Sep 18 '08 #43

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

Thanks.
Sep 18 '08 #44

100+
P: 418
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.
Sep 18 '08 #45

NeoPa
Expert Mod 15k+
P: 31,186
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.
Sep 18 '08 #46

NeoPa
Expert Mod 15k+
P: 31,186
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 :)
Sep 18 '08 #47

100+
P: 418
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.
Sep 18 '08 #48

NeoPa
Expert Mod 15k+
P: 31,186
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?
Sep 18 '08 #49

NeoPa
Expert Mod 15k+
P: 31,186
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.
Sep 18 '08 #50

176 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.