Improve my code? CmdBtns calling filtered form.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm making an org chart style form using cmd btns for each org. When the org
is clicked, it opens a filtered datasheet for the people in that org. Some
orgs need only one parameter, some need two to define the people (Group and
Offc). I made two text boxes for the form to hold and read the values (text
44 and 46). I can probably figure out how to do away with using the text
boxes (I build up subs slowly to see what goes on), but if you could
recommend a shorter way to list the On_Click subs for the fifty or so orgs?
I only put two in this example, one showing the Group value and another
showing the Group and Offc value. Would you call the ShowMembers sub or do
it another way? Learning to code so be nice...Thanks.

Public strGroup As String
Public strOffc As String

Private Sub Form_AfterUpdate()
Text44.Value = ""
Text46.Value = ""
End Sub

Private Sub Command3_Click()
Text44.Value = "ARW"
strGroup = Text44.Value
Call ShowMembers
End Sub

Private Sub Command10_Click()
Text44.Value = "MSG"
Text46.Value = "EM"
strGroup = Text44.Value
strOffc = Text46.Value
Call ShowMembers
End Sub

Private Sub ShowMembers()
Dim stDocName As String
Text44.Value = ""
Text46.Value = ""
Debug.Print strGroup, strOffc
stDocName = "frmOrgChtsub"
If strOffc = "" Then
DoCmd.OpenForm stDocName, acFormDS, , "[group]= '" & strGroup & "'"
Else
DoCmd.OpenForm stDocName, acFormDS, , "[group]= '" & strGroup & "'"
& "AND" & "[office_symbol]= '" & strOffc & "'"
End If
strGroup = ""
strOffc = ""
End Sub
 
Maarkr,

I think I would pass the parameters directly to your ShowMembers subroutine.

Private Sub ShowMembers(ByVal strGroup As String, _
Optional ByVal strOffc As String = "")
....

End Sub

Now, the click event of the command buttons (BTW, change the name of your
command buttons to something meaningful cmd_ARW, cmd_MSG_EM) you can pass the
values directly to the subroutine.

Private Sub cmd_ARW_Click
Call ShowMembers("ARW")
End Sub

Private Sub cmd_MSG_EM
Call ShowMembers("MSG", "EM")
End Sub

This way, you can get rid of all the Text44 and txt46 garbage interspersed
throughout your code.

HTH
Dale
 
excellent...i did learned sumthing 2day...

Dale Fye said:
Maarkr,

I think I would pass the parameters directly to your ShowMembers subroutine.

Private Sub ShowMembers(ByVal strGroup As String, _
Optional ByVal strOffc As String = "")
...

End Sub

Now, the click event of the command buttons (BTW, change the name of your
command buttons to something meaningful cmd_ARW, cmd_MSG_EM) you can pass the
values directly to the subroutine.

Private Sub cmd_ARW_Click
Call ShowMembers("ARW")
End Sub

Private Sub cmd_MSG_EM
Call ShowMembers("MSG", "EM")
End Sub

This way, you can get rid of all the Text44 and txt46 garbage interspersed
throughout your code.

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


Maarkr said:
I'm making an org chart style form using cmd btns for each org. When the org
is clicked, it opens a filtered datasheet for the people in that org. Some
orgs need only one parameter, some need two to define the people (Group and
Offc). I made two text boxes for the form to hold and read the values (text
44 and 46). I can probably figure out how to do away with using the text
boxes (I build up subs slowly to see what goes on), but if you could
recommend a shorter way to list the On_Click subs for the fifty or so orgs?
I only put two in this example, one showing the Group value and another
showing the Group and Offc value. Would you call the ShowMembers sub or do
it another way? Learning to code so be nice...Thanks.

Public strGroup As String
Public strOffc As String

Private Sub Form_AfterUpdate()
Text44.Value = ""
Text46.Value = ""
End Sub

Private Sub Command3_Click()
Text44.Value = "ARW"
strGroup = Text44.Value
Call ShowMembers
End Sub

Private Sub Command10_Click()
Text44.Value = "MSG"
Text46.Value = "EM"
strGroup = Text44.Value
strOffc = Text46.Value
Call ShowMembers
End Sub

Private Sub ShowMembers()
Dim stDocName As String
Text44.Value = ""
Text46.Value = ""
Debug.Print strGroup, strOffc
stDocName = "frmOrgChtsub"
If strOffc = "" Then
DoCmd.OpenForm stDocName, acFormDS, , "[group]= '" & strGroup & "'"
Else
DoCmd.OpenForm stDocName, acFormDS, , "[group]= '" & strGroup & "'"
& "AND" & "[office_symbol]= '" & strOffc & "'"
End If
strGroup = ""
strOffc = ""
End Sub
Click to expand...
Click to expand...
 
Don't know how you are accessing the newsgroup, but if you are using the
Microsoft web site (instead of Outlook Express or some other method) there is
a blue bar at the bottom of the message area for you to rate the post.

Don't forget to do so if you are using that method.

--
Email address is not valid.
Please reply to newsgroup only.


Maarkr said:
excellent...i did learned sumthing 2day...

Dale Fye said:
Maarkr,

I think I would pass the parameters directly to your ShowMembers subroutine.

Private Sub ShowMembers(ByVal strGroup As String, _
Optional ByVal strOffc As String = "")
...

End Sub

Now, the click event of the command buttons (BTW, change the name of your
command buttons to something meaningful cmd_ARW, cmd_MSG_EM) you can pass the
values directly to the subroutine.

Private Sub cmd_ARW_Click
Call ShowMembers("ARW")
End Sub

Private Sub cmd_MSG_EM
Call ShowMembers("MSG", "EM")
End Sub

This way, you can get rid of all the Text44 and txt46 garbage interspersed
throughout your code.

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


Maarkr said:
I'm making an org chart style form using cmd btns for each org. When the org
is clicked, it opens a filtered datasheet for the people in that org. Some
orgs need only one parameter, some need two to define the people (Group and
Offc). I made two text boxes for the form to hold and read the values (text
44 and 46). I can probably figure out how to do away with using the text
boxes (I build up subs slowly to see what goes on), but if you could
recommend a shorter way to list the On_Click subs for the fifty or so orgs?
I only put two in this example, one showing the Group value and another
showing the Group and Offc value. Would you call the ShowMembers sub or do
it another way? Learning to code so be nice...Thanks.

Public strGroup As String
Public strOffc As String

Private Sub Form_AfterUpdate()
Text44.Value = ""
Text46.Value = ""
End Sub

Private Sub Command3_Click()
Text44.Value = "ARW"
strGroup = Text44.Value
Call ShowMembers
End Sub

Private Sub Command10_Click()
Text44.Value = "MSG"
Text46.Value = "EM"
strGroup = Text44.Value
strOffc = Text46.Value
Call ShowMembers
End Sub

Private Sub ShowMembers()
Dim stDocName As String
Text44.Value = ""
Text46.Value = ""
Debug.Print strGroup, strOffc
stDocName = "frmOrgChtsub"
If strOffc = "" Then
DoCmd.OpenForm stDocName, acFormDS, , "[group]= '" & strGroup & "'"
Else
DoCmd.OpenForm stDocName, acFormDS, , "[group]= '" & strGroup & "'"
& "AND" & "[office_symbol]= '" & strOffc & "'"
End If
strGroup = ""
strOffc = ""
End Sub
Click to expand...
Click to expand...
Click to expand...
 
I always click the button to rate anything that's answered well...sometimes
the way our server works it won't always fire thru or I need to log on again
to get it to work... this time I must have clicked it several times and had
to log in twice...

Dale Fye said:
Don't know how you are accessing the newsgroup, but if you are using the
Microsoft web site (instead of Outlook Express or some other method) there is
a blue bar at the bottom of the message area for you to rate the post.

Don't forget to do so if you are using that method.

--
Email address is not valid.
Please reply to newsgroup only.


Maarkr said:
excellent...i did learned sumthing 2day...

Dale Fye said:
Maarkr,

I think I would pass the parameters directly to your ShowMembers subroutine.

Private Sub ShowMembers(ByVal strGroup As String, _
Optional ByVal strOffc As String = "")
...

End Sub

Now, the click event of the command buttons (BTW, change the name of your
command buttons to something meaningful cmd_ARW, cmd_MSG_EM) you can pass the
values directly to the subroutine.

Private Sub cmd_ARW_Click
Call ShowMembers("ARW")
End Sub

Private Sub cmd_MSG_EM
Call ShowMembers("MSG", "EM")
End Sub

This way, you can get rid of all the Text44 and txt46 garbage interspersed
throughout your code.

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


:

I'm making an org chart style form using cmd btns for each org. When the org
is clicked, it opens a filtered datasheet for the people in that org. Some
orgs need only one parameter, some need two to define the people (Group and
Offc). I made two text boxes for the form to hold and read the values (text
44 and 46). I can probably figure out how to do away with using the text
boxes (I build up subs slowly to see what goes on), but if you could
recommend a shorter way to list the On_Click subs for the fifty or so orgs?
I only put two in this example, one showing the Group value and another
showing the Group and Offc value. Would you call the ShowMembers sub or do
it another way? Learning to code so be nice...Thanks.

Public strGroup As String
Public strOffc As String

Private Sub Form_AfterUpdate()
Text44.Value = ""
Text46.Value = ""
End Sub

Private Sub Command3_Click()
Text44.Value = "ARW"
strGroup = Text44.Value
Call ShowMembers
End Sub

Private Sub Command10_Click()
Text44.Value = "MSG"
Text46.Value = "EM"
strGroup = Text44.Value
strOffc = Text46.Value
Call ShowMembers
End Sub

Private Sub ShowMembers()
Dim stDocName As String
Text44.Value = ""
Text46.Value = ""
Debug.Print strGroup, strOffc
stDocName = "frmOrgChtsub"
If strOffc = "" Then
DoCmd.OpenForm stDocName, acFormDS, , "[group]= '" & strGroup & "'"
Else
DoCmd.OpenForm stDocName, acFormDS, , "[group]= '" & strGroup & "'"
& "AND" & "[office_symbol]= '" & strOffc & "'"
End If
strGroup = ""
strOffc = ""
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top