John,
I have been buried with work but I just wanted to let you know that I implemented this fix for sorting a combo box on a new record and ...
it worked PERFECT!!!
Thank you very much.
Bill
Minnesota
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Bill-
>
> It will look something like:
>
> Private Sub Form_Current()
>
> ' If on a new record
> If Me.NewRecord Then
> ' Don't show the obosolete service areas
> Me.cmbServiceArea.RowSource = "SELECT * FROM tblServiceAreas WHERE
> Obsolete = False;"
> Else
> ' Existing record - show them all
> Me.cmbServiceArea.RowSource = "SELECT * FROM tblServiceAreas;"
> End If
> End Sub
>
> ... and:
>
> Private Sub cmbServiceArea_BeforeUpdate(Cancel As Integer)
> ' Don't allow selecting an obsolete area
> If Me.cmbServiceArea.Column(2) <> 0 Then
> ' Cancel the edit
> Cancel = True
> ' Tell user
> MsgBox "You cannot set Service Area to an obsolete value."
> End If
> End Sub
>
> The above code assumes that the "obsolete" column is the THIRD column of the
> combo box Row Source.
>
> 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 Bill Singer
> Sent: Friday, August 24, 2012 5:48 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Re: Combo Box
>
> John,
>
> I am hoping to get to this fix this weekend. I have the obsolete field
> added. I am just not sure how to change the row source based on the If Then
> statement. You would not by chance have a generic sample I could model my
> language after?
>
>
>
> Thanks for your help. I will let you know how it goes.
>
>
>
> Bill Singer
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
> Sent: Saturday, August 18, 2012 12:59 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Re: Combo Box
>
>
>
>
>
> Bill-
>
> Sure you can with a bit of sleight of hand. In the Current event of the
> form,
> check Me.NewRecord. If True, set the Row Source to exclude the obsolete
> ones.
> If False, set the Row Source to include them all. And in the Before Update
> event of the combo box, disallow changing to an Area of Service that has
> been
> marked Obsolete.
>
> 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%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of agent1of6
> Sent: Saturday, August 18, 2012 1:43 AM
> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: [MS_AccessPros] Re: Combo Box
>
> Bill,
> I was considering that. I believe that will leave the old ID number in the
> field but I also belive that the combo box will not display the "Area of
> Service" for those old records that have been checked as obsolete.
>
> If I reviewed the old records via another form I believe I could see the
> correct
> information as long as I did not filter the combo box.
>
> I am betting I cannot have it both ways...
>
> Thanks,
> Bill
>
> --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas
> <JohnV@> wrote:
> >
> > Bill-
> >
> > Consider adding an "Obsolete" Yes/No flag to the Area of Service lookup
> table.
> > If there's a code they don't want to use anymore, have them mark it
> "Obsolete"
> > but not delete the row. In your Row Source for your combo box for a new
> record,
> > add WHERE Obsolete = False.
> >
> > 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%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of agent1of6
> > Sent: Friday, August 17, 2012 4:44 PM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: [MS_AccessPros] Combo Box
> >
> > I have a database that is used by a non profit agency to track the
> assistance
> > given to people in need.
> > On the "Needs request form" we track the area of need by selecting from a
> drop
> > down box. Areas of service include 1. Auto Repair 2. Moving 3.
> Shopping.....
> > etc.
> >
> > This non profit is going to be making som adjustmets in their areas of
> service.
> > They are going to be dropping #3 shopping assistance and adding a Mobile
> Cloths
> > Closet. I have no problem adding in the new areas of service but is there
> any
> > way to keep the old areas of service showing up on the drop down list with
> out
> > affecting the old service projects done.
> >
> > For example, if someone needed shopping in the past and we pulled up that
> > information we would still like to see the "shopping" catagory in the drop
> down
> > box but we do not want to see shopping for the new projects.
> >
> > I do not know if this is even possible so I thought I would ask.
> >
> > thanks for your help.
> > Bill
> > Minnesota
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
Tidak ada komentar:
Posting Komentar