Hyperlink within a query (1 Viewer)

[cawash]

i have a query that gives me artist's names. is it possible to click on a name from the query and it takes me to that particular name in a form.

the form i have has artist's and a subform containing a list of the artist's songs.

this is why i would like to have a hyperlink from the query for convenience purposes.

if there are any other ideas for what i am trying to do please pass it on.

the 'find' key is OK, but if i don't know what artist to find, than the 'find' key won't help me much.

TIA

 

[TessB]

I have no idea how you would do that from a "Query" per se... but here is how I would handle this issue.

Create a new form. Now, build a dropdown list on that form that displays the artist names from your query. Then, you can either add a "GO" button that opens up your existing form and subform displaying songs. To do this, make sure that the existing form is based off a query where the criteria for artist name is taken from the new form. (= NewFormName!cboArtist)

Does this make sense to you?

Tess

 

[cawash]

Hi Tess, thanks for the idea.

I think I understand what your saying except for the "Go" button. I'm not good at coding so I will need help in this area.

I've made a new form with a dropdown based off the Artist query.

As the user selects the Artist from the dropdown, it will go to that Artist record in the original form/subform.

How do I 'on click of the selected Artist name' go the the specific record?

Thanks,
cw

 

[cawash]

Tess I just thought of something else about your idea.

You mentioned to base the original form/subform to the query. The original form/subform is used to input new Artist and their songs. If I base the original form to a query won't this keep me from adding any new data to the existing records or even add new records to the original form/subform?

Thanks again.
cw

 

[TessB]

cawash,

Not necessarily. The form should still allow editing and additions and deletions, etc. But, if you are concerned about keeping the functionality of your "Maintain Artists" and ""View Artists" forms isolated, you can always copy the original and modify the copy for your "View" functionality.

So, write your new query with all the information that is needed for your form and in the criteria section of your Artist name, reference your new Form

Criteria =

NewForm![cboArtistName]

Then in that New Form, the code you are looking for, whether you make a button to do it or an AfterUpdate event on your combo box, is;

DoCmd.OpenForm "Whatever you named your form based on the query"

I know that when you are first starting out, sometimes you don't catch on what to do, or where to put code for the first time, so if you are still stuck, don't even hesitate to reply again.... or even PM me and we can take it off line for your convenience.

 

[cawash]

Tess,

Thanks for the offer of Help, I may take you up on that.

I created the query and based the form/SF to it and was able to add new records to the form.

However, (the form with the Artist dropdown) when I select an Artist from the dropdown I go directely to the Artist form/SF rather than to the Artist record.

I want to select Metallica from the dropdown list, and than go directely to Metallica's record in the form/SF.

I think I'm missing something



CW

 

[TessB]

okay... tell me what the criteria section of your query reads under the Artist field.

 

[cawash]

[Artists Query]![cboArtistsID]

'Artists Query' is the Query name and 'ArtistsID' is the form/SF name

 

[TessB]

Okay, I need you to enter the FORM name in the criteria...

What is the name of the form where you choose the artist name?

Okay so open the form that you want to choose the artist name on the drop down box..... Find out what you named the form... click on the combo box... see what that is named....

Now, in the criteria of your ArtistID, in your query type

=[Whatever you named the search form]![Whatever you named the combo box]

I am going home now, and it will take me about an hour to get there..... but I'll sign on again at home and see how you did.

 

[cawash]

I think I'm going to start over.... I'm really confussed now!

I'll give you the info I have so far.

The original form/SF is named frmArtistsID, and based on the query named 'qyArtistID' the new form that is used to select an artist is named 'frmSelect' and the combo box within the new form is named 'cboDropDown'.

The qyArtistID has "Distinct" written in the SQL otherwise I would be getting Metallica listed 115 times. (does this make a difference with what I'm wanting to do?)

Also, this same query has only one field, which is Artist.

Right now, I do not have anything written in the Criteria of 'qyArtistsID".

How many queries I'm I supposed to have anyway? I only have one, which gives me a list of Artists. I'm beginning to think that I need two.

I'll let you settle in at home and I'm going to reread and rethink your posts.

CW

 

[cawash]

Hi Tess,

I tried it again this morning and I'm still going to the form instead of a specific record. No matter which artist I select, I go to the first record of the form.

Do you think there is a simpler way to this than what we're trying?

Thanks again
CW

 

[TessB]

Okay, let me explain exactly what we are trying to do. I apologize for not taking the time to illustrate better previously. If I had, I could have saved you hours of aggravation.

Your original form is now based on a query that, when opened, returns ALL artists in your database. When you put, in the criteria section of your query, under ArtistID, this code;

=[frmSelect]![cboDropDown]

The query will then look at the Select form, and see which artist you want to pull up and return ONLY that artist. Therefore, when the form is opened, since it is based on that query, you will only see that Artist's record.

Now, I want you to go ahead and try that.

And then read my next post because including it here makes it too lengthy for one posting.

 

[TessB]

You asked if there was another way of doing this. The answer - a resounding YES. There are a million ways of doing the same thing. Another way of doing it is putting the same combo box in your Form Header, and, in your AfterUpdate event of the combo box, type code that might resemble this:

Me.RecordsetClone.FindFirst "[ArtistID] = '" & Me![cboDropDown] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark

Your form, in this case would be based on the query minus criteria in the ArtistID field, returning all records... and the combobox in the header would serve as a "find" feature.

Whichever way you prefer. But, I think it's great to learn to do it both ways. So, play a little!
(But I think that Music may be more your hobby as opposed to programming? So, if you want to learn more, play with it... if you just want to get the job done and move on... go with either plan of attacks for your form.)