How can I fit my form and controls to their contents


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
ghiguy NzambaITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
ghiguy NzambaITAuthor 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")
    Set RS = Nothing

End Sub
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.
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

ghiguy NzambaITAuthor Commented:
May be I should just change my design schema.
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.
ghiguy NzambaITAuthor 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.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ghiguy NzambaITAuthor 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.
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.