Create form like "Insert", "Form", "Tabular" menu

[Poduska]

Trying to create a form with unknown number of columns from a cross-tab query.

If I highlight the name of my cross-tab query within the queries tab and then select the menu items "Insert" ... "Form" and then click "Autoform: Tabular" this makes the form I want, creating the correct number of columns for what ever my cross-tab query requires! Simple but effective.

I know this sounds like a pivot table but a pivot table is a bit much for just showing dates.

How can I call this same process from VBA, say for example a command button? [/color red]

If I can get this to work my thoughts were to make a copy of the cross-tab query, renaming it with the users Environment variable and then creating the new form which will be saved with the new query name. I have to save with this process as we have many users on the database at the same time.

To help visualize the form,
1)Many Projects - Form will only show one

2)Variable Number of Tasks - These are rows so no problem there can have many as necessary.

3)Variable Number of Phases (Time Periods). Could be 5 could be 20 and as these are columns, here lies the problem.

4)If a date has been established for an Event and Project then it will appear in the proper column.

Yes, Yes, I know, I have been asked to recreate someones XLS sheet in Access but I cannot say NO.

I assume there is some WEB application which will do this but I only do Access but willing to learn if anyone has ideas.

Thanks.

 

[MajP]

I am a little confused on the recordsource and how it varies from one case to another. But I assume these forms look similar, but have a different amount of columns.

question: When you say "could be 5 could be 20", does that mean anywhere from 5 - 20 phases ? Or is that figurative anywhere from 0 - infinity? Need to be very specific about this.
question: Is there a defined set of 20 phases?

If the meaning is that 20 is the Max and there is a defined set. Then this is how I would do it.
1) Build a tabular form with 20 textboxes and their labels.
2) Shrink them all down real small and make them invisible.
3) In the forms on load event. Return the recordset
4) Read through the recordset fields using the fields collection. For each field ensure the caption property is set in the query or table.
5) Get the caption from the fields property. Make this your label
6) Get the field name, and make it the control source of the textbox to the field name
7) Set the position of the label and text box
8) set the size of the label and textbox
9) make them visible
So there is only one form and it builds dynamically based on the users recordsource.

This demo shows some of the technique.

http://www.4shared.com/document/T3EdvTsr/DynamicColumn.html

You could create all the controls dynamically, but in Access I feel it is much safer to start with hidden blank controls and then format and set their properties.

Look at the control naming convention.

Here is some of the code from the demo for discussion. But you really need to see this demoed to get the strategy.

Private Sub cmd4_Click()
  Me.RecordSource = "qry4Columns"
  hideColumns
  createForm
End Sub

Private Sub cmd5_Click()
  Me.RecordSource = "qry5Columns"
  hideColumns
  createForm
End Sub

Private Sub Form_Load()
  MsgBox "Normally all controls are hidden. The controls are visible as a demo. Now hit a button in the footer and dynamically build the form."
  DoCmd.Maximize
  'here is where you fire the code and build a form based on the recordsource
  'createForm
End Sub

Public Sub createForm()
  On Error GoTo errlbl
  Dim rs As dao.Recordset
  Dim fld As dao.Field
  Dim cnt As Integer
  Dim txt As Access.TextBox
  Dim lbl As Access.Label
  Dim lft As Long
  Dim rt As Long
  Set rs = Me.Recordset
  
  hideColumns
  For Each fld In rs.Fields
    cnt = cnt + 1
    Set lbl = Me.Controls("lbl" & cnt)
    Set txt = Me.Controls("txt" & cnt)
    lbl.Caption = fld.Properties("Caption")
    lbl.Visible = True
    lbl.Left = rt
    lbl.Width = 1 * 1440
    txt.ControlSource = fld.Name
    txt.Visible = True
    txt.Left = rt
    txt.Width = 1 * 1440
    rt = (lbl.Left + lbl.Width) + (0.5 * 1440)
  Next fld
  Exit Sub
errlbl:
  If Err.Number = 3270 Then
    lbl.Caption = fld.Name
    Resume Next
  Else
    MsgBox Err.Number & " " & Err.Description
  End If
End Sub

Public Sub hideColumns()
  Dim ctl As Access.Control
  Me.cmd4.SetFocus
  For Each ctl In Me.Controls
    If ctl.Tag = "?" Then
      ctl.Left = 0
      ctl.Width = 2
      ctl.Visible = False
    End If
  Next ctl
End Sub

You can use this concept to build what seems like Active X controls.

http://www.4shared.com/document/CeypAxB0/OffsetGraph.html

 

[dhookom]

If you want to display a dynamic crosstab query in a form, all you need to do is add a large subform control to an unbound main form. Then use a little code to set the source object of the subform control to the crosstab query.

Assume a combo box named cboQueryName and a Row Source that returns all of the none-action queries in your MDB:
[tt][blue]
SELECT msysobjects.Name
FROM msysobjects
WHERE (((msysobjects.Name) Not Like "~*" And (msysobjects.Name) Not Like "qapp*" And (msysobjects.Name) Not Like "qupd*") AND ((msysobjects.Type)=5))
ORDER BY msysobjects.Name;
[/blue][/tt]
Assume the subform control is named sfrmMySub.

Add this code in the after update event of the combo box:

Private Sub cboQueryName_AfterUpdate()
    Me.sfrmMySub.SourceObject = "query." & Me.cboQueryName
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

My fault. The OP said wanted an "Autoform Tabular", but I should have asked if a datasheet was OK. I assumed had to be a tabular form. Much simpler solution.

 

[dhookom]

Considering how little code is involved in setting the Source Object, I didn't really care if the OP wanted a continuous or datasheet. My users would get a datasheet whether they wanted it or not. I would probably not write a ton of code when a few lines would work.

Duane
Hook'D on Access
MS Access MVP

 

[Poduska]

WOW thanks for the ideas, I will try them tonight.

I will try and post results soon

I really don't care data sheet or tabular at this point. I just discovered the method I described which gave me SOMETHING which appeared to satisfy the changing number of columns even if I had to recreate the form each time. Just seems a waste to not be able to use the auto creation feature.

As to the number of Phases, it is difficult to tell, could be more than 20 as these are "major" phases of projects spanning up to 5 years.

Duane, I know EXACTLY how you feel but the requester has a "very large office" so I only dare say so much