Combos, 6 of them. Loop Perhaps? Help!

[freespiritcherishes]

Hi everyone...

In a table i have 6 hyperlink fields
ItemLinks 1 to 6.

On a form I created 6 combos. One for each ItemLink field.

Eg. CDItemLink1 has a hyperlink to a document on my hardisk.
CDItemLink1Cbo looks up all the records where a user has entered a hyperlink in this field.

But i don't want all 6 combos to show. I want 6 combo buttons numbered 1 to 6 each when clicked will make visible their respective combos so that the user can select from that combo.

But if i make the combo visible on the onclick event of each button, access won't let me make the combo itself invisible when it has lost the focus, so i can't hide the control for when another combo button is clicked and an alternative combo appears. As a result all combos will appear when the idea is to have all 6 sitting on top of each other where they only take up one line of space instead of 6 on my form.

Again, the word loop or case comes to mind, but as per usual, I have no idea. Ive tried everything!

Can anyone help?

 

[PHV]

A starting point:
For i = 1 To 6
Me("CDItemLink" & i & "Cbo").Visible = False
Next i

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya freespiritcherishes . . . . .

A [purple]ShortCut Menu[/purple] would be perfect here. Basically you'll always have one of the combos visible. [blue]A right-click on the combo will bring up the menu[/blue] (a list of the combo's from which to make your selection active/visible, while making the others hidden).

How does this appeal to ya?

See Ya! . . . . . .

 

[freespiritcherishes]

mmmm.. u guys are great. BRB!

 

[TheAceMan1]

OK freespiritcherishes . . . . .

Give a list of descriptions you would like to see in the menu for each combo . . . .

See Ya! . . . . . .

 

[Terpsfan]

First name command buttons Command0, Command1, etc. Then name combo boxes Combo1, Combo2, etc.

Then run this procedure on the click event of each command button.

Private Function ShowCombo()
   Dim ctl as Control
   Dim ctlActive as Control

   For Each ctl in Me.Controls
      If TypeOf ctl Is ComboBox Then
         If Right$(ctl.Name,1) = Right$(ctlActive.Name,1) Then
            ctl.Visible = True
         Else
            ctl.Visible = False
         End If
      End If
   Next ctl

Set ctl = Nothing
Set ctlActive = Nothing

 

[freespiritcherishes]

Hi Aceman,

Well Just the Hyperlink will do.

My combos are:

CDItemLink1Cbo
ControlSource = CDItemLink1
BoundColumn =1
RowSourceType = Table/Query
RowSource = SELECT ContactsDiary.CDItemLink1
FROM ContactsDiary
WHERE (((ContactsDiary.CDItemLink1)<>" "));

CDItemLink2Cbo
ControlSource = CDItemLink2
BoundColumn =1
RowSourceType = Table/Query
RowSource = SELECT ContactsDiary.CDItemLink2
FROM ContactsDiary
WHERE (((ContactsDiary.CDItemLink2)<>" "));

and so on for the other 4.

1 record may have 1 hyperlink used out of the 6 in it and in another record, the user may have used all six hyperlinks.

Which is why i created a separate combo for each CDItemLink field.

In a perfect world, i would cocatenate all six and filter by a param query, but 1) it shows up only the display, and is not an active hyperlink and two, 2) i don't know how to search all 6 Itemlink fields and show the value as a hyperlink in one text box. So right now, any ideas are welcome!


freespirit
aka donna

 

[TheAceMan1]

reespiritcherishes . . . . .

I've heldoff writing the code and instructions for the [blue]ShortCut Menu[/blue] because it appears your latest post is heading in another direction. So a question:

You have a table with six [blue]HyperLink[/blue] fields. [purple]What is the end result of what you wnat to do, and how would the user, use it?[/purple]

See Ya! . . . . . .

 

[PHV]

If a single combo for the 6 fields is an option:
SELECT CDItemLink1 FROM ContactsDiary WHERE CDItemLink1<>" " UNION
SELECT CDItemLink2 FROM ContactsDiary WHERE CDItemLink2<>" " UNION
SELECT CDItemLink3 FROM ContactsDiary WHERE CDItemLink3<>" " UNION
SELECT CDItemLink4 FROM ContactsDiary WHERE CDItemLink4<>" " UNION
SELECT CDItemLink5 FROM ContactsDiary WHERE CDItemLink5<>" " UNION
SELECT CDItemLink6 FROM ContactsDiary WHERE CDItemLink6<>" ";

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

freespiritcherishes . . . . .

Please answer [blue]PHVs[/blue] post. One way or another it will make things clearer . . . . .

See Ya! . . . . . .

 

[freespiritcherishes]

Hi all...

PHV.. thats sounds good will have a go.
Omega.. I got errors.

Aceman, in answer to your question...

the end result ideally, is to see the documents people have linked to each record. As each record has 6 hyperlink fields, Im expecting to see all documents in CDItemLink1Cbo, all the docs in CDItemLink2Cbo and so on.

The idea is for the user to look up documents related to one record. I already have a form that lists the records which shows documents it is linked to. But there is no way of searching the document links themselves in the table without using a param query so i thought just having a combo for each of the fields will at least give the user a list they can look at and see the link to the document whether it be in CDItemLink1 field, CDItemLink2 or whatever. Eg, There could be 10 records related to Childcare but only one record will have a document attached to it. Its knowing which record that is. Am I making any sense?
donna

 

[freespiritcherishes]

Hi PHV,

am i creating an unbound combo with the RowsourceType as Table/Query?

donna

 

[PHV]

Yes.

 

[freespiritcherishes]

Hi PHV,

ignore last post.

It works in that it lists all my documents from all my CDItemLink fields which is great but does not store it as an active hyperlink. Only the display so I can't click it to take me to my document.

As individual combos, it stores as the actual hyperlink. I want the single combo, whether it be 6 sitting on top of each other or just the one, but the hyperlink needs to be Blue instead of Black text! Know what i mean
donna

 

[TheAceMan1]

freespiritcherishes . . . . .

Have you tried setting the [blue]Is HyperLink[/blue] property of the combobox to [purple]Yes![/purple]

See Ya! . . . . . .

 

[freespiritcherishes]

hold the phone...using PHV's code and it showing the display of the hyperlink, i changed the unbound combo to recordsource CDItemLink1 one and it still shows all CDItemLinks1 to 6 in the list and made the links blue as well!!! Thats good .. thats what i want. They're all active.. ive tested also to make sure its not replacing my data where it shouldn't and its not.

Thanks PHV!! Thanks Aceman and to the rest of you. You're amazing. Peace be with u.x
donna

 

[freespiritcherishes]

im back!

i was wrong. It does change the data in my other records if i set the control property of the combo that PHV helped me to create to one of the fields ie CDItemlink1. And, yes Aceman, but it change it to active. Still black text.. now what?.. gettin all excited then
donna:-(

 

[PHV]

Setting the IsHyperlink property of the unbound combo to True should make the text blue underlined.
You may amend the RowSource like this:
SELECT HyperlinkPart(CDItemLink1,2),HyperlinkPart(CDItemLink1,0) FROM ContactsDiary WHERE CDItemLink1<>" " UNION
SELECT HyperlinkPart(CDItemLink2,2),HyperlinkPart(CDItemLink2,0) FROM ContactsDiary WHERE CDItemLink2<>" " UNION
...
SELECT HyperlinkPart(CDItemLink6,2),HyperlinkPart(CDItemLink6,0) FROM ContactsDiary WHERE CDItemLink6<>" ";
With hidden column 1 bounded.
And finally in the Change event procedure:
FollowHyperlink (Me![name of combo])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[freespiritcherishes]

PHV... MARRY ME!
WORKS A DREAM.. THANK YOU VERY MUCH!
DONNA

 

[freespiritcherishes]

Hi PHV, just out of interest, can that be parametized, to prompt for input to restrict the list to select from?

donna