Results 1 to 7 of 7
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Editing forms in Code (Access 2002/SP2)

    I have a bunch (15) of identical forms that I want to edit (add a new field, reposition existing fields, make a group of fields locked). It is going to be tedious to open each form and tweak it individually. Is it possible to write a macro that will do the above for me? So I can just open the form, run the macro and that is it?
    I've tried - but couldn't make it work for me.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Editing forms in Code (Access 2002/SP2)

    I don't know if you can do it with a Macro, but you could do it with code. Of course, this begs the question, why do you have 15 identical forms?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Editing forms in Code (Access 2002/SP2)

    Well, she says scratching her head - I took the path of least resistance at the time, though now it doesn't seem like the best choice.
    Each form writes different info back into a couple of tables. I didn't want to give the users a choice about what got written. If they choose the form, then the info is defaulted. Also each form has a different filtered view associated with it.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

    Re: Editing forms in Code (Access 2002/SP2)

    As Mark wrote, it is possible to write code:

    - Make a backup of the database - things WILL go wrong when you do this kind of thing.
    - Use DoCmd.OpenForm "frmMyForm", acDesign to open a form in design view in code.
    - Use CreateControl to add a control; you can set its position, control source etc.
    - Set the Top and Left property of controls (measured in twips, 1440 twips = 1 inch)
    - Set the Locked property of controls.
    - Use DoCmd.Close , , acSaveYes to close and save the modified form.

    If you will have to do this from time to time, it might be worth the effort - it will take several tries to get it right. If it's a one time modification, it might be faster to do it manually.

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Editing forms in Code (Access 2002/SP2)

    Make a backup of the database - things WILL go wrong when you do this kind of thing Reminds me of the old saying, "there are two kinds of people, those that have lost data and those who WILL lose data" <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Use DoCmd.OpenForm "frmMyForm", acDesign to open a form in design view in code
    Is this run from a function or attached to the form in question?

    Thanks
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

    Re: Editing forms in Code (Access 2002/SP2)

    You would write a procedure in a standard module. The procedure would open a form in design view, modify some things, then close and save it. Here is an example; it moves all controls 0.5" down, then inserts a new text box bound to a field named "Data", with a label attached to it.

    <code>Sub ModifyForm(strForm As String)</code>
    <code> Dim ctl As Control</code>
    <code> DoCmd.OpenForm strForm, acDesign</code>
    <code> For Each ctl In Forms(strForm).Controls</code>
    <code> ctl.Top = ctl.Top + 720</code>
    <code> Next ctl</code>
    <code> Set ctl = CreateControl(strForm, acTextBox, acDetail, , "Data", 2880, 240, 2880, 240)</code>
    <code> ctl.Name = "txtData"</code>
    <code> With CreateControl(strForm, acLabel, acDetail, "txtData", , 240, 240, 1440, 240)</code>
    <code> .Caption = "Data"</code>
    <code> .Name = "lblData"</code>
    <code> End With</code>
    <code> DoCmd.Close acForm, strForm, acSaveYes</code>
    <code> Set ctl = Nothing</code>
    <code>End Sub</code>

    You can call it from the immediate window, or create another procedure to call it repeatedly:

    <code>Public Sub ModifySeveralForms()</code>
    <code> ModifyForm "frmOne"</code>
    <code> ModifyForm "frmTwo"</code>
    <code> ModifyForm "frmThree"</code>
    <code> ModifyForm "frmTest"</code>
    <code>End Sub</code>

    Note: during testing, you may want to comment out the DoCmd.Close line, to be able to inspect the results, and if necessary close the form without saving.

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Editing forms in Code (Access 2002/SP2)

    Beautiful! Thanks!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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