Rabu, 12 Oktober 2011

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

 

Connie you're making it harder than it should be.

If you click on the gray bar in the left margin of the code window next to any line of code you will see a red circle and the code line will be bold. That is a break point.

Do the above on first line in your private sub. Then open your form and work with it so the sub is fired. I think John suggested putting it in the form's afterUpdate event.

When the sub runs it will stop on the breakpoint. From there, you press F8 so the code runs one line at a time. This is called "stepping through" the code.

Keep an eye on your longs that you use in your If blocks. As you come to each If line hover over the variables to see what they are, or you can add them to your Watch window so you don't have to hover. I seldom use the Watch window.

Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
https://mvp.support.microsoft.com/profile/Bill.Mosca

--- In MS_Access_Professionals@yahoogroups.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, "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@yahoogroups.com] On Behalf Of mrsgoudge
> > Sent: Wednesday, October 12, 2011 8:48 PM
> > To: MS_Access_Professionals@yahoogroups.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, "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
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > 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