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

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:

Answer 1

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.

Answer 2

Hi,

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

Cheers!

Answer 3

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!!

Answer 4

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?

Answer 5

Hi Jamie,

Any progress with the Position_tbl.

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

Imb.

Answer 6

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 [e-mail].

Answer 7

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 [e-mail].

Answer 8

Hi Deepak,

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

Thanks!

Answer 9

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.

Answer 10

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.

Answer 11

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.

Answer 12

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).

Answer 13

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.

Answer 14

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.

Answer 15

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.

Answer 16

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

Answer 17

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.

Answer 18

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

Answer 19

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.

Answer 20

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?

Answer 21

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.

Answer 22

Answer 23

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.

Answer 24

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.

Answer 25

I'm getting an error "item not found in this collection" and it's highlighting the Fill_subform Me("Sub".....

Answer 26

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.

Answer 27

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.

Answer 28

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.

Answer 29

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

Answer 30

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 [e-mail].

Answer 31

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.

Answer 32

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.

Answer 33

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

Answer 34

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.

Answer 35

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.

Answer 36

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.

Answer 37

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.

Answer 38

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 >.<.

Answer 39

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.

Answer 40

Error, invalid use of null because rs!top and rs!Left are null

Answer 41

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.

Answer 42

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

Answer 43

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?

Answer 44

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)

Answer 45

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.

Answer 46

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.

Answer 47

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.

Answer 48

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.

Answer 49

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.

Answer 50

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

Answer 51

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

Answer 52

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 [e-mail].

Answer 53

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.

Answer 54

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.