Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms / May 2007

Tip: Looking for answers? Try searching our database.

Creating User Customize Form

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Please Help - 23 May 2007 15:19 GMT
Good morning,

I would like to create a form for a report where users can select the fields
on the form that they want on the report.  

For example, on the left side of the form, we have a list of fields, and the
users can move the fields that they want from the left side of the form to
the right side.  Then click on a command button to preview the report.  

I went through my Access 2003 book, and I do not see a way doing that.  

Can someone help me or point me to the sources on doing?

Thanks.
Sprinks - 23 May 2007 17:42 GMT
While you can create an entire report on-the-fly using VBA, it might be
easier to place hidden controls for all the possible fields in your report,
and pass the names of the selected fields in the optional OpenArgs parameter
of the OpenReport method.  You can set the Visible and Left properties in the
report's On Open event.

' The code assumes that the textbox corresponding to each checkbox has the
same
' name except a different prefix, e.g., chkName/txtName.  While it has basic
' error-handling, you may wish to augment it (for example, as it's currently
' written, it will cause an "Invalid Use of Null" error if the report is
opened in any
' context than by the control button and an "Invalid Procedure Call or
argument"
' error if no fields are selected).

' Command button On Click event code
On Error GoTo Err_Handler
   
   Dim strOA As String             ' OpenArgs string
   Dim stDocName As String     ' Report name
   Dim ctl As Control                ' Control variable
   
   strOA = ""
   
   ' Loop through all controls
   ' If control name matches array element, add to OpenArgs string
   For Each ctl In Me.Controls
       If ctl.ControlType = acCheckBox Then
           If ctl.Value = True Then
               strOA = strOA & "txt" & Mid(ctl.Name, 4) & ","
           End If
       End If
   Next ctl
   
   ' Strip off last comma
   strOA = Left(strOA, Len(strOA) - 1)
 
   ' Change to the name of your report
   stDocName = "Report1"
   
   ' Open report in preview mode with optional OpenArgs parameter
   DoCmd.OpenReport stDocName, acPreview, , , , strOA

Err_Exit:
   Exit Sub

Err_Handler:
   MsgBox Err.Description
   Resume Err_Exit

' Report On Open event code
   On Error GoTo Err_Handler

   Dim astrControl() As String     ' Control name array
   Dim i As Integer                     ' Loop counter
   Dim ctl As Control                  ' Control variable
   Dim sglCurPos As Single         ' Position to place next visible control
   
   ' Horizonal offset, in twips, from one control to the next
   ' 1440 twips = 1 inch, set here to .05"
   Const cintOffset As Integer = 72

   sglCurPos = 0
   
   ' Parse OpenArgs to string array
   astrControl() = Split(Me.OpenArgs, ",")
   
   For i = 0 To UBound(astrControl)
       For Each ctl In Me.Controls
           If ctl.Name = astrControl(i) Then
               ctl.Visible = True
               
               ' Set left edge of control to sglCurPos
               ctl.Left = sglCurPos
               
               ' Increment sglCurPos
               sglCurPos = sglCurPos + ctl.Width + cintOffset

               Exit For
           End If
       Next ctl
   Next i

Err_Exit:
   Exit Sub

Err_Handler:
   MsgBox Err.Description
   Resume Err_Exit

End Sub

Hope that helps.
Sprinks
Sprinks - 23 May 2007 20:01 GMT
Note that you must also ensure that the total width of the selected fields
and corresponding offsets does not exceed the page width.

Sprinks
missinglinq - 23 May 2007 22:11 GMT
You might want to simply invoke the Report Wizard behind a button, for
instance:

Application.Run "acwzmain.frui_Entry", "ReportSourceGoesHere", acReport

Run as given above, the Wizard will open up and give the user the option of
picking the source for the report.  You could also set up a hack to enter the
name of the table/query to use for the report, maybe a dropdown box to do
this, hen substitue the actual source name for "ReportSourceGoesHere". This
would have the Wizard open with that table/query already selected.

Signature

There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Please Help - 24 May 2007 14:18 GMT
Good morning guys,

Thanks for your helps!

> You might want to simply invoke the Report Wizard behind a button, for
> instance:
[quoted text clipped - 6 lines]
> this, hen substitue the actual source name for "ReportSourceGoesHere". This
> would have the Wizard open with that table/query already selected.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.