mputter
asked on
Create Form from DAO ?
Don't ask me why, but I would like to create a form based on a specified table from code. Is this possible and how ?
Mike
Mike
Have you tried the wizards? They aren't perfect but they do take care of some of the more mundae tasks of alignment and control-source setting. In code this would be very hard to do because there are so many different kinds of tables.
mputter:
More details please. Do you want to store the properties of the form in a table and then create the form dynamically? I've heard of that being done before.
Wes
More details please. Do you want to store the properties of the form in a table and then create the form dynamically? I've heard of that being done before.
Wes
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
O.K. Let's get open:
I want to make a tool to split unnormalized tables. The idea I'm following is this:
1. Have a form with a combo to select the table to split. The combo's rowsource is dynamically set to contain all available tablenames in the db.
2. The creation of a subform, based on the selected table, which shows the data of the table. This subform needs therefore be created on-the-fly. The subform control's source on the mainform also has to be set dynamically from code.
3. A combo to select the fieldname that contains the criterium for splitting.
4. A combo to select the data-value of this field which acts as the criterium to extract the records from this table and put them in a new one.
My question is related to step 2.
The createform sounds good, but contains only part of the process. The actions performed when dragging a table on a form would be nice to be performed ultimately from code: Automatic creation of a subform control on the main form, automatic creation of the subform with all data-fields in it, and show its data! Sounds like a lot of work to do in code...
If someone knows an available tool to do this otherwise ....
Mike
I want to make a tool to split unnormalized tables. The idea I'm following is this:
1. Have a form with a combo to select the table to split. The combo's rowsource is dynamically set to contain all available tablenames in the db.
2. The creation of a subform, based on the selected table, which shows the data of the table. This subform needs therefore be created on-the-fly. The subform control's source on the mainform also has to be set dynamically from code.
3. A combo to select the fieldname that contains the criterium for splitting.
4. A combo to select the data-value of this field which acts as the criterium to extract the records from this table and put them in a new one.
My question is related to step 2.
The createform sounds good, but contains only part of the process. The actions performed when dragging a table on a form would be nice to be performed ultimately from code: Automatic creation of a subform control on the main form, automatic creation of the subform with all data-fields in it, and show its data! Sounds like a lot of work to do in code...
If someone knows an available tool to do this otherwise ....
Mike
I'll have to study this a bit more but I'm wondering that you don't use the wizards like cjswimmer suggested.
I'm not necessarily talking about the menus from the Forms tab - Design, but using the code that runs that wizard and make develop your own routine which does what you want. Sure beats the hell out of trying to write something like this from scratch.
All of the built-in wizards are available in MDA format from Microsoft. The code is wide open and you can use it for your own use. They are also a good, if not intense, way to learn more about some of the behind the scenes programming in Access.
I also believe that there is a way to create the simulation of running the form design wizard from code, which would also seem to do what you want.
If you want to explore this more, just say so and I'll see what I can come up with for you. Of course, you will not be able to do this (run the wizards) in an MDE, I think.
Jim
I'm not necessarily talking about the menus from the Forms tab - Design, but using the code that runs that wizard and make develop your own routine which does what you want. Sure beats the hell out of trying to write something like this from scratch.
All of the built-in wizards are available in MDA format from Microsoft. The code is wide open and you can use it for your own use. They are also a good, if not intense, way to learn more about some of the behind the scenes programming in Access.
I also believe that there is a way to create the simulation of running the form design wizard from code, which would also seem to do what you want.
If you want to explore this more, just say so and I'll see what I can come up with for you. Of course, you will not be able to do this (run the wizards) in an MDE, I think.
Jim
This code should do it.
Private Sub cboSelectTable_AfterUpdate
Dim ctlDefault As Control 'default control on the form that is being modified
Dim fldTable As Field 'a field in the table that is being analyzed
Dim tdefTest2 As TableDef 'table def of table that is being anlayzed
Dim frmTableDisplay As Form 'form that is being modified to display fields of analyzed table
Dim ctlNew As Control 'new control on form
Dim ctlolder As Control 'old control on form
Dim intcount As Integer 'counter for loops
Dim intcontrols As Integer 'number of controls on form
On Error GoTo ErrorHandler
Set tdefTest2 = DBEngine(0).Databases(0).T
''''set source object to a dummy form so that we can redesign the real subform
Me.fsubTabledisplay.Source
Me.fsubTabledisplay.Requer
DoCmd.OpenForm "fsubTableDisplay", acDesign, , , , acHidden
Set frmTableDisplay = Forms("fsubTableDisplay")
'''''Delete all controls on subform
With frmTableDisplay.Controls
'''''For each should have worked but had trouble so I bagged it
intcontrols = .Count - 1
For intcount = 0 To intcontrols
strName = .Item(0).Name ''''NB everytime I delete an item the index of the items left changes
''''so just delete the zero item every time
DeleteControl frmTableDisplay.Name, strName
Next intcount
End With
DoCmd.Close acForm, frmTableDisplay.Name, acSaveYes 'close and save redesigned subform
'note this is done because of potential dup names
' Create new text box for each field in table and bind it to that field
DoCmd.OpenForm "fsubTableDisplay", acDesign, , , , acHidden
Set frmTableDisplay = Forms("fsubTableDisplay")
frmTableDisplay.RecordSour
For Each fldTable In tdefTest2.Fields
Set ctlNew = Nothing
Set ctlNew = CreateControl(frmTableDisp
ctlNew.Name = fldTable.Name
Next fldTable
DoCmd.Close acForm, frmTableDisplay.Name, acSaveYes 'close and save redesigned subform
Me.fsubTabledisplay.Source
Me.fsubTabledisplay.Requer
Exit Sub
ErrorHandler:
MsgBox "oops " & Err.Description & " the error number is " & Err.Number
Resume Next
End Sub
Several things to note:
1) this code works in access 97
2) It is commented enough to be self explanatory I hope, but maybe not - if there is something obscure please let me know
3) I basically did not have to create a form in code; rather I created two totally blank forms (fsubtestit and fsubTableDisplay) to start with and added and deleted controls as necessary.
(Performance wise this is a bit better)
4)I ran this code off the after update event of a combo box that was filled with the names of the tables that were in the database
5)Jim I could be wrong but I think in Access97 the wizards are wrapped as mde's and so the code is not wide open.
However I think the book Access Developeres handbook has some of the code
Private Sub cboSelectTable_AfterUpdate
Dim ctlDefault As Control 'default control on the form that is being modified
Dim fldTable As Field 'a field in the table that is being analyzed
Dim tdefTest2 As TableDef 'table def of table that is being anlayzed
Dim frmTableDisplay As Form 'form that is being modified to display fields of analyzed table
Dim ctlNew As Control 'new control on form
Dim ctlolder As Control 'old control on form
Dim intcount As Integer 'counter for loops
Dim intcontrols As Integer 'number of controls on form
On Error GoTo ErrorHandler
Set tdefTest2 = DBEngine(0).Databases(0).T
''''set source object to a dummy form so that we can redesign the real subform
Me.fsubTabledisplay.Source
Me.fsubTabledisplay.Requer
DoCmd.OpenForm "fsubTableDisplay", acDesign, , , , acHidden
Set frmTableDisplay = Forms("fsubTableDisplay")
'''''Delete all controls on subform
With frmTableDisplay.Controls
'''''For each should have worked but had trouble so I bagged it
intcontrols = .Count - 1
For intcount = 0 To intcontrols
strName = .Item(0).Name ''''NB everytime I delete an item the index of the items left changes
''''so just delete the zero item every time
DeleteControl frmTableDisplay.Name, strName
Next intcount
End With
DoCmd.Close acForm, frmTableDisplay.Name, acSaveYes 'close and save redesigned subform
'note this is done because of potential dup names
' Create new text box for each field in table and bind it to that field
DoCmd.OpenForm "fsubTableDisplay", acDesign, , , , acHidden
Set frmTableDisplay = Forms("fsubTableDisplay")
frmTableDisplay.RecordSour
For Each fldTable In tdefTest2.Fields
Set ctlNew = Nothing
Set ctlNew = CreateControl(frmTableDisp
ctlNew.Name = fldTable.Name
Next fldTable
DoCmd.Close acForm, frmTableDisplay.Name, acSaveYes 'close and save redesigned subform
Me.fsubTabledisplay.Source
Me.fsubTabledisplay.Requer
Exit Sub
ErrorHandler:
MsgBox "oops " & Err.Description & " the error number is " & Err.Number
Resume Next
End Sub
Several things to note:
1) this code works in access 97
2) It is commented enough to be self explanatory I hope, but maybe not - if there is something obscure please let me know
3) I basically did not have to create a form in code; rather I created two totally blank forms (fsubtestit and fsubTableDisplay) to start with and added and deleted controls as necessary.
(Performance wise this is a bit better)
4)I ran this code off the after update event of a combo box that was filled with the names of the tables that were in the database
5)Jim I could be wrong but I think in Access97 the wizards are wrapped as mde's and so the code is not wide open.
However I think the book Access Developeres handbook has some of the code
fedsuns:
In regards to your comment(5), the Access wizards are available on MSN in .mda files. You can take them apart, mess with the code, add your own captions to the forms, etc.
Wes
In regards to your comment(5), the Access wizards are available on MSN in .mda files. You can take them apart, mess with the code, add your own captions to the forms, etc.
Wes
Thank you Wes for your information. I was pretty sure that the mda files were not delivered with Access but I was unaware that they were available on MSN. Again thanks for your info
Fedsuns
Fedsuns