Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,
    Okay I have read books, and articles and still can not figure out how to do what I want to do. What I want to do is look at all the forms in a databases collection. Open up each form in the collection and for every object (field), that is in the detail section of the form, that I can enter or change data in. I would like to be able to take and add a line of code to the On Got Focus that says: Take the Status Bar Text if not empty and move it to the value of the Status Bar Text to a field in the Form footer called HelpMess. I woud also like to move the On Lost Focus a line saying move null to HelpMess. The problem is how do I write the looping code I need. I know it will start as Form!, But what is next? Ther must be a way to reference the forms and the fields without having to code Form!fmrCustomer!FirstName.?????????????. Please point me to a book, an article or such???
    Sarge

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Without Writing it all, the Forms Collection is the list of all OPEN Forms, which is I think the Collection you are looking to loop through

    Dim frm as Form

    For Each frm In Forms

    Next

    Will loop through OPEN Forms

    To Loop through ALL Forms in the database Open or NOT you can use

    Dim doc as DAO.Document

    For each doc in CurrentDb.Documents("Forms")

    Next

    To Loop through the Fields in a Form...

    Do you mean Fields or Controls??

    Fields are members of the forms recordset collection
    but that does not mean that every field is on the form

    Controls are on the form, BUT you would need to know OR work out which ones
    used for fields, and checking the control type for a control source is probably not enough.
    Controls include Text Boxes, Combo boxes, Check Boxes, Command Buttons etc....

    BUT to loop through controls

    Dim ctl As Control

    For Each ctl In frm.Controls

    Next

    If you did want to loop through all the fields in the forms RecordSource then that would be as below,
    but see notes above.

    Dim fld AS DAO.Field

    For Each fld In frm.RecordsetClone

    Next

    You cannot loop through the fields OR Controls on a Form that is NOT Open.

    That does NOT answer your question in detail, BUT gives you an idea of where to start looking.
    Andrew

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    The CurrentProject.AllForms collection is the collection of all the forms in the database.

    So this bit of code would print out the name of every form.

    Code:
    Dim objObject As Object
    For Each objObject In CurrentProject.AllForms
    	Debug.Print objObject.Name
    Next objObject
    Set objObject = Nothing
    So you could try

    Code:
    Dim objObject As Object
    Dim strForm as string
    For Each objObject In CurrentProject.AllForms
    	strform = objObject.Name
     docmd.openform strform, acDesign
     ????
     ????
    Next objObject
    Set objObject = Nothing
    But I don't know what the code is that is needed at the ?? to do what you want then.
    Regards
    John



  4. #4
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi John,
    The Forms!frmCustomer!FirstName!????????. I ment how do I code to Move Forms!frmCustomer!FirstName.[Status Bar Text] to Forms!frmCustomer!HelpMess. And place the move of the Status Bar Text into the HelpMess on the On Got Focus event of the Forms!frmCustomer!FirstName. I want this to work for every form I have and every text, Combo, Check Box and such on every form in the database and for every text box, drop down list, check bob and such on all the forms detail section of the form.
    Sarge

  5. #5
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Andrew,
    Thanks I will play with what you gave me.
    Sarge

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Here is some code to take it a bit further.

    Do forms have a HelpMess property? You seem to be taking lots of Status Bar Text messages and putting them all in there. won't that create a mess?

    I don't know how to write code that writes code, and that is what you asking. To take something from a property and put it in an event procedure is to write code.


    Code:
    Dim objObject As Object
    Dim strForm as string
    Dim ctrl as control
    Dim txBox as textbox
    Dim strMessage as string
    For Each objObject In CurrentProject.AllForms
     strform = objObject.Name
     docmd.openform strform, acDesign
     for each ctrl in forms(stDocname).controls
     if ctrl.type = acTextBox then
     strMessage = nz(ctrl.StatusBarText,"")
     ??? = strMessage
     end if
     
     Next ctrl
    
     docmd.close acForm,strform, acSaveYes
    Next objObject
    Set objObject = Nothing
    Regards
    John



  7. #7
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi John,
    You are right, I do want to write code to write code.
    I have found Help screens to be a waste of time for most projects. Most users do not want to hit F1 and read help. Instead they call you up and ask you what do I enter in such and such a text box. So I tell them to look for the Help Message on the bottom of the screen and tell ne what does it display. As a rule I am never called again about what to enter on a field on a form. No I enter the data I display in the Comment section of each row of each item in a table. I find it helps me remember what the fiels is used for. Then on the bottom of each form I place a Label saying "Help Message". Next to which I place a locked text box, that is sixty characters long and three lines high, with horizontial scroll bar. Now most of the comment that I use as Help message are less than 120 characters, and I have not run into one that I needed mot than 255 characters. So as the user goes from entery item to entery item. I move the table row comment into the Help Massage (called HelpMess) on the entery item's Got Focus event, and move Nulls to the Help Message on the entery item's Lost Focus event. I would like to have a wizard that would build a form with the Help Message field in the form footer section and have it write the code for all of the table item's got and lost focus events.
    So I am trying to get a better understanding of what the code looks like behind the forms so I can do things like build a form or even a report the way I want it to look. And with some of the VBA code written using a wizard. I am lazy!!! I like to find way not to have to work hard, if at all.
    I am frustrated that I can not seem to find the answers to my questions and that I am not making my questions more understandable. I hope this sheds some light on what my mother's nutty son is trying (very trying) to accomplish.
    Sarge

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I have a suggestion for a way of avoiding trying to write code via code.

    It is possible to set a property of a control to the name of a macro. It is also possible to write a macro to run code, so here is a three step process that has promise.

    Create a public function in a general module.

    Code:
    Public Function fnshowhelp()
    On Error Resume Next
    Screen.ActiveForm.HelpMess = Nz(Screen.ActiveControl.StatusBarText)
    End Function
    Then create a macro called, say, ShowHelp, whose action is RunCode,and argument is fnshowHelp().


    Then set the OnGotFocus property of all controls to "ShowHelp"

    That can be done using code like what is above.

    Code:
     for each ctrl in forms(strForm).controls
     ctrl.OnGotFocus ="showhelp" 
     Next ctrl
    Regards
    John



  9. #9
    New Lounger
    Join Date
    Dec 2009
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Sarge View Post
    You are right, I do want to write code to write code.
    Here are some ideas to get you started. In my code I am modifying a form's events (the form is called frmQn) and VBA code, so the form is programatically opened in Design mode. Note that the module doing the code-writing cannot be debugged, so testing is more difficult that it might otherwise be!

    [code] Private mdlQn As Module
    ...
    DoCmd.OpenForm "frmQn", acDesign
    Set mdlQn = Forms("frmQn").Module
    ....
    With mdlQn
    lngLineNumStart1 = 0
    .Find "'FormDesign MaxTabs", lngLineNumStart1, lngColNumStart, lngLineNumEnd, lngColNumEnd
    .ReplaceLine lngLineNumStart1 + 1, "Private Const MAXTABS = " & intPageNum

    For i = 1 To intPageNum
    .InsertLines lngLineNumStart1, _
    vbTab & vbTab & vbTab & vbTab & "Case " & i - 1 & vbCrLf _
    & vbTab & vbTab & vbTab & vbTab & vbTab & "basFocus " & astrFirstControlOnPage(intI)
    lngLineNumStart1 = lngLineNumStart1 + 2
    Next i
    End With
    ...

    There's also a .DeleteLines method.

    Look up Module in help.

  10. #10
    New Lounger
    Join Date
    Dec 2009
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Sarge View Post
    You are right, I do want to write code to write code.
    Here are some ideas to get you started. In my code I am modifying a form's events (the form is called frmQn) and VBA code, so the form is programatically opened in Design mode. Note that the module doing the code-writing cannot be debugged, so testing is more difficult that it might otherwise be!

    Code:
      Private mdlQn As Module
    ...
      DoCmd.OpenForm "frmQn", acDesign
      Set mdlQn = Forms("frmQn").Module
    ....
      With mdlQn
        lngLineNumStart1 = 0
        .Find "'FormDesign MaxTabs", lngLineNumStart1, lngColNumStart, lngLineNumEnd, lngColNumEnd
        .ReplaceLine lngLineNumStart1 + 1, "Private Const MAXTABS = " & intPageNum
    
        For i = 1 To intPageNum
          .InsertLines lngLineNumStart1, _
              vbTab & vbTab & vbTab & vbTab & "Case " & i - 1 & vbCrLf _
            & vbTab & vbTab & vbTab & vbTab & vbTab & "basFocus " & astrFirstControlOnPage(intI)
          lngLineNumStart1 = lngLineNumStart1 + 2
        Next i
      End With
    There's also a .DeleteLines method.

    Look up Module in help.

  11. #11
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Guys,
    Thanks for the ideas. With you or any one else has any more, PLEASE let me know. Once again thanks.
    Sarge

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Another trick we use is the ToolTip property for each text box that data is to be entered in. It requires that the user hoover the mouse over the control for a second or so, but that way they don't have to look at the bottom of the screen. Once they discover it is there, and it is consistently there, the questions go away. And like you, I'm inherently lazy - any code I don't have to write is a VERY good thing.
    Wendell

  13. #13
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Wendell,
    I use that trick for buttons. The only reason not to use it for text. combo, check and drop down boxes is that I have to code each of these. Since I like to code the table comment line for my own documentation. I kill two birds with one large rock.
    Thanks Sarge

  14. #14
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    We use SQL Server back-ends almost exclusively, and the comments you put in linked tables aren't sticky, so we do that sort of thing on the forms. Seems to serve the purpose fairly well. Different strokes depending on your environment.
    Wendell

  15. #15
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Wendell,
    Well thanks! I did not know that, since I just play fast and loose with Jet. (LOL) Today was not a loss, I learned something, and I do thank you for the information.
    Sarge

Posting Permissions

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