Thanks much, Bill. I changed the form's recordset to only include the Sales table with an inner join on Listings table and that took care of that issue.
Connie
--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@...> wrote:
>
> Connie
>
> Since that error is occurring when you try to save a record it must be a required field in a table in the form's recordset.
>
> 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:
> >
> > Good morning!
> >
> > I have a button "Sell" on the "Listings" form. It is supposed to open a form "Sales". When I press it, I get the error: Error 3314 "You must enter a value in Listings.HomeInfoID field". When I debug, the line "Forms!Sales.Dirty = False" is highlighted. (I have **** in front of that line) So I'm understanding that to mean that when the Sales form is saved, it has a field Listings.HomeInfoID that is required.
> >
> > Have looked high and low for that field--the form and subforms. Looked in the dropdown list of Properties sheet for it. Also did a find in the code for Listings form and Sales form. Have also decompiled and repaired.
> >
> > What am I missing? Code for the button and sql for Sales form is below.
> >
> > Thanks,
> > 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
> >
> > '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
> >
> > 'Transfer related Pending info to Sales table, deleting pending info and opening Sales form
> > ' 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.Value
> > 'Set CurrentListing in Listing form to No
> > Forms!Listings!CurrentListing.Value = 0
> > *****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
> > Forms!Listings!CurrentListing.Value = 0
> >
> > '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
> >
> > SQL for Sales form:
> > SELECT Sales.*, Listings.CurrentListing
> > FROM Listings INNER JOIN Sales ON Listings.ListID = Sales.ListID;
> >
>
Tidak ada komentar:
Posting Komentar