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, "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@yahoogroups.com] On Behalf Of mrsgoudge
> Sent: Wednesday, October 12, 2011 7:45 PM
> To: MS_Access_Professionals@yahoogroups.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, "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@yahoogroups.com] On Behalf Of mrsgoudge
> > Sent: Wednesday, October 12, 2011 6:05 PM
> > To: MS_Access_Professionals@yahoogroups.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
>
Rabu, 12 Oktober 2011
[MS_AccessPros] Re: Opening form based upon conditions. One section not working.
__._,_.___
MARKETPLACE
.
__,_._,___
Langganan:
Posting Komentar (Atom)
Tidak ada komentar:
Posting Komentar