If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Ability for user to create a "custom" form during runtime.

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 72
Ability for user to create a "custom" form during runtime.

I'm trying to puzzle a way to make this work. I'm not sure it's even possible and if it is, not easily done. But anyways here it is:

The database I've been working on is a type of auditing software. Several forms (audits) are available and the user simply runs through the fields, pretty much just answering yes or no along with some specific data. There are only eight audits at this time and usually it would never be more than 10 at one time and at least two of those audits would change.

The idea is that all of these audits are disabled at the end of their period but the data remains for report creation. The old audits can be reinstated at any time.

What i'm trying to puzzle out is a way that the simple user could create a new audit without having Access knowledge. I'm talking something that would probably be more suited to a Visual Studio project. The ability to add controls (with hard-coding behind the scenes to determine their position in the form), simple comboboxes to determine if the created field is required or not, some logic matters such as if one field is "Yes" then another field is disabled, the creation of a new table for that audit to hold it's data, the creation of a query to edit the data, and the creation of a report to show all of that data in a user friendly way.

Is what I'm asking, beyond the scope of Access or just not worth the trouble, and simply creating new audits and the VBA programming behind it, SQL statements, rowsources, etc.?

Any thoughts?
__________________
Version: Access 2010
Reply With Quote
  #2 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,177
Although this is not usual, nor easy, it can be done in Access. That's how Access Wizards work.

Just a little example:
Code:
Sub CreateForm()

    Dim frm As Form
    Dim ctl As Control
    Dim strName As String
    
    Set frm = Application.CreateForm
    frm.RecordSource = "Tbl_Orders"
    strName = frm.Name
    DoCmd.Close acForm, strName, acSaveYes
    DoCmd.Rename "Frm_Orders", acForm, strName
    DoCmd.OpenForm "Frm_Orders", acDesign
    Set ctl = Application.CreateControl("Frm_Orders", acComboBox, acDetail, , , 500, 500, 1720, 240)
    With ctl
        .Name = "ComboOrders"
        .ColumnCount = 3
        .RowSource = "SELECT OrderId,StartDate,EndDate FROM Tbl_Orders;"
        .LimitToList = True
        .AfterUpdate = "[Event Procedure]"
    End With
    Set frm = Forms("Frm_Orders")
    With frm.Module
        .AddFromString "Private Sub ComboOrders_AfterUpdate()" & vbNewLine & _
                       "" & vbNewLine & _
                       "    Dim rst As DAO.Recordset" & vbNewLine & _
                       "" & vbNewLine & _
                       "    Set rst = Me.RecordsetClone" & vbNewLine & _
                       "    rst.FindFirst ""OrderId="" & Me.ComboOrders.Value" & vbNewLine & _
                       "    If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark" & vbNewLine & _
                       "    rst.Close" & vbNewLine & _
                       "    Set rst = Nothing" & vbNewLine & _
                       "" & vbNewLine & _
                       "End Sub" & vbNewLine
    End With
    DoCmd.Close acForm, "Frm_Orders", acSaveYes
    
End Sub
__________________
Have a nice day!
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 72
Makes sense to me. Also looks daunting, though I'm definitely willing to try it.

You made the example of creating a combobox. I'm seeing a lot of variables that are needed. For example, several textboxes where you can type desired combobox values which could then be used.

Is there a specific way to position controls on the form in vba?

Also, I see you create the form in design mode. Then at the end,
Code:
 DoCmd.Close acForm, "Form_Orders", acSaveYes
takes the form out of design mode where i can then be opened with
Code:
 DoCmd.OpenForm Frm_Orders
?
__________________
Version: Access 2010
Reply With Quote
  #4 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,177
1. The position of the control is determined when it is created:
Code:
CreateControl(FormName, ControlType, Section, Parent, ColumnName, Left, Top, Width, Height
It can also be changed later on, when the form is in Design view:
Code:
ctl.Left = ..., etc.
2. Once the form is created and saved, you can open it the usual way:
Code:
DoCmd.OpenForm "FormName", ...
__________________
Have a nice day!
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 72
Wonderful! I'll have tons of questions down the road so I'll just bookmark this post. Thanks for the help!
__________________
Version: Access 2010
Reply With Quote
  #6 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,177
You're welcome!
__________________
Have a nice day!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On