• Dynamically create a form from Crosstab query (XP)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Dynamically create a form from Crosstab query (XP)

    Author
    Topic
    WSaap2
    AskWoody Lounger
    September 26, 2003 at 6:09 pm #394213

    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.
    thankyou

    Reply | Quote
    Viewing 2 reply threads
    Author
    Replies
    • WSHansV
      AskWoody Lounger
      September 26, 2003 at 6:32 pm #720612

      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?

      Reply | Quote
    • WSHansV
      AskWoody Lounger
      September 26, 2003 at 6:32 pm #720613

      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?

      Reply | Quote
    • WSZAve
      AskWoody Lounger
      September 29, 2003 at 8:59 am #721469

      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

      Reply | Quote
      • WSaap2
        AskWoody Lounger
        October 2, 2003 at 2:33 pm #723143

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

        Reply | Quote
      • WSaap2
        AskWoody Lounger
        October 2, 2003 at 2:33 pm #723144

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

        Reply | Quote
    Viewing 2 reply threads
    Reply To: Dynamically create a form from Crosstab query (XP)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    April 2025
    S M T W T F S
     12345
    6789101112
    13141516171819
    20212223242526
    27282930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.