Dynamic Fields on a form

[zyxwvu44]

Hello everyone, I have a bit of a complex problem (for me anyway). I need dynamic fields in a form. I got them to work on a report using the microsoft tutorial here: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q328320 but i can't get it to work on a form. I basically tried to copy over the code from the report and modify it, but it does not work at all. My code is as follows:

Option Compare Database

   Const conTotalColumns = 50

   '  Variables for Database object and Recordset.
   Dim dbsReport As DAO.Database
   Dim rstReport As DAO.Recordset

   '  Variables for number of columns and row and report totals.
   Dim intColumnCount As Integer
   Dim lngRgColumnTotal(1 To conTotalColumns) As Long
   Dim lngReportTotal As Long
Private Sub InitVars()
   Dim intX As Integer
   ' Initialize lngReportTotal variable.
   lngReportTotal = 0
  
End Sub
Private Function xtabCnulls(varX As Variant)
    ' Test if a value is null.
   If IsNull(varX) Then
      ' If varX is null, set varX to 0.
      xtabCnulls = 0
   Else
      ' Otherwise, return varX.
      xtabCnulls = varX
   End If
End Function
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   ' Put values in text boxes and hide unused text boxes.
    
   Dim intX As Integer
   '  Verify that you are not at end of recordset.
   If Not rstReport.EOF Then
      '  If FormatCount is 1, put values from recordset into text boxes
      '  in "Detail" section.
      If FormatCount = 1 Then
         For intX = 1 To intColumnCount
            '  Convert Null values to 0.
            Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
         Next intX
    
         '  Hide unused text boxes in the "Detail" section.
         For intX = intColumnCount + 1 To conTotalColumns
            Me("Col" + Format(intX)).Visible = False
         Next intX

         '  Move to next record in recordset.
         rstReport.MoveNext
      End If
   End If
    
End Sub
Private Sub Detail_Retreat()

   ' Always back up to previous record when "Detail" section retreats.
   rstReport.MovePrevious

End Sub


Private Sub formHeadersection_Format(Cancel As Integer, FormatCount As Integer)
    
   Dim intX As Integer
    
   '  Put column headings into text boxes in page header.
   For intX = 1 To intColumnCount
      Me("Head" + Format(intX)) = rstReport(intX - 1).Name
   Next intX


   '  Hide unused text boxes in page header.
   For intX = (intColumnCount + 2) To conTotalColumns
      Me("Head" + Format(intX)).Visible = False
   Next intX

End Sub


Private Sub form_Close()
    
   On Error Resume Next

   '  Close recordset.
   rstReport.Close
    
End Sub


Private Sub form_NoData(Cancel As Integer)

   MsgBox "No records match the criteria you entered.", vbExclamation, "No Records Found"
   rstReport.Close
   Cancel = True

End Sub


Private Sub form_Open(Cancel As Integer)

   '  Create underlying recordset for report using criteria entered in
   '  EmployeeSalesDialogBox form.
    
   Dim intX As Integer
   Dim qdf As QueryDef
   Dim frm As Form

   '  Set database variable to current database.
   Set dbsReport = CurrentDb
  
   '  Open QueryDef object.
   Set qdf = dbsReport.QueryDefs("matrix query")
   ' Set parameters for query based on values entered
   ' in EmployeeSalesDialogBox form.
   
   '  Open Recordset object.
   Set rstReport = qdf.OpenRecordset()
   
   '  Set a variable to hold number of columns in crosstab query.
   intColumnCount = rstReport.Fields.Count
    
End Sub





Private Sub formHeader_Format(Cancel As Integer, FormatCount As Integer)

   '  Move to first record in recordset at the beginning of the report
   '  or when the report is restarted. (A report is restarted when
   '  you print a report from Print Preview window, or when you return
   '  to a previous page while previewing.)
   rstReport.MoveFirst

   'Initialize variables.
   InitVars

End Sub

Does anyone have any pointers or tips on how I could get this to work? Thanks in advance.

 

[Rabbie]

Can you be more specific as to what does not work. "Does not work at all" does not tell us much. Do you get error messages? The more info you give us the more help you will get.

 

[zyxwvu44]

I see the empty unbound text boxes but no information is put into them. I get no errors at all, however when i compile it i have problems with the formatcount function. After looking it up it says that it's only used to reports, is there a form equivilant for this function or is this whole code not adaptable to forms?

 

[WayneRyan]

Z,

The reason that you're not seeing anything is that "most" of your code
is not executing at all.

Forms have very different events than reports. The "retreat" and "format"
events aren't available on the form.

Making this work is a pretty formidable undertaking. The first step is
realizing that the "current" event code is useless.

just some thoughts,
Wayne

 

[zyxwvu44]

Thanks for your input, every bit helps. After searching for hours i think this may not be feasible for me because i don't know enough about sql.