Kamis, 13 Oktober 2011

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

 

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]

__._,_.___
Recent Activity:
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

Tidak ada komentar:

Posting Komentar