Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Dynamically Create Fields in Form View

Posted on 2004-09-30
9
Medium Priority
?
277 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
[X]
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
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 1000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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 …

597 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