ACCESS FEED: RE: [MS_AccessPros] When press btnSold the current Listing is assocciated with an old sale..

Connie-

When you try to do stuff through the UI, funky things can happen. If the user
types on the keyboard while your code is running, all heck can break loose. Did
you upgrade to a faster machine perhaps? It's all a matter of timing. You
could have helped ensure the "go to new record" went to the right place by doing
a SetFocus on the form after opening it. Or, you could have opened the form in
Data Entry mode. But doing the insert directly in code is the best way.

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: Tuesday, October 25, 2011 7:22 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] When press btnSold the current Listing is
assocciated with an old sale..

Thanks a million John! I have a question. I single-stepped through the code and
your guess was right on the mark. The Sales form was not going to a new record.

Question: Why was it working these many months and then it didn't this week?
Looks like I should go through similar instances and change them to inserting a
new record. I think I have quite a few :-(

Thanks again for ALL you do!
Connie

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Connie-
>
> The only bit of code that I see could be a problem is this:
>
> If rstI.EOF Then
> 'Open Sales form to enter info
> DoCmd.OpenForm "Sales"
> DoCmd.GoToRecord , , acNewRec
> Forms!Sales.ListID.Value = Me.ListID
> 'Set CurrentListing in Listing form to No
> Me.CurrentListing.Value = 0
> Me.Status.Value = "SOLD"
> Forms!Sales.Dirty = False
> rstI.Close
> Set rstI = Nothing
> Set db = Nothing
> Exit Sub
> End If
>
> The OpenForm *starts* the process of opening the Sales form, but it may not be
> fully open and have the focus when you execute the acNewRec, so you *could* be
> going to a new record in the Listings form. Why not simply insert a new
record
> in the Sales table? Trying to "create" or "change" a record using the User
> Interface is always dicey.
>
> But that should do something other than what you describe. Have you tried
> putting a halt near the top of your code and then single-step through it to
see
> what's really happening?
>
> 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: Tuesday, October 25, 2011 12:16 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] When press btnSold the current Listing is assocciated
> with an old sale..
>
> This is scary. It has been working but is no longer.
>
> A new listing is entered on Listing form into Listing table. When it sells a
> button on Listings form is pressed and among other things
> 1. the "CurrentListing" on Listing form is changed to 0
> 2. If the ListID is on the Pending table the data is transferred to the Sales
> table and the Sales form opens in order to enter the rest of the info
> 3. If the ListID is not on the Pending table the Sales form opens and the
> ListID is entered thus starting a new Sales record.
>
> Problem: I have a newer Listing with ListID 1366. I have checked and
rechecked
> the tables and it is a currentlisting, Not in the Pending table, Not on the
> Sales table or the SalesBuyers table.
>
> There is also a very old listing with ListID 5, SalesID 47.
>
> When I click the Sales button, its CurrentListing status changes to 0, and on
> the Sales table the old Listing has its ListID changed from 5 to 1366. Thus
> associating the new sale with old data and losing the connection for the old
> Sale.
>
> I have changed the data in the tables back to the presale condition:
> CurrentListing for 1366 to -1
> Sales table to associate ListID 5 with SalesID 47
>
> This keeps happening.
>
> Code for the button is below.
>
> Heellllllppp!
> Connie
>
> Private Sub btnSell_Click()
>
> 'set up Error Handler
> On Error GoTo Proc_Err
> '~~~~~~~~~~~~~~~~~~~~~~
>
> Dim db As DAO.Database, rstI As DAO.Recordset, rstO As DAO.Recordset, rstB As
> DAO.Recordset, rstS As DAO.Recordset
> Dim lngSalesID As Long
>
> 'If new record
> If Me.NewRecord Then
> 'Message box giving directions
> Msgbox "To mark a listing as Sold, go to that record THEN press Sold button."
> Exit Sub
> End If
>
> 'Must be a current listing to sell
> If CurrentListing = 0 Then
> Msgbox "This is not a current listing. Only current listings can be sold"
> Exit Sub
> End If
>
> 'Save changes to current record
> If Me.Dirty Then
> RunCommand acCmdSaveRecord
> End If
>
> 'If Sales is open give message that it needs to be closed
> If IsLoaded("Sales") Then
> Msgbox "Please close the Sales form first"
> Exit Sub
> End If
>
>
> 'Message box asking if really want it sold
> Dim msg, Style, Title, Response As String
> msg = "Are you sure you want to move this listing to SOLD status?" & _
> vbCrLf & "The minute you click yes, it is moved to sold status "
> Style = vbYesNo + vbDefaultButton2
> Title = "Move Listing to Sold Status?"
> Response = Msgbox(msg, Style, Title)
> If Response = vbNo Then
> Exit Sub
> End If
>
> ' Point to this database
> Set db = CurrentDb
> ' Open the related pending record
> Set rstI = db.OpenRecordset("SELECT * FROM Pending " & _
> "WHERE ListID = " & Me.ListID)
> ' If no related Pending table start new Sales record using Sales form
> If rstI.EOF Then
> 'Open Sales form to enter info
> DoCmd.OpenForm "Sales"
> DoCmd.GoToRecord , , acNewRec
> Forms!Sales.ListID.Value = Me.ListID
> 'Set CurrentListing in Listing form to No
> Me.CurrentListing.Value = 0
> Me.Status.Value = "SOLD"
> Forms!Sales.Dirty = False
> rstI.Close
> Set rstI = Nothing
> Set db = Nothing
> Exit Sub
> End If
> ' Since a pending record, Open the Sales table to insert
> Set rstO = db.OpenRecordset("Sales", dbOpenDynaset, dbAppendOnly)
> ' Start a new record
> rstO.AddNew
> ' Copy the data
> rstO!ListID = rstI!ListID
> rstO!PendingDate = rstI!PendingDate
> rstO!SoldPrice = rstI!SoldPrice
> rstO!BuyerAgencyID = rstI!BuyingAgencyID
> ' Save the IDs of the new record
> lngSalesID = rstO!SalesID
> lngPendingID = rstI!PendingID
> ' Save the record
> rstO.Update
>
>
> 'Set CurrentListing in Listing form to No
> Me.CurrentListing.Value = 0
> 'Status changes to "SOLD"
> Me.Status = "SOLD"
> Me.Refresh
> 'Open PendingBuyers table to copy data to SalesBuyers
> Set rstB = db.OpenRecordset("SELECT * FROM PendingBuyers " & _
> "WHERE PendingID = " & lngPendingID)
> ' Open the SalesBuyers table to insert from PendingBuyer
> Set rstS = db.OpenRecordset("SalesBuyers", dbOpenDynaset, dbAppendOnly)
> 'Loop through the matching records until reach end of records
> Do While Not rstB.EOF
> ' Start a new record
> rstS.AddNew
> ' Copy the data
> rstS!SaleID = lngSalesID
> rstS!ContactID = rstB!ContactID
> rstS!Ordr = rstB!Ordr
> 'Save the record
> rstS.Update
> 'Move to next record in PendingBuyer
> If Not rstB.EOF Then
> rstB.MoveNext
> End If
> Loop
>
> ' Delete the Pending record--this deletes the Pending buyers too
> rstI.Delete
>
>
> ' Open sales on the new record
> DoCmd.OpenForm "Sales"
> DoCmd.GoToControl "SalesID"
> DoCmd.FindRecord lngSalesID
>
>
> ' Clean up
> rstB.Close
> rstS.Close
> rstI.Close
> rstO.Close
> Set rstB = Nothing
> Set rstS = Nothing
> Set rstI = Nothing
> Set rstO = Nothing
> Set db = Nothing
>
> Proc_Exit:
> Exit Sub ' or Exit Function
>
> Proc_Err:
> Msgbox Err.Description, , _
> "ERROR " & Err.Number _
> & " btnSell_Click"
>
> 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