How can I create a dynamically FORM?

G

Guest

I would like create a FORM and open it in datasheet view and binding a
recordset via ADO.

I can bind the recordset to a form without a problem.

But, I would like create just one form for all my recordsets and I need the
following changes programmatically.

1. Change programmatically the name of the controls (acTextControl)
2. Change programmatically the source control of the controls
3. Change programmatically the format
3. If the number fields in recordset is lesse than controls, the remains
should be invisible.
4. These changes can be executed in ADE or mde?

Suggestions??

Thanks,
JP
 
R

Ron Weiner

I have done this kind of thing "A LOT" for forms that are datasheets.
Basically what I do is to add more text boxes and their associated labels
than I ever think I will need. I name them something clever like Text1,
Text2, ..Text30. Same thing with the attached labels. Then in the form
load event I run some code like this to sync things up.

Dim rst As DAO.Recordset
Dim i As Integer, j As Integer
Set rst = Me.Form.RecordsetClone
j = 0
For i = 0 To rst.Fields.Count - 1
If j < 30 Then
Me("Text" & j + 1).ControlSource = rst.Fields(i).Name
Me("Label" & j + 1).Caption = rst.Fields(i).Name
Me("Text" & j + 1).ColumnHidden = False
Me("Text" & j + 1).ColumnWidth = 1500
j = j + 1
Else
pblnFieldOverflow = True ' Not enough controls
End If
Next
For i = j + 1 To 30
Me("Text" & i).ColumnHidden = True
Next
Set rst = Nothing

I have used this method in forms and reports to display the results of Xtab
queries where you never know how many columns you are going to get in the
result.

The down side to this is you can get some pretty ugly labels as the label
text is the name of the field. The only way around this would be to create
a whole new Meta Data layer around your database which would describe your
data. I have done this a couple of times too, but requires a LOT more work.

I currently support two applications (ASP/Sql and VB/Access) that use a
Database with one table that contains up to 100 Columns of NText/Memo fields
(named C1, C2, etc.) which gives me great freedom in storing any kind of
data in a common data structure. I am able to get away with it because of
the Meta Data layer that describes all of the attributes of the data (type,
display format, display name, etc.) and which column they are displayed in.
This gives great flexibility for the user as they can design their own data
layout and start entering data in the form that I provide. The data entry
and reporting routines are quite complex, but it does put the customer in
charge if his data with out having to write a program. I would only
recommend doing something like this when the customer needs to be able to
define their own data layouts.
 
G

Guest

Thanks a lot Ron.

I apply your code and it works very well.
But I have mnore questions about it.
1. I think, I can not show in datasheet view, isn't it? Well, I can show but
the headers shows text 1, text 2...
I tried to change the control name (Me("Text" & j + 1).Name=
rst.Fields(i).Name)
but i doesn't accept, only in design view.
There is away to give names dinamacally?

2. When the number of the fields is low, for example 5, the horizontal
scroll bars looks as 30 text boxes.
There is away to reduce the length of horizontal scroll bar?
3. How can I create a table to change the format, width and alias name? Is
it possible?
4. What is Meta Data layer ?

Thanks,
Jose Perdigao
 
G

Guest

Thanks a lot for your example.
It works fine and is really quickly to open queries.

One question.
When I open a query or form in datasheet view, it is easy and quickly to
export to excel from tools Office Links and then Analyze it with ms excel.
If the table or query is in sub form this command doesn't work. How can I
create a button to do the same functions to export to excel? Is it possible?
Frequently, I need to export data from a query or form in data sheet view to
excel or from query apply the filter with the right click and then export to
excel.

Thanks
 
R

Ron Weiner

Comments In Line
--
Ron W
www.WorksRite.com
Jose Perdigao said:
Thanks a lot Ron.

I apply your code and it works very well.
But I have mnore questions about it.
1. I think, I can not show in datasheet view, isn't it? Well, I can show but
the headers shows text 1, text 2...
I tried to change the control name (Me("Text" & j + 1).Name=
rst.Fields(i).Name)
but i doesn't accept, only in design view.
There is away to give names dinamacally?
If the Labels are attached to the text boxes then Me("Label" & j +
1).Caption = rst.Fields(i).Name will change the names of the header in
datasheet view. When you add a new text box to a form Access also places a
Label on the form for you. This label is attached to (actually a child of)
the text box. If you want to attach a label to a text box, Select the label
with the mouse, and do a Cut (CtrlX). Then select the text box with the
mouse and do a Paste (CtrlV).
2. When the number of the fields is low, for example 5, the horizontal
scroll bars looks as 30 text boxes.
There is away to reduce the length of horizontal scroll bar?
I do not know. I have not noticed this before and no one has ever reported
it back to me. Perhaps I have just not looked hard enough :)
3. How can I create a table to change the format, width and alias name? Is
it possible?
This is up to you, and how you want to implement this in your project.
Basically you should approach this just as you would approach any database
creation problem. Figure out what data is needed, what the releationships
are, etc., and roll your own. You can make it as simple or complex as your
application warrants.
4. What is Meta Data layer ?
See 3 above. "Meta Data" is your data that describes how to display (or
otherwise deal with) with the raw data.
 
D

Duane Hookom

To push the entire query/table to Excel, you could add a command button with
code like:

Private Sub cmdSendToExcel_Click()
On Error GoTo Err_cmdSendToExcel_Click
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
Me.cboQueryName, "c:\" & Me.cboQueryName & ".xls", True

Exit_cmdSendToExcel_Click:
Exit Sub

Err_cmdSendToExcel_Click:
MsgBox Err.Description
Resume Exit_cmdSendToExcel_Click

End Sub

If you need to apply a filter, you might need to use code to create a new
query (or modify an existing one) using the filter property of the subform
contol. The filter property is
=subformctrl.Form.Filter
 
G

Guest

This solution is fantastic; I was looking for this solution long time ago.

About the scroll bar that I wrote before, it happens if I open in the view
form, in datasheet view, it works fine.

I still need help about format.

I get some properties as the following example:

If j<30 then
Me("Text" & j + 1).ControlSource = rec.Fields(i).Name
Me("Label" & j + 1).Caption = rec.Fields(i).Name
Me("Text" & j + 1).ColumnHidden = False
Me("Text" & j + 1).ColumnWidth = 1500

Debug.Print rec.Fields(i).Name, TypeName(rec.Fields(i).Value),
Len(rec.Fields(i).Value)
Select Case TypeName(rec.Fields(i).Value)
Case "Integer"
Me("Text" & j + 1).Format = "#,##0"
Case "Single"
Me("Text" & j + 1).Format = "STANDARD"
Case "Double"
Me("Text" & j + 1).Format = "STANDARD"
Case "Decimal"
Me("Text" & j + 1).Format = "STANDARD"
Case "String"
End Select

If IsNumeric(rec.Fields(i).Value) = True Then
Me("Text" & j + 1).TextAlign = 3
End If

j = j + 1
Else

But the format is not complete.

1. I'm using adp and when I create a table in caption I type the alias name
but in datasheet view doesn't show as mdb. It shows the field name (column
name)
So, is it possible to some properties from the table about the column name?
Example:
Data Type; Lenth; Format; Caption..

My idea is get the column settings from the table (original setting) and
apply to format text boxes in a form.

Once again, thanks a lot.

Jose perdigao
 
R

Ron Weiner

Jose

I have never ever built an Access Project as an ADP, so I am completely
unfamiliar with any differences between ADP's and MDB's. I guess it is
reasonable to expect that there would be differences, especially with the
way the database treats the data store (as ADP's are Sql2K not Jet4). What
you are attempting seems reasonable to me, but I have never done it this
way.

I have always used another table that held the values for the control type
(text box, combo, check box, etc.), formatting, look up values, etc. I'd
run a query against that table to get the values that were applicable to the
current job, and apply the formatting based on what was returned by this
query. This is what I was referring to when I used the term Meta Data
layer.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top