Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Dynamically Create Fields in Form View

Posted on 2004-09-30
9
253 Views
Last Modified: 2012-06-21

I have a form, on which I would like to dynamically create fields based on the results of a query while in form view. In another thread in this forum, I read that the CreateControl method may be used to do this, but when I try it, I receive an error message that states "Must be in design view to create fields". Well, that won't work, so was wondering if, perhaps, there is another method for doing this. Sample of what I am trying to do is below:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim cfSQLStr As String
Dim i As Double

cfSQLStr = "SELECT FieldA,FieldB FROM TableA"
Set db = CurrentDb
Set rs = db.OpenRecordset(cfSQLStr)

'Start looping through query results and: 1. Creating fields and 2. Setting field values to  
'query results

For i = 0 to rs.RecordCount
     'Create Fields - Name it: "FieldNameA_" & i AND "FieldNameB_" & i
     CreateControl("FieldNameA_" & i,"Other Function Arguments Here")
     CreateControl("FieldNameB_" & i,"Other Function Arguments Here")

     'Make newly created field values = query results
     Me("FieldNameA_" & i).Value = rs!FieldA
     Me("FieldNameB_" & i).Value = rs!FieldB
Next

Any help you can provide would be greatly appreciated. I am assigning this question a value of 250 points. Please do not hesitate to contact me if you need more details.

Thanks!
0
Comment
Question by:Painfree888
  • 5
  • 3
9 Comments
 
LVL 5

Author Comment

by:Painfree888
ID: 12192613

One addition to my question above, I was wondering also how I create the fields on a given piece of screen real estate. For example, I want the fields to be created, starting at the top of the form, one after the other, straight down the right side of the screen. So, would look like this when I got done:

FieldA_1     FieldB_1
FieldA_2     FieldB_2
etc.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 12192629
Hello Painfree888,

I'm afraid the message is correct.
You could create a form with 'enough' textboxes and then assign their controlsources at run time.  Maybe make any unassigned controls invisible.


Pete
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 12192708
sample code

try this,
create a blank form and save it as ROX

create another form with command button command1
copy and paste this codes

Private Sub Command1_Click()
Dim ctl As Control, frm As Form, i As Integer, j As Integer
Dim intX As Integer, intY As Integer
Dim intLblX As Integer, intLblY As Integer

intX = 500
intY = 500
intLblX = 800
intLblY = 500
DoCmd.OpenForm "ROX", acDesign
Set ctl = CreateControl("ROX", acOptionGroup, acDetail, , , 150, 150, 2500, 2500)
j = 0

For i = 1 To 4
Set ctl = CreateControl("Rox", acOptionButton, acDetail, , , intX, intY + j)
Set ctl = CreateControl("Rox", acLabel, acDetail, , "Option" & i, intLblX, intLblY + j)
j = j + 400
Next i
End Sub
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 5

Author Comment

by:Painfree888
ID: 12192731

Wow, quick answer. Capricorn, going to do as you suggest and then will let you know how it went. Thanks for your help.
0
 
LVL 5

Author Comment

by:Painfree888
ID: 12192770

One issue, I need to create the fields on a form that is already open. I probably should have mentioned this, but the form will remain open, and a timer event, every 60 seconds, runs a query and then will repopulate both the fields and the field values based on the results of the query. So, not sure this answer is going to work.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 12192839
sorry you can not.
you can only create controls in a form in design view.
0
 
LVL 5

Author Comment

by:Painfree888
ID: 12194316

Capricorn,

OK, very close to accepting your answer. What I've decided to do is to have the process to create these fields run from a different form that will also always remain open. I will hide the target form, open it in design mode, create and populate the fields and then open it in normal mode and make it visible.

I am having one issue, though, that I cannot seem to overcome. When I create the control, in this case a rectangle or field, no matter what I specify as the top or width values, the control is created at left=0 and width = 0 (or something close to that). So, the control is created in the upper left most corner of the detail window and the control is tiny. Have any idea what I am doing wrong?
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 12194547
this line will create an OptionFrame

Set ctl = CreateControl("ROX", acOptionGroup, acDetail, , , 150, 150, 2500, 2500)
--------------------------------------------------------------------  X----Y    W      H  
W=width
H=height   in twips
1 inch=1440 twips
0
 
LVL 5

Author Comment

by:Painfree888
ID: 12200086
Capricorn,

Understand, now, what I am doing wrong. Thank you for the clarification. I was confusing twips with inches, as you pointed out. Much appreciated!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question