New Data in Not in List Event (1 Viewer)

AlexN · Jan 25, 2017

Hi everyone,

I’m designing a music collection database, where I input all my music by Artist, Recording (album etc.), Media (CD, LP, mp3 etc.).
There are two significant tables in this database, tblArtists and tblRecordings. There is also a data entry form frmRecordings based on tblRecordings, where control Artist is a combo box with values from tblArtists.

In the NotInList event of this combo box, there is a little piece of code to allow adding new artist name in tblArtists, first finding the space character in the new name registered and then dividing it to FirstName and LastName of the Artist. This way I manage to input new artist by normally writing the name (e.g. Phil Collins) and adding it in tblArtists by FirstName and LastName, in order to have them in alphabetical order (by LastName) in the combo box (or elsewhere).

That works perfectly all right as long as I input an individual artist. However, when it comes to groups (e.g. The Alan Parsons Project) my code is not effective and produces funny results.

Could any of you kind people come with an idea of how I would accomplish adding new groups in tblArtists, while keeping all names in alphabetical order?

Thank you

Gasman · Jan 25, 2017

I have all my music by Artist and then Folder on the computer.

For that reason I never use 'The' for any groups.?

You could use the split function and then do what you want with the data after that?

https://www.techonthenet.com/access/functions/string/split.php

AlexN · Jan 25, 2017

Gasman said:
I have all my music by Artist and then Folder on the computer.

For that reason I never use 'The' for any groups.?

You could use the split function and then do what you want with the data after that?

https://www.techonthenet.com/access/functions/string/split.php

Thank you for your answer. I'm sorry it doesn't provide any new idea or solution to my issue.

I too of course keep my music by Artist and Folder (each artist a separate folder) on my disk. I also don't mind the "The" for the group name.

But..

the way I split the new artist name in the NotInList event of the combo box, when I input (e.g.) Phil Collins a new entry is inserted in tblArtists with FirstName Phil, LastName Collins. You then expect to find Phil Collins under first letter "C" which is correct.

When it comes to (e.g.) Pink Floyd this separation doesn't make any sense. You don't expect to find Pink Floyd under "F" when you're searching by first letter.

Imagine what happens when group's name consists of three or more words (e.g. Creedence Clearwater Revival)

Gasman · Jan 25, 2017

AlexN said:
When it comes to (e.g.) Pink Floyd this separation doesn't make any sense. You don't expect to find Pink Floyd under "F" when you're searching by first letter.

Imagine what happens when group's name consists of three or more words (e.g. Creedence Clearwater Revival)

We have a similar taste in music.

If I was having this problem, I'd look at a way of identifying if the 'artist' is a group or not and code accordingly, as Loudon Wainwright III or Nat King Cole would cause you the same problem.?

Could you perhaps combine a checkbox in the combo?

AlexN · Jan 26, 2017

Gasman said:
We have a similar taste in music.

If I was having this problem, I'd look at a way of identifying if the 'artist' is a group or not and code accordingly, as Loudon Wainwright III or Nat King Cole would cause you the same problem.?

Could you perhaps combine a checkbox in the combo?

Thanks again for your answer. It seems like the only way to overcome my issue. Thought it would be something like that, but I wanted to avoid making things more complicated adding controls in the input form.
However, it looks like it's the only way (at least for the time being).

Thanks again, you've been helpful.

Gasman · Jan 26, 2017

How about prefixing the text with an identifier?

GRP

ink Floyd

Your code could then look for GRP: and do the necessary?

AlexN · Jan 27, 2017

Gasman said:
How about prefixing the text with an identifier?

GRPink Floyd

Your code could then look for GRP: and do the necessary?

Well,

I'm designing this database mostly for personal use, but I want to it to be in such a way, anyone could use it. There's no way a user would remember to prefix the text in that combo, and even so, I think this would make things more complicated.

So,
I adapted the checkbox solution, and coded accordingly, and everything works perfect.

Thanks again for your time and ideas. You've been kind and helpful.

Gasman · Jan 27, 2017

Care to upload or share as it might come in handy for someone else, possibly me in the future?

AlexN said:
Well,

I'm designing this database mostly for personal use, but I want to it to be in such a way, anyone could use it. There's no way a user would remember to prefix the text in that combo, and even so, I think this would make things more complicated.

So,
I adapted the checkbox solution, and coded accordingly, and everything works perfect.

Thanks again for your time and ideas. You've been kind and helpful.

Steve R. · Jan 27, 2017

Though you have marked this question as solved, I have some additional thoughts on database design which you may find helpful assuming that you have not already incorporated them.

You should have at least two tables, one for each person and one listing each group. The person table, besides the obvious first name and last name, would also have a foreign key field for the group that they belong to.

A more sophisticated approach would involve the use of an intermediary table that would allow a person to be linked to every group that that person may have been working for.

Of course the suggested database design would not specifically address your original question concerning the "not in list" event when adding new people and groups. It would seem that you may need at least two pop-up screens, one for a new person and the other for a new a group.

AlexN · Jan 31, 2017

Gasman said:
Care to upload or share as it might come in handy for someone else, possibly me in the future?

Why not...
As soon as it is completed I have no problem sharing it. Of course I'll have to translate it a bit, 'cause all captions and messages would be in Greek, but I'll do it.

I'm sure you'll have a good number of ideas on improving it or even correcting it, since I'm a novice in database designing and surely made some mistakes (if not all of it).

Right now I'm searching for a way of limiting that combo's values, while typing Artist's name.

AlexN · Jan 31, 2017

Steve R. said:
Though you have marked this question as solved, I have some additional thoughts on database design which you may find helpful assuming that you have not already incorporated them.

You should have at least two tables, one for each person and one listing each group. The person table, besides the obvious first name and last name, would also have a foreign key field for the group that they belong to.

A more sophisticated approach would involve the use of an intermediary table that would allow a person to be linked to every group that that person may have been working for.

Of course the suggested database design would not specifically address your original question concerning the "not in list" event when adding new people and groups. It would seem that you may need at least two pop-up screens, one for a new person and the other for a new a group.

Thanks for your suggestions.
It crossed my mind for a moment while I was searching for ways to overcome my issue, but it seemed too complicated, and it surely surpasses my skills.

After all, I interpret any artist (or group) as an individual and I don't really mind whether an artist was a group's member at any time in his career. (e.g. Phil Collins and Genesis are two different artists in my design, with no relation - probably, there's no relation from an artistic point of view also

).

Gasman · Jan 31, 2017

Thank you. Explanation of the concept should be sufficient though.
Have you considered two fields, one for Artist and another for Group?

I'd have no qualms putting a value in one or the other if I was collating my Music Collection.

TBH I have done that, but I just trawled the folders and took the names from there. That was really just to be able to show others what my Music Collection was and I used to download it to a Psion 3a so that I did not end up buying the same CD twice.

AlexN said:
Why not...
As soon as it is completed I have no problem sharing it. Of course I'll have to translate it a bit, 'cause all captions and messages would be in Greek, but I'll do it.

I'm sure you'll have a good number of ideas on improving it or even correcting it, since I'm a novice in database designing and surely made some mistakes (if not all of it).

Right now I'm searching for a way of limiting that combo's values, while typing Artist's name.

New Data in Not in List Event (1 Viewer)

AlexN

Registered User.

Gasman

Enthusiastic Amateur

AlexN

Registered User.

Gasman

Enthusiastic Amateur

AlexN

Registered User.

Gasman

Enthusiastic Amateur

AlexN

Registered User.

Gasman

Enthusiastic Amateur

Steve R.

Retired

AlexN

Registered User.

AlexN

Registered User.

Gasman

Enthusiastic Amateur

Similar threads

Users who are viewing this thread