Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Public Function (A2k)

    The following code resides on a single command button.
    There are numerous buttons ranging from cmdUnit01 thru to cmdUnit17.
    The caption part of the code (Which I have highlited as bold) tglUnit01 in this case, comes from numerous toggles ranging from tglUnit01 thru to tglUnit17.
    My code as below works fine but needs to be placed on each cmdUnit and tglUnit(xx) number changed respectively.
    Can this code be changed to a public function so cutting down the repetitive coding on each button.

    Private Sub cmdUnit01_Click()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strFilter As String
    Dim strSQL As String
    Dim strValue As String
    Set db = CurrentDb
    strFilter = "qryStaff.Unit = " & Chr(34) & Forms!frmAttendance!tglUnit01.Caption & Chr(34)
    strSQL = "Select * From tblStaff Where " & strFilter
    DoCmd.OpenForm "frmStaff", acViewNormal, , strFilter
    Untoggle
    End Sub

  2. #2
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Public Function (A2k)

    why not dim a string variable, set that equal to the caption of the active control, and concantetate (sp?) that variable to the strfilter?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Public Function (A2k)

    Hi Dave,

    I'm working on something that looks similar so I've posted a copy.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Public Function (A2k)

    Try this function:

    Private Function UnitClick(n As Integer)
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strFilter As String
    Dim strSQL As String
    Dim strValue As String
    Set db = CurrentDb
    strFilter = "qryStaff.Unit = " & Chr(34) & Forms!frmAttendance.Controls("tglUnit" & Format(n, "00")).Caption & Chr(34)
    strSQL = "Select * From tblStaff Where " & strFilter
    DoCmd.OpenForm "frmStaff", acViewNormal, , strFilter
    Untoggle
    End Function

    In the On Click event of cmdUnit01, replace [Event Procedure] by =UnitClick(1)
    Analogous for cmdUnit02, etc., until:
    In the On Click event of cmdUnit17, replace [Event Procedure] by =UnitClick(17)

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Public Function (A2k)

    Thanks all for the input.

    Rupert, your example looks interesting.

    Hans I've used your version, it works fine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •