Solved

Dynamically Create Fields in Form View

Posted on 2004-09-30
9
261 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

696 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