Help with complex drop down where selection will determine VBA code

[magmike]

I have multiple buttons on a form. I would like to replace all the
buttons with a DropDown/ComboBox.

Each button uses the Application.FollowHyperlink function to visit a
particular webpage for research using the pages hyperlink structure
mixed with field data. For example, one button is for google maps.
Although this is not the exact link, the OnClick event looks like
this:

Application.FollowHyperlink "http://maps.google.com?address=" &
[address] & "&zip=" & [zip]

Another button uses a newspaper's search function to search for news
stories about the company in the record, and another for the contact's
name.

Of course, every time a new source is discovered, a new button must be
created (taking up form space). To streamline this, I am creating a
table where I can at any time add another source by adding a record,
giving it a name (i.e. New York Times) for the combo box, and a link
(i.e. "newpaperweb.com?search=" & [company]) for the event command.
When the user would use the pull down and make a selection, the
AfterUpdate event would use "Application.FollowHyperlink
[cmbSourceLink]" to direct to the source. This is the easy part.

What I am unsure about, however, is how to add an "All" selection to
the combo box. I currently have a button for this purpose, which has a
separate line in the code for each page. With IE's new tabbed
browsing, it conveniently opens one browser window with multiple tabs,
making researching a company or contact very efficient for the user.
Currently, when I do add a source, not only do we have to add a button
for it, we have to add the code to the SearchAll button as well.

Can anyone help me add an "All" selection to this combo box that would
use the Application.FollowHyperlink function with every record in this
table I am going to create and automatically use any new record that
are added later?

Thanks in advance!
magmike

 

[Dirk Goldgar]

I have multiple buttons on a form. I would like to replace all the
buttons with a DropDown/ComboBox.

Each button uses the Application.FollowHyperlink function to visit a
particular webpage for research using the pages hyperlink structure
mixed with field data. For example, one button is for google maps.
Although this is not the exact link, the OnClick event looks like
this:

Application.FollowHyperlink "http://maps.google.com?address=" &
[address] & "&zip=" & [zip]

Another button uses a newspaper's search function to search for news
stories about the company in the record, and another for the contact's
name.

Of course, every time a new source is discovered, a new button must be
created (taking up form space). To streamline this, I am creating a
table where I can at any time add another source by adding a record,
giving it a name (i.e. New York Times) for the combo box, and a link
(i.e. "newpaperweb.com?search=" & [company]) for the event command.
When the user would use the pull down and make a selection, the
AfterUpdate event would use "Application.FollowHyperlink
[cmbSourceLink]" to direct to the source. This is the easy part.

What I am unsure about, however, is how to add an "All" selection to
the combo box. I currently have a button for this purpose, which has a
separate line in the code for each page. With IE's new tabbed
browsing, it conveniently opens one browser window with multiple tabs,
making researching a company or contact very efficient for the user.
Currently, when I do add a source, not only do we have to add a button
for it, we have to add the code to the SearchAll button as well.

Can anyone help me add an "All" selection to this combo box that would
use the Application.FollowHyperlink function with every record in this
table I am going to create and automatically use any new record that
are added later?

Here's a rough example of the general idea. It's based on the idea that any
text box on the form might contain something to be searched for by one of
the search URLs in the combo box. Any of the URLs might contain one or more
of the names of the text boxes on the form, enclosed in square brackets
("[controlname]").

'----- start of code for form module -----
Sub FillInFormValues(ByRef strURL As String)

' This proc replaces "[controlname]" in the argument URL with
' the value of that control.

Dim ctl As Access.Control

For Each ctl In Me.Controls
With ctl
If .ControlType = acTextBox Then
strURL = Replace(strURL, "[" & .Name & "]", .Value)
End If
End With
Next ctl

End Sub


Private Sub cboLookup_AfterUpdate()

Dim ctl As Access.Control
Dim strURL As String
Dim i As Long

With cboLookup

If IsNull(.Value) Then Exit Sub

If .Value = "All" Then

For i = 0 To .ListCount - 1
If .ItemData(i) <> "All" Then
strURL = .ItemData(i)
FillInFormValues strURL
Application.FollowHyperlink strURL
End If
Next i

Else

strURL = .Value
FillInFormValues strURL
Application.FollowHyperlink strURL

End If

End With

End Sub
'----- end of code for form module -----

 

[magmike]

I have multiple buttons on a form. I would like to replace all the
buttons with a DropDown/ComboBox.

Each button uses the Application.FollowHyperlink function to visit a
particular webpage for research using the pages hyperlink structure
mixed with field data. For example, one button is for google maps.
Although this is not the exact link, the OnClick event looks like
this:

   Application.FollowHyperlink "http://maps.google.com?address=" &
[address] & "&zip=" & [zip]

Another button uses a newspaper's search function to search for news
stories about the company in the record, and another for the contact's
name.

Of course, every time a new source is discovered, a new button must be
created (taking up form space). To streamline this, I am creating a
table where I can at any time add another source by adding a record,
giving it a name (i.e. New York Times) for the combo box, and a link
(i.e. "newpaperweb.com?search=" & [company]) for the event command.
When the user would use the pull down and make a selection, the
AfterUpdate event would use "Application.FollowHyperlink
[cmbSourceLink]" to direct to the source. This is the easy part.

What I am unsure about, however, is how to add an "All" selection to
the combo box. I currently have a button for this purpose, which has a
separate line in the code for each page. With IE's new tabbed
browsing, it conveniently opens one browser window with multiple tabs,
making researching a company or contact very efficient for the user.
Currently, when I do add a source, not only do we have to add a button
for it, we have to add the code to the SearchAll button as well.

Can anyone help me add an "All" selection to this combo box that would
use the Application.FollowHyperlink function with every record in this
table I am going to create and automatically use any new record that
are added later?

Here's a rough example of the general idea.  It's based on the idea that any
text box on the form might contain something to be searched for by one of
the search URLs in the combo box.  Any of the URLs might contain one ormore
of the names of the text boxes on the form, enclosed in square brackets
("[controlname]").

'----- start of code for form module -----
Sub FillInFormValues(ByRef strURL As String)

    ' This proc replaces "[controlname]" in the argument URL with
    ' the value of that control.

    Dim ctl As Access.Control

    For Each ctl In Me.Controls
        With ctl
            If .ControlType = acTextBox Then
                strURL = Replace(strURL, "[" & .Name & "]", .Value)
            End If
        End With
    Next ctl

End Sub

Private Sub cboLookup_AfterUpdate()

    Dim ctl As Access.Control
    Dim strURL As String
    Dim i As Long

    With cboLookup

        If IsNull(.Value) Then Exit Sub

        If .Value = "All" Then

            For i = 0 To .ListCount - 1
                If .ItemData(i) <> "All" Then
                    strURL = .ItemData(i)
                    FillInFormValues strURL
                    Application.FollowHyperlink strURL
                End If
            Next i

        Else

            strURL = .Value
            FillInFormValues strURL
            Application.FollowHyperlink strURL

        End If

    End With

End Sub
'----- end of code for form module -----

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -

Wow - that's amazing! Thanks.

Let me clarify a couple of things. cboLookup would be the name of my
combo box, and I would just have it pull the values from the table I
mentioned. Is this correct?

And what is the textbox referenced?

Other than that, is there anything else in there I am supposed to
modify to fit my situation? Normally I am able to tell, but this
particular scenario is uniquely knew to me.

Thanks!
magmike

 

[magmike]

news:7adcc0d6-d3c0-4c5c-a4b8-81bcb4eac31f@k13g2000hse.googlegroups.com....

I have multiple buttons on a form. I would like to replace all the
buttons with a DropDown/ComboBox.
Each button uses the Application.FollowHyperlink function to visit a
particular webpage for research using the pages hyperlink structure
mixed with field data. For example, one button is for google maps.
Although this is not the exact link, the OnClick event looks like
this:
   Application.FollowHyperlink "http://maps.google.com?address=" &
[address] & "&zip=" & [zip]
Another button uses a newspaper's search function to search for news
stories about the company in the record, and another for the contact's
name.
Of course, every time a new source is discovered, a new button must be
created (taking up form space). To streamline this, I am creating a
table where I can at any time add another source by adding a record,
giving it a name (i.e. New York Times) for the combo box, and a link
(i.e. "newpaperweb.com?search=" & [company]) for the event command.
When the user would use the pull down and make a selection, the
AfterUpdate event would use "Application.FollowHyperlink
[cmbSourceLink]" to direct to the source. This is the easy part.
What I am unsure about, however, is how to add an "All" selection to
the combo box. I currently have a button for this purpose, which has a
separate line in the code for each page. With IE's new tabbed
browsing, it conveniently opens one browser window with multiple tabs,
making researching a company or contact very efficient for the user.
Currently, when I do add a source, not only do we have to add a button
for it, we have to add the code to the SearchAll button as well.
Can anyone help me add an "All" selection to this combo box that would
use the Application.FollowHyperlink function with every record in this
table I am going to create and automatically use any new record that
are added later?

Here's a rough example of the general idea.  It's based on the idea that any
text box on the form might contain something to be searched for by one of
the search URLs in the combo box.  Any of the URLs might contain one or more
of the names of the text boxes on the form, enclosed in square brackets
("[controlname]").

'----- start of code for form module -----
Sub FillInFormValues(ByRef strURL As String)

    ' This proc replaces "[controlname]" in the argument URL with
    ' the value of that control.

    Dim ctl As Access.Control

    For Each ctl In Me.Controls
        With ctl
            If .ControlType = acTextBox Then
                strURL = Replace(strURL, "[" & .Name & "]", .Value)
            End If
        End With
    Next ctl

End Sub

Private Sub cboLookup_AfterUpdate()

    Dim ctl As Access.Control
    Dim strURL As String
    Dim i As Long

    With cboLookup

        If IsNull(.Value) Then Exit Sub

        If .Value = "All" Then

            For i = 0 To .ListCount - 1
                If .ItemData(i) <> "All" Then
                    strURL = .ItemData(i)
                    FillInFormValues strURL
                    Application.FollowHyperlink strURL
                End If
            Next i

        Else

            strURL = .Value
            FillInFormValues strURL
            Application.FollowHyperlink strURL

        End If

    End With

End Sub
'----- end of code for form module -----

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -

Wow - that's amazing! Thanks.

Let me clarify a couple of things. cboLookup would be the name of my
combo box, and I would just have it pull the values from the table I
mentioned. Is this correct?

And what is the textbox referenced?

Other than that, is there anything else in there I am supposed to
modify to fit my situation? Normally I am able to tell, but this
particular scenario is uniquely knew to me.

Thanks!
magmike- Hide quoted text -

- Show quoted text -

Oops! I almost forgot - one of these buttons uses an If/Then/Else to
determine which Application.FollowHyperlink to use - as it would
search by URL if present (preferable), then by Company name if URL is
not present. How would I fold that one into the above concept? And
there is one other button that actually calls up a form using about
six lines of code. Could I use an additional field in the table to
select "Link" or "Code" and how would that fall into the above
scenario?

Thanks (and sorry for the run around)!
magmike

 

[Dirk Goldgar]

Oops! I almost forgot - one of these buttons uses an If/Then/Else to
determine which Application.FollowHyperlink to use - as it would search by
URL if present (preferable), then by Company name if URL is not present.
How would I fold that one into the above concept? And there is one other
button that actually calls up a form using about six lines of code. Could
I use an additional field in the table to

select "Link" or "Code" and how would that fall into the above scenario?


You certainly could use an addtional field in the table, and set the
properties of the combo box to include that as another column. Maybe you'd
have a table with three columns (just an idea), like this:

Actions
--------------------------------
ActionDescription
ActionType
ActionTarget



The ActionDescription field would be what your combo shows to the user; the
other fields would be present in your combo box as hidden columns (column
width = 0). The ActionType would be a token or keyword that would instruct
your code what to do with the ActionTarget value.

Now the code that processes the user's selection in the combo's AfterUpdate
event must be more elaborate, checking the ActionType column and taking
action appropriately. Here's rough example:

'----- start of code -----
Private Sub cboLookup_AfterUpdate()

With cboLookup
If Not IsNull(.Value) Then
ProcessAction .Column(1), .Column(2)
End If
End With

End Sub


Private Sub ProcessAction(pAction As String, pTarget As String)

' Process action <pAction> with target <pTarget>

Dim i As Long
Dim strAction As String
Dim strTarget As String
Dim astrSplitTarget() As String
Dim strForm As String
Dim strCriteria As String

If pAction = "All" Then

' Loop through all the other entries in the
' combo box and process them.

With Me!cboLookup
For i = 0 To .ListCount - 1
strAction = .Column(1, i)
If strAction <> "All" Then
strTarget = .Column(2, i)
' Call this routine recursively
ProcessAction strAction, strTarget
End If
Next i
End With

Else
' Just process this entry.

strTarget = pTarget

FillInFormValues strTarget

Select Case pAction

Case "Hyperlink"
Application.FollowHyperlink strTarget

Case "OpenForm"
' Extract form and criteria from target. Assume they
' are separated by a comma.

astrSplitTarget = Split(strTarget, ",")

If UBound(astrSplitTarget) > 0 Then
DoCmd.OpenForm astrSplitTarget(0), _
WhereCondition:=astrSplitTarget(1)
Else
DoCmd.OpenForm astrSplitTarget(0)
End If

Case "URL/Name"
' Search by URL if that field is not Null;
' else search by company name. The alternate
' hyperlinks are in strTarget, separated by a
' comma.

astrSplitTarget = Split(strTarget, ",")

If IsNull(Me!URL ) Then
' No URL, so use second hyperlink
Application.FollowHyperlink astrSplitTarget(1)
Else
' Use first hyperlink
Application.FollowHyperlink astrSplitTarget(0)
End If

End Select

End If

End Sub
'----- end of code -----

The above code doesn't contain any error-checking, which I leave up to you,
and it's based on a lot of assumptions that probably aren't exactly correct,
but it should give you an idea of how to go about this, and some of the
tools you might need to do it.

 

[Dirk Goldgar]

(re-posting, as my original reply hasn't appeared)

Let me clarify a couple of things. cboLookup would be the name of my combo
box, and I would just have it pull the values from the table I mentioned.
Is this correct?

Yes. I just picked "cboLookup" as the combo's name arbitrarily, while I was
setting up a test form to see how this would work.

And what is the textbox referenced?

There is no specific text box mentioned in the code. What it does -- and
this may well be changed based on how you have your form set up -- is loop
through all the controls on the form:

.... and of those controls, look only at the ones that are text boxes:

to see if that text box's name is used in the URL that came from the table,
and replace that name with the value of the text box.

Now, this particular behavior is arbitrary. You might want to have other
types of controls than just text boxes be candidates for replacement. In
that case, you still need to limit it to controls that have Value properties
(no labels, buttons, lines, or rectangles, for example). So you might limit
it to control types acTextBox, acComboBox, acListBox, and acCheckBox.
However, as you broaden the range of control types you're looking at, you
have to allow for the fact that some of them only have a Value property
under certain circumstances. For example, a check box that is part of an
option group doesn't have a Value property, and referring to that property
will raise an error. So you need code to allow for that.

Another alternative is to tag the controls you want to consider for
replacement. That would allow for a more efficient process. You would set
the Tag property of each such control to some specific value -- say,
"REPLACE" -- and then have your replacement-loop look only at those
controls:

For Each ctl In Me.Controls
With ctl
If .Tag = "REPLACE" Then
strURL = Replace(strURL, "[" & .Name & "]", .Value)
End If
End With
Next ctl

I actually like this better than what I originally posted, but I didn't want
to get into those details last night.

Other than that, is there anything else in there I am supposed to modify
to fit my situation? Normally I am able to tell, but this particular
scenario is uniquely knew to me.

Just remember that this is based on the simple use of the control's name,
enclose in square brackets, in the URL you get from the table. So name your
controls meaningfully.

 

[???]

select "Link" or "Code" and how would that fall into the above scenario?


You certainly could use an addtional field in the table, and set the
properties of the combo box to include that as another column. Maybe you'd
have a table with three columns (just an idea), like this:

Actions
--------------------------------
ActionDescription
ActionType
ActionTarget



The ActionDescription field would be what your combo shows to the user; the
other fields would be present in your combo box as hidden columns (column
width = 0). The ActionType would be a token or keyword that would instruct
your code what to do with the ActionTarget value.

Now the code that processes the user's selection in the combo's AfterUpdate
event must be more elaborate, checking the ActionType column and taking
action appropriately. Here's rough example:

'----- start of code -----
Private Sub cboLookup_AfterUpdate()

With cboLookup
If Not IsNull(.Value) Then
ProcessAction .Column(1), .Column(2)
End If
End With

End Sub


Private Sub ProcessAction(pAction As String, pTarget As String)

' Process action <pAction> with target <pTarget>

Dim i As Long
Dim strAction As String
Dim strTarget As String
Dim astrSplitTarget() As String
Dim strForm As String
Dim strCriteria As String

If pAction = "All" Then

' Loop through all the other entries in the
' combo box and process them.

With Me!cboLookup
For i = 0 To .ListCount - 1
strAction = .Column(1, i)
If strAction <> "All" Then
strTarget = .Column(2, i)
' Call this routine recursively
ProcessAction strAction, strTarget
End If
Next i
End With

Else
' Just process this entry.

strTarget = pTarget

FillInFormValues strTarget

Select Case pAction

Case "Hyperlink"
Application.FollowHyperlink strTarget

Case "OpenForm"
' Extract form and criteria from target. Assume they
' are separated by a comma.

astrSplitTarget = Split(strTarget, ",")

If UBound(astrSplitTarget) > 0 Then
DoCmd.OpenForm astrSplitTarget(0), _
WhereCondition:=astrSplitTarget(1)
Else
DoCmd.OpenForm astrSplitTarget(0)
End If

Case "URL/Name"
' Search by URL if that field is not Null;
' else search by company name. The alternate
' hyperlinks are in strTarget, separated by a
' comma.

astrSplitTarget = Split(strTarget, ",")

If IsNull(Me!URL ) Then
' No URL, so use second hyperlink
Application.FollowHyperlink astrSplitTarget(1)
Else
' Use first hyperlink
Application.FollowHyperlink astrSplitTarget(0)
End If

End Select

End If

End Sub
'----- end of code -----

The above code doesn't contain any error-checking, which I leave up to you,
and it's based on a lot of assumptions that probably aren't exactly correct,
but it should give you an idea of how to go about this, and some of the
tools you might need to do it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)