ACCESS FEED: [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, "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@yahoogroups.com] On Behalf Of mrsgoudge
> Sent: Thursday, October 13, 2011 10:30 AM
> To: MS_Access_Professionals@yahoogroups.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> , 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> , "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@yahoogroups.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>
> > > 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> , "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@yahoogroups.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>
> > > > 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> , "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@yahoogroups.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>
> > > > > 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]
>