none
Allow users to modify placement of the subforms in my main form? RRS feed

  • Question

  • Hey guys, here's what I got. A main form, "frmMain" with 75 subform controls "sub1, sub2, sub3, etc..." . Each subform is linked to one record "qryContacts." My question is... can I make 4 buttons ("Add Box," "Remove Box," "Enable Move," "Disable Move"), every time the user clicks "Remove Box" button, the subforms (sub75, sub74, sub73, ...sub3, sub2, etc...) go invisible, vice-versa every time the user clicks "Add Box" button, the subforms (sub2, sub3,...sub73, sub74, sub75, etc...) become visible. Also when the user clicks "Enable Move" button, it would allow them to drag and place the subs anywhere on the form they want, and when they click "Disable Move" button, the subforms are locked in the selected place. Here's a picture for reference:

    Tuesday, August 15, 2017 2:30 PM

Answers

  • Private Sub cmdRight_Click()
    Dim x As Integer
    
    For x = 1 To 75
        If Me.Controls("sub" & x).Form.tglMove = True And Me.Controls("sub" & x).Left < 14159 Then
           Me.Controls("sub" & x).Left = Me.Controls("sub" & x).Left + 24
        End If
    Next
    
    End Sub

    Hi Jamie,

    I now see that you have a tglMove button on the subform. That means the when you look at your frmMain. you will see that many toggle buttons?

    Together with the fact that you have to loop through 75 control to "actuate" the movement, this demonstrates in my opinion that this functionality is better placed on a higher organisational level, that is on the main form.

    By clicking the toggle button on the main form, you loose the focus of the subform, but that is easily handled: when a sublot control is "entered", you write the sublot number in a hidden control on the main form.

    When you next click the toggle button, the appearance of the subform control can be changed (e.g. backcolor), and you are ready to move.

    After a move, it is simply (the same for the other movements):

    Private Sub cmdRight_Click()
    
        With Me("sub" & Me!subform_nr)
          If (.Left < 14159) Then .Left = .Left + 24
        End With
    
    End Sub

    In the main form's module you can add an Enter event for each subform control:

    Private Sub Sub1_Enter()
      On_sub_Enter 1
    End Sub
    

    You type this one time, copy it 74 times, and modify the sub-numbers :   Sub2_Enter(),   On_sub Enter 2,  etc.

    In the same module you make a sub that does the job:

    Sub On_enter (subform_nr as integer)
        Me!Subform_nr = subform_nr
    End Sub
    In a next stage of your development you can extend the code in On_Enter for additional tasks, you can even place the Sub in a general module for general use in you application.

    Imb.

    • Marked as answer by InnVis Tuesday, August 22, 2017 11:00 AM
    Tuesday, August 22, 2017 7:58 AM

All replies

  • Hey guys, here's what I got. A main form, "frmMain" with 75 subform controls "sub1, sub2, sub3, etc..." . Each subform is linked to one record "qryContacts." My question is... can I make 4 buttons ("Add Box," "Remove Box," "Enable Move," "Disable Move"), every time the user clicks "Remove Box" button, the subforms (sub75, sub74, sub73, ...sub3, sub2, etc...) go invisible, vice-versa every time the user clicks "Add Box" button, the subforms (sub2, sub3,...sub73, sub74, sub75, etc...) become visible. Also when the user clicks "Enable Move" button, it would allow them to drag and place the subs anywhere on the form they want, and when they click "Disable Move" button, the subforms are locked in the selected place. Here's a picture for reference:

    Hi Jamie,

    Great!!! This is fun, this is what I all the time do with my generalized dynamic menus.

    There is only a small problem with my time. Tonight I have my running evening, and tomorrow I am to Arnhem the whole day. Perhaps I can do some small things in between.

    Only the dragging is new for me, because in my applications the users don't drag, though they can make a control wider or smaller.

    Looking forward!

    Imb.

    Tuesday, August 15, 2017 3:28 PM
  • Hi,

    Not entirely sure but the samples in this UtterAccess thread might be useful.

    Cheers!

    Tuesday, August 15, 2017 3:48 PM
  • Hi Jaime,

    My first answer is that you can make a table Positions_tbl with 75 rows per Service. In this table you make a couple ol columns, one to store the top dimension of the control, and one for the left position, and a field for the visibility. Later on you problably will need some more columns. It is better to store "readable"dimensions" (in or cm) than the twips. As all subform controls have the same width and height it is not (yet) necessary to store that value.

    In the Open or Load event, while processing the controls, you can adjust the real position of the subform controls from the in formation in the table, including the visibility. Use the control properties: Top, Left and Visible. Because you have a set of values for each Service, each Service have their own set of data.

    You can make a function (place it in a general module) to convert inches or cm's to twips.

    There are different ways to get the data of the Positions_tbl, and have it co-operate with the qryContacts.

    What you want to do with disabled controls? Are these visible as "gray holes"?

    Imb.


    PS. A simple way as workaround for dragging is make a form to edit the respective record in the Position_tbl. You have to take quite a lot of precautions with respect to overlapping, outside the window etc. In fact it means a kind of Position Management System. But fun!!
    • Edited by Imb-hb Tuesday, August 15, 2017 4:01 PM PS
    Tuesday, August 15, 2017 3:54 PM
  • The disabled controls I want to make me.visible = false to completely disappear from view until call upon by the user. So each department has 75 records available in my database for edits, but if one department only has 67 employee and other departments have 20, etc... they don't need to view 75 boxes on their screen (some or most of them being empty). So the user should be able to remove or add subforms with records as they need instead of having a lot of wasted space.

    P.S. I like the demo in utteraccess, but the location of the drag is not saved. Also, the items being moved are labels and not subforms.

    EDIT: I tried to mimic the demo in utteraccess, but does not work with subforms, I think it's because the subforms' fields add a cursor, anyway to disable that?

    • Edited by InnVis Tuesday, August 15, 2017 5:21 PM
    Tuesday, August 15, 2017 4:42 PM
  • Hi Jamie,

    Any progress with the Position_tbl.

    After 15 km running I concluded that it is THE way to go.

    Imb.

    Tuesday, August 15, 2017 9:49 PM
  • Hi J. Alexander Batista,

    By default, Sub form has Top and Left property which is used to set it's location on the form.

    we can create buttons to reset and modify this property at run time.

    Code:

    Option Compare Database
    Option Explicit
    
    Private Sub Command0_Click()
    Me.Child2.Top = Me.Child2.Top + 100
    
    End Sub
    
    Private Sub Command1_Click()
    Me.Child2.Left = Me.Child2.Left + 100
    End Sub
    
    Private Sub Command4_Click()
    Me.Child2.Top = Me.Child2.Top - 100
    End Sub
    
    Private Sub Command5_Click()
    Me.Child2.Left = Me.Child2.Left - 100
    End Sub
    

    you can use Enter event of subform to get the name of that subform to move any specific subform on form.

    you can create one combobox that you can loop through all sub forms on form load and fill it with name of sub forms.

    and create two buttons for hide and visible sub form.

    so you can hide or show selected sub form in the combobox.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, August 16, 2017 1:53 AM
    Moderator
  • Hi J. Alexander Batista,

    other thing you can do that use the Mouse_Down event of Detail section of the form.

    in which you will get X and Y. which you can use to set the desire location of your sub form.

    you just need to click on the form where you want to move your sub form.

    it is not actual drag and drop but it can solve your issue.

    code:

    Private Sub Detail_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Me.Child2.Top = Y
    Me.Child2.Left = X
    End Sub

    in the same event you can fire an update query which will update the location of control in your table. you can identify each subform with its unique name. so when you open the form next time. you will get your control at the same position where you move it last time.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, August 16, 2017 2:13 AM
    Moderator
  • Hi Deepak,

    I like this idea. It's not as smooth as drag and drop but so much easier to implement.

    Thanks!

    Wednesday, August 16, 2017 3:13 AM
  • By default, Sub form has Top and Left property which is used to set it's location on the form.

    Hi,

    This is about the way the users of my application can tune the width of controls.

    But all these changes must be stored so that the next time you open the form, you can use these new positions. Also, changes for one Service should not interfere with changes for a different Service.

    To store all these data you need a table, with the fields: Service, ID, Top, Left, Visibility, ...

    Imb.


    • Edited by Imb-hb Wednesday, August 16, 2017 5:52 AM correction
    Wednesday, August 16, 2017 5:22 AM
  • ImB, I like your idea with the table named "tblSubPosition" because it takes into account that each service has a different loop and each service will have a different layout based on their preference. I went ahead and created the table with the fields "ID" (unique autonum), "RecordID" (1-75 for each service, total of 1275 records, 17 different services), "Service", "Top", "Left", and "Visibility" (yes/no field, true/false statement with a checkbox). Should I link tblSubPosition's "Service" with tblContacts "Service" or should I manually type in the service name per record in "tblSubPosition?" Also, how do I get this table to record position of each individual sub and most importantly how do I get the frm_Main to execute it?

    EDIT: If I like the two tables based on autonumber unique fields, I don't think I'll need a "Service" field in "tblSubPositions" or will I? It seems that linking the autonumber automatically created a unique relationship between the two tables.

    • Edited by InnVis Wednesday, August 16, 2017 11:30 AM
    Wednesday, August 16, 2017 11:11 AM
  • Should I link tblSubPosition's "Service" with tblContacts "Service" or should I manually type in the service name per record in "tblSubPosition?" Also, how do I get this table to record position of each individual sub and most importantly how do I get the frm_Main to execute it?

    EDIT: If I like the two tables based on autonumber unique fields, I don't think I'll need a "Service" field in "tblSubPositions" or will I? It seems that linking the autonumber automatically created a unique relationship between the two tables.

    Hi Jamie,

    Are all your Services in a Service_tbl, with a Service_id as AutoNumber, that is the PK of that table?

    If yes, then it is OK, else I would advise to do so, and use the Service_id to link to other tables.

    In the Contact_tbl you combine Positions with Persons. Persons have a field "Service" to indicate in which Service they belong. In the Position_tbl you also have a Service field in order to make the frmMain per Service. This way of working has disadvantages, as it can lead to circular references, which you want to avoid. Besides, when a Person moves from one Service to an other, you have to correct this change on two different places. That is not structured.

    You can solve this by not looking Person centered, but Service (Organization) centered. You have a Service with 75 Positions (75 positions on your frmMain, or 75 functional Positions in an Organisation), to which Persons can be assigned.

    The field "Service" in the Person_record can be removed: a Person belongs to a Service because he occupies a Position in the Service. Working in this way will make things easier, but you have to make a mental turn.

    This gives also the possibility that a Person can be (temporarily) assigned tw more than one Service: according to Murphy it will happen one time.

    Think about it!

    Imb.

    Wednesday, August 16, 2017 5:44 PM
  • Let me clarify a few things and maybe provide a solution to having a person transfer services.  First let me walk through the structure of my database. I only have 1 Table (tblContacts), period. This Table has 1275 records (17 services, 75 records per Service; 17*75 = 1275). I manually entered each Service name in the "Service" field of my Table: 4-CENTER, 4-EAST, 4-WEST, 5-CENTER, 5-EAST, etc... So I just made a second Table for this new feature, the Table is called (tblSubPosition). In this Table (tblSubPosition), I created 6 fields: "ID", "RecordID", "Service", "Top", "Left", and "Visibility." I made "ID" an autonumber field and "Visibility" a yes/no (true/false) field with a checkbox. For the "Service" and "RecordID", I manually entered the 1275 records by hand for each service and I linked the autonumber field in "tblContacts" with the autonumber field in "tblSubPosition." This made my "tblSubPosition" and "tblContacts" align perfectly.... each autonumber is linked to one another to where each "Service" AND "RecordID" (1-75) in tblSubPosition match perfectly with the "Service" and "ID" (1-75) fields in tblContacts. After making my fingers bleed entering data by hand "Service" and "ID" (1-75 repeating 17 times), I realized that I wasted my time because by creating a relationship between the autonumber fields in tblContacts (AutoNUM) and tblSubPosition (ID), it automatically assigned the correct records to one another without needing to also identify "Service" and "RecordID" (1-75 repeating 17 times, once per service). I guess it never hurts to have extra reference point in any case (LOL).

    As far as one person moving from one Service to the other, I can make an error appear after the OnClick of my "Save and Exit" button in the data entry form if there is a duplicate record. With this, the person would have to be deleted from their previous place of work in order to be placed in the new place of work (Service).

    Wednesday, August 16, 2017 6:43 PM
  • After making my fingers bleed entering data by hand "Service" and "ID" (1-75 repeating 17 times), I realized that I wasted my time because by creating a relationship between the autonumber fields in tblContacts (AutoNUM) and tblSubPosition (ID), it automatically assigned the correct records to one another without needing to also identify "Service" and "RecordID" (1-75 repeating 17 times, once per service). I guess it never hurts to have extra reference point in any case (LOL).

    Hi Jamie,

    You must see the manual typing as a marvelous physical exercise. You need that as programmer behind the screen. That is the reason that I have my 3 times a week running events.

    Yes, it is possible to join tables based on two fields "Service" and "ID", and it is one of the "good" solutions. I never use such a construction though, because for linking you use two very specific fields: "Service" and "ID".

    I succeeded in automating more than 95 % of each application by using "entity names": there is an entity "Service", in a "Service_tbl", using an autonumber  "Service_id" as PK. Just another (firm) structure.

    "Extra references" seem additional safety, but my experience is that in long term they add more headaches then relieve.

    Back to your original question. Are you able to make now the different Main_forms per Service based on the data in the Position_tbl? Is yes, then you have your versatility. If no, What do you still need?

    If you used an autonumber Position_id for uniquely identifying as Position (independant of the Service), then you could - in the Load or Open event when processing a Subform - add that Position_id to the Tag of the control. With a Dblclick or righthand Mouseclick you could inspect the value of the Tag, to open edit form for the Position_record to change e.g. Top and Left.

    Just some thoughts ...

    Imb.

    Wednesday, August 16, 2017 7:19 PM
  • I have the setup ready to go, what I don't know how to do is automatically record the position of each subform from my frmMain to the Table tblSubPosition. And then how do I load the specific positions based on the loop being loaded.
    Wednesday, August 16, 2017 7:47 PM
  • I have the setup ready to go, what I don't know how to do is automatically record the position of each subform from my frmMain to the Table tblSubPosition. And then how do I load the specific positions based on the loop being loaded.

    Hi Jamie,

    To fill the Main_form you use on this moment an sql-string that looks like:

        "SELECT * FROM Contact_tbl" _
        " WHERE Service = " & As_text(cur_service)

    You can change this in:

        "SELECT * FROM Position_tbl INNER JOIN Contact_tbl ON Contact_tbl.Service = Position_tbl.Service AND Contact_tbl.ID = Position_tbl.ID" _
        " WHERE Contact_tbl.Service = " & As_text(cur_service)

    As in the joined tables both Service and ID occur more than once, you have to add the tablename, to indicate which Service take (in this case both are the same), or use an alias: "SELECT *,Contact_tbl.Service as Service FROM ....."

    The looping etc is the same.

    Imb.

    Wednesday, August 16, 2017 8:20 PM
  • So here's how my Main form looks like.

    Private Sub Form_Load()
      Dim rs As DAO.Recordset
    
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts WHERE Service ='" & Me.OpenArgs & "'")
    
      Do While (Not rs.EOF)
     Fill_subform Me("Sub" & rs!ID), rs
    
        rs.MoveNext
      Loop
    End Sub
    

    And this is what I should do?

    Private Sub Form_Load()
      Dim rs As DAO.Recordset
    
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts INNER JOIN qryContacts ON qryContacts.Service = tblSubPosition.Service AND qryContacts.AutoNUM = tblSubPosition.ID" _
                "WHERE qryContacts.Service ='" & Me.OpenArgs & "'")
    
      Do While (Not rs.EOF)
     Fill_subform Me("Sub" & rs!ID), rs
    
        rs.MoveNext
      Loop
    End Sub
    

    Thursday, August 17, 2017 1:42 PM
  • And this is what I should do?

      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts INNER JOIN qryContacts ON qryContacts.Service = tblSubPosition.Service AND qryContacts.AutoNUM = tblSubPosition.ID" _
                "WHERE qryContacts.Service ='" & Me.OpenArgs & "'")
    
    

    Hi Jamie,

    Almost! You Join qryContacts with qryContacts. One of the two should be tblsubPosition. Further you missed a space before WHERE.

    Did you try it already? And what is the result?

    But, according to your other thread, personally I prefer to use autonumber fields to join instead of multiple "natural" fields.

    Imb.

    Thursday, August 17, 2017 1:53 PM
  • How about now? Note, "qryContacts.AutoNUM = tblSubPosition.ID" both of those fields are auto numbers (unique).

    Private Sub Form_Load()
      
    Dim rs As DAO.Recordset
    
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblSubPosition INNER JOIN qryContacts ON qryContacts.AutoNUM = tblSubPosition.ID WHERE qryContacts.Service ='" & Me.OpenArgs & "'")
      
      Do While (Not rs.EOF)
     Fill_subform Me("Sub" & rs!ID), rs
    
        rs.MoveNext
      Loop
    
    End Sub

    EDIT: I got an error here, it can't identify rs!ID

     Fill_subform Me("Sub" & rs!ID), rs

    EDIT2: Probably has something to do with this general module.

    Sub Fill_subform(cur_sub As SubForm, rs As Recordset)
    
       With cur_sub
          !txtTitle = rs![Job Title]
          !txtContactName = rs![Contact Name]
          !txtMPhone = rs![M Phone]
          !txtOPhone = rs![O Phone]
          !txtOtherPhone = rs![H Phone]
          !txtBranch = rs!Branch
          !txtID = rs!ID
          !txtAutoNUM = rs!AutoNUM
          !txtService = rs!Service
          
          Select Case rs!Branch
          Case "": .Form.Detail.BackColor = 9342606
          Case "USN": .Form.Detail.BackColor = 8388608
          Case "USA": .Form.Detail.BackColor = 1007160
          Case "USAF": .Form.Detail.BackColor = 16760576
          Case "CIV": .Form.Detail.BackColor = 6333684
          Case "CONTRACT": .Form.Detail.BackColor = 9846527
          End Select
    
    If rs![Contact Name] = " , ." Then
    !txtContactName = ""
    End If
    
       End With
    
    End Sub
    • Edited by InnVis Thursday, August 17, 2017 2:10 PM
    Thursday, August 17, 2017 2:02 PM
  • How about now? Note, "qryContacts.AutoNUM = tblSubPosition.ID" both of those fields are auto numbers (unique).

    Hi Jamie,

    I thought AutoNUM goes from 1-1275, whereas ID goes from 1-75 per Service. Is that right?

    Probably there lies the problem, that you want to position a Contact with AutoNUM 76 in the subform 76, that is not defined.

    I think it should work for the Contacts with AutoNum 1-75.

    Imb.

    Thursday, August 17, 2017 2:22 PM
  • EDIT: Ok there is a confusion. Let me lay it out so it's easier to understand (I guess I got myself in this problem by renaming the number fields).

    QUERY Name: qryContacts

    Field name: AutoNUM = PrimaryKey (1-1275, unique values)

    Field name: ID = 1-75 (repeating 17 times, once per service) i.e. 3-CENTER 1-75, 4-CENTER 1-75

    OVERVIEW of qryContacts

    AutoNUM 1 = ID 59 in 3-WEST

    AutoNUM 2 = ID 60 in 3-WEST

    ...

    AutoNUM 81 = ID 1 in 3-CENTER

    -----------------------------------------------

    TABLE Name: tblSubPosition

    Field Name: ID = PrimaryKey (1-1275, unique values)

    Field Name: RecordID = 1-75 (repeating 17 times, once per service) i.e. 3-CENTER 1-17, 4-CENTER 1-75

    -----------------------------------------------

    NOTE: I linked the primary keys of tblSubPosition and tblContacts (the Table where qryContacts gets its values from). So every AutoNumber from tblSubPosition and tblContacts are linked together making every repeating service and number sequence also perfectly aligned with one another.


    ... So, how do I fix the "Item not found in this collection" error?
    • Edited by InnVis Thursday, August 17, 2017 2:44 PM
    Thursday, August 17, 2017 2:32 PM
  • NOTE: I linked the primary keys of tblSubPosition and tblContacts (the Table where qryContacts gets its values from). So every AutoNumber from tblSubPosition and tblContacts are linked together making every repeating service and number sequence also perfectly aligned with one another.

    Hi Jamie,

    Can you post a screen shot of the two tables with the fields that are relavant for joining (AutoNUM, ID, ...) including fieldname, and a couple of rows, where I can see the relationship(s)?

    Imb.

    Thursday, August 17, 2017 2:57 PM
  • Thursday, August 17, 2017 3:13 PM
  • The last picture might be a bit confusing because field "ID" in tblSubPositions is the primarykey (AutoNumber) and "ID" in tblContacts refers to 1-75 repeating sequence 17 times (once per service). The real primarykey (AutoNumber) for tblContacts is called "AutoNUM" as you can see in the pictures.
    • Edited by InnVis Thursday, August 17, 2017 3:17 PM
    Thursday, August 17, 2017 3:16 PM
  • The last picture might be a bit confusing because field "ID" in tblSubPositions is the primarykey (AutoNumber) and "ID" in tblContacts refers to 1-75 repeating sequence 17 times (once per service). The real primarykey (AutoNumber) for tblContacts is called "AutoNUM" as you can see in the pictures.

    Hi Jamie,

    Thank you for the screenshots.

    The connecting fields in the tables are:

    in tblContacts: AutoNUM
    in tblsubPosition: ID

    Now you can make your recordset in frmMain. In the loop to select the right subform you can then use:

        "SELECT * FROM tblsubPosition INNER JOIN tblContacts ON tblContacts.AutoNUM = tblsubPosition.ID WHERE ..."

    In the Open/Load event you then can use:

        Fill_subform Me("Sub" & rs!RecordID), rs  

    because RecordID are the numbers 1-75 for each service.

    Does this work?

    As you can feel now, structure and clarity in naming is very important, not only for you, and not only for me, but also for your successor in the development.

    Imb.

    Thursday, August 17, 2017 4:05 PM
  • I'm getting an error "item not found in this collection" and it's highlighting the Fill_subform Me("Sub".....
    Thursday, August 17, 2017 4:21 PM
  • I'm getting an error "item not found in this collection" and it's highlighting the Fill_subform Me("Sub".....

    Hi Jamie,

    Can you step through the code with the debugger? The debugger is my closest friend in developping.

    Has  rs!RecordID  a value, and is it acceptable, within 1-75? Does the subform   "Sub" & rs!RecordID   exist?

    Imb.

    Thursday, August 17, 2017 4:30 PM
  • Okay.... I think I see where the problem is coming from HELP! (lol)

    So now I'm being flagged for the fields in my general mod named "modUpdateMain." here's the code for the mod.

    Option Compare Database
    
    Sub Fill_subform(cur_sub As SubForm, rs As Recordset)
    
       With cur_sub
          !txtTitle = rs![Job Title]
          !txtContactName = rs![Contact Name]
          !txtMPhone = rs![M Phone]
          !txtOPhone = rs![O Phone]
          !txtOtherPhone = rs![H Phone]
          !txtBranch = rs!Branch
          !txtID = rs!ID
          !txtAutoNUM = rs!AutoNUM
          !txtService = rs!Service
          
          Select Case rs!Branch
          Case "": .Form.Detail.BackColor = 9342606
          Case "USN": .Form.Detail.BackColor = 8388608
          Case "USA": .Form.Detail.BackColor = 1007160
          Case "USAF": .Form.Detail.BackColor = 16760576
          Case "CIV": .Form.Detail.BackColor = 6333684
          Case "CONTRACT": .Form.Detail.BackColor = 9846527
          End Select
    
    If rs![Contact Name] = " , ." Then
    !txtContactName = ""
    End If
    
       End With
    
    End Sub

    I think because my FROM is now "tblSubPositions" and not "qryContacts" it's causing some error through the general module.

    FYI: Although my Tables are linked (tblContacts and tblSubPosition), the data that belongs to "tblContacts" I pull it using a QUERY called "qryContacts" for display purposes on my subform. I don't know if that is a problem with the INNER JOIN


    MISC. QUESTION: Wouldn't this all be easier just adding the fields "Top, Left, and Visibility" to the existing Table, tblContacts? Instead of using a completely different table.
    • Edited by InnVis Thursday, August 17, 2017 4:53 PM
    Thursday, August 17, 2017 4:51 PM
  • Deepak, the Mouse_Down event won't work for me for some reason. I believe it's because all of my subforms have the same SourceObject.

    EDIT: nvm, I got it to work, but it only allows 1 subform to move at a time. I have 75 subform objects on my form.

    • Edited by InnVis Thursday, August 17, 2017 7:06 PM
    Thursday, August 17, 2017 5:51 PM
  • I compiled this code based on what I learned so far from you and Leo. It works great for my current database. The only issue I'm having now is, I need to record the positions of each sub by Primary Key.

    Private Sub cmdUp_Click()
    Dim x As Integer
    
    For x = 1 To 75
    
    If Me.Controls("sub" & x).Form.tglMove = True Then
    Me.Controls("sub" & x).Top = Me.Controls("sub" & x).Top - 100
    End If
    Next
    
    End Sub

    P.S. I found this great module in Utter Access, that works better than my code. But this damn thing is so complicated, it like hieroglyphics to me at this stage. Check out a small piece of the code. This demo uses labels, I attempted to adapt it to subforms with no success.

    Option Explicit
    Option Compare Text
    
    Private Const conModuleName     As String = "clsActionLabel"
    
    Private Const conDbOpenDynaset  As Long = 2
    Private Const conMinimumSize    As Long = 60
    
    Private WithEvents lblThisLabel As Label
    
    
    Private frmForm                 As Form
    Private sngOldX                 As Single
    Private sngOldY                 As Single
    Private lngOldXForDblClick      As Long
    Private lngOldYForDblClick      As Long
    Private lngBorder               As Long
    
    Public blnHorizontalMove        As Boolean
    Public blnHorizontalSizing      As Boolean
    Public blnHorizontalSizePointer As Boolean
    Public blnVerticalMove          As Boolean
    Public blnVerticalSizing        As Boolean
    Public blnVerticalSizePointer   As Boolean
    Public blnLeftButton            As Boolean
    Public lngTopBorder             As Long
    Public lngBottomBorder          As Long
    Public lngLeftBorder            As Long
    Public lngRightBorder           As Long
    Public sngCoordinateX           As Single
    Public sngCoordinateY           As Single
    
       
    Public Property Get Label() As Label
        
        If (conHandleErrors) Then On Error GoTo ErrorHandler
        
        Set Label = lblThisLabel
    
    ExitProcedure:
        Exit Property
    
    ErrorHandler:
        DisplayError "Get Label", conModuleName
        Resume ExitProcedure
    
    End Property
    
    
    Public Property Set Label(lblNewLabel As Label)
    
        If (conHandleErrors) Then On Error GoTo ErrorHandler
        
        Set lblThisLabel = lblNewLabel
        Set frmForm = lblThisLabel.Parent
    
        lngTopBorder = lblThisLabel.Parent.BoxBorder.Top
        lngRightBorder = lblThisLabel.Parent.BoxBorder.Left + lblThisLabel.Parent.BoxBorder.Width
        lngBottomBorder = lblThisLabel.Parent.BoxBorder.Top + lblThisLabel.Parent.BoxBorder.Height
    
    ExitProcedure:
        Exit Property
    
    ErrorHandler:
        DisplayError "Set Label", conModuleName
        Resume ExitProcedure
    
    End Property
    
    
    Private Sub lblThisLabel_DblClick(ByRef intCancel As Integer)
        
        If (conHandleErrors) Then On Error GoTo ErrorHandler
        
        DoCmd.OpenForm "frmDetails", , , , , acDialog, Mid$(lblThisLabel.Name, 8)
        
        DisplayTheCaption lblThisLabel
        
    ExitProcedure:
        Exit Sub
    
    ErrorHandler:
        DisplayError "lblThisLabel_DblClick", conModuleName
        Resume ExitProcedure
    
    End Sub
    
    
    Public Property Set DisplayCaption(ByRef lblThisLabel As Label)
    
        If (conHandleErrors) Then On Error GoTo ErrorHandler
    
        DisplayTheCaption lblThisLabel
        
    ExitProcedure:
        Exit Property
    
    ErrorHandler:
        DisplayError "DisplayCaption", conModuleName
        Resume ExitProcedure
        
    End Property
    
    
    Private Sub DisplayTheCaption(ByRef lblThisLabel As Label)
        Dim strParentName  As String
        Dim strLabelNumber As String
      
        If (conHandleErrors) Then On Error GoTo ErrorHandler
        
        strLabelNumber = Mid$(lblThisLabel.Name, 8)
        strParentName = lblThisLabel.Parent.Name
        
        If lblThisLabel.Tag = "Chair" Then
            If DLookup("Occupied", "tblLabels", "LID = " & strLabelNumber) Then
            Dim lidx
            lidx = DLookup("lid", "tblLabels", "LID = " & strLabelNumber)
                Forms(strParentName)("lblTest" & strLabelNumber).Caption = "O"
                Forms(strParentName)("lblTest" & strLabelNumber).ControlTipText = _
                    Nz(DLookup("FName", "tblLabels", "LID = " & strLabelNumber), "") & " " & _
                    Nz(DLookup("LName", "tblLabels", "LID = " & strLabelNumber), "")
            Else
                Forms(strParentName)("lblTest" & strLabelNumber).Caption = ""
                Forms(strParentName)("lblTest" & strLabelNumber).ControlTipText = ""
            End If
        Else
            lblThisLabel.Caption = Nz(DLookup("Caption", "tblLabels", "LID = " & strLabelNumber), "")
        End If
        
    ExitProcedure:
        Exit Sub
    
    ErrorHandler:
        DisplayError "DisplayTheCaption", conModuleName
        Resume ExitProcedure
    
    End Sub
    
    
    Private Sub lblThisLabel_MouseDown(ByRef intButton As Integer, _
                                       ByRef intShift As Integer, _
                                       ByRef sngX As Single, _
                                       ByRef sngY As Single)
        
        If (conHandleErrors) Then On Error GoTo ErrorHandler
        
        sngOldX = sngX
        sngOldY = sngY
        lngOldXForDblClick = sngX
        lngOldYForDblClick = sngY
    
    ExitProcedure:
        Exit Sub
    
    ErrorHandler:
        DisplayError "lblThisLabel_MouseDown", conModuleName
        Resume ExitProcedure
    
    End Sub
    
    
    Private Sub lblThisLabel_MouseMove(ByRef intButton As Integer, _
                                       ByRef intShift As Integer, _
                                       ByRef sngX As Single, _
                                       ByRef sngY As Single)
        Dim lngNewX As Long
        Dim lngNewY As Long
        
        If (conHandleErrors) Then On Error GoTo ErrorHandler
        
        With CurrentDb.OpenRecordset(" SELECT *" & _
                                     " FROM tblLabels" & _
                                     " WHERE tblLabels.LID = " & Mid$(lblThisLabel.Name, 8) & ";")
            If (.RecordCount) Then
                Forms(lblThisLabel.Parent.Name).lblTest130.Caption = Nz(!FName, "")
                Forms(lblThisLabel.Parent.Name).lblTest131.Caption = Nz(!LName, "")
            End If
            
            .Close
        End With
        
        Call SetKeysButtonsCoordinates(intButton, intShift, sngX, sngY)
        
        blnHorizontalMove = blnLeftButton
        blnHorizontalSizePointer = True
        blnHorizontalSizing = blnLeftButton
        blnVerticalMove = blnLeftButton
        blnVerticalSizePointer = True
        blnVerticalSizing = blnLeftButton
    
        Call SetKeysButtonsCoordinates
        
        If Screen.MousePointer = 0 Then
            If blnHorizontalMove Then
                lngNewX = lblThisLabel.Left + sngX - sngOldX
                If lngNewX >= lngLeftBorder And lngNewX <= lngRightBorder - lblThisLabel.Width Then
                   lblThisLabel.Left = lngNewX
                ElseIf lngNewX < lngLeftBorder Then
                   lblThisLabel.Left = lngLeftBorder
                ElseIf lngNewX > lngRightBorder - lblThisLabel.Width Then
                   lblThisLabel.Left = lngRightBorder - lblThisLabel.Width
                End If
            End If
            
            If blnVerticalMove Then
                lngNewY = lblThisLabel.Top + sngY - sngOldY
                If lngNewY >= lngTopBorder And lngNewY <= lngBottomBorder - lblThisLabel.Height Then
                   lblThisLabel.Top = lngNewY
                ElseIf lngNewY < lngTopBorder Then
                   lblThisLabel.Top = lngTopBorder
                ElseIf lngNewY > lngBottomBorder - lblThisLabel.Height Then
                   lblThisLabel.Top = lngBottomBorder - lblThisLabel.Height
                End If
            End If
        End If
        
        If Not (blnHorizontalSizing Or blnVerticalSizing) Then
            If 0 < sngX And sngX < 45 Then
                lngBorder = 1
            ElseIf lblThisLabel.Width - 45 < sngX And sngX < lblThisLabel.Width Then
                lngBorder = 3
            ElseIf 0 < sngY And sngY < 45 Then
                lngBorder = 2
            ElseIf lblThisLabel.Height - 45 < sngY And sngY < lblThisLabel.Height Then
                lngBorder = 4
            Else
                lngBorder = 0
            End If
            
            If lngBorder = 1 Or lngBorder = 3 And blnHorizontalSizePointer Then
                Screen.MousePointer = 9
            ElseIf lngBorder = 2 Or lngBorder = 4 And blnVerticalSizePointer Then
                Screen.MousePointer = 7
            Else
                Screen.MousePointer = 0
            End If
        End If
        
        If blnHorizontalSizing And Screen.MousePointer = 9 Then
            If lngBorder = 1 Then
                If lblThisLabel.Width - sngX >= conMinimumSize Then
                    If lblThisLabel.Left + sngX >= lngLeftBorder Then
                        lblThisLabel.Width = lblThisLabel.Width - sngX
                        lblThisLabel.Left = lblThisLabel.Left + sngX
                    End If
                Else
                    lblThisLabel.Width = conMinimumSize
                End If
            Else
                If sngX > conMinimumSize Then
                    If lblThisLabel.Left + sngX <= Me.lngRightBorder Then lblThisLabel.Width = sngX
                Else
                    lblThisLabel.Width = conMinimumSize
                End If
            End If
        End If
        
        If blnVerticalSizing And Screen.MousePointer = 7 Then
            If lngBorder = 2 Then
                If lblThisLabel.Height - sngY >= conMinimumSize Then
                    If lblThisLabel.Top + sngY >= lngTopBorder Then
                        lblThisLabel.Height = lblThisLabel.Height - sngY
                        lblThisLabel.Top = lblThisLabel.Top + sngY
                    End If
                Else
                    lblThisLabel.Height = conMinimumSize
                End If
            Else
                If sngY + 15 > conMinimumSize Then
                    If lblThisLabel.Top + sngY <= lngBottomBorder Then lblThisLabel.Height = sngY
                Else
                    lblThisLabel.Height = conMinimumSize
                End If
            End If
        End If
        
    ExitProcedure:
        Exit Sub
    
    ErrorHandler:
        DisplayError "lblThisLabel_MouseMove", conModuleName
        Resume ExitProcedure
        
    End Sub
    
    
    Private Sub lblThisLabel_MouseUp(ByRef intButton As Integer, _
                                     ByRef intShift As Integer, _
                                     ByRef sngX As Single, _
                                     ByRef sngY As Single)
        Dim lngLID As Long
        
        If (conHandleErrors) Then On Error GoTo ErrorHandler
        
        sngOldX = 0
        sngOldY = 0
        
        lngLID = CLng(Mid$(lblThisLabel.Name, Len("lblTest") + 1))
        
        SaveSettings "tblLabels", lngLID
        SaveSettings "tblLabelsBackup", lngLID
    
    ExitProcedure:
        Exit Sub
    
    ErrorHandler:
        DisplayError "lblThisLabel_MouseUp", conModuleName
        Resume ExitProcedure
    
    End Sub
    
    
    Private Sub SaveSettings(ByVal strTableName As String, _
                             ByVal lngLID As Long)
                             
        If (conHandleErrors) Then On Error GoTo ErrorHandler
        
        With CurrentDb.OpenRecordset(strTableName, conDbOpenDynaset)
            .FindFirst "LID = " & lngLID
            
            If .NoMatch Then
                .AddNew
            Else
                .Edit
            End If
            
            !LID = lngLID
            !LHeight = lblThisLabel.Height
            !LWidth = lblThisLabel.Width
            !LTop = lblThisLabel.Top
            !LLeft = lblThisLabel.Left
            !Caption = IIf(lblThisLabel.Caption = "", Null, lblThisLabel.Caption)
            !Occupied = DLookup("Occupied", "tblLabels", "LID = " & lngLID)
            
            .Update
            .Close
        End With
        
    ExitProcedure:
        Exit Sub
    
    ErrorHandler:
        DisplayError "SaveSettings", conModuleName
        Resume ExitProcedure
    
    End Sub
    
    
    Public Property Let SetEventHandlers(ByVal strHandler As String)
    
        If (conHandleErrors) Then On Error GoTo ErrorHandler
        
        lblThisLabel.OnMouseMove = strHandler
        lblThisLabel.OnMouseUp = strHandler
        lblThisLabel.OnMouseDown = strHandler
        lblThisLabel.OnDblClick = strHandler
    
    ExitProcedure:
        Exit Property
    
    ErrorHandler:
        DisplayError "Let SetEventHandlers", conModuleName
        Resume ExitProcedure
    
    End Property
    
    
    Private Sub SetKeysButtonsCoordinates(Optional ByVal intButton As Integer = 0, _
                                          Optional ByVal intKey As Integer = 0, _
                                          Optional ByVal sngX As Single = 0, _
                                          Optional ByVal sngY As Single = 0)
        
        If (conHandleErrors) Then On Error GoTo ErrorHandler
        
        Me.blnLeftButton = (intButton And acLeftButton) > 0
        Me.sngCoordinateX = sngX
        Me.sngCoordinateY = sngY
    
    ExitProcedure:
        Exit Sub
    
    ErrorHandler:
        DisplayError "SetKeysButtonsCoordinates", conModuleName
        Resume ExitProcedure
    
    End Sub

    • Edited by InnVis Friday, August 18, 2017 12:52 AM
    Friday, August 18, 2017 12:10 AM
  • Hi J. Alexander Batista,

    you had mentioned that,"it only allows 1 subform to move at a time. I have 75 subform objects on my form."

    you can try to loop through all of them and try to change the location.

    Access have its limitation here and you need to find work around for that.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 18, 2017 3:12 AM
    Moderator
  • Okay.... I think I see where the problem is coming from HELP! (lol)

    So now I'm being flagged for the fields in my general mod named "modUpdateMain." here's the code for the mod.

    Option Compare Database ... With cur_sub ... !txtID = rs!ID
    ...

    I think because my FROM is now "tblSubPositions" and not "qryContacts" it's causing some error through the general module.

    FYI: Although my Tables are linked (tblContacts and tblSubPosition), the data that belongs to "tblContacts" I pull it using a QUERY called "qryContacts" for display purposes on my subform. I don't know if that is a problem with the INNER JOIN


    MISC. QUESTION: Wouldn't this all be easier just adding the fields "Top, Left, and Visibility" to the existing Table, tblContacts? Instead of using a completely different table.

    Hi Jamie,

    First, in the module I miss the line  "Option Explicit". For easy debugging it is a must to have that line there.

    In the join between qryContacts and tblSubContacts you have now two times the field "ID" (with two different meanings however, what is confusing). To distinguish between the two, the field must be preceeded "table"name, in this case:

             !txtID = rs![qryContacts.ID]

    With this correction you should be able to show the different frmMain's. 

    I don't think the use of qryContacts instead of tblContacts is the problem. To be honest, I never use QueryDefs, but make the querystring dynamical depending on the context on that moment.

    About your misc, question:
    Yes, the Top, Left, etc. fields can also be placed in tblContacts. That makes your model of the real world very simple, in my opinion just a little bit too simple. I think it is better to use the current model and use consistent short and clear names for the fields, and see that this works. Then the work with relations becomes easy, and can you continue with more nuanciated models of the real world.

    It is a good exercise for the use of the field "Service", Probably you have typed about 70 times the word "3-Center". In my opinion that is 69 times too much.

    But first thing: frmMain must be opened successfully.

    Imb.

    Friday, August 18, 2017 5:17 AM
  • Hi Jamie,

    Back to "modelling the real world": Why you want to have the user move around with the subforms? There must be some reason for.

    Want the users see the subforms in some kind of organizational order? I don't think is for a kind of playing at draughts.

    Imb.

    Friday, August 18, 2017 5:24 AM
  • With this code and Deepak's 4 button method, I managed to make any number of forms move simultaneously using a toggle button in the subforms.

    Private Sub cmdUp_Click()
    Dim x As Integer
    
    For x = 1 To 75
    
    If Me.Controls("sub" & x).Form.tglMove = True Then
    Me.Controls("sub" & x).Top = Me.Controls("sub" & x).Top - 100
    End If
    Next
    
    End Sub

    Friday, August 18, 2017 11:03 AM
  • So, the reason I started this project was because 17 departments had 17 different ways of laying out their "chain of command" due to their unique characteristics. Adding a move option to my standardized form, allows each department to still maintain their unique layout, while still compiling the data for me. I figured how to get the forms to move, now I just need to figure out how to record their movements in the table, using primary key and then load them in my loop when each department opens up the form.
    Friday, August 18, 2017 11:05 AM
  • So, the reason I started this project was because 17 departments had 17 different ways of laying out their "chain of command" due to their unique characteristics. Adding a move option to my standardized form, allows each department to still maintain their unique layout, while still compiling the data for me. I figured how to get the forms to move, now I just need to figure out how to record their movements in the table, using primary key and then load them in my loop when each department opens up the form.

    Hi Jamie,

    After a subform is moved, you can collect the values of .Top and .Left, and store these values (in inch or cm) in the tblsubPositions. Next time the Service opens frmMain, they see the subform in the right position.

    It is interesting to know their unique charteristics, because you can further automate the process, and the users do not need to play at draughts after a modification. I'm sure you will get that question anyway after some time of use.

    By the way, can you show now the correct frmMain for each Service, apart from the final position?

    Imb.

    Friday, August 18, 2017 12:27 PM
  • Yes, the database works flawlessly. This final feature is all that is missing. I need to record the position of each subform in the field "Top" and "Left" in Table "tblContacts" based on the primarykey value. Then I need to modify by loop on the onload event to take into account and pull the positions of the subforms based on primary key value. I even found a way to move the subforms (just not recording the location values!!!!). Eventually I'd like to evolve the 4 button (up, down, left, right) into a drag and drop, but that's way more advanced.
    • Edited by InnVis Friday, August 18, 2017 2:10 PM
    Friday, August 18, 2017 2:09 PM
  • Yes, the database works flawlessly. This final feature is all that is missing. I need to record the position of each subform in the field "Top" and "Left" in Table "tblContacts" based on the primarykey value. Then I need to modify by loop on the onload event to take into account and pull the positions of the subforms based on primary key value. I even found a way to move the subforms (just not recording the location values!!!!). Eventually I'd like to evolve the 4 button (up, down, left, right) into a drag and drop, but that's way more advanced.

    Hi Jamie,

    You say: "Top" and "Left" in Table "tblContacts", does this mean that you dropped tblsubPositions?

    At second thought, because of performance reasons, it is better to store "Top" and "Left" in twips, that means that the DataType of the fields should be Long. In your screenshot these fields were locsted in tblsubPositions, with DataType: Text.

    Please let me know if you need some further assistence.

    Imb.

    Friday, August 18, 2017 6:19 PM
  • Yes, I dropped tblSubPositions and I made those fields into numbers. I can't get the positions to actually record in the qryContacts on load >.<.
    Friday, August 18, 2017 6:48 PM
  • Yes, I dropped tblSubPositions and I made those fields into numbers. I can't get the positions to actually record in the qryContacts on load >.<.

    Hi Jamie,

    I copied a piece of your Fill_subform routine:

       With cur_sub 
          .Top = rs!Top
          .Left = rs!Left
    
          !txtTitle = rs![Job Title]
          !txtContactName = rs![Contact Name]
          !txtMPhone = rs![M Phone]
          !txtOPhone = rs![O Phone]
          !txtOtherPhone = rs![H Phone]
          !txtBranch = rs!Branch
          !txtID = rs!ID
          !txtAutoNUM = rs!AutoNUM
          !txtService = rs!Service
          
          Select Case rs!Branch
          Case "USN" : .Form.Detail.BackColor = 8388608
          Case "USA": .Form.Detail.BackColor = 1007160
          Case "USAF": .Form.Detail.BackColor = 16760576
          Case "CIV": .Form.Detail.BackColor = 6333684
          Case "CONTRACT": .Form.Detail.BackColor = 9846527
     
          Case Else:  .Form.Detail.BackColor = 9342606
    
          End Select
    
    

    I have assumed that Top and Left are expressed in twips.

    I also changed the order in  Select Case rs!Branch. In this way you catch ALL occurances of Branch. Especially in multiselect statement (a couple of Case's, or nested If-Then-Else constructions), try to cover all situations by using an Else part. 

    Imb.

    Friday, August 18, 2017 7:04 PM
  • Error, invalid use of null because rs!top and rs!Left are null
    Friday, August 18, 2017 7:37 PM
  • Error, invalid use of null because rs!top and rs!Left are null

    Hi Jamie,

    It sounds reasonable that these fields have a value in order to place the subform, isn't it?

    There are 1440 twips in an inch, or 567 in a cm.

    To tackle this problem in the transition state to complete filled fields, you could use:

        If (Not IsNull(rs!Top)) Then .Top = rs!Top
        If (Not IsNull(rs!Left)) Then .Left = rs!Left

    Imb.

    Friday, August 18, 2017 8:15 PM
  • EDIT: So... I added more code to the general module. I created a sub Update_subform and then looped the recordset on the close event of the form. Any thoughts, comments, improvements?

    Sub Update_subform(cur_sub As SubForm, rs As Recordset)
    
    With cur_sub
    
        rs.Edit
    rs!Top = cur_sub.Top
    rs!Left = cur_sub.Left
        rs.Update
    End With
    
    End Sub
    Private Sub Form_Close()
      Dim rs As DAO.Recordset
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts WHERE Service ='" & Me.OpenArgs & "'")
    
      Do While (Not rs.EOF)
     Update_subform Me("Sub" & rs!ID), rs
    
        rs.MoveNext
      Loop
      mSaved = True
      
    End Sub


    • Edited by InnVis Friday, August 18, 2017 10:42 PM
    Friday, August 18, 2017 10:21 PM
  • EDIT: So... I added more code to the general module. I created a sub Update_subform and then looped the recordset on the close event of the form. Any thoughts, comments, improvements?

    Hi Jamie,

    You did not tell if this all works. If you move a control, close the form, and reopen it again, do you see the subform then in the new position?

    If you were successful, then you can remove the routine from the Close event of the form. It is only effective in initializing the fields Top and Left. Imagine, the form is opened thousands of times, with mostly no modifications of the positions, that is quite inefficient.

    After a movement of a control you can better adjust immediately the values op Top and Left for that specific control, and there is no need anymore to save all positions when closing the form.

    Imb.


    PS: Have you already thought about adding a new Contact, and initializing the Top and Left positions?
    • Edited by Imb-hb Saturday, August 19, 2017 5:46 AM PS added
    Saturday, August 19, 2017 5:42 AM
  • Heh, sorry for the incomplete post. Yesterday I had been working on this database for so long I began to over-write codes in the wrong places and deleting codes that were working so I nearly made a mess because my brain was so overwhelmed. Now I've had some good sleep and motivated to finish this feature. SO, yes it all works (boxes change colors based on Branch, all subforms get update well on load and on double click, the edit form works well although the required field code in the save button needs some major improvements, the subform's positions update on load and save on close of main form or close of edit form, the toggle button works to position the subforms), but if you notice the module only identifies the actions I want done and the close event executes it. I do agree with you though, there are inefficiencies in the database that I must fix, I just haven't thought of a way yet. For example, I had an issue that since the positions were saving on the close event of the main form, if I opened the edit form and added information to the contacts then the position I had shifted prior would revert to its initial load position. In that case I duplicated the save position code on the on close of the edit form, so it's entered twice now on the close event of both the edit form and the main form. I also had one incident when during the close of my edit form, where the database had one of those "too much data" errors, but after I closed and re-opened the database I had not had it happen again.... but I am mortified of this happening to the user during data entry. It would completely ruin a transition from their old way of doing things. Besides these redundancies here's a list of things I have planned for this database.

    Missing functions:

    1). turn the move buttons invisible and make subform movements with the arrow keypad

    2). change the values of the combo box ranks based on selection of combobox branch in the edit form (i.e. if I elect USN then O-1, O-2, O-3 turns into ENS, LTJG, LT or if I elect USA then O-1, O-2, O-3 turn into 2LT, 1LT, CAPT)

    Improvements and bug fixes:

    1). when the form loads, sometimes the button bugs and stays black

    2). when the button is active to move the subforms, It sometimes stays black permanently when clicking on and off

    3). missing a box that defines borders so that moving the subforms out of the parameters is not allowed (otherwise boxes will go missing or an error will occur)

    4). improve the save and close button to search for unique parameters

    5). add a function that allows me to hide/show excess subforms

    6). the qryContactList, the open hyperlink disappears on the selected record and turns into a cursor instead -_-* drives me crazy.

    7). sometimes after moving my subforms with the toggled button, I can't turn off the toggle button's color until I move another subform (this is why I would have loved the drag and drop much better, but oh well)


    • Edited by InnVis Saturday, August 19, 2017 4:07 PM
    Saturday, August 19, 2017 3:54 PM
  • Missing functions:

    1). turn the move buttons invisible and make subform movements with the arrow keypad

    Improvements and bug fixes:

    3). missing a box that defines borders so that moving the subforms out of the parameters is not allowed (otherwise boxes will go missing or an error will occur)


    Hi Jamie,

    1). Set all controls default as invisible, and make them visible on load.

    3). Restrict the movements of the control within certain boundaries. Do not allow a movement to Top < 0, Left < 0, Right > ..., Height > ...

    Tomorrow I am off the whole day, so next answers will take a little longer.

    Imb.

    Saturday, August 19, 2017 7:07 PM
  • 2). change the values of the combo box ranks based on selection of combobox branch in the edit form (i.e. if I elect USN then O-1, O-2, O-3 turns into ENS, LTJG, LT or if I elect USA then O-1, O-2, O-3 turn into 2LT, 1LT, CAPT)

    Hi Jamie,

    One way is to extent the Fill_subform. Probably you want to change the Rank. Declare a variable tmp_rank, and use something like:

        Select Case rs!Branch
        Case "USN"
          .Backcolor = ...
          Select Case rs!Rank
          Case "O-1": tmp_rank = "ENS"
          Case ...
          End Select
        ...
        Case Else: tmp_rank = rs!Rank
        End Select
        !Rank = tmp_rank

    It works fast, but has the great disadvantage that you use user data in code. If something changes on user level, YOU have to change code, and do a new installation. Your code is then dependant on user definition, something you have to avoid. (You even could go a step further to make code application independant <grin>)

    Far better is to use a definition table, that the user can manage, through add, remove, change.

    A still more efficient alternative could be that you store already the right Rank (dependant on Branch) in tblContacts. Instead of doing the translation every time you open the form, you need to do that translation only on definition of the Contacts-record.

    Imb.


    • Edited by Imb-hb Saturday, August 19, 2017 8:22 PM typo
    Saturday, August 19, 2017 8:20 PM
  • Thanks Imb! I got close to what you suggested on the setting definitions for how far the user can move the subforms. I went into my form on design view and saw the farthest I wanted the subs to move then converted the size from inches to twips and altered the following code.

    Private Sub cmdRight_Click()
    Dim x As Integer
    
    For x = 1 To 75
        If Me.Controls("sub" & x).Form.tglMove = True And Me.Controls("sub" & x).Left < 14159 Then
           Me.Controls("sub" & x).Left = Me.Controls("sub" & x).Left + 24
        End If
    Next
    
    End Sub

    I did the same thing for the bottom button. The Top and Left were easy because it was just >0.

    Saturday, August 19, 2017 9:27 PM
  • For making the buttons invisible, I actually just want them to remain that way and make the up, left, right, and down arrows control those buttons while invisible OR better yet just delete the buttons and add some sort of custom button binding so that the up, left, down, and right arrows on the keyboard represent the codes that the current buttons have.
    Saturday, August 19, 2017 9:31 PM
  • For making the buttons invisible, I actually just want them to remain that way and make the up, left, right, and down arrows control those buttons while invisible OR better yet just delete the buttons and add some sort of custom button binding so that the up, left, down, and right arrows on the keyboard represent the codes that the current buttons have.

    Hi Jamie,

    Excuse me, I did not read good enough. You were talking about the move buttons, but I was thnking you meant the subcontrol's.

    It is now too late for the other question, but when you move one control, it is not necessary to loop through 75 controls. Tomorrow I can zoom in in this subject.

    Imb.

    Saturday, August 19, 2017 9:54 PM
  • Private Sub cmdRight_Click()
    Dim x As Integer
    
    For x = 1 To 75
        If Me.Controls("sub" & x).Form.tglMove = True And Me.Controls("sub" & x).Left < 14159 Then
           Me.Controls("sub" & x).Left = Me.Controls("sub" & x).Left + 24
        End If
    Next
    
    End Sub

    Hi Jamie,

    I do not understand the above code quite well. What I understand is that with cmdRight_Click ALL subforms move if within the limits.

    Only the "ActiveControl" should move. A problem is that clicking the cmdRight button, that button becomes the ActiveControl.

    Have you solved this?

    Imb.

    PS: What do you mean with:  4). improve the save and close button to search for unique parameters
    • Edited by Imb-hb Sunday, August 20, 2017 7:41 PM PS
    Sunday, August 20, 2017 7:36 PM
  • This code only allows movement of the subform if the toggle button "tglMove" is active. It actually works quite well, I just wish I could remove the buttons and make it work with just moving the arrows on the keyboard instead. So... here's what I mean by improve my save and close buttons. I have fields that are REQUIRED before the form can be saved, but on the phone number section I only need AT LEAST 1 phone number. The current code works, but I'm sure it can be optimized. I'm not sure of the effects of a longer code vs a shorter more efficient code, but I can only assume that the longer my code the higher chances of a crash or error during front end user data entry.

    Dim strMsg As String
    Dim null_phones As Boolean
    
    On Error Resume Next
    null_phones = IsNull(Me.txtOfficePhone And Me.txtMobilePhone And Me.txtOtherPhone)
    On Error GoTo 0
    
    If IsNull([txtJobTitle]) Then
         MsgBox "Employee's JOB TITLE is required!", vbCritical, "REQUIRED FIELD"
         [txtJobTitle].SetFocus
         Exit Sub
     ElseIf IsNull(txtBranch) Then
         MsgBox "Employee's BRANCH is required!", vbCritical, "REQUIRED FIELD"
         [txtBranch].SetFocus
         Exit Sub
     ElseIf IsNull(txtRank) Then
         MsgBox "Employee's RANK is required!", vbCritical, "REQUIRED FIELD"
         [txtRank].SetFocus
         Exit Sub
     ElseIf IsNull(txtLastName) Then
         MsgBox "Employee's LAST NAME is required!", vbCritical, "REQUIRED FIELD"
         [txtLastName].SetFocus
         Exit Sub
     ElseIf IsNull(txtFirstName) Then
         MsgBox "Employee's FIRST NAME is required!", vbCritical, "REQUIRED FIELD"
         [txtFirstName].SetFocus
         Exit Sub
     ElseIf IsNull(txtEMail) Then
         MsgBox "Employee's E-MAIL ADDRESS is required!", vbCritical, "REQUIRED FIELD"
         [txtEMail].SetFocus
         Exit Sub
     ElseIf null_phones = True And Not IsNull(txtLastName) Then
         MsgBox "At least ONE employee PHONE NUMBER is required!", vbCritical, "REQUIRED FIELD"
         [txtMobilePhone].SetFocus
         Exit Sub
    
    Else
         strMsg = "Record Saved!"
         If MsgBox(strMsg, vbInformation + vbOKOnly) = vbOK Then
         mSaved = True
    DoCmd.Close
    DoCmd.Requery
       End If
     End If
    
    End Sub

    Monday, August 21, 2017 11:12 AM
  • Hi J. Alexander Batista,

    I find that , you had created a thread in which you discuss about the same question that you had mentioned in your last post.

    HOW TO CREATE A SPECIFIC KEY PRESS AN INVISIBLE BUTTON IN FORM?

    I can see that thread was marked as answered.

    so if your issue is solved then I suggest you to mark the answer for this thread too.

    so that we can close this thread.

    the thread is still open and will remain open till you mark the answer.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 22, 2017 5:34 AM
    Moderator
  • Private Sub cmdRight_Click()
    Dim x As Integer
    
    For x = 1 To 75
        If Me.Controls("sub" & x).Form.tglMove = True And Me.Controls("sub" & x).Left < 14159 Then
           Me.Controls("sub" & x).Left = Me.Controls("sub" & x).Left + 24
        End If
    Next
    
    End Sub

    Hi Jamie,

    I now see that you have a tglMove button on the subform. That means the when you look at your frmMain. you will see that many toggle buttons?

    Together with the fact that you have to loop through 75 control to "actuate" the movement, this demonstrates in my opinion that this functionality is better placed on a higher organisational level, that is on the main form.

    By clicking the toggle button on the main form, you loose the focus of the subform, but that is easily handled: when a sublot control is "entered", you write the sublot number in a hidden control on the main form.

    When you next click the toggle button, the appearance of the subform control can be changed (e.g. backcolor), and you are ready to move.

    After a move, it is simply (the same for the other movements):

    Private Sub cmdRight_Click()
    
        With Me("sub" & Me!subform_nr)
          If (.Left < 14159) Then .Left = .Left + 24
        End With
    
    End Sub

    In the main form's module you can add an Enter event for each subform control:

    Private Sub Sub1_Enter()
      On_sub_Enter 1
    End Sub
    

    You type this one time, copy it 74 times, and modify the sub-numbers :   Sub2_Enter(),   On_sub Enter 2,  etc.

    In the same module you make a sub that does the job:

    Sub On_enter (subform_nr as integer)
        Me!Subform_nr = subform_nr
    End Sub
    In a next stage of your development you can extend the code in On_Enter for additional tasks, you can even place the Sub in a general module for general use in you application.

    Imb.

    • Marked as answer by InnVis Tuesday, August 22, 2017 11:00 AM
    Tuesday, August 22, 2017 7:58 AM
  • I like this idea Imb, I will have to play around with it. The only downfall is that I can only move one subform control at a time, while with a toggle I can move all of them or a select few or however many I click on.
    Tuesday, August 22, 2017 11:01 AM