Solved

Dynamically Create Fields in Form View

Posted on 2004-09-30
9
230 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 119

Expert Comment

by:Rey Obrero
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
 
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dropdown Not In List - not working correctly 11 34
Excess tables to Excel BackUp 3 27
MS Access Calculation wont work 5 35
format date field on certain entries 8 22
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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 …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

948 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

22 Experts available now in Live!

Get 1:1 Help Now