Rabu, 12 Oktober 2011

[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
>

__._,_.___
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