VBA to set Form properties when the form opens

[Satal Keto]

What I am trying to do is create a VBA Function which takes a form as a parameter and then sets certain properties on that form, which Im thinking could either be run on the Form Open event or when actually opening the form (so creating another function which performs the property updates and then opens the form).
So something like CSS for Forms (for anyone who isn't familiar with CSS, it is a way for web developers to set style properties for their web pages and if they make one change to the CSS all of the pages incorporate that change).

I have been looking into it and from what I have found the only way of doing this is have the code which opens the form open the form in design view, make the appropriate changes, save it and then open the form normally.
Although this would work, it does mean that the Access Database cannot be made into an MDE as once it is in MDE form you cannot open forms in design view.

I was wondering whether anyone knows of anyway that it could be done so that it would allow for the Access Project to be made into an MDE.

 

[boblarson]

which properties?

 

[Satal Keto]

I was looking at setting the Modal, Min Max Buttons and Border style Properties.
I'm not certain on all of the properties which will be set as I am looking to see if there is an easier way of doing something which is already done by the company which I work for, which is when we're developing a Database System we want to be able to do things like move about forms without having to deal with them being modal but we want the end users of the system to have to use the forms modally. At the moment we run a Access add-on which loops through the forms changing their properties when we start work and before we make it into a mde to give to the end user. But if the properties were set according to a setting then it would save having to run the add-on.
I hope that that makes sense

 

[CyberLynx]

This can be done quite easily but as you have said, the Form in question will need to be in Design View to modify specific properties. Here is a Function that can do the job. You really wont notice any real difference when the Form is opened.

Public Function SetFormProperties(ByVal Frm As String, _
                Optional ByVal FrmMode As Integer = 1) As Integer
   [COLOR="DarkGreen"]'If SuccessFul this Function willl Return 1.
   'If Unsuccessfull this Function will Return 0.
   'The Frm parameter is to contain the Form Name string.
   'The optional FrmMode parameter is to contain either 0 or
   '1 (default). If nothing is passed then this parameter will
   'automatically be 1.
   
   'Prepare Error Trapping...
   On Error GoTo Error_SetFormProperties
   
   'Declare a Static Integer variable to be used to
   'prevent Form Open/Close looping[/COLOR]
   Static FrmProc As Integer
   
   [COLOR="DarkGreen"]'If the Static Variable FrmProc is greater than 0
   'then we have already run this funtion. So we set
   'the Variable to 0 and Exit[/COLOR]
   If FrmProc > 0 Then FrmProc = 0: Exit Function
   
   [COLOR="DarkGreen"]'Set the Static Variable FrmProc to 1 so as to
   'indicate that we have run this Function.[/COLOR]
   FrmProc = 1
   
  [COLOR="DarkGreen"] 'Close the supplied Form.[/COLOR]
   DoCmd.Close acForm, Frm
   [COLOR="DarkGreen"]'ReOpen the supplied Form but in Design View.[/COLOR]
   DoCmd.OpenForm Frm, acDesign
      
   [COLOR="DarkGreen"]'Set the Form Properties we want to set
   'Controls within the Form can be set as
   'well at this point if desired.
   
   'If FrmMode = 1 (default) then set the desired Form Properites[/COLOR]
   If FrmMode = 1 Then
      Forms(Frm).PopUp = True
      Forms(Frm).Modal = True
      Forms(Frm).BorderStyle = 3    [COLOR="DarkGreen"]'Dialog[/COLOR]
      Forms(Frm).MinMaxButtons = 0 [COLOR="DarkGreen"] 'None[/COLOR]
   Else   [COLOR="DarkGreen"]'If FrmMode <> 1  then set the Form Properites to original[/COLOR]
      Forms(Frm).PopUp = False
      Forms(Frm).Modal = False
      Forms(Frm).BorderStyle = 2    [COLOR="DarkGreen"]'Sizable[/COLOR]
      Forms(Frm).MinMaxButtons = 3  [COLOR="DarkGreen"]'Both Enabled[/COLOR]
   End If
      
   [COLOR="DarkGreen"]'Save changes to the supplied Form.[/COLOR]
   DoCmd.Save acForm, Frm
   [COLOR="DarkGreen"]'Open the supplied Form in Normal view. If you want additional
   'OpenForm parameters then you will need to add them to the
   'Parameters list for this Function.[/COLOR]
   DoCmd.OpenForm Frm, acNormal
   
  [COLOR="DarkGreen"] 'The Function was successfull so let's return 1[/COLOR]
   SetFormProperties = 1
   
Exit_SetFormProperties:
   Exit Function
   
Error_SetFormProperties:
   [COLOR="DarkGreen"]'Display Error if any.[/COLOR]
   MsgBox Err.Number & "  - " & Err.Description, _
          vbExclamation, "SetFormProperties Function Error"
   Resume Exit_SetFormProperties
End Function

To call this Function, call it from within the OnOpen event of the Form to modify. Use either:

Call SetFormProperties(Me.Name)

to set the Form Properties or:

Call SetFormProperties(Me.Name, 1)

which does the same thing or....

Call SetFormProperties(Me.Name, 0)

which will set the properties to original (or whatever you want them to be as well).
You can call this Function from anywhere. It doesn't need to be from within the Form you want to modify the properties to.
.

 

[gemma-the-husky]

you can get a similar effect by calling a form as follows

docmd.openform "formname"
if normaluser then
while isopen("formname",acform)
doevents
wend
end if
-----------
so you need some function to determine whether a user is a normal user, and a function (syscommand something or other) to determine whether the form is open.

any good?