Recordset.update

  • Thread starter Thread starter John Petty
  • Start date Start date
J

John Petty

I am trying to add a new record to a table using a form. Everything looks
fine until the code hits frs.update. I then get the following error:

"Cannot add or change record because a related record is required in table
...."

The field that is causing the error is WheelID, but the way the table (that
I am adding the record to) is created the WheelID is a foreign key that has a
one to many relationship from it's own table (i.e. it tags the record to the
WheelID) . How can I get this table to accept the WheelID?

Dim frs As DAO.Recordset
Dim FWID As Variant
Dim FPR As Variant
Dim FPS As Variant
Dim FAI As Variant
Dim FIT As Variant
Dim FRQ As Variant
Dim FCE As Variant
Dim FDR As Date
Dim FDE As Date
Dim FWC As Variant
Dim FWF As Variant
Dim FPN As Variant
Dim FLC As Variant
Dim FLR As Variant
Dim FLI As Variant
Dim FLA As Variant
Dim FCC As Variant
Dim FCR As Variant
Dim FIH As Variant
Dim FIA As Variant
Dim FFL As Variant

' Activate project table and verify that the new analysis iteration
' does not currently exist



With Forms!fmMasterForm.sfmFeaData.Form
If !tbFIteration.Value = DLookup("[FIteration]", "[tblFProj]",
"[WheelID] = " & !WheelID) Then
MsgBox ("Iteration Already exists.")
Exit Sub
Else


' Copy the Required fields to a dummy collector
With Forms!fmMasterForm.sfmFeaData.Form
FWID = !WheelID
FPR = !tbFWPNo.Value
FPS = !tblPStatusID.Value
FAI = !tblFAnalysisID.Value
FIT = !tbFIteration.Value
FRQ = !tbFRequestor.Value
FCE = !tbFCAE.Value
FDR = !tbRDate.Value
FDE = !tbPECD.Value
FWC = !tbFWeightCurrent.Value
FWF = !tbFWeightFEA.Value
FPN = !tbFPNotes.Value
FLC = !tbFLoadCorner.Value
FLR = !tbFLoadRadial.Value
FLI = !tbFLoadImpact.Value
FLA = !tbFLoadAir.Value
FCC = !tbFCycleCorner.Value
FCR = !tbFCycleRadial.Value
FIH = !tbFImpactHeight.Value
FIA = !tbFImpactAngle.Value
FFL = !tbfWFile.Value
End With

' Add the data into the tblfproj table and save record
Set frs = CurrentDb.OpenRecordset("tblFProj")
frs.AddNew
WheelID = FWID
FWProj = FPR
PStatusID = FPS
FAnalysisID = FAI
FIteration = FIT
FRequestor = FRQ
FCAE = FCE
FDateRequest = FDR
FDateEstimate = FDE
FWeightCad = FWC
FWeightFEA = FWF
FProjectNotes = FPN
FLoadCorner = FLC
FLoadRadial = FLR
FLoadImpact = FLI
FLoadAir = FLA
FCycleCornering = FCC
FCycleRadial = FCR
FImpactHeight = FIH
FImpactAngle = FIA
FFile = FFL
frs.Update
frs.Close


End If
End With
 
How can I get this table to accept the WheelID?
Click to expand...

By being sure that there is a matching WheelID value in the related table
before you do the insert. That's what referential integrity is FOR! Do you
*want* to insert a nonexistant ID??
 
With all do respect Mr. Vinson, I kind of thought that was what I was doing
by copying the Existing Wheel ID to a collector and .addnew to the new
project record.

Apparently, you think that is not what I am doing. Such being the case, HOW
do I make sure that I am accessing the existing record??
 
G'day John

Friendly word of advice

All of the MVP's who provide & contribute their infinite knowledge do so
because they want to, not having to.

The paradox you face is that, the more negative attitude you display, the
less responses will also be true.

John is a respected contributor to many NG's and will assist anyone who
genuinely wants & needs help.

Park your attitude and heed what John is try to help you with.

Have a great day
HTH
Mark.
 
Back
Top