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
>
Tidak ada komentar:
Posting Komentar