Results 1 to 11 of 11

Controlsql for date range in VBA controlled form

  1. #1
    TanyaCVV is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    6

    Controlsql for date range in VBA controlled form

    I have a form (screenshot uploaded) which allows criteria to pull reports to show based on criteria from the following VBA code:


    Option Compare Database

    Private Sub cboReport_AfterUpdate()


    If IsNull(DLookup("ControlID", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit1""")) = False Then

    Me.cboCrit1.Visible = True

    Me.cboCrit1.RowSource = DLookup("ControlSql", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit1""")

    Me.lblCrit1.Caption = DLookup("ControlCaption", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit1""")

    Me.cboCrit1.Tag = DLookup("FieldName", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit1""")

    Else

    Me.cboCrit1.Visible = False

    End If

    If IsNull(DLookup("ControlID", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit2""")) = False Then

    Me.cboCrit2.Visible = True

    Me.cboCrit2.RowSource = DLookup("ControlSql", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit2""")

    Me.lblCrit2.Caption = DLookup("ControlCaption", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit2""")

    Me.cboCrit2.Tag = DLookup("FieldName", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit2""")

    Else

    Me.cboCrit2.Visible = False

    End If

    If IsNull(DLookup("ControlID", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit3""")) = False Then

    Me.cboCrit3.Visible = True

    Me.cboCrit3.RowSource = DLookup("ControlSql", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit3""")

    Me.lblCrit3.Caption = DLookup("ControlCaption", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit3""")

    Me.cboCrit3.Tag = DLookup("FieldName", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit3""")

    Else

    Me.cboCrit3.Visible = False

    End If


    Me.lblDetails.Caption = Me.cboReport.Column(2)

    Me.cboPrintMethod = 1


    End Sub

    Private Sub cmdClearCrit_Click()


    Me.cboCrit1.Value = ""

    Me.cboCrit2.Value = ""

    Me.cboCrit3.Value = ""


    End Sub

    Private Sub Ctl_cmdPrint_Click()


    Dim strCrit As String

    If Me.cboCrit1 <> "" Then

    strCrit = Me.cboCrit1.Tag & "=""" & Me.cboCrit1.Value & """"

    End If

    If Me.cboCrit2 <> "" Then

    If strCrit = "" Then

    strCrit = Me.cboCrit2.Tag & "=#" & Me.cboCrit2 & "#"

    Else

    strCrit = strCrit & " AND " & Me.cboCrit2.Tag & "=#" & Me.cboCrit2.Value & "#"

    End If

    End If

    If Me.cboCrit3 <> "" Then

    If strCrit = "" Then

    strCrit = Me.cboCrit3.Tag & "=#" & Me.cboCrit3 & "#"

    Else

    strCrit = strCrit & " AND " & Me.cboCrit3.Tag & "=#" & Me.cboCrit3.Value & "#"

    End If

    End If


    If Me.cboPrintMethod = 1 Then


    DoCmd.OpenReport Me.cboReport.Column(3), acViewPreview, , strCrit


    Else


    DoCmd.OpenReport Me.cboReport.Column(3), acViewNormal, , strCrit


    End If


    DoCmd.Close acForm, "frmDlgRpts"




    End Sub

    This code pulls Controlsql information to execute the criteria from the tables which are attached as screenshots. What I would like to do is pull a report by a date range entered into the criteria. The query that runs the report only has one date column TxnDate. I have attemped to use "build" to apply the setting to pull the criteria from the form but this does not work because the form must pull from the controlsql. Is there a formula I can enter into the control sql for this or what would the code be to add to the vba to omit the controlsql criteria for report 04?

    Thank you for your help
    Attached Thumbnails Attached Thumbnails form.JPG   appsysreports.JPG   appsyscontrolreports.JPG  

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,476
    For future, please post lengthy code within CODE tags to retain indentation and readability.

    Where would those data range parameters be input?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    TanyaCVV is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    6
    Quote Originally Posted by June7 View Post
    For future, please post lengthy code within CODE tags to retain indentation and readability.

    Where would those data range parameters be input?
    The data that the criteria fields in my form pull from is the Controlsql section of the table named Appsysreportcontrols

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,476
    Sorry, I meant date (not data) range parameters.

    Where would user enter the date range criteria?

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    TanyaCVV is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    6
    I am unsure why but I have attempted multiple times and I am unable to upload the database.

    The date range would be entered into crit2 and crit3 as start date and end date. How these fields execute is based upon what is entered in the controlsql

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,476
    Did you try zipping file with Windows Compression? 2mb zip allowed.

    What exactly is report RecordSource?

    Why can't you just build the range criteria?

    "TxnDate BETWEEN #" Me.crit2 & "# AND #" & Me.crit3 & "#"

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    TanyaCVV is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    6
    Quote Originally Posted by June7 View Post
    Did you try zipping file with Windows Compression? 2mb zip allowed.

    I am not sure what you mean by "must pull from the controlsql" - what exactly is report RecordSource?

    As per the VBA Code the criteria field on the form pulls using the following:
    Me.lblCrit3.Caption = DLookup("ControlCaption", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit3""")

    This pulls from the table shown in my screenshot named "AppSysReportControls" in this table there is a column called "Controlsql" this is where the formula is found that executes to fulfill the information requirement for the criteria field on the form dependent upon which report is selected. There is an example in my table for report number one for Customer and Date.

    What I need is to create a way for report number 4 to utilize all 3 available criteria the first for Sales Rep (thats easy I can do that) the second for Start Date and the third for End Date, this is where I cannot get a working formula to get the proper information. I cannot get the TxnDate to pull from between a start and end date based on the form cruiteria using Build to enter a between statement because the form is controlled by the table utilizing the information entered in the Controlsql. I cannot utilize a straight formula in the query to create a start and end date or it overrides the use of the form and creates a parameter instead, which works but I would prefer to utilize the criteria fields in the form rather than override with a parameter.

    I will try to upload again tomorrow not at that location currently

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,476
    You posted Caption code, not combobox RowSource.

    The RowSource is set by SQL statement pulled from table?

    You want date range criteria in combobox RowSource so as to restrict list choices?

    Perhaps have placeholder text in SQL string then use Replace() function. So if SQL is like: SELECT * FROM table <> GROUP BY something ORDER BY this;

    VBA code:

    Replace(strSQL, "<>", strCriteria)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    TanyaCVV is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    6
    Sorry here is the row source code


    Me.cboCrit3.RowSource = DLookup("ControlSql", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit3""")

  10. #10
    TanyaCVV is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    6
    Where are you thinking to build this is, in the AppSysControl table in the controlsql? Or are you thinking in the code for the form as an if this pertains to report 4 do this? Right now the form code is only really to tell the form which fields to pull the info from and the table gives it the how to pull the information from the query connected to the report.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,476
    Yes, I figured that out from original post. Doesn't tell me if I am now on right track with suggested approach.

    The SQL statement with the placeholder would be in the table. Then code runs the Replace(). Regardless of report, <> placeholder has to be replaced. Can be replaced with empty string if the condition requires that.

    Really need a very clear statement of exactly what you want to accomplish.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 17
    Last Post: 07-31-2018, 01:23 PM
  2. Hiding fields in tab controlled form
    By chohan78 in forum Forms
    Replies: 6
    Last Post: 06-03-2017, 03:44 PM
  3. Replies: 3
    Last Post: 01-26-2016, 01:56 PM
  4. Form Date Range formatting
    By datachick in forum Forms
    Replies: 1
    Last Post: 11-05-2014, 12:04 PM
  5. Date Range on Form
    By normie in forum Access
    Replies: 16
    Last Post: 03-13-2012, 08:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums