How can I fit my form and controls to their contents

ghiguy Nzamba
ghiguy Nzamba used Ask the Experts™
on
Hello,

I want to fit my controls to their contents and also my form to Its too but I don't know to proceed.

  1) In my controls, I would like to dynamically remove the extra spaces by making a go to the line
  2) As for my form, It's to fit to Its content in whatever screen It's displayed.

Please see the attached image

I will  be gratefull for any reply from you.

Thanks in advance

* Sorry for my English I'm from a french country
C--Users-L0340909-Pictures-form-imag.JPG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
There is no good way to do this.  For starters, the controls on a form do not automatically resize as the controls on a report do (vertical only though).  That means you would have to do it manually.  How would you determine the width should you decide you really want to do this?  Well, for starters, you will have to figure out the maximum character count of of each column  by examining the recordset returned by the Form's ControlSource.  If the ControlSource is the table name rather than a query with criteria, you will be analyzing every single row in the table.  Just knowing the character count of a field is not sufficient for determining the width because you also need to know whether you are using a proportional or non-proportional font and the point size.  an "i" takes far less space than a "W" and you would need to include that in your calculation.  Once you have determined the width required for each column, you need to decide if the form is wide enough.  Do you also want to make the form wider?  If not, how do you decide which column to make narrower.  Then you need to decide if there should be any space between controls and create the loop to reposition all the controls based on your calculations.

And on and on and on.

Try using DS view.  The columns can be resized either with code or manually and the height and column order may also be adjusted.  If you want the values to be saved, you will need to create a table in which to store the values for EACH user and code to do it.

Author

Commented:
Hi Pat,
Thanks for your reply.
I already have a module that repositions my controls on the form I just wanted to know if I could find a way or customize my module to remove extra spaces in the textboxes.

Public Sub PositionFields()

    Dim strSQL As String
    Dim RS As DAO.Recordset
    Dim lngLeft As Long
    Dim ctrl As Control
    Dim frm As Form
   
    Set frm = Forms!frmItem
   
    strSQL = "SELECT DisplayThis, FieldName FROM tbl_Field_Sort_and_Select " _
           & "ORDER BY Sort_Order"
    Set RS = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
   
    lngLeft = 0.5 * 1440
    While Not RS.EOF
        Set ctrl = frm.Controls("lbl_" & RS("FieldName"))
        ctrl.Visible = RS("DisplayThis")
        ctrl.Left = lngLeft
       
       
        Set ctrl = frm.Controls("txt_" & RS("FieldName"))
        ctrl.Visible = RS("DisplayThis")
        ctrl.Left = lngLeft
       
        lngLeft = ctrl.Left - ctrl.Width * RS("DisplayThis")
   
        RS.MoveNext
    Wend
    RS.Close
    Set RS = Nothing



End Sub
Distinguished Expert 2017

Commented:
Well you've gotten that far, you might as well figure out an average twips width for each character given the font and point size you are using and count the characters in each column.  That will allow you to determine a width for the maximum length piece of data in the recordset.  It won't be exact but it should be close.

BTW - this is not a normal feature of a user app.  If you have to do it, it may be due to a design flaw in your schema.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
May be I should just change my design schema.
Distinguished Expert 2017

Commented:
It's hard to say since we don't know what the current schema is or why you embarked on this design that requires design changes at runtime.

In general, it is poor practice to make design changes at runtime since it prevents you from distributing as an .accde or .accdr or from using the Access runtime.  Design changes cause bloat so you would also have to deal with that and finally, you shouldn't be sharing the actual FE and making design changes at runtime will absolutely prevent this.

Author

Commented:
well I was asked to develop an access database for project cost estimation with 2 parts.
 one part to retreive Item costs and the other one to make calculations to estimate a project.

All the data were already stored in a excel file. So I started by creating one access table from my excel file. the result was a table with more than 10 columns that made my form larger to fit on one screen.
Distinguished Expert 2017
Commented:
Access is a relational database.  It is not a spreadsheet.  It is very unusual to be able to simply port a spreadsheet and have it function correctly as a table.  Most need at least some normalization and ultimately end up as several tables.  It isn't possible to give you specific advice without seeing all your inputs and knowing what you are trying to do but  if you post a copy of the spreadsheet (change any sensitive data) someone may be able to offer a specific solution.

Author

Commented:
I've finally changed my mind thanks to yours comments and what I need now, It's to build a database that will manage cost data so that we estimate project costs.

thanks for your support.
Distinguished Expert 2017

Commented:
You're welcome  

I have a sample app that uses what I call "bound denormalized forms" that is very appropriate for forecasting.  I'll attach a .pdf with an outline but the actual database is to large to post on EE.  If you think you want it when you get into the design, drop me a PM and include your email so I can send it to you.Bound-Denormalized-Forms.pdf

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial