Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamically create a form from Crosstab query (XP)

    We have a form where users select a start date (cboStartDate) and end date (cboEndDate). The after update event of these combo boxes assign values to global variables for start and end dates. There are functions called GetStartDate() and GetEndDate() that return those values in a Crosstab query as criteria (Between GetStartDate() And GetEndDate()). The Date fields are the Column Headers in the crosstab query.

    We want this crosstab query to be the data source for a form that will be opened in datasheet view. However, the form has a couple of calculations on it based on the data in each month. Since the date ranges are selected on the fly, how can we populate the form with the user's date selections dynamically?

    Please let me know if my explanation is not clear or you need a sample.
    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamically create a form from Crosstab query (XP)

    There are many examples of dynamic crosstab reports; I posted an example within the last two days. Dynamic crosstab forms are unusual, however - forms are mostly used to enter or edit data, and crosstab queries are not updateable.
    Is it essential that you view the query in a form?

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dynamically create a form from Crosstab query (XP)

    I wrote the following code. If you have any improvements let me know. "strQueryName " is hte name of a crosstab query that you saved.
    Zave


    Sub PrintLandscapeQuery(strQueryName As String)

    Dim frm As Form
    Dim ctlLabel As Control, ctlText As Control
    Dim rst As Recordset
    Dim intNumFields As Integer
    Dim strName As String
    Dim i As Integer


    On Error GoTo HandleErr


    ' Create new form with and recordsource.
    Set frm = CreateForm
    frm.RecordSource = strQueryName

    Set rst = New Recordset
    Set rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenKeyset
    rst.LockType = adLockReadOnly
    rst.Open (strQueryName)
    intNumFields = rst.Fields.Count

    For i = 0 To intNumFields - 1
    strName = rst.Fields(i).Name

    ' Create unbound default-size text box in detail section.
    Set ctlText = CreateControl(frm.Name, acTextBox, , , strName)
    ' Create child label control for text box.
    Set ctlLabel = CreateControl(frm.Name, acLabel, , _
    ctlText.Name, strName)
    Next i

    ' Restore form.

    On Error Resume Next
    DoCmd.DeleteObject acForm, "temp"
    On Error GoTo HandleErr
    DoCmd.Save , "temp"
    Forms!temp.Caption = strQueryName
    DoCmd.Restore
    DoCmd.OpenForm "temp", acFormDS
    Forms("temp").Printer.Orientation = acPRORLandscape



    ExitHere:

    Exit Sub

    ' Error handling block added by VBA Code Commenter and Error Handler Add-In. DO NOT EDIT this block of code.

    HandleErr:
    Select Case Err.Number
    Case Else
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "mdlFunctions.PrintLandscapeQuery"
    End Select
    ' End Error handling block.
    End Sub

  4. #4
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically create a form from Crosstab query (XP)

    ZAve,
    This code works great. Many thanks for your help. I have implemented it successfully.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •