473,289 Members | 1,866 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Insert Existing Fields to a Form through code or macro

2 2Bits
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)
Aug 14 '22 #1
8 7820
NeoPa
32,554 Expert Mod 16PB
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.
Aug 14 '22 #2
isladogs
454 Expert Mod 256MB
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.
Aug 15 '22 #3
jithb4u
2 2Bits
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. On Error Resume Next
  3. If Len(Dir(CurrentProject.Path & "\" & Me![camera] & "\" & Me![photo no] & ".jpg")) > 0 Then
  4.     Me![ImageFrame].Picture = CurrentProject.Path & "\" & Me![camera] & "\" & Me![photo no] & ".jpg"
  5. Else
  6.     Me![ImageFrame].Picture = CurrentProject.Path & "\" & "Absent.jpg"
  7. End If
  8.  
  9. 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...
Aug 15 '22 #4
isladogs
454 Expert Mod 256MB
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.
Aug 15 '22 #5
NeoPa
32,554 Expert Mod 16PB
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.
Aug 16 '22 #6
ADezii
8,834 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rst As DAO.Recordset
  3. Dim intFldCtr As Integer
  4. Dim frm As Access.Form
  5. Dim ctlText As Access.TextBox
  6. Const conTWIPS_PER_IN = 1440
  7. Dim intLeft As Integer: Dim intTop As Integer
  8. Dim intWidth As Integer: Dim intHeight As Integer
  9.  
  10. intLeft = conTWIPS_PER_IN * 0.25        '1/4 inch from Left of Screen
  11. intWidth = conTWIPS_PER_IN * 6          '6 inches Wide
  12. intHeight = conTWIPS_PER_IN * 0.5       '1/2 inch in Height
  13.  
  14. Set frm = CreateForm
  15.     frm.RecordSource = "tblData"
  16.  
  17. Set MyDB = CurrentDb
  18. Set rst = MyDB.OpenRecordset("tblData", dbOpenSnapshot)
  19.  
  20. For intFldCtr = 0 To rst.Fields.Count - 1
  21.   'Each Text Box is 1/8 inch from Top and 1/8 inch below previous Text Box
  22.   intTop = (intFldCtr * (conTWIPS_PER_IN * 0.5) + (IIf(intFldCtr = 0, 1, intFldCtr + 1) * 180))
  23.  
  24.     Set ctlText = CreateControl(frm.Name, acTextBox, acDetail, "", rst.Fields(intFldCtr).Name, _
  25.                                 intLeft, intTop, intWidth, intHeight)
  26. Next
  27.  
  28. rst.Close: Set rst = Nothing
  29.  
  30. 'Close and Save Form
  31. DoCmd.Close acForm, frm.Name, acSaveYes
Attached Images
File Type: jpg Capture.JPG (69.4 KB, 123 views)
Aug 16 '22 #7
CJ_London
27 16bit
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. Dim fld As DAO.Field
  3. Dim i As Integer
  4.  
  5. Me.RecordSource = Me.OpenArgs 'pass name of query or sql in the openargs parameter of docmd.openform
  6. i = 0
  7. For Each fld In Me.Recordset.Fields
  8.     With Me("T" & i)
  9.         .ControlSource = fld.Name
  10.         .Move (i * (2000 + 60), 0, 2000, 300   'change these to suit positioning - 60 is a touch over 1mm and provides a margin between controls
  11.    End With
  12.    'do the same for labels
  13.     i = i + 1
  14. Next fld
  15. 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.
Aug 17 '22 #8
NeoPa
32,554 Expert Mod 16PB
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.
Aug 18 '22 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: WindAndWaves | last post by:
Hi there, Can anyone tell me how I can run code/macro in a different database??? Below are the two situations where this may be applicable. 1. run a macro/code in another database that sends a...
0
by: henry fung | last post by:
Hi, How do you create a shoutcut to a table in an existing group using code only in Access 2003. The closest I can do is to create a shoutcut to a selected object to a new group using:...
1
by: Dixie | last post by:
I wish to add some fields to an existing table in code. I am using the following code from rkc. CurrentDb.Execute ("ALTER TABLE MyTable ADD MyNewField Text 25") This works , but I need to also set...
1
by: RbanBph | last post by:
I want to add an existing web form in my web application. I selected webform11.aspx in 'Add Exsting Item' dialauge box. But it includes webform11.aspx.resx file in project. I could not see the form...
4
by: leaf0209 | last post by:
Hi, I am new to this forum and have been troubled by this problem for the past few days. I would appreciate if someone would take a look and help me out. I am using vb express 2005 edition... ...
3
by: Iluvatar | last post by:
Hi, last week our project upgraded from vs.net 2005 to vs.net 2008. Our project still uses framework 2.0, because the deployment servers are not 3.x ready yet. Everything so far while migrating...
1
by: sarvesh1987 | last post by:
am new in C# ASP.NET. I have a problem in CheckBoxList to add selected items in database with multiple rows. I have one page where I insert Product Information in database on submit button. In the...
7
by: CD Tom | last post by:
A funny thing has happened and I'm at a loss as to where to find the answer. I'm using Access 2007, My Property sheet and Add Existing Fields are in the command bar but when I click on either one I...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.