Create form from table with variable number of fields? 2

[MePenguin]

Hi!

I would like to be able to create (with code) a form that can display a table with a variable number of fields - i.e. every time the form is openned it will check the number of fields in it's RecordSource table and display all the fields... (the underlying table is created by a TransferSpreadsheet command, importing with no field names).

This will then be used as a subform in a main form for assisting the convertion of an imported Excel sheet into our standard format...

Can I use code with a loop based on a field count to create text boxes as needed?

Help please!

Thanks,

Phil

 

[KenReay]

Hi

You can do that, but you would need to open the form (in code) in design view, make the changes, then open it in 'normal' mode.

Probably easier if you can possibly do it this way, is to define the maximim numbe rof fields you expect to get

set the visible property of each control to false

at run time, populate the controls, and set the visible property of controls/columns which exist to visible = tre

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MePenguin]

Thanks for the suggestion KenReay, I'll keep it in mind.
Part of the problem is that I'm working with Excel sheets created by many people, under many years, and with no real limits to the number of fields (sheets of data on fossil insect finds)... I suppose I could limit the number of fields that I import and then recombine the rest of the data on export using SQL...

 

[KenReay]

Hi

I think, if you are working with Excel, there is a limit on the number of Columns allowed, so there is in fact a maximum number of controls needed.

However I do not know off the top of my head what the limit is for each version of Excel, so cannot quote it here

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MePenguin]

Solved it (with a little help from KB etc)! But thank's anyway. Here's the (untidy!) code for anyone that is interested:

Dim MyForm As Form, MyControl As Control
Dim db As Database, rs As Recordset, fld As Field
Dim CountsheetFields(), fieldName(), NewControlName As String
Dim FieldLoop, CountFields As Integer

DoCmd.OpenForm "FToolsNonBugsCountsheet", acDesign

Set MyForm = Forms![FToolsNonBugsCountsheet]

    Set db = CurrentDb
    Set rs = db.OpenRecordset("Non Bugs Countsheet", DB_OPEN_DYNASET)
    rs.MoveFirst
    FieldLoop = 1
    
    CountFields = rs.Fields.Count
    ReDim fieldName(CountFields)
    For Each fld In rs.Fields
        fieldName(FieldLoop) = fld.Name
                
        Set MyControl = CreateControl(MyForm.Name, 109)
        With MyControl
            NewControlName = "Box" & fieldName(FieldLoop)
            .Name = NewControlName
            .ControlSource = fieldName(FieldLoop)
           .Width = 1500
           .Height = 200
           .Top = 0
           .Left = (FieldLoop * 1500) - 1500
        End With
        FieldLoop = FieldLoop + 1
    Next fld
    DoCmd.Save acForm, "FToolsNonBugsCountsheet"
    DoCmd.Close acForm, "FToolsNonBugsCountsheet"

If anyone can predict any problem's that it will cause me later, then I'd appreciate them saying so.

Regards,

Phil

 

[KenReay]

Hi

Just a thought, to blight your day! and I am not sure about this but..

in a table there is a maximumn number of columns (511 I think), plus if you create a column, delete it, then create another one, the 'slot' is used up, so you can get a too many columns error, even before you get to 511 columns. The 'slots' are recovered when the databse is compacted (I believe), as I say I am not sure, but a similar mechanism may exist in forms, in which case depending on how many controls you typically create and how often you compact your db, you may hit problems

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MePenguin]

Thanks for that. Could prove useful later. I suppose I could cater for it in advance by having the database compact on close, or at least every so often.

Regards,

Phil

 

[MichaelRed]

There is also a limit on the form width. If you are simply always adding the field to the (immediate) right of the previous field, you can easily run out of real estate. A further 'hassle' is thee lack of lables to go along with the fields. See thread705-481652 for a slightly different procedure for creating a report / form. It also does not account for the horizontal (width) limitation of forms, but does address additng lables / captions to the form header.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MePenguin]

Just found that out about an hour ago. I've made it create text boxes down the form instead of across, and set it's default view to datasheet (which is fine since it's Excel data, labels are not an issue either, but thanks for the warning and tip).

Phil