Solved Problems creating entries in the "wrong" table (1 Viewer)

lodmark

Member
Local time
Today, 14:14
Joined
Jul 24, 2020
Messages
232
Hey!

As you can see, I'm new to the forum and have not yet read through everything in here. I also suspect that my question may be due to a misconception when I created the database.
I have created a database in Access (of course) that handles my vinyl record collection. I searched for a long time for a template online but without being able to find what I was looking for. So I decided to create it myself. I have some "old" knowledge about Access.
The basis of the database is an Excel list where I collected songs that I played in my radio programs. So my main table is a list of songs from my records without connection to a specific record, only to the artist. Today it contains about 1600 entries.
I have created a table for these songs, a table for the artists and a table for the records.
Since a song can appear on several discs, there is a many to many relationship here. Therefore, I have also created a table for this, it is between songs and records in the relationship.
My problem is the form that should add new records or update ones that are already in the record table. The form is based on the record table with a subform based on the table that handles the many to many relationship. This is the table I want to update or add new songs to the records.
The subform contains a combo box with a selection from the song table so that I can select songs for the records that are already in the table.
But I also want to be able to enter new songs if these are not in the table for songs via this subform.

Now to my question.
Each time I select a song from the combo box, a new entry is created in the song table. I only want this to happen if I enter a song that is not in the table already.
I hope my description of the problem can give you an idea of my dilemma.

I will try to post my database here soon. If This will help.

Grateful for all the help are:
Leif
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:14
Joined
May 7, 2009
Messages
19,169
You make the combo unbound so it will not create new record. You use the unbound combo to Filter the form/subform.
 

lodmark

Member
Local time
Today, 14:14
Joined
Jul 24, 2020
Messages
232
Hey again!
Here is the file.
Thanks again!
Leif
 

Attachments

  • Skivor.zip
    518.8 KB · Views: 274

lodmark

Member
Local time
Today, 14:14
Joined
Jul 24, 2020
Messages
232
Thanks "arnelgp" I will try this to night!
Leif
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:14
Joined
May 7, 2009
Messages
19,169
ah.. which form is in question?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:14
Joined
May 7, 2009
Messages
19,169
sorry I have difficulty understanding your table names and relationship.
 

lodmark

Member
Local time
Today, 14:14
Joined
Jul 24, 2020
Messages
232
I can understand that naming the tables and fields in the swedish vocabulary makes it harder to follow.
Is there anything I can do to meke it easyer for you to follow?
I probably don't want to rename them, but maybe I can do something else?
Leif
 

bob fitz

AWF VIP
Local time
Today, 13:14
Joined
May 23, 2011
Messages
4,717
Forgive me for poking my nose in here.
I down loaded the db that you posted. The form that you mentioned called "f_skiva1" opened but required a a parameter.
I haven't read all of the thread so I do I understand your requirement.
The form opens now without the required Parameter Box.
I hope this of some small help.
 

lodmark

Member
Local time
Today, 14:14
Joined
Jul 24, 2020
Messages
232
Hey Bob!
That's not my experience. I downloaded the database and the form f_skiva1 opens with the fisrt post wich is "Spirits Having Flown" by the Bee Gees.
My problem appears when I create a new post in the subform and try to choose a song thats already in the table t_låtar which I can choose from in the combo box "Titel_ny". After moving to next field "Längd" the field "t_Låtar_ID" updates with a new record. I want this to happen only if i write in a song thats not in the list i field "Titel_ny".

And @arnelgp the field that updates with a new record "t_Låtar_ID" is not a combo box and after unbound it it still updates.
Could it be something with the relations between the tables?

Leif
 

bob fitz

AWF VIP
Local time
Today, 13:14
Joined
May 23, 2011
Messages
4,717
In the db that you uploaded in post #3 form f_skiva1 has a subform. The subform as an invalid reference in its WHERE statement:
[Formulär]![f_skiva1]![Skiva_artist_ID]
which I replaced with:
[Forms]![f_skiva1]![Skiva_artist_ID]
 

bob fitz

AWF VIP
Local time
Today, 13:14
Joined
May 23, 2011
Messages
4,717
As others have said, I also find looking at DBs in languages other than English quite challenging.
Does the attached db work any better for you.
 

Attachments

  • SkivorBOB.zip
    563.2 KB · Views: 424

lodmark

Member
Local time
Today, 14:14
Joined
Jul 24, 2020
Messages
232
Thank you Bob.
No not realy, it fixed one problem but the real issue remains unsolved.

Leif
 

isladogs

MVP / VIP
Local time
Today, 13:14
Joined
Jan 14, 2017
Messages
18,186
I haven't looked at your database, but to prevent adding a song title where it already exists you should index the field and set the index to no duplicates
 

lodmark

Member
Local time
Today, 14:14
Joined
Jul 24, 2020
Messages
232
Hey isladogs!
Thats true and i've thought of that. But a song can appear several times in the table but with different artists.
I was thinking about having a double key with both the song and the table ID.
But I do not think it solves the problem with the combo box.
But I will try this.
Thanks.
Leif
 

isladogs

MVP / VIP
Local time
Today, 13:14
Joined
Jan 14, 2017
Messages
18,186
Not sure why you suggest TableID.
Try a composite index of song and artist with no duplicates allowed
 

lodmark

Member
Local time
Today, 14:14
Joined
Jul 24, 2020
Messages
232
Thanks for the suggestion. I have made the change and now I get a message that the record can not be saved because it had created a duplicate.
So far so good.
But even if I have selected an entry that is already in the table, it wants to add a new entry.
What I want is to add the song to another table with the correct ID that I retrieved from the song table.
Feeling sad!
Good night.
Leif
 

isladogs

MVP / VIP
Local time
Today, 13:14
Joined
Jan 14, 2017
Messages
18,186
Sorry I'm lost.
You say it now stops duplicates but that it still tries to add a new entry. How can both be true?

Are you saying that you want to add the original song to a new table automatically?
Is that duplication of data or for another purpose e.g. Songwriters data.

Anyway, this can be achieved if you apply referential integrity between the two tables with cascade update/delete
See my article Relationships and Referential Integrity
 

lodmark

Member
Local time
Today, 14:14
Joined
Jul 24, 2020
Messages
232
I understand this and apologize. I realize it can be perceived as strange. But even though I received a message that the new record could not be saved due to duplicates, there was a new record in the table. I have had other things to do today but I will try to recreate the event and deliver what happens in pictures to you. You write that you did not look in my file otherwise you could add a disc and a song to cause the error yourself. I have had other things to do today but will return as soon as I can. I'm very grateful that you take the time to help me.
Leif
 

isladogs

MVP / VIP
Local time
Today, 13:14
Joined
Jan 14, 2017
Messages
18,186
I didn't look at your file due to the language issues other members had commented on.
Not your fault but most of us are only fluent in English.

If you haven't yet done so, please look at my article which, by chance, includes an example related to albums and songs
 

Users who are viewing this thread

Top Bottom