Solved: Dynamically populate form with fields!

[afh110]

Hi guys,

Im using Access 2007 on a Microsoft XP OS.

I need to create a form that prompts the user for the number of input fields they require, when entered the form automatically creates these forms as the per the number requested by the user! So if i request 4 fields, the form will show me 4 forms for me to use.

Or is there any built in controls in Access 2007 that mimics what the "Repeater" control does in InfoPath?

[Imdabaum]

Hi guys,

Im using Access 2007 on a Microsoft XP OS.

I need to create a form that prompts the user for the number of input fields they require, when entered the form automatically creates these forms as the per the number requested by the user! So if i request 4 fields, the form will show me 4 forms for me to use.

Or is there any built in controls in Access 2007 that mimics what the "Repeater" control does in InfoPath?

Do you need the form to show custom information or just prompts? If you are storing the values you can use an array

Dim Dim FieldArray() As Variant
Dim size as Integer

size = InputBox ("Enter the number of fields you need.")
Dim i as Integer
For i=0 To size-1
FieldArray(i) = InputBox();
Next i

But I might not understand completely what you're trying to do. Do you want custom forms? Or just 4 forms to input values?

[afh110]

Ok well your suggesting to use an array in memory to save my data, that works, but im thinking of using a recordset instead! Is there any reason not to?

As for my original problem, if for example i loaded my array with 5 values and now i want to display these 5 values back to the user on a form. Im assuming to display these 5 values i will need to position 5 different controls in the form. However, the number of values loaded in the array not always gonna be 5 ? May be 2 maybe 20... depending on what the user wants. I can use a dataview to do that, but i need to do some validation on these values and some calculations! I was hoping to find some kind of DataSet grid that can be displayed on the form as in ASP.NET. Any ideas ? :)

[Imdabaum]

I suppose you could do something like this.


Dim dbs As Database, tbl As TableDef, fld As Field
Dim rs as DAO.Recordset
Dim i as Integer
Dim size as Integer
size = InputBox ("Enter the number of fields you need.")
Set dbs = CurrentDb
Set tbl = dbs.CreateTableDef("tblTemp")

For i=0 To inputs-1
Set fld = tbl.CreateField("Field" & (i+1), dbText)
tbl.Fields.Append fld
Next i
dbs.TableDefs.Append tbl
dbs.TableDefs.Refresh

Set rs = dbs.OpenRecordset("tblTemp", dbOpenDynaset)
With rs
For i=0 to rs.Fields.Count -1
.AddNew
.Fields(i) = InputBox("Value for field" & i)
.Update
Next i
End With
End Sub


As for creating a form dynamically based on that table, I'm not sure how you would do that.

I'm sure there's some control.Add functionality in vba, but I haven't ever used it. I'll google around and see if I can find something.

[Imdabaum]

Yep just as I thought. Check this out and see if this gives you any direction.

http://msdn.microsoft.com/en-us/library/aa221167(office.11).aspx

[afh110]

Hi Imdabaum, thanks for you last two threats, i actually didnt get to see the till now! I didnt get an email alert on them really. But looking at that last link you gave me i think that would have been a great option, but i resorted to a work around. I simply created a new table populated it with the Array content then binded it to my subform to display as a datasheet! It works nicely. So here is the code for anybody that might make use of it.

[VBA]
Dim i, j As Integer
Dim db As DAO.Database
Dim ObjWeightTbl As DAO.TableDef
Dim NewQry As QueryDef
Dim TableExist As Boolean
Dim DBname As String
Dim rs As DAO.recordset
Dim AryEnd As Integer
Dim SqlResult, MySQL As String

'Check if Table exist adn delete
With CurrentDb
Set db = CurrentDb
DBname = db.Name
TableExist = IsTable(DBname, "ObjWeightTbl")
If TableExist = True Then
db.TableDefs.Delete ("ObjWeightTbl")
End If
MySQL = "CREATE TABLE ObjWeightTbl (ID counter, Object text (50),Weight number)"
DoCmd.SetWarnings False
DoCmd.RunSQL MySQL
DoCmd.SetWarnings True

AryEnd = CInt(Forms!Step2_BSC_Creation!Txt_ObjCounter.Value) 'UBound(AryObj())
For i = 0 To AryEnd - 1
SqlResult = "INSERT INTO ObjWeightTbl(object,Weight)VALUES('" & AryObj(i, 1) & "','" & AryObj(i, 3) & "');"
.Execute (SqlResult)
Next i
End With

'link table to subform datasource
With CurrentDb
Set db = CurrentDb
DBname = db.Name

QryExist = IsQuery(DBname, "GetObjWeightQry")
If QryExist = True Then
db.QueryDefs.Delete ("GetObjWeightQry")
End If
Set NewQry = db.CreateQueryDef("GetObjWeightQry", "Select * from ObjWeightTbl;")
SF_Step21DS.SourceObject = "Query.GetObjWeightQry"
End With