join key of table not in recordset and searchform

[suckyboy]

Hi

I have a few problems here and hopefully you all can help me.

Basically I will need to create this song database where I can input song fields into the database like artist name, album name etc.

My Relationship is as follow

Record Company ---> Artist ----> Song ------> Album
*************** *************** *********** |
*************** *************** ***********Genr e

Please ignore the asterix

For Record Company:
Record Company ID (Primary Key)
Record Company name

For Artist:
Artist ID (Primary Key)
Artist Name
Company ID

For Song:
Song ID (Primary Key)
Song Name
Album ID
Artist ID
Genre ID
Duration of Song

For Album:
Album ID (Primary Key)
Album Name

For Genre:
Genre ID (Primary Key)
Genre

I created an input dorm which includes

Album ID
Album Name
Artist ID
Artist Name
Genre ID
Genre
Record Company ID
Record Company
Song ID
Song Name
Duration

However, I cant key in information for Artist ID, Artist Name, Song ID, Song Name and Duration.
It gives me the error Cannot add records; Join key of table " " not in recordset. So what can be the problem here?

My 2nd problem is I created a search form with a macro "openquery" linked back to the query which searches for some fields like song name, artists name and genre etc.

My query criteria uses the Forms!Formname! controlset! format

Currently it can only search for one field at a time. So if I want to search for a song which fulfils the Song Name and Artist I which type, the results does not show up correctly. What can be the problem here?

[Stewart Ross]

Hi, and Welcome to Bytes!

If you could post the SQL for your query we will be able to check it out for you.

When a query uses multiple tables joined to reflect the underlying relationships the foreign keys must be from the 'correct' side of these relationships or updating is not possible.

-Stewart

[suckyboy]

Hi,

But How do i get the SQL out for you to see? Sorry, I am a bit newbie in this

[Stewart Ross]

From Design View in the Access Query Editor select View, SQL. Select all the text, copy it to the clipboard, paste it into your post, apply the code tags provided in the message editor (to help us see the structure of your post more clearly).

-Stewart

[suckyboy]

Hi,

I need to create an input form. As such, I have no query. Hence, I am unable to go into the SQL. Do you just want to see the relationship between the different tables?

[Stewart Ross]

Well, the relationships would at least help - but forms are normally bound to (preferably) a query based on the underlying tables, or one underlying table. Unbound forms can't be used for general data entry and editing, so I'm surprised you are going this route.

-Stewart

[suckyboy]

Hi,

How do I capture the relationship for you to see?

Hmm.... Can give me an example how do I link it to a query in this case if I wanna make a form

[Stewart Ross]

The Access Form Wizards will guide you through creating and linking forms to tables or queries. Given that you are clearly at an early stage in understanding Access the best advice I can offer is that you would benefit from reading a good book on Access, or from learning via on-line examples which you may find if you Google search.

The relationships window is not normally capturable in a suitable form for you to post the relationships.

I think it unlikely at this stage that there is much we can do to assist further until you have yourself undertaken basic design and build exercises in Access, particularly regarding tables and table relationships.

-Stewart

[suckyboy]

Hi...Thanks

Anyway, I have a small problem here. Not sure if you can help me. I created a search form using the query by form method.

I have a few fields to search namely artists, song name etc...When I apply the like parameters to one field using the criteria Like "*" & [Forms]![Search]![Artist] & "*" and I search for partial artist name. It works

However, when I apply the Like parameter across the board to all the fields like song name and genre etc, my search form does not work already. What could be the problem?

This is my query SQL

SELECT Artist.[Artist Name], Album.[Album Name], Genre.Genre, Song.[Song Name], Song.[Year of Release], Song.Duration, [Record Company].[Record Company Name], Song.[Play Song]
FROM Genre INNER JOIN (([Record Company] INNER JOIN Artist ON [Record Company].[Record Company ID] = Artist.[Company ID]) INNER JOIN (Album INNER JOIN Song ON Album.[Album ID] = Song.[Album ID]) ON Artist.[Artist ID] = Song.[Artist ID]) ON Genre.[Genre ID] = Song.[Genre ID]
WHERE (((Artist.[Artist Name]) Like "*" & [Forms]![Search]![Artist] & "*" Or (Artist.[Artist Name]) Is Null)) OR (((Album.[Album Name]) Like "*" & [Forms]![Search]![Album] & "*" Or (Album.[Album Name]) Is Null)) OR (((Genre.Genre) Like "*" & [Forms]![Search]![Genre] & "*" Or (Genre.Genre) Is Null)) OR (((Song.[Song Name]) Like "*" & [Forms]![Search]![Song] & "*" Or (Song.[Song Name]) Is Null)) OR (((Song.[Year of Release]) Like "*" & [Forms]![Search]![Year] & "*" Or (Song.[Year of Release]) Is Null)) OR (((Song.Duratio n) Like "*" & [Forms]![Search]![Duration] & "*" Or (Song.Duration) Is Null)) OR ((([Record Company].[Record Company Name]) Like "*" & [Forms]![Search]![Company] & "*" Or ([Record Company].[Record Company Name]) Is Null));