Senin, 19 September 2011

[MS_AccessPros] Re: Error referring to a field that I cannot find in the form

 

Wow Clive--your memory is awesome!

--- In MS_Access_Professionals@yahoogroups.com, "Clive" <zctek@...> wrote:
>
> Hi Connie,
>
> Assuming that this is the DB that I looked at
> some time ago, the field 'HomeInfoID' occurs in
> the 'HomeInfo' and the 'Listings' tables.
>
> Your ErrorMessage indicates that you are trying
> to save a record in Listings that is missing a
> value in the Listings_Table.HomeInfoID field.
>
> (You probably know that Forms!Sales.Dirty = False
> will attempt to save the current record if it is
> Dirty.)
>
> Regards, Clive.
>
> --- 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;
> > >
> >
>

__._,_.___
Recent Activity:
MARKETPLACE
A bad score is 596. A good idea is checking yours at freecreditscore.com.

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

Tidak ada komentar:

Posting Komentar