Modal Form Operator

[mm07nkb]

Hi,

I have seen the Sales Reports Dialog Form on Northwind 2007 database and I want to replicate this for my reports on my Recruitment Database.

In the modal I would like a list of all my reports where the 'select sales report' is.

Underneath that I would like the same filter but for my 'business unit' in place of the "Sales items" list.

Instead of the 'select sales period' section I want two unbound Text boxes. One for start date and one for end date so people can select the time period for the reports.

I then want a button underneath to 'run report', this button needs to run the report highlighted in the first box, with the business unit highlighted in the filter, between the dates chosen in the unbound boxes.

Thanks for the help!!

 

[mm07nkb]

Hi,

I have seen the Sales Reports Dialog Form on Northwind 2007 database and I want to replicate this for my reports on my Recruitment Database.

In the modal I would like a list of all my reports where the 'select sales report' is.

Underneath that I would like the same filter but for my 'business unit' in place of the "Sales items" list.

Instead of the 'select sales period' section I want two unbound Text boxes. One for start date and one for end date so people can select the time period for the reports.

I then want a button underneath to 'run report', this button needs to run the report highlighted in the first box, with the business unit highlighted in the filter, between the dates chosen in the unbound boxes.

Thanks for the help!!

 

[CBrighton]

Is this a request for support (if so, what do you need explaining / answering) or are you asking for someone to make you a database?

 

[mm07nkb]

Yes its a question for support. Ive seen it on Northwind and I don't know how to do it on my database. I just need someone to explain how to do it.

Thanks

 

[mm07nkb]

Having looked into this further, I have set up a modal form with a list box which detail my report titles, these are unbound and i need to find a way to link these to the reports themselves.

I also have unbound text boxes titled 'txtstartdate' and 'txtenddate' where required the start and end date for the query is sourced from here. Where not I will use conditional formatting to grey scale these boxes.

I also have a button to run the reports but I want it to run only the highlighted report in the list.

Hope anyone can help im new to access sorry for previous long post!!

 

[mm07nkb]

Having looked into this further, I have set up a modal form with a list box which detail my report titles, these are unbound and i need to find a way to link these to the reports themselves.

I also have unbound text boxes titled 'txtstartdate' and 'txtenddate' where required the start and end date for the query is sourced from here. Where not I will use conditional formatting to grey scale these boxes.

I also have a button to run the reports but I want it to run only the highlighted report in the list.

Hope anyone can help im new to access sorry for previous long post!!

 

[CBrighton]

1. List box

If I were you I would create a table to use as the row source of the listbox. I'd have a field for the name as displayed to the user and a field for the report's name within the database.

The listbox can then be set to 2 columns, and the recordsource set to the new table. Set the bound column to the one containing the report's name within the database.

This will let you use ListBoxName.Value to get the name (i.e. rptReportName, assuming you use that naming convention) which can be sued for things like DoCmd.OpenReport to specify which report to open.

2. Unbound date textboxes

What would the results be displayed on? Similar to the above, OpenReport has a filter property which can be set via VBA.

Here's an example piece of code from one of my search forms (in this case I have removed the other criteria and just look at a date field):

Dim strSQLFilter as string
If Not IsNull(txtStart1) And Not txtStart1 = "" Then
    If cbbDateStartControl = "Between" Then
        If IsNull(txtStart2) And Not txtStart2 = "" Then
            MsgBox "Check start date selections."
        End If
        strSQLFilter = strSQLFilter & "StartDate Between #" & Format(txtStart1, "mm/dd/yyyy") & "# AND #" & Format(txtStart2, "mm/dd/yyyy") & "# AND "
    ElseIf cbbDateStartControl = "Before" Then
        strSQLFilter = strSQLFilter & "StartDate < #" & Format(txtStart1, "mm/dd/yyyy") & "# AND "
    ElseIf cbbDateStartControl = "After" Then
        strSQLFilter = strSQLFilter & "StartDate > #" & Format(txtStart1, "mm/dd/yyyy") & "# AND "
    End If
End If
 
If Len(strSQLFilter) > 6 Then
    strSQLFilter = Left(strSQLFilter, Len(strSQLFilter) - 5)
End If
 
DoCmd.OpenReport cbbReportType2.Column(1), acPreview, , strSQLFilter

This code uses 3 controls, a combobox allowing the user to choose between, before or after (txtDateStartControl), and a textbox for start and end dates (txtStart1 & txtStart2).

After creating a string containing the WHERE clause it trims off the trailing " AND " then it opens the report specified in my combobox (the same way I detailed above for point 1) and applies the string as the filter.

3. Command button opening reports

This was covered in point 1 and in the code in point 2.