Solved

Dynamically Create Fields in Form View

Posted on 2004-09-30
9
219 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
Comment Utility

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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
 
LVL 5

Author Comment

by:Painfree888
Comment Utility

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

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 5

Author Comment

by:Painfree888
Comment Utility

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 119

Expert Comment

by:Rey Obrero
Comment Utility
sorry you can not.
you can only create controls in a form in design view.
0
 
LVL 5

Author Comment

by:Painfree888
Comment Utility

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 119

Accepted Solution

by:
Rey Obrero earned 250 total points
Comment Utility
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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now