Insert Existing Fields to a Form through code or macro

[jithb4u]

In an existing Form I want to show every field and data..
For that I have to go Form Design View and Insert Existing Field and manually drag and drop every field to the current form....

I wonder if there is any chance to Insert Existing Fields to a Form via a VBA code.. Because I created the table using import from excel and the field name may vary each time depending on the excel file heading rows.
(For example first time there were 5 heading rows and for the next file it has 8 heading rows.. So if it is automated I can see every records)

[NeoPa]

That's an interesting question.

Welcome to Bytes.com :-)

To start with, bear in mind this is absolutely not where Access' strengths lie - in as much as it really isn't a spreadsheet - but nevertheless it is flexible and powerful enough to accommodate a great deal. It is often unwise to use it this way but I'll leave you to determine that as it's not really the scope of the question.

Creating new Controls - and let's be clear we're talking about Controls on a Form and Fields in a Recordset as confusing the two as you have will only lead you into unnecessary difficulty - on a Form is probably possible but not straightforward . Rather, you could consider creating a whole bunch of Controls such that there will always be more than you need. Those you will always require can be set in position and visible while the others can be left invisible and at position x=0;y=0.

In your code, when you determine a new one is required, simply assign one that's already there and set up its position, size & visibility - as well as any other attributes that may change based on the data you know about but we don't - and expand as required.

[isladogs]

Yes, it can be done for a datasheet form using code as follows:
1. Replace the existing form with a new blank form
2. Loop through the source object table or query and add each in turn to the blank form then populate it with data
3. Adjust the column widths to fit the amount of data in each column
4. Open the new form

The process is very quick and I can supply the code if you wish.

I use the code for special situations such as displaying the data for imported JSON files.
I'm currently using it with a query multi viewer app which is almost ready for release.
This has all 3 query views (SQL/design/datasheet) views together on one form (similar to SSMS).

However, for standard situations, this isn't something you should normally be doing.
The number of fields required in tables & queries should be planned in advance.
You shouldn't be continually adding or deleting fields. Doing that suggests a poorly designed database and perhaps using Excel would be better.

[jithb4u]

Hello,
Thank you for your time and I am glad you really understood my question.
I used this Access database for making ID Cards for schools.

The fields may varies with each school or colleges (eg: In lower classes they don't need mobile numbers
but they need parent name, and for higher classes they add Subject etc...)

The users are not much familiar with access database, so I decided to import excel files to Tables.

In my existing form there is an Image frame to display pictures from the same folder, Below is the code

Private Sub Form_Current()
On Error Resume Next
If Len(Dir(CurrentProject.Path & "\" & Me![camera] & "\" & Me![photo no] & ".jpg")) > 0 Then
    Me![ImageFrame].Picture = CurrentProject.Path & "\" & Me![camera] & "\" & Me![photo no] & ".jpg"
Else
    Me![ImageFrame].Picture = CurrentProject.Path & "\" & "Absent.jpg"
End If
 
End Sub

I need to insert all the fields from the table (table name will be always same) and an image frame with above code..
So if you import an excel file with 5 header rows there should be 5 fields to be inserted, and if you import with 10 header there should be 10 fields to be inserted, like that

I don't know is it possible or not...

[isladogs]

Hi
Its not obvious whether your reply was directed at one or both of us.

Almost anything is possible with Access but doing it the way you describe isn't a good idea.
Data is frequently imported from Excel into Access but although a spreadsheet and an Access table look similar, they don't function in the same way.
It is often said that Excel files are short and wide with many columns.
A well designed Access table is normally long and narrow-many records but few fields.

So, for example, in Excel you might have a field for exam marks in each subject - English, History, Geography Science etc, etc
In Access, you would have two fields: Subject and Mark. Each subject mark would be in a separate record. This makes data processing much more efficient

That means when you import Excel data into Access you link the Excel file and then need to do some processing to modify the structure before importing into Access tables.. This is called normalisation.

The result is that it would be very rare that you would need to add additional fields in the way you describe.

[NeoPa]

I will first draw your attention to the post from isladogs. Please read that first before proceeding.

From that point I would repeat what has been said in as much as what you're looking to do seems fundamentally unwise. However, we're also here to offer answers so that anyone with similar questions has a good resource to draw from. As such I would suggest we could expand slightly on my earlier suggestion as we now know (We would have known earlier if you'd included this information in your question of course.) that you're asking about a very finite set of Fields (and thus Controls on the Form) that may or may not be used in the various different circumstances you may be called on to handle.

This makes it more sensible to create Controls for those Fields specifically, but any which are not used universally should be left as invisible until your code determines they are needed and thus makes them visible as well as positioning and sizing them as required.

Access is massively flexible. It simply requires a little ingenuity to get it to do what you want. However, be careful with such a design. Sometimes it turns out that being clever gets you so far, but when you need to go further you end up having to take a different route.

PS. isladogs has brought up the concept of Normalisation (See Database Normalisation and Table Structures.), which is a fundamental set of concepts required for properly handling databases. I very much recommend that you take the opportunity to review the linked article for a better understanding of this.

[ADezii]

First and foremost, I agree wholeheartedly with everything that NeoPa and isladogs have stated in prior Posts. They always have, and always will, provide excellent advice that should always be taken when possible. In the event that you still need to dynamically create Controls on a Form based on the number of Fields in a Table, In have created a Demo for you that should at least point you in the right direction. As you will see, it may be a little more involved than you realize. First, some basic information related to the Demo:

1. I created a Table named tblData that consists of eight Fields (TEXT) numbered Field1 thru Field8.
2. The Code will dynamically create a Form and set it's RecordSource = tblData.
3. Next, X number of Controls (TextBoxes) will be created on this Form where X = the number of Fields in tblData.
4. The Control Sources of these TextBoxes will be set to the appropriate Fields, namely Field1 thru Field8.
5. These TextBoxes will be precisely positioned, 1/8" from Top of the Screen for Field1, then 1/8" between TextBoxes.
6. Each TextBox will be 1/2" in Height and 6" in Width.
7. There are no associated Labels for these Controls, gotta give you something to do (LOL).
8. The final step in the Code is to Close, then Save, the Form.
9. I have attached an Image of the Form in Design View after the Code has been executed. I have applied a Background color to the Detail Section for effect.

   

10. At this point, it just becomes a matter of adjusting the nine Arguments of the CreateControl() Method to accomplish exactly what you are looking for. IMHO, the most difficult part is in the positioning of these Controls.
11. I hope that I haven't confused matters for you, shouold you need further assistance, simply ask.

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim intFldCtr As Integer
Dim frm As Access.Form
Dim ctlText As Access.TextBox
Const conTWIPS_PER_IN = 1440
Dim intLeft As Integer: Dim intTop As Integer
Dim intWidth As Integer: Dim intHeight As Integer
 
intLeft = conTWIPS_PER_IN * 0.25        '1/4 inch from Left of Screen
intWidth = conTWIPS_PER_IN * 6          '6 inches Wide
intHeight = conTWIPS_PER_IN * 0.5       '1/2 inch in Height
 
Set frm = CreateForm
    frm.RecordSource = "tblData"
 
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tblData", dbOpenSnapshot)
 
For intFldCtr = 0 To rst.Fields.Count - 1
  'Each Text Box is 1/8 inch from Top and 1/8 inch below previous Text Box
  intTop = (intFldCtr * (conTWIPS_PER_IN * 0.5) + (IIf(intFldCtr = 0, 1, intFldCtr + 1) * 180))
 
    Set ctlText = CreateControl(frm.Name, acTextBox, acDetail, "", rst.Fields(intFldCtr).Name, _
                                intLeft, intTop, intWidth, intHeight)
Next
 
rst.Close: Set rst = Nothing
 
'Close and Save Form
DoCmd.Close acForm, frm.Name, acSaveYes

[CJ_London]

I use a similar technique to Adezii but I create the form with around 50 textboxes plus associated labels plus 50 labels in the header and 50 textboxes in the footer - so around 200 controls altogether.

Labels are named in a similar way T0, T1, T2 etc for textboxes, L0, L1 for associated labels, H0, H1.. for header labels and as you may guess F0, F1 etc for the footer textboxes.

50 may seem to be overkill, but I have had occasions where that number is required.

All controls are visible, but left, top, width and height are all set to 0. Textbox control sources are left empty

All form views allowed are allowed.

This technique also works with .accde

The technique is similar to that used for dynamic reports based on crosstabs

in the form on load event, you have code along these lines

Private Sub Form_Load()
Dim fld As DAO.Field
Dim i As Integer
 
Me.RecordSource = Me.OpenArgs 'pass name of query or sql in the openargs parameter of docmd.openform
i = 0
For Each fld In Me.Recordset.Fields
    With Me("T" & i)
        .ControlSource = fld.Name
        .Move (i * (2000 + 60), 0, 2000, 300   'change these to suit positioning - 60 is a touch over 1mm and provides a margin between controls
   End With
   'do the same for labels
    i = i + 1
Next fld
End Sub

There is potentially a lot more you might want to do in configuration - change form view, add code to a control event, add conditional formatting or change other control properties such as backcolor, forecolor, font etc. Can also apply code to resize width of control dependant on contents. Pretty much all the sorts of things you might want to do with a specifically designed form.

[NeoPa]

For any interested parties, I have moved some of the newer posts across to a new thread (Two Experts Meet) where they make better sense together and don't confuse this one.