Amy-
You have to build a dynamic filter string in code. Let's say your Listbox Row
Source is:
SELECT AutoNumber, Directorate, SAG, MDEP
FROM MyTable;
.. where "MyTable" is the name of your table.
Other Listbox properties:
Name: lstSelect
Bound Column: 1
Column Count: 4
Column Widths: 0";1";1";1" (adjust the lengths of the three display columns
appropriately)
Multi Select: Extended
In code behind a command button, do this:
Private Sub cmdSearch_Click()
Dim varItem As Variant, strSearch As String
' Make sure some items were selected
If Me.lstSelect.ItemData.Count = 0 Then
MsgBox "You must select at least one item in the list."
Exit Sub
End If
' Find all the selected items and add them to the search string
For Each varItem In Me.lstSelect ' lstSelect is the name of the Listbox
strSearch = strSearch & Me.lstSelect.ItemData(varItem) & ", "
Next varItem
' Strip off the last comma
strSearch = Left(strSearch, Len(strSearch) - 2)
' Open the target form filtered
DoCmd.OpenForm "name of continuous form here", _
WhereCondition:="AutoNumber IN (" & strSearch & ")"
' Close me
DoCmd.Close acForm, Me.Name
End Sub
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of amy_hollows
Sent: Monday, October 03, 2011 10:02 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Open Multiple Instances of a Form, each to a
specific record
John,
That is exactly what I want to do but when I went down that road I couldn't
figure out how to get it to work. (=newbie) My search criteria are 3 data
elements in a funding line:
1. Directorate
2. SAG
3. MDEP
The results show up in a continuous form. The double-click event of the
AutoNumber field opens up the "edit form".
I am now modifying my form to show the results in a list box. How would I
"build a filter from the rows selected"? This is where I got caught up
before...
thanks again sooo much for your help! :)
Amy
P.S. I noticed that you are located in Paris. I am in Stuttgart, Germany. We
are practically neighbors! haha.
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Amy-
>
> What are your search criteria on your "search" form? Consider creating an
> unbound form with a list box that has all the columns of interest. Make the
> list box multi-select so the user can pick multiple rows. Then build a filter
> from the rows selected and open them all up on a Continuous View form.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of amy_hollows
> Sent: Friday, September 30, 2011 1:59 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Open Multiple Instances of a Form, each to a
> specific record
>
> John,
>
> I am opening multiple instances because I want to see different records next
to
> each other in order to edit them simultaneously. I can search for a record in
> my search form, double click to open it in a separate form, then search for
> another record in my search form and double-click to open it...and so on.
Then
> I can edit them simultaneously.
>
> IS there an easier way to do this?
>
> amy
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Amy-
> >
> > Why are you opening multiple instances? It would seem to me (unless the
> > "search" form is also frmFY12Targets1Edit) that you simply should do a
> > DoCmd.OpenForm and apply the filter directly. Applying the filter after the
> > form opens gets the job done, but it's much less efficient because the form
> > opens on ALL records before you set the filter.
> >
> > DoCmd.OpenForm "frmFY12TargetsEdit", WhereCondition:="AutoNumber = " &
> > Me.AutoNumber
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of amy_hollows
> > Sent: Friday, September 30, 2011 11:32 AM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Re: Open Multiple Instances of a Form, each to a
> > specific record
> >
> > I totally just did some crazy googling and figured out how to make this work
> AND
> > I changed the caption in each instance of the form to make it slightly more
> > meaningful to the user.
> >
> > 1. code in my mdPublic module:
> >
> > Option Compare Database
> >
> > Public clnfrmFY12Targets1Edit As New Collection 'Instances of
> > frmFY12Targets1Edit.
> >
> > Public Sub OpenAfrmFY12Targets1Edit(objForm As Form)
> > 'Purpose: Open an independent instance of form frmFY12Targets1Edit to
a
> > specific record based on the AutoNumber field in the "search form".
> >
> > Dim frm As Form
> >
> > 'Open a new instance to a particular record, show it, and set a caption.
> > Set frm = New Form_frmFY12Targets1Edit
> > frm.Filter = "[AutoNumber]= " & objForm.[AutoNumber]
> > frm.FilterOn = True
> > frm.Visible = True
> > frm.Caption = "Record Number" & objForm.[AutoNumber]
> >
> >
> > 'Append it to our collection.
> > clnfrmFY12Targets1Edit.Add Item:=frm, Key:=CStr(frm.Hwnd)
> >
> > Set frm = Nothing
> > End Sub
> >
> > 2. code on the double-click event of the AutoNumber field (i know i know,
bad
> > field name) of my "search form":
> >
> > Private Sub AutoNumber_DblClick(Cancel As Integer)
> > Call OpenAfrmFY12Targets1Edit(Me)
> > End Sub
> >
> >
> > My question to you: do you foresee me having any issues in the future based
> on
> > how I built this. The forms are based on only one table. I just want to
make
> > updating the table values very easy for the user to do.
> >
> > thanks!
> >
> > amy
> >
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "amy_hollows" <amy_hollows@>
> > wrote:
> > >
> > > John,
> > >
> > > Thanks so much for the quick reply. This is my first time joining a MS
> Access
> > group and so far so good! :) Here is the code I used to open independent
> > instances of my form:
> > >
> > > Option Compare Database
> > >
> > > Public clnfrmFY12Targets1Edit As New Collection
> > > 'Instances of frmFY12Targets1Edit.
> > >
> > > Function OpenAfrmFY12Targets1Edit()
> > > 'Purpose: Open an independent instance of form frmFY12Targets1Edit.
> > >
> > > Dim frm As Form
> > >
> > > 'Open a new instance, show it, and set a caption.
> > > Set frm = New Form_frmFY12Targets1Edit
> > > frm.Visible = True
> > > frm.Caption = frm.Hwnd & ", opened " & Now()
> > >
> > >
> > > 'Append it to our collection.
> > > clnfrmFY12Targets1Edit.Add Item:=frm, Key:=CStr(frm.Hwnd)
> > >
> > > Set frm = Nothing
> > > End Function
> > >
> > > I would like to call this function on a double-click event of a record in
a
> > "search form" that I have created and pass it an argument (i think this is
the
> > correct terminology) to open to a specific record. The "search form" has an
> > AutoNumber field that uniquely identifies each record. The "search form"
> allows
> > users to search by up to 3 criteria and returns a list of records in a
> > continuous form. I want the user to be able to double-click on a record (or
a
> > button or whatever...I can just decide later where to set the dbl click
event)
> > to open a separate "edit form" to edit the record. The user will often need
> to
> > edit 2+ records at a time, thus the need for opening multiple instances of a
> > form (to a specified record).
> > >
> > > Thanks in advance for your help. :)
> > >
> > > amy
> > >
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
> wrote:
> > > >
> > > > Amy-
> > > >
> > > > Because you open multiple copies be setting an object reference to the
> > form's
> > > > class module, you can use that object to manipulate the form, including
> > setting
> > > > the Filter and FilterOn properties or doing a search on the Recordset to
> > move to
> > > > a specific record. Post the code you're using now with a more detailed
> > > > description of how you want to apply a search, and I can probably give
you
> > the
> > > > modifications you need.
> > > >
> > > > John Viescas, author
> > > > Microsoft Office Access 2010 Inside Out
> > > > Microsoft Office Access 2007 Inside Out
> > > > Building Microsoft Access Applications
> > > > Microsoft Office Access 2003 Inside Out
> > > > SQL Queries for Mere Mortals
> > > > http://www.viescas.com/
> > > > (Paris, France)
> > > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: MS_Access_Professionals@yahoogroups.com
> > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
amy_hollows
> > > > Sent: Thursday, September 29, 2011 4:50 PM
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > Subject: [MS_AccessPros] Open Multiple Instances of a Form, each to a
> > specific
> > > > record
> > > >
> > > > I have used code from Allen Browne to successfully open multiple
instances
> > of a
> > > > form in my database (pretty sweet). Now, I would like to be able to
open
> > the
> > > > instances of this form to a specific record, which is specified in the
> > search
> > > > results of another form that I created that searches a table based on 3
> > > > criteria. I can do these two things separately (open multiple
instances,
> > and
> > > > open a form to a specific record). Any ideas on how to combine these
two
> > ideas
> > > > into something work-able for me?
> > > >
> > > > (P.S. I am now at the "cut and paste and modify and debug" stage of VB
> > > > programming so bear with me if my questions sound totally vague. I'm
> super
> > > > excited about programming and making this db work though, so any help is
> > much
> > > > appreciated!)
> > > >
> > > > thanks!
> > > >
> > > > ~Amy
> > > >
> > > >
> > > >
> > > >
> > > > ------------------------------------
> > > >
> > > > Yahoo! Groups Links
> > > >
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar