Formulate Index Value

[NeoPa]

As things stand, your GetCDKey() function will not work because the ComboBoxes are returning the numeric code value of the item rather than the alpha code that's displayed.

If it turns out that these items need to be stored separately in the record (I doubt it) then we can access other columns within the ComboBox, but we will probably end up redesigning these ComboBoxes (as unbound) to return the alpha codes required (and previously discussed).

I'll stop here, as any more may introduce information overload. There is plenty here for you to be thinking about.

[MNNovice]

I did. I have been having some very strange experiences with it.

Ultimately though, I can tell you a number of things which are wrong, but not test it properly, as you haven't yet explained how it should work (from post #49). Your instructions take me to the form, but how to use the form is not immediately obvious (I wasn't able to work out the logic of it).

What I will do, until you can explain this, is to drop in a few observations on your database in the hope that it will be helpful.

Firstly, the switchboard effect is really impressive. I've never used an automatically generated switchboard myself. I will think about using it for my next database.

Secondly, all for now, you can arrange for the main database window to be hidden completely if you would like (I do in my dbs). Simply go into Tools / Startup... and deselect the CheckBox. Using F11 will show it for you if you need it.

At present when I enter a new CD I open the Form frmCDDetails and type in into starting with the CD title. I have a running log that I keep manually to generate the ID for this new CD and type it in the text box called Serial Number (the text box at the far top right corner). It was originally added to store a running IDs for each CD.

Next I enter the track details using the sub form (bottom half of this form).

This is how I have been adding CDs to my database.

Thanks for your compliments on the switchboard. A friend helped me with it so I cannot take full credit for it. Although I modified and customized it to meet my needs. I know how to hide the main database but for now, I am keeping it this way as I need to access the tables quite frequently. Once I am done with the entire data entry, I will do what you suggested. Thanks.

[MNNovice]

On your form (and in your table) you have a Serial No - AS WELL AS a CDID. I can't make any sense of this.

I thought we were looking at ways to create an index value for a new record. I can see no way to get to where a new record is added. Instead, the code seems to be executing for EXISTING records. I cannot see why this would ever be necessary.

This database seems to be a great idea, and, from what I can see of it, is well implemented with some good ideas at the heart of it. HOWEVER, there seem to be some misunderstandin gs in the technical design of it that should really be ironed out before proceeding with the niceties. Otherwise you are building on sand.

The basic logic flow of the project is what I have in mind. How are records added? What can/cannot be done with existing records? I think you need to give this some thought (or maybe just explain to me what I've misunderstood).

Based on your instructions I have added the CDID. My intention was to replace the SerialNumber once I get txtCDID to work. But if this is a mistake on my part, I am sorry. If the CDID is to create an index value for a new record (is it the same as creating a running ID for new CDs?), then tell me what I need to do. I don’t mind keeping the SerialNumber for all the CDs already entered and CDID for the new CDs (is it possible to have both?)

How the records are added?

When you open the form frmCDDetails and select the last record (Record #554), it opens a blank page. Here I start with the CD title, tab to the next field and keep filling it in. Then move on to the next record.

What can and cannot be done with existing records?

This is tough to answer. I would prefer to keep the core design and data without making changes that may affect the reports. It was a lot of work for me and I am almost there to finish the database.

May be if I can keep the txtCDID as a temporary to populate the running numbers for me that will be helpful. Let me explain. Let’s say I am in Record #554.
1. I enter a CD Title
2. I select the category (let’s say Nazrul Geeti). This will populate the cboCategoryID with the two letters NG for Nazrul Geeti .
3. Next I move on to entering type and language.
4. Next when I enter the Artist’s name Firoza Begum, cboArtistID will populate with the three letters FBA.
5. At this point I would like the txtCDID to populate the running number for Firoza Begum followed by the running number for Nazrul Geeti. In this case it should 02.041
6. Now I can fill in the SerialNumber with correct info without looking at the manual log book I am keeping. The serial number for this new CD will be: NG.FBA.02.041.0 554.

There are couple of drawbacks:
1. I can still make a mistake and have a typo while typing the data in the SerialNumber box
2. Having the CDID right on the form is an eyesore

But I cannot think of a better solution nor do I have the expertise to figure out a more efficient way to accomplishing this. Since I and my husband will be the ones using the database, we will have to deal with it without any complain, I guess.

[MNNovice]

As things stand, your GetCDKey() function will not work because the ComboBoxes are returning the numeric code value of the item rather than the alpha code that's displayed.

If it turns out that these items need to be stored separately in the record (I doubt it) then we can access other columns within the ComboBox, but we will probably end up redesigning these ComboBoxes (as unbound) to return the alpha codes required (and previously discussed).

I'll stop here, as any more may introduce information overload. There is plenty here for you to be thinking about.

If the combo boxes returns a numeric code can the txtCDID translate it to the alpha code? For example if I select Nazrul Geeti and the combo box displays 2 (that’s the numeric code for Nazrul Geeti) and I select Firoza Begum and the cboArtist displays 44 for her code, can I get the txtCDID to display NG.FBA.02.041?

That’s all for now. I cannot thank you enough for your help.

[NeoPa]

I will look at these in more detail when I get home this evening (where I have a copy of the database) :)

[NeoPa]

At present when I enter a new CD I open the Form frmCDDetails and type in into starting with the CD title. I have a running log that I keep manually to generate the ID for this new CD and type it in the text box called Serial Number (the text box at the far top right corner). It was originally added to store a running IDs for each CD.

Next I enter the track details using the sub form (bottom half of this form).

This is how I have been adding CDs to my database.

I assume you start off by selecting new record with the record selectors at the bottom. Otherwise following your instructions simply updates the first record.

There are a few things we need to get sorted out :

• The [CDID] IS the [Serial No]. There should not be two separate fields or controls.
• ONLY new records should should have the selecting of the Category and Artist enabled. This should be disabled for existing records.
• The [CDID] or [Serial No] control should be prominent at the start of the form somewhere (Genrally this means top-left but not necessarily).
• If either of Artist or Category changes, or is found to be entered incorrectly, then simply delete the record (and the tracks) and enter it again.

[NeoPa]

Based on your instructions I have added the CDID. My intention was to replace the SerialNumber once I get txtCDID to work. But if this is a mistake on my part, I am sorry. If the CDID is to create an index value for a new record (is it the same as creating a running ID for new CDs?), then tell me what I need to do. I don’t mind keeping the SerialNumber for all the CDs already entered and CDID for the new CDs (is it possible to have both?)

You would have one or the other, but not both. The existing data would happily remain in whichever field you keep.

How the records are added?

When you open the form frmCDDetails and select the last record (Record #554), it opens a blank page. Here I start with the CD title, tab to the next field and keep filling it in. Then move on to the next record.

Ah, this confirms what I assumed before. Starting to make more sense now (I understand that what I was looking at before is done wrong so I can ignore it).

What can and cannot be done with existing records?

This is tough to answer. I would prefer to keep the core design and data without making changes that may affect the reports. It was a lot of work for me and I am almost there to finish the database.

Ah, we can look at this in more detail later. When we have covered some other ground I think some of the complications will melt away.

May be if I can keep the txtCDID as a temporary to populate the running numbers for me that will be helpful. Let me explain. Let’s say I am in Record #554.
1. I enter a CD Title
2. I select the category (let’s say Nazrul Geeti). This will populate the cboCategoryID with the two letters NG for Nazrul Geeti .
3. Next I move on to entering type and language.
4. Next when I enter the Artist’s name Firoza Begum, cboArtistID will populate with the three letters FBA.
5. At this point I would like the txtCDID to populate the running number for Firoza Begum followed by the running number for Nazrul Geeti. In this case it should 02.041
6. Now I can fill in the SerialNumber with correct info without looking at the manual log book I am keeping. The serial number for this new CD will be: NG.FBA.02.041.0 554.

There are couple of drawbacks:
1. I can still make a mistake and have a typo while typing the data in the SerialNumber box
2. Having the CDID right on the form is an eyesore

But I cannot think of a better solution nor do I have the expertise to figure out a more efficient way to accomplishing this. Since I and my husband will be the ones using the database, we will have to deal with it without any complain, I guess.

Here I think is where most of the complications can melt away.

To start, as the Artist and Category fields are fundamental to the key ([Serial No]) of the record, it should only be possible to set these for new records. They should be disabled for existing records. Also, as they are directly key related they should also be near the top (start) of your form.

Looking simply at the Category info for the moment, you currently have two ComboBoxes directly bound to that field ([MusicCategoryID]). This is not a good idea. What you need really is a ComboBox containing the ID, the abbreviation, and the description. Which of these are visible is up to you. All are visible to (accessible by) the code.

Me.MusicCategoryID.Column(0) refers to the ID.
Me.MusicCategoryID.Column(1) refers to the Artist Name.
Me.MusicCategoryID.Column(2) refers to the Abbreviation.

[NeoPa]

If the combo boxes returns a numeric code can the txtCDID translate it to the alpha code? For example if I select Nazrul Geeti and the combo box displays 2 (that’s the numeric code for Nazrul Geeti) and I select Firoza Begum and the cboArtist displays 44 for her code, can I get the txtCDID to display NG.FBA.02.041?

That’s all for now. I cannot thank you enough for your help.

I think we can forget about this now.

Do the redesign losing some of the extraneous controls (the ones we no longer need), then pop the new version in the thread again. I will always delete it immediately after downloading it, unless you say otherwise.

PS. I have the code working properly for me now the correct data is being used. When I see the new layout, I can tell you how to update the code so that it matches the controls you have.

[MNNovice]

I assume you start off by selecting new record with the record selectors at the bottom. Otherwise following your instructions simply updates the first record.

There are a few things we need to get sorted out :

• The [CDID] IS the [Serial No]. There should not be two separate fields or controls.
• ONLY new records should should have the selecting of the Category and Artist enabled. This should be disabled for existing records.
• The [CDID] or [Serial No] control should be prominent at the start of the form somewhere (Genrally this means top-left but not necessarily).
• If either of Artist or Category changes, or is found to be entered incorrectly, then simply delete the record (and the tracks) and enter it again.

My intention is to replace the SerialNumber with txtCDID. I don't want to delete it now as this text box (SerialNumber) is tied to so many reports and existing records. It took me hours to photocopy each CDCase, put them in a binder, print lables using the SerialNumber and stick it on each CD Case as well as the photocopies in each binder. Simply put, I just don't want to mess with this part. Is it possible to have it hidden so that it does not interfere with what you are trying to accomplish?

For data entry errors: Are you suggesting I type over erroneous data with correct data? How does this take care of the data that comes after it? For example, if I had a category NG in record #554 but it should have been RS and I don't find my error until record #1000. How does this correction affect all other RS or NG that came after record #554? Am I making sense?

[MNNovice]

You would have one or the other, but not both. The existing data would happily remain in whichever field you keep.

Ah, this confirms what I assumed before. Starting to make more sense now (I understand that what I was looking at before is done wrong so I can ignore it).

Ah, we can look at this in more detail later. When we have covered some other ground I think some of the complications will melt away.

Here I think is where most of the complications can melt away.

To start, as the Artist and Category fields are fundamental to the key ([Serial No]) of the record, it should only be possible to set these for new records. They should be disabled for existing records. Also, as they are directly key related they should also be near the top (start) of your form.

Looking simply at the Category info for the moment, you currently have two ComboBoxes directly bound to that field ([MusicCategoryID]). This is not a good idea. What you need really is a ComboBox containing the ID, the abbreviation, and the description. Which of these are visible is up to you. All are visible to (accessible by) the code.

Me.MusicCategoryID.Column(0) refers to the ID.
Me.MusicCategoryID.Column(1) refers to the Artist Name.
Me.MusicCategoryID.Column(2) refers to the Abbreviation.

Of course my intention is to keep ONE field to capture the running count/ID for the CDs. But without having the CDID worked out I just didn’t feel comfortable deleting the SerialNumber. Also, I am not sure how txtCDID will impact the existing records. If this pertains to new records only, how do I handle the existing records if I delete SerialNumber?


To start, as the Artist and Category fields are fundamental to the key ([Serial No]) of the record, it should only be possible to set these for new records. They should be disabled for existing records. Also, as they are directly key related they should also be near the top (start) of your form.
How do I disable these for existing records?


Thanks.

[NeoPa]

My intention is to replace the SerialNumber with txtCDID. I don't want to delete it now as this text box (SerialNumber) is tied to so many reports and existing records. It took me hours to photocopy each CDCase, put them in a binder, print lables using the SerialNumber and stick it on each CD Case as well as the photocopies in each binder. Simply put, I just don't want to mess with this part. Is it possible to have it hidden so that it does not interfere with what you are trying to accomplish?

I don't mean for you to delete any existing data from your table. Assuming all the data follows your stated rules there should be no need.

We would simply use EITHER [CDID] OR [Serial No] as the field and only one of the controls. Does that make better sense.

I notice, looking through the existing data, that not all the [Serial No]s DO match your rules. I see some zero values which should never occur (from what you stated). Some others don't match the layout at all.

For data entry errors: Are you suggesting I type over erroneous data with correct data? How does this take care of the data that comes after it? For example, if I had a category NG in record #554 but it should have been RS and I don't find my error until record #1000. How does this correction affect all other RS or NG that came after record #554? Am I making sense?

This makes sense, but you simply have to realise that if data entry errors ever occur (of course they will) it is impossible to have contiguous sets of numbers running through your data anyway, unless you redo all the ones around it.

It's quite usual to have gaps in sequences in this sort of thing. You would simply add it at the end, or in the position assigned by the form when you add it. At that point in time.

In this way, nothing else need change for the system to carry on working perfectly. Not only for the newly entered record, but also for all subsequent ones. The procedure for assigning new numbers is designed to handle new records regardless of the numbers already assigned. It will never produce an invalid number (unless/until it runs out of digits).

[NeoPa]

Of course my intention is to keep ONE field to capture the running count/ID for the CDs. But without having the CDID worked out I just didn’t feel comfortable deleting the SerialNumber. Also, I am not sure how txtCDID will impact the existing records. If this pertains to new records only, how do I handle the existing records if I delete SerialNumber?

As the [Serial No] field is exactly the data we created the GetCDKey() function for, we simply convert it to use this instead. We will deal with not allowing it to be changed shortly

To start, as the Artist and Category fields are fundamental to the key ([Serial No]) of the record, it should only be possible to set these for new records. They should be disabled for existing records. Also, as they are directly key related they should also be near the top (start) of your form.
How do I disable these for existing records?

You lock the three related controls on the form (in the design). That would be all of [MusicCategoryID], [RecordingArtist ID] & [Duet_Trio]. They all effect what the [Serial No] should be.

In the Form_Current() event procedure you set the locked status depending on whether or not you are on an existing record or a new one. Details to follow.

[NeoPa]

From your latest posted version, replace the [Serial No] control ([txtSerialNumber]). We will forget about CDID - I only suggested this because I couldn't get the name of the field from you before. Everything that was done before with [CDID] & [txtCDID] can be done with [Serial No] and [txtSerialNumber]. [txtSerialNumber] should be put back at the top of the form where it was before.

Use Me.NewRecord to determine if the New record is current in the form.

PS. If I get some time later I will post some example code.

[MNNovice]

The Database I sent you is a copy of the original. However, it is not a duplicate copy. It's just a working copy I use to play with codes and stuff. That's why there are some discripencies.

Later this evening I will upload a true copy with the changes you requested.

Thanks.

[NeoPa]

The top of your form's module, should now look like :

Option Compare Database
Option Explicit

Private Sub MusicCategoryID_AfterUpdate()
  Me.TxtSerialNumber = GetCDKey()
End Sub

Private Sub RecordingArtistID_AfterUpdate()
  Me.TxtSerialNumber = GetCDKey()
End Sub

Private Sub Form_Current()
  With Me
    If .NewRecord Then Call .RecordingTitle.SetFocus
    .MusicCategoryID.Locked = Not .NewRecord
    .RecordingArtistID.Locked = Not .NewRecord
    .Duet_Trio.Locked = Not .NewRecord
  End With
End Sub

This will handle locking and unlocking the three controls (remember, they should be locked in the design of the form).

PS. Until the CODE tags are sorted out properly, copy the code from the reply window only (Click Reply then select and copy the code from there).