ACCESS FEED: [MS_AccessPros] Re: Opening form based upon conditions. One section not working.

Ha-ha-ha! Shades of the dancing baby!

You're too funny, Connie.

Bill

--- In MS_Access_Professionals@yahoogroups.com, mrsgoudge <no_reply@...> wrote:
>
> It looked something like this ;-)
> http://s683.photobucket.com/albums/vv193/mrsgoudge/Weight%20Loss%20Board/Congratulations/?action=view&current=DancingStickPerson.gif
>
> --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@> wrote:
> >
> > Thanks for your kind words, Connie.
> >
> >
> >
> > Did you take a video of the Happy Dance. I'm sure the members would love to see
> > it. J
> >
> >
> >
> > Regards,
> >
> > Bill
> >
> >
> >
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> > Sent: Thursday, October 20, 2011 1:00 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Re: Opening form based upon conditions. One section not
> > working.
> >
> >
> >
> >
> >
> > Thanks Bill!
> >
> > Definitely with a "bit" more time. I spend a day doing what you guys can do in
> > minutes. I love the code part though!
> >
> > Thanks for the encouragement. My hubby gave me a high five with your post and I
> > did the happy dance with hime :-) And I've saved the email with your kind words
> > so that it'll continue to show in my box.
> >
> > Have a great day!
> > Connie
> >
> > --- In MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Mosca" <wrmosca@>
> > wrote:
> > >
> > > Connie
> > >
> > >
> > >
> > > I think you are going to become one of our gurus given a bit more time. We
> > > seldom see members who take code and actually dissect it and learn what it is
> > > doing. Those who do go from students to teachers.
> > >
> > >
> > >
> > > You go, Girl!
> > >
> > >
> > >
> > > Regards,
> > >
> > > Bill
> > >
> > >
> > >
> > > 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 mrsgoudge
> > > Sent: Thursday, October 13, 2011 10:30 AM
> > > To: MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > Subject: [MS_AccessPros] Re: Opening form based upon conditions. One section
> > not
> > > working.
> > >
> > >
> > >
> > >
> > >
> > > John and Bill,
> > >
> > > Mwah! Mwah! to each of you. I know my hubby would be okay with that since
> > you've
> > > made me so happy ;-) . I used both of your directions to make sure I
> > understood.
> > >
> > > The problem was that the code was only looking at the first Status Id for the
> > > Contact. I had to create a Loop to go through and look at each StatusID for
> > the
> > > Contact.
> > >
> > > Yay! You made my day! And I'm excited because I can hopefully figure out more
> > of
> > > my own mistakes and do it faster.
> > >
> > > THANK YOU!
> > > Connie
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com> , mrsgoudge <no_reply@>
> > > wrote:
> > > >
> > > > John,
> > > >
> > > > Do you mean to watch? If so, I have not--don't know how. I have tried to use
> > > the Immediate, Local and Watch windows to Debug, but can't figure out how.
> > I've
> > > looked at your book "Access 2007 Inside Out" and looked online but... Am
> > feeling
> > > kind of dumb. I can usually figure out written directions.
> > > >
> > > > -I changed the sub from private to public. (By changing "Private" to
> > "Public"
> > > right?)
> > > > -Opened the three windows
> > > > -Added a watch for lngLooking selecting "break when value changes"
> > > > -clicked in the Public Sub ContactID_AfterUpdate() and pressed the green fun
> > > arrow in the debug toolbar.
> > > > -A window pops up asking for the Macro name.
> > > >
> > > > What do I do?
> > > >
> > > > Also, in your book you mention pressing the Continue button on the
> > > toolbar--what does it look like?
> > > >
> > > > thank you!
> > > > Connie
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com> , "John Viescas" <john@>
> > > wrote:
> > > > >
> > > > > Connie-
> > > > >
> > > > > Have you set a halt on that statement to verify that, in fact, all three
> > are
> > > > > true?
> > > > >
> > > > > 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%40yahoogroups.com>
> > > > > [mailto:MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of mrsgoudge
> > > > > Sent: Wednesday, October 12, 2011 8:48 PM
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > Subject: [MS_AccessPros] Re: Opening form based upon conditions. One
> > section
> > > not
> > > > > working.
> > > > >
> > > > > John or anyone else,
> > > > >
> > > > > Any ideas why that last statement where all conditions are true is not
> > > opening
> > > > > the Contact form?
> > > > >
> > > > > Connie
> > > > >
> > > > >
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com> , "John Viescas" <john@>
> > > wrote:
> > > > > >
> > > > > > Connie-
> > > > > >
> > > > > > If a Contact can be both, then ignore my comments about BEntered and
> > > PBEntered
> > > > > > being mutually exclusive.
> > > > > >
> > > > > > 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%40yahoogroups.com>
> > > > > > [mailto:MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of mrsgoudge
> > > > > > Sent: Wednesday, October 12, 2011 7:45 PM
> > > > > > To: MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > Subject: [MS_AccessPros] Re: Opening form based upon conditions. One
> > > section
> > > > > not
> > > > > > working.
> > > > > >
> > > > > > John,
> > > > > >
> > > > > > I've added the Cancel Event to the first statement and moved the rest of
> > > the
> > > > > sub
> > > > > > to the AfterUpdate event. I've rarely used the Cancel event so this
> > helps
> > > to
> > > > > > put it in my head so I'll used it. thanks!
> > > > > >
> > > > > > Regarding my Logic:
> > > > > > I would add to "Looking" logic: BuyerAgencyID is null or has a value
> > other
> > > > > than
> > > > > > one.
> > > > > >
> > > > > > A contact can be both a buyer and a past buyer. The ContactStatus table
> > > makes
> > > > > > this possible. Perhaps my code is assuming a one to one relationship?
> > > > > >
> > > > > > Thanks!
> > > > > > Connie
> > > > > >
> > > > > >
> > > > > >
> > > > > > --- In MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com> , "John Viescas" <john@>
> > > wrote:
> > > > > > >
> > > > > > > Connie-
> > > > > > >
> > > > > > > First, you should also be setting Cancel = True if the user responds
> > No
> > > to
> > > > > the
> > > > > > > first question. Then I think the rest of the code should be in the
> > > > > > > Form_AfterUpdate event to ensure that the user has had the chance to
> > > fill in
> > > > > > all
> > > > > > > the fields that you're testing.
> > > > > > >
> > > > > > > Then here's how I see your logic:
> > > > > > >
> > > > > > > "Looking" is true if the ContactID is in LookingContacts and the
> > > > > BuyerAgencyID
> > > > > > > has a value and is something other than 1.
> > > > > > >
> > > > > > > "BEntered" is true the StatusID in Contact_Status for this contact is
> > 1.
> > > > > > >
> > > > > > > "PBEntered" is true if the StatusID in Contact_Status for this contact
> > > is 6.
> > > > > > >
> > > > > > > You open the Contacts form if:
> > > > > > >
> > > > > > > Looking is False and BEntered is True.
> > > > > > >
> > > > > > > Looking is True and both BEntered and PBEntered are False.
> > > > > > >
> > > > > > > Looking is True, and both BEntered and PBEntered are True.
> > > > > > >
> > > > > > > I assume that it cannot be the case that a unique ContactID is BOTH a
> > > buyer
> > > > > > and
> > > > > > > a previous buyer, so your last case will never be true. I think you
> > mean
> > > to
> > > > > > > check for Looking and BEntered or Looking and PBEntered. Testing both
> > > > > > BEntered
> > > > > > > and PBEntered should be a waste of time if a contact cannot have both
> > of
> > > the
> > > > > > > status codes.
> > > > > > >
> > > > > > > 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%40yahoogroups.com>
> > > > > > > [mailto:MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of mrsgoudge
> > > > > > > Sent: Wednesday, October 12, 2011 6:05 PM
> > > > > > > To: MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > Subject: [MS_AccessPros] Opening form based upon conditions. One
> > section
> > > not
> > > > > > > working.
> > > > > > >
> > > > > > > Hi all,
> > > > > > >
> > > > > > > I hit my head against a wall all day yesterday with this one. All of
> > the
> > > > > > > following code is (or at least seems to be) working except the last If
> > > > > > > statement. This statement's goal is that when entering a buyer into a
> > > > > subform
> > > > > > > (RecordSource = SalesBuyers) on the Sales form the Contact form will
> > > open if
> > > > > > all
> > > > > > > of the following are true:
> > > > > > > 1. They ever looked with AER (LookingContacts table)
> > > > > > > 2. Ended up buying with another agency (Current record of open Sales
> > > form
> > > > > has
> > > > > > > BuyerAgencyID <> 1 or is null)
> > > > > > > 3. Entered as a buyer in the Contact_Status table (StatusID = 1)
> > > > > > > 4. Entered as a past buyer in the Contact_Status table (StatusID = 6)
> > > > > > >
> > > > > > >
> > > > > > > The Contact form is not opening.
> > > > > > >
> > > > > > > I have checked and doublechecked and then recreated another buyer with
> > > the
> > > > > > > required conditions just to make sure:
> > > > > > > Contact I'm testing has "buyer" (StatusID=1) and "past buyer"
> > > (StatusID=6)
> > > > > > > entered as status in the table ContactStatus. The contact is also
> > > entered in
> > > > > > the
> > > > > > > LookingContacts table hooking him to a LookingID. It makes no
> > difference
> > > > > > whether
> > > > > > > I make the AgencyID null or another agency. It does work if the
> > AgencyID
> > > is
> > > > > > > AER--the case where it shouldn't open anyways.
> > > > > > >
> > > > > > > The code is under: 'If looked with AER and bought with another realty
> > > and
> > > > > > > "Buyer" and "Past Buyer" were entered as statuses, open contact form
> > > with
> > > > > > > directions
> > > > > > >
> > > > > > > Help!
> > > > > > > Connie
> > > > > > >
> > > > > > > Private Sub ContactID_BeforeUpdate(Cancel As Integer)
> > > > > > > 'set up Error Handler
> > > > > > > On Error GoTo Proc_Err
> > > > > > > '~~~~~~~~~~~~~~~~~~~~~~
> > > > > > > Dim lngLooking As Long
> > > > > > > Dim lngBEntered As Long
> > > > > > > Dim lngLookingID As Long
> > > > > > > Dim lngPBEntered As Long
> > > > > > > Dim lngL As Long
> > > > > > >
> > > > > > > 'Prevent data from being changed accidentally because user thinks they
> > > are
> > > > > > > searching
> > > > > > > 'Message if data is being changed on an old record
> > > > > > > If Me.NewRecord = False Then
> > > > > > > Dim msg, Style, Title, Response
> > > > > > > msg = "Did you really intend to change the contact of this listing?"
> > > > > > > Style = vbYesNo + vbDefaultButton2
> > > > > > > Title = "Changing Address?"
> > > > > > > Response = MsgBox(msg, Style, Title)
> > > > > > > If Response = vbNo Then
> > > > > > > Me.Undo
> > > > > > > MsgBox "Use Ctrl F to find."
> > > > > > > Exit Sub
> > > > > > > End If
> > > > > > > If Response = vbYes Then
> > > > > > > End If
> > > > > > >
> > > > > > > End If
> > > > > > > 'Open Contact form if 'buyer' or 'past buyer' might need to be deleted
> > > from
> > > > > > > status and have directions on the top of Contact form
> > > > > > >
> > > > > > > 'Create variables
> > > > > > > 'Set value of lngBEntered = -1 on if status of "buyer" was entered
> > > > > in
> > > > > > > Contact form
> > > > > > > If (DLookup("StatusID", "Contact_Status", "ContactID = " &
> > > > > > Me.ContactID)
> > > > > > > = 1) Then
> > > > > > > lngBEntered = -1
> > > > > > > Else: lngBEntered = 0
> > > > > > >
> > > > > > > End If
> > > > > > >
> > > > > > > 'Set value of lngPBEntered if status of "past buyer" was entered in
> > > > > > > Contact form (past buyer entered)
> > > > > > > If (DLookup("StatusID", "Contact_Status", "ContactID = " &
> > > > > > Me.ContactID)
> > > > > > > = 6) Then
> > > > > > > lngPBEntered = -1
> > > > > > > Else: lngPBEntered = 0
> > > > > > > End If
> > > > > > >
> > > > > > > 'Set value of lngLooking = -1 if looked with AER and bought with
> > > > > > another
> > > > > > > realty (past buyer derived)
> > > > > > > lngL = Nz(DLookup("LookingID", "LookingContacts", "ContactID = "
> > > > > &
> > > > > > > Nz([ContactID], 0)), 0)
> > > > > > > If (lngL <> 0) Then
> > > > > > > If ((IsNull(Me.Parent.Form.BuyerAgencyID)) Or
> > > > > > > (Me.Parent.Form.BuyerAgencyID <> 1)) Then
> > > > > > > lngLooking = -1
> > > > > > > Else: lngLooking = 0
> > > > > > > End If
> > > > > > > End If
> > > > > > >
> > > > > > > 'Open appropriate forms with messages
> > > > > > > 'If not entered in a LookingEvent w AER and "buyer" was typed in as
> > > > > > > status open contact form with directions
> > > > > > > If ((lngLooking = 0) And (lngBEntered = -1)) Then
> > > > > > > DoCmd.OpenForm "Contacts"
> > > > > > > Forms!Contacts.Recordset.FindFirst "ContactID = " &
> > > > > > > Forms![Sales]![sub_SoldBuyersSales].Form![ContactID]
> > > > > > > Forms!Contacts.WhyOpenedMsg.Caption = "Remove status of Buyer
> > > > > > unless
> > > > > > > he/she is continuing to look." _
> > > > > > > & vbNewLine & "Directions under email section"
> > > > > > > Forms!Contacts.WhyOpenedMsg.Visible = True
> > > > > > > End If
> > > > > > >
> > > > > > > 'If entered in a LookingEvent w AER and bought with another realty
> > > > > AND
> > > > > > > Did not have "buyer" typed in as a status _
> > > > > > > 'AND they are not entered as a past buyer, open contact form
> > > > > > > 'with directions ask re past buyer.
> > > > > > > If ((lngLooking = -1) And (lngBEntered = 0) And (lngPBEntered = 0))
> > > > > > Then
> > > > > > > DoCmd.OpenForm "Contacts"
> > > > > > > Forms!Contacts.Recordset.FindFirst "ContactID = " &
> > > > > > > Forms![Sales]![sub_SoldBuyersSales].Form![ContactID]
> > > > > > > Forms!Contacts.WhyOpenedMsg.Caption = "This buyer has looked
> > > > > > > previously with AER." _
> > > > > > > & vbNewLine & "Enter status of 'Past buyer' if appropriate"
> > > > > > > Forms!Contacts.WhyOpenedMsg.Visible = True
> > > > > > > End If
> > > > > > >
> > > > > > > 'If looked with AER and sold with another realty AND "buyer" was
> > > > > typed
> > > > > > > in as a status open contact form with directions _
> > > > > > > 'AND "Past buyer" is not already entered.
> > > > > > > If ((lngLooking = -1) And (lngBEntered = -1) And (lngPBEntered = 0))
> > > > > > > Then
> > > > > > > DoCmd.OpenForm "Contacts"
> > > > > > > Forms!Contacts.Recordset.FindFirst "ContactID = " &
> > > > > > > Forms![Sales]![sub_SoldBuyersSales].Form![ContactID]
> > > > > > > Forms!Contacts.WhyOpenedMsg.Caption = "You'll probably need to
> > > > > do
> > > > > > > two things:" _
> > > > > > > & vbNewLine & "1. Remove status 'Buyer' unless he/she is
> > > > > > continuing
> > > > > > > to look for property" _
> > > > > > > & vbNewLine & "2. Enter status of 'Past buyer' if needed since
> > > > > > this
> > > > > > > buyer has looked previously with AER. "
> > > > > > > Forms!Contacts.WhyOpenedMsg.Visible = True
> > > > > > >
> > > > > > > End If
> > > > > > >
> > > > > > > 'If looked with AER and bought with another realty and "Buyer" and
> > > > > "Past
> > > > > > > Buyer" were entered as statuses, open contact form with directions
> > > > > > > If (lngLooking = -1) And (lngBEntered = -1) And (lngPBEntered = -1)
> > > > > > Then
> > > > > > > DoCmd.OpenForm "Contacts"
> > > > > > > Forms!Contacts.Recordset.FindFirst "ContactID = " &
> > > > > > > Forms![Sales]![sub_SoldBuyersSales].Form![ContactID]
> > > > > > > Forms!Contacts.WhyOpenedMsg.Caption = "Remove status 'Buyer'
> > > > > > unless
> > > > > > > he/she is continuing to look for property"
> > > > > > > Forms!Contacts.WhyOpenedMsg.Visible = True
> > > > > > > End If
> > > > > > >
> > > > > > >
> > > > > > > Proc_Exit:
> > > > > > > Exit Sub ' or Exit Function
> > > > > > >
> > > > > > > Proc_Err:
> > > > > > > MsgBox Err.Description, , _
> > > > > > > "ERROR " & Err.Number _
> > > > > > > & " ContactID_BeforeUpdate"
> > > > > > >
> > > > > > > Resume Proc_Exit
> > > > > > >
> > > > > > > 'if you want to single-step code to find error, CTRL-Break at MsgBox
> > > > > > > 'then set this to be the next statement by right-clicking on Resume
> > > > > > > Statement, then press F8 to execute one line at a time
> > > > > > > Resume
> > > > > > > End Sub
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > ------------------------------------
> > > > > > >
> > > > > > > Yahoo! Groups Links
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > ------------------------------------
> > > > > >
> > > > > > Yahoo! Groups Links
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > ------------------------------------
> > > > >
> > > > > Yahoo! Groups Links
> > > > >
> > > >
> > >
> > >
> > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>