Solved Problems with SQL statement to form (1 Viewer)

[lodmark]

Hey!
I have a new question regarding my database that several of you have contributed with tips and code to.
The basis of the database is an excel file where I entered all the songs I used when I made my radio programs.
The idea with the database was to catalog my vinyl record collection and then use this Excel-list as a starting point.
But all the songs in the list are not from my record collection, some songs are included in the programs because they fit into the theme and then I have downloaded these from Spotify.
Now to my question about how I can add which record the songs are on in the form for Artist / Group (frm_artist).
There are two subforms in that form. The first contains which records I have with the artist and the second which songs the artist recorded and which I used in my programs. Thats why there's a much larger number of songs then records in the database.

I would like to include a field in the last subform which shows the name of the record the song is on.
That way I will be able to see which songs are taken from Spotify, they have no record.
I do not manage to create this, just a lot of duplicates.
I attach my database, headings in Swedish but the objects are in English.

Thanks in advance, Leif does, as Yoda said.

 

[neuroman9999]

I would like to include a field in the last subform which shows the name of the record the song is on.
That way I will be able to see which songs are taken from Spotify, they have no record.

hmmmm...well it looks like your relationships are set up just fine. but I didn't see anywhere in the form interface that you have either sub linked to the main. do you? not that it matters, but that would be, and the pros say, ""proper"".

and since you have the relationships set up just fine, more than likely you can simply lookup the value by putting in the field, something like:

=dlookup("","","")

so that it populates whatever value is needed based on date in the adjacent columns.

 

[lodmark]

hmmmm...well it looks like your relationships are set up just fine. but I didn't see anywhere in the form interface that you have either sub linked to the main. do you? not that it matters, but that would be, and the pros say, ""proper"".

and since you have the relationships set up just fine, more than likely you can simply lookup the value by putting in the field, something like:

=dlookup("","","")
[/QUOTE]

[USER=156907]@neuroman9999[/USER] 
I've looked at the funktion but I'm not shure about how to use it in the form.
can you explain further?

Leif

 

[lodmark]

@neuroman9999

I've looked at the funktion but I'm not shure about how to use it in the form.
can you explain further?

Leif

 

[neuroman9999]

sure thing, Leif.

dlookup is part of the collection of built-in functions called "domain" functions. they all have the same arguments. they are:

function_name("field_name_here", "table_name_here", "optional_field_name_as_lookup_criteria_here = '" & string_value_to_look_up_here & "'")

so, essentially what it is supposed to do is look up information in a specific field, in a table of your choosing, with optional input criteria as the trailing argument (arg 3). so, say for instance, on your subform, you have a field called ""song"" and there is also another field in the sub called ""artist"", you can look up the information in the field called ""record"" in an associated table if that table has some of the same data as your form has. So, in this example, if you want to look up what record the song ""take me home tonight"" by Eddie Money was released on, you would write this in the new field on your subform:

=dlookup("record", "records_table_name", "song = '" & me.field_name_that_holds_song_name_on_subform & "'")

where the me.etc, etc... code literally points to the field on the subform, in the same record, where the value ""take me home tonight"" is.

You can do much more with this function. this is just the basics. Hope it gives some value to you.

 

[lodmark]

=dlookup("record", "records_table_name", "song = '" & me.field_name_that_holds_song_name_on_subform & "'")

I feel stupid.
I cant put the code "in the field".
Probably it should be in the code section or in the controlsource of the field.
But more exatly where?

Leif

 

[neuroman9999]

oh well he$$s bells, I didn't mean to make it sound overly-complex. sorry about that! yes, you are correct. the control source property can accept any function if it is written this way:

=function(argument section here)

so in the case of dlookup, which is what we're talking about, it would be:

=dlookup(field, table, optional_critera_here)

does that help?

if not, I, or someone else, can simply give you a boost by doing a mockup via screenshots or something, or even do it in your file itself. It is attached to your original post.

by the way, is it true that Sewdish people are highly sophisticated? I've always heard that. I've also always heard that they are very into taking care of their own health and emphasizing exercise over driving cars. Is that true? I have a contact I met in Sweden years ago who works for the country's government, and she once told me that she owned a car but rarely drives it.

 

[lodmark]

@neuroman9999
Okay, I think I almost understand.
I wrote this in the control source for the field where I want the name for the record.
"= dlookup ([record_name]; [tbl_record]; [tbl_record]![record_ID] = [music_title])"

And the result is that all the fields look like below.

In this example with the Bee Gees, the row that has a time for the song would have contained the name of the album Sprits Having Flown.

 

[neuroman9999]

hmmmmm....let me take a look myself. I will download your file and tinker with it. I'm not an access expert and never have been, so this may need to be tweaked a bit. I was giving advice based on what I know is possible with this program. but there are many possibilities. what is the actual line of text you put in the control source of this subform field?

 

[lodmark]

Thanks @neuroman9999

 

[neuroman9999]

oh my goodness, I missed what you said! you already posted what you tried. sorry. yeah, this is incorrect, which is what you wrote:

 "= dlookup ([record_name]; [tbl_record]; [tbl_record]![record_ID] = [music_title])"

for one thing, the parsing character is a comma, not a semicolon. let me take that, figure out what you're trying to do, and write it correctly. thanks. hopefully something can be done here...

 

[lodmark]

Sorry, didn't read your comment at the end @neuroman9999
Yes in a way it's true.
My wife and I have two cars, she takes the car to work every day but I ride a bike.
When I inspect my car, the inspector always points out that it has not gone that far. The same is said when it is in for service.
When it cames to be sophisticated I think that it depends on who you are as a person. People from many ather countrys probably say the same about themself.

 

[neuroman9999]

When I inspect my car, the inspector always points out that it has not gone that far.

ha ha ha. that's kind of funny. at least he's informing you that it is still in good shape.

When it cames to be sophisticated I think that it depends on who you are as a person. People from many ather countrys probably say the same about themself.

oh, absolutely. every country wants to think they're the best. I also read recently, as the article came through the news feed, that apparently there are many people in the world that are desiring to move to norwegian countries like Sweden, Denmark and Norway. Article didn't mention the reason though.

 

[lodmark]

oh my goodness, I missed what you said! you already posted what you tried. sorry. yeah, this is incorrect, which is what you wrote:

 "= dlookup ([record_name]; [tbl_record]; [tbl_record]![record_ID] = [music_title])"

for one thing, the parsing character is a comma, not a semicolon. let me take that, figure out what you're trying to do, and write it correctly. thanks. hopefully something can be done here...

In the swedish version of Access it will be a semicolon. Maybe someting about translation? Wonder what happens when you use it in the code-section?

 

[neuroman9999]

In the swedish version of Access it will be a semicolon. Maybe someting about translation? Wonder what happens when you use it in the code-section?

that is good to know. I was unaware. thanks. I don't think these differences are published by microsoft, are they?

 

[lodmark]

@neuroman9999
With your help when you show'ed me a way to do this I managed to get it right.
A little googling and a little trial and error.
I've had to create a query to get all the songs on all the record and then these two lines in it's fields shows record name and record media.
=DLookUp("[qryArtistMusicRecord]![record_name]";"[qryArtistMusicRecord]";"[qryArtistMusicRecord]![music_ID] = [Text8]")
=DLookUp("[qryArtistMusicRecord]![record_media]";"[qryArtistMusicRecord]";"[qryArtistMusicRecord]![music_ID] = [Text8]")

Thanks again!

Leif

 

[neuroman9999]

Hey well that's wonderful news, leif. I'm terribly sorry I was late with my reply but like I said in the p.m. I got sidetracked. But yes getting all the information in one record by way of a query is the only way you could have done it because your data was not closely linked between two tables for this function to work properly without anything else to be done. Good luck to you and I was happy to engage with you in this thread. take care