can i simplify my codes?

Topic

New Reply

Can i simplify my codes?

In a lot of forms and reports i have repeatedly used one and the same condition:
Public function example()
Dim strDocName As String
strDocName = “FOrderinformation”
dim office as control
set office = Forms![FOrderInformation![office]
If IsOpen(strDocName) = True Then
if office = 1 then
do something
elseif office = 2 then
do something
elseif office = 3 then
do something
elseif office = 4 then
do something
elseif office = 5 then
do something
elseif office = 6 then
do something
end if
end if
end function
I have diferent RecordSources RowSources,in the different forms and reports, etc depending on the office number i have selected in the main form called FOrderInformation.
I am writing the above code again and again in different functions but i wonder is it possible
to simplify my process and write one global function?I mean to refer to one basic array or function.

Reply | Quote

Replies

I do a lot of that kind of coding. It isn’t unlike the code you write for commandbars in that you have to pass information into the routine instead of expecting it to grab the values directly from the form. For example, here’s a couple of routines that can be called from various events on a variety of forms. In the first routine, I’m grabbing the active form and then passing that on as an object. By passing the form object to the routine, the function can get the rest of the information it needs from that form object.

'*********************************
Public Function CloseMe()
  On Error Resume Next
  Call ExitForm(Screen.ActiveForm)
End Function

'*************************
Public Sub ExitForm(frm As Form, _
                   Optional GoToForm As String, _
                   Optional blnUnbound As Boolean)
  On Error GoTo glrExitForm_err
  Dim intResult As Integer
  
  If GoToForm = "" Then
    GoToForm = "fmnuSwitchboard"
  End If
  intResult = ConditionalSave(frm, blnUnbound)
  If intResult  vbCancel Then
    DoCmd.Close acForm, frm.Name
    DoCmd.OpenForm GoToForm
  End If
ExitForm_exit:
    Exit Sub
ExitForm_err:
    MsgBox Err.Description
    Resume ExitForm_exit
End Sub

'********************************
Public Function ConditionalSave(frm As Form, _
                Optional blnUnbound As Boolean) As Integer
  On Error Resume Next
  Dim intResponse As Integer
  
  'if the form is dirty ask the user if the record
  'should be saved
  If frm.Dirty Then
    'if the form is not unbound, continue
    If Not blnUnbound Then
      intResponse = MsgBox(Prompt:="Save changes to the " _
               & "current record?" _
               , Buttons:=vbYesNoCancel + vbDefaultButton1, _
               Title:="Save Changes")
      Select Case intResponse
        Case vbYes
          'attempt to save the record
          DoCmd.RunCommand acCmdSaveRecord
          If Err  0 Then
            MsgBox "Unable to save changes."
            frm.Undo
          End If
        Case Else
          'the record/changes will be discarded
      End Select
      'return the user's response
      ConditionalSave = intResponse
    Else
      ConditionalSave = vbNo
    End If
  Else
    ConditionalSave = vbNo
  End If
End Function

In your code, if you actually pass the Office control into the routine, the rest of the information can be extracted from there. The form already has to be open in order to pass the control as an object, and the control carries its Parent property with it, which will return an instance of its parent form object. Here’s just a few ideas of things you could do within your routine just by passing that control object.

Public function example(ctl as control)
Dim strDocName As String
Dim frm As Form

'get the control's parent form object
Set frm = ctl.Parent
strDocName = frm.Name

'see what kind of a control it is and
'take the appropriate action
Select Case ctl.ControlType
 Case acTextBox, acComboBox, acListBox, _
      acCheckBox, acOptionButton, acOptionGroup
   'you can only test for values on controls
   'that can hold data, although you can test for the
   'tag property on almost any control
   If ctl.tag = "1" then
     do something   
     ...
   ElseIf ctl.ControlSource = "" Then
     'reset unbound controls     
     Call ResetUnboundCtl(ctl)
   End If
 ...
End Select
End Function

Does that give you a better idea of the kind of things you can do with a single public routine?

Reply | Quote

I’m not sure whether you questions was a general question on using a global function or specific to this function? The nature of your function (Using the IsOpen function to check whether a form is opened) would seem to indicate you are calling this function from yet another form? Perhaps if you could explain the nature of the function call, I could give some clues as to how to make it a global function usable by any form.

As for some specifics, you might want to use Select Case instead of all the nested ElseIf statements.

Reply | Quote