How can I fit my form and controls to their contents

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.

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

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.

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.

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.

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