Rabu, 15 Mei 2013

[MS_AccessPros] Re: Combo Box row source search criteria (one or all items)

 

GREAT Bill and anyone else who put thoughts to this!!!

Although I could not make this revision work.... your code got me thinking differently, and I found a solution! The user won't now the difference but using Bill's UNION code on the select and using an if statement to use two different reports depending on the user wanting individual department data or all departments. I think the end user will be happy with this solution.

Dim stDocName As String
If Me.Dept_COMBO = "*" Then
stDocName = "(R)NCM_SearchDept_ALL"
DoCmd.OpenReport stDocName, acPreview
Else
stDocName = "(R)NCM_SearchDept"
DoCmd.OpenReport stDocName, acPreview
End If

Thanks again, this group is always a tremendous help and I am very appreciative of the collaboration here.

Rohn

--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@...> wrote:
>
> Rohn
>
> Change your Click event to this:
>
> Private Sub Run_Report__R_NCM_SearchDates_Click()
> On Error GoTo Err_Run_Report__R_NCM_SearchDates_Click
>
> Dim stDocName As String
>
> stDocName = "(R)NCM_SearchDates"
> DoCmd.OpenReport stDocName, acPreview, , _
> "DEPARTMENT like '" & Me.Dept_COMBO & "*'"
>
> Exit_Run_Report__R_NCM_SearchDates_Cl:
> Exit Sub
>
> Err_Run_Report__R_NCM_SearchDates_Click:
> MsgBox Err.DESCRIPTION
> Resume Exit_Run_Report__R_NCM_SearchDates_Cl
>
> End Sub
>
>
> Set the combo's Properties:
> RowSource: SELECT dbo_DEPARTMENT.DEPRT_ID, dbo_DEPARTMENT.DEPRT_NAME FROM dbo_DEPARTMENT UNION SELECT "*", "<All>" FROM dbo_DEPARTMENT ORDER BY dbo_DEPARTMENT.DEPRT_NAME;
>
> Column count: 2
>
> Column widths: 0
>
> Bound Column: 1
>
> Let me know if it isn't working and I'll upload the db with the changes I made.
>
> Bill
>
> --- In MS_Access_Professionals@yahoogroups.com, "Rohn" <reverson@> wrote:
> >
> > Bill,
> >
> > I added QDS.ZIP to the AssistanceNeeded folder. I converted the tables to Local tables since the BE is SQL. Also I deleted many of the table records to get it from 63 megs to 2 megs. Last night I ended up added a third command button (All Depts.) that points to a different Query and report so that the end used can get all departments for a meeting today but I was hoping to accomplish it by only two command buttons (Dates Report & Dept. Report)
> >
> > I am working in the
> > (F)NCM_SearchDates Form
> > (Q)NCM_SearchDates Query
> > (R)NCM_SearchDates Report
> >
> > Thanks for taking a look at this.
> > Rohn
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@> wrote:
> > >
> > > Rohn
> > >
> > > Can you upload a zipped sample of your database to our AssistanceNeeded folder in the Files section? We need to see what your table structure is like.
> > >
> > > Bill
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "Rohn" <reverson@> wrote:
> > > >
> > > > Wondering if I need to build the Form, Query, Report process differently to accomplish this type of search/selection? I have been trying different variations without success so far!
> > > >
> > > > Any ideas?
> > > > Thanks in advance, Rohn
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@> wrote:
> > > > >
> > > > > Rohn
> > > > >
> > > > > You have to have the fields in the same order in both SELECT statements. I don't know which is your bound column in the combo but it should be the dept_id.
> > > > >
> > > > > In that case it should be:
> > > > > SELECT dbo_DEPARTMENT.DEPRT_ID, dbo_DEPARTMENT.DEPRT_NAME
> > > > > FROM dbo_DEPARTMENT
> > > > > UNION
> > > > > SELECT "*", "<All>" FROM dbo_DEPARTMENT ORDER BY
> > > > > dbo_DEPARTMENT.DEPRT_NAME;
> > > > >
> > > > > And you should only need on combo. The <All> will appear at the top of the dropdown list.
> > > > >
> > > > > How are you filtering the report? Do you have parameters in the report's query?
> > > > >
> > > > > Bill
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com, "Rohn" <reverson@> wrote:
> > > > > >
> > > > > > Thanks for the reply Bill,
> > > > > >
> > > > > > Will I need two different controls if I want to be able to select a department separately via the combo box and select "*" for all departments.
> > > > > >
> > > > > > My select now reads:
> > > > > > SELECT dbo_DEPARTMENT.DEPRT_NAME, dbo_DEPARTMENT.DEPRT_ID FROM dbo_DEPARTMENT UNION SELECT "*", "<All>" FROM dbo_DEPARTMENT ORDER BY dbo_DEPARTMENT.DEPRT_NAME;
> > > > > >
> > > > > > But with the UNION, I always get all departments.
> > > > > > Thanks, Rohn
> > > > > >
> > > > > > --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@> wrote:
> > > > > > >
> > > > > > > Rohn
> > > > > > >
> > > > > > > You can do this with a UNION query as the combo's RowSource
> > > > > > >
> > > > > > > SELECT Dept, Dept
> > > > > > > FROM myTable
> > > > > > > UNION
> > > > > > > SELECT "*", "<All>"
> > > > > > > FROM myTable
> > > > > > >
> > > > > > > Regards,
> > > > > > > Bill Mosca, Founder - MS_Access_Professionals
> > > > > > > http://www.thatlldoit.com
> > > > > > > Microsoft Office Access MVP
> > > > > > > https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
> > > > > > > My nothing-to-do-with-Access blog
> > > > > > > http://wrmosca.wordpress.com
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > --- In MS_Access_Professionals@yahoogroups.com, "Rohn" <reverson@> wrote:
> > > > > > > >
> > > > > > > > Hi all,
> > > > > > > > We cannot seem to figure out how to select the departments from a combo
> > > > > > > > box and allow selection of all departments in the combo box when
> > > > > > > > desired.
> > > > > > > > We have a search Form that provides a start and ending date for a date
> > > > > > > > report via a command button or you can select the dates and a
> > > > > > > > department combo box and run a report via a different command button.
> > > > > > > > They both work fine except for selecting all departments.
> > > > > > > > We need to add the ability to select ALL DEPARTMENTS in the combo box
> > > > > > > > for printing/viewing but cannot seem to figure out how.
> > > > > > > > Contents of the Combo Box:* (we need to add this feature, or something
> > > > > > > > like it)AssemblyFabricationPaintWeldEct.
> > > > > > > >
> > > > > > > > the Search form uses OnOpen to get the last work week dates:Private Sub
> > > > > > > > Form_Open(Cancel As Integer) Me![Date_From] = Date - Weekday(Date,
> > > > > > > > 2) - 6 Me![Date_To] = Me![Date_From] + 5End Sub
> > > > > > > > Any guidance would be appreciated.
> > > > > > > >
> > > > > > > >
> > > > > > > > [Non-text portions of this message have been removed]
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (11)
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar