Senin, 24 Oktober 2011

[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

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