Optional FK, attempting to create relationship, errors when adding parent record

[cpberg1]

Hey all,

I'm trying to fix what I believe to be a design flaw in the database I'm working on. I'm creating relationships that didn't exist so I can avoid orphan records.

So I find a suitable PK/FK to create a relationship and I attempt to add it with RI enabled. Sorry you have orphan records. I created a unmatched query using the wizard, removed the offending records and created the relationship. Sweet!

So I go on to testing DB with my new relationship by adding a record through a form to the parent table. Err 3201 can't add or change, a related record is required in the child table. Hmm, over to the back end, open up the parent table in design view, select FK field default value 0. I cleared the default value to stick a null in there instead of 0 (which I believe is correct) as the FK's presence is optional.

Ok, Back to testing! Add a new parent record to the table via form entry button click. Error, Access can't update all the records in the update query. Didn't update 1 field due to key violation. Do you want to ignore and move on with your life? Well that doesn't seem like a viable long term option. Into the VBA for attempting to decipher the event procedure behind the button click.

So among many other things the button click does it calls this function. When I get the "Can't update error" described above and choose not to continue I get a VBA error SQL cancelled and the code in Green below is highlighted.

With my limited VBA knowledge I assume the code in Red is the offending code, which to me appears to set null fields to 0. That does have a purpose on the form and I want it to happen for all fields except my PK/FK that I'm trying to enforce RI. To attempt to skip the code setting nulls to 0 for my PK/FK field I created the select case in Blue. To identify the appropriate fldName I ran the code with the debug.print first and then made the select case.

My current issue when I try to add a parent record is a runtime 94. Invalid use of null. The highlighted line is my PK/FK in the select case statement. Case [AircraftN]

Function PostFlightSub()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSql As String
Dim tdf As TableDef
Dim cntr As Long
Dim FldName As String
DoCmd.RunCommand acCmdSaveRecord

If Nz(Me!SelectLesson) = 0 Or Nz(Me.LessonFlightNum) = 0 Then
   MsgBox (" You MUST select a lesson and flight first! ")
   PostFlightSub = False
   Exit Function
End If
If Nz(Me.RecordEntryDate) = 0 Then
   Me.RecordEntryDate = Now()
End If
DelTableIfExists ("STDFlightsDummy")
strSql = "SELECT STDFlightsTemp.* INTO STDFlightsDummy FROM STDFlightsTemp;"
DoCmd.RunSQL (strSql)
Me.RecordSource = "STDFlightsDummy"
Call PostElements
Set db = CurrentDb()
'plug zeros in any null fields
Set rst = db.OpenRecordset("StdFlightsTemp")
rst.MoveFirst
Set tdf = db.TableDefs("STDFlightsTemp")
FldCount = tdf.Fields.count
[COLOR=Red]For cntr = 1 To FldCount - 1
    FldName = "[" & (tdf.Fields(cntr).Name) & "]"[/COLOR]
[COLOR=Red]    [COLOR=RoyalBlue]Debug.Print FldName[/COLOR]
[COLOR=Blue]'Select Case FldName
 '   Case [AircraftN]
  '      If IsNull(rst(FldName)) Then
   '     End If
   ' Case Else[/COLOR][/COLOR]
[COLOR=Blue]       [COLOR=Red] If IsNull(rst(FldName)) Then
            rst.Edit
            rst(FldName) = 0
            rst.Update
        End If[/COLOR][/COLOR]
[COLOR=Blue]'End Select[/COLOR]
[COLOR=Red]Next cntr[/COLOR]
Set tdf = Nothing
rst.Close
Set rst = Nothing

strSql = "UPDATE STDFlightsTemp SET STDFlightsTemp.FltComments = Null " & _
        "WHERE (((STDFlightsTemp.FltComments)='0'));"
    DoCmd.RunSQL (strSql)
    
strSql = "UPDATE STDFlightsTemp SET STDFlightsTemp.LessonCmpDate = Null " & _
        "WHERE (((STDFlightsTemp.LessonCmpDate)=0));"
    DoCmd.RunSQL (strSql)
    
'post to STDFlights
strSql = "UPDATE STDFlightsTemp INNER JOIN STDFlights ON STDFlightsTemp.FlightID = STDFlights.FlightID SET STDFlights.FlightDate = [stdflightsTemp]![flightdate], STDFlights.InvoiceNum = [stdflightsTemp]![invoicenum], STDFlights.AircraftN = [stdflightsTemp]![aircraftn], STDFlights.InstructorID = [stdflightsTemp]![instructorID], STDFlights.GroupGround = [stdflightsTemp]![groupground], STDFlights.IndividualGround = [stdflightsTemp]![individualground], STDFlights.CPT = [stdflightsTemp]![cpt], STDFlights.SEDual = [stdflightsTemp]![sedual], " & _
        "STDFlights.SESolo = [stdflightsTemp]![sesolo], STDFlights.SEPIC = [stdflightsTemp]![sepic], STDFlights.SEComplex = [stdflightsTemp]![secomplex], STDFlights.MEDual = [stdflightsTemp]![medual], STDFlights.MEPIC = [stdflightsTemp]![MEPIC], STDFlights.MEPDPIC = [stdflightsTemp]![mepdpic], STDFlights.XCDual = [stdflightsTemp]![xcdual], STDFlights.XCPICSolo = [stdflightsTemp]![xcpicsolo], STDFlights.NightDual = [stdflightsTemp]![nightdual], STDFlights.NightDualXC = [stdflightsTemp]![nightdualxc], STDFlights.NightPICSolo = [stdflightsTemp]![nightpicsolo], STDFlights.InstHood = [stdflightsTemp]![insthood], STDFlights.InstActual = [stdflightsTemp]![instactual], STDFlights.FTD = [stdflightsTemp]![ftd], STDFlights.PCATD = [stdflightsTemp]![pcatd], STDFlights.SpinUpset = [stdflightsTemp]![spinupset], STDFlights.ILS = [stdflightsTemp]![ils], STDFlights.LOC = [stdflightsTemp]![loc], STDFlights.VOR = [stdflightsTemp]![vor], " & _
        "STDFlights.[RNAV/GPS] = [stdflightsTemp]![rnav/gps], STDFlights.NDB = [stdflightsTemp]![NDB], STDFlights.[LnD] = [stdflightsTemp]![LnD], " & _
        "STDFlights.[LnN] = [stdflightsTemp]![LnN], STDFlights.FltComments = [stdflightsTemp]![fltComments], STDFlights.LessonCmpDate = [stdflightsTemp]![LessonCmpDate], STDFlights.RecordEntryDate = [stdflightsTemp]![RecordEntryDate];"
[COLOR=Lime]DoCmd.RunSQL (strSql)[/COLOR]

'if completed date is greater than zero plug lesson completed date into all flights for the lesson

strSql = "UPDATE STDFlights INNER JOIN STDFlightsTemp ON (STDFlights.LessonID = STDFlightsTemp.LessonID) AND (STDFlights.STDID = STDFlightsTemp.STDID) SET STDFlights.LessonCmpDate = [stdflightstemp]![lessoncmpdate] " & _
        "WHERE (((STDFlightsTemp.LessonCmpDate)>0));"
    DoCmd.RunSQL (strSql)
    
    
Set db = Nothing
LessonUpdate = False
Me.RecordSource = "STDFlightsTemp"
DelTableIfExists ("StdFlightsDummy")
PostFlightSub = True
End Function

I didn't write the database, the form or the code and I'm pretty amateur using Access for complex solutions. I believe I just need to skip the red code for my PK/FK field but the select case was my only idea for doing so. Any thoughts/ideas for another direction would certainly be appreciated!

Chris

 

[RainLover]

The error is obviously in the code but where and why is difficult to solve due to the shear volume.

Have you considered using Bound Forms which is really what Access is designed for.

Other than that you are going to need to locate the error and explaing what is going wrong with that piece of code.

 

[cpberg1]

Thanks Rain,

I have considered using bound forms, and they are used extensively in the database. This form is unbound due to a (perceived?) limitation of bound continuous forms. Namely, the layout of the form needs to be similar to a continuous form but there needs to be running totals between the various detail records.

We had a go at it using a continuous form and couldn't make it happen. Therefore the form was developed to appear as a bound continuous form, hence all the lines of VBA! Maybe someone else can chime in on the use of null error otherwise I will probably live without the RI enforced on the relationship.

BTW as in my profile you should check out Portland, OR. It rains all the time here!

 

[RainLover]

The design of your form has Naught o do with it being bound.

With a Bound form you can add additional Text Boxes that show the result of calculation but that result is not saved.

Using RI is important. Without it you will have a bunch of orphaned records that will do nothing. So you need to fix the problem.

Perhaps you can REM out the bulk of the code and test small parts at a time.

 

[RainLover]

cpberg

Our weather at the moment is dangerous. Extensive flooding has started and it is going to get worse as the day goes on.

As far as my name, Rainlover was a Champion Horse. He won the biggest cup in Australia two years in a row. (Decades ago) He like me was eventually retired to Stud.

 

[Pat Hartman]

I will probably live without the RI enforced on the relationship.

That is not a good solution since you are already having trouble with orphans. What is the purpose of the running total on the form? Is it ABSOLUTELY necessary to the form's function? Running totals on a report are no problem at all. Running totals on a form require summing previous records the most efficient methods of doing that render the recordset non-updateable. You could use DSum() in the query to create the running sum but this is EXTREMELY inefficient since it runs a new query for every row in the recordset. However if the recordset is not more than a couple hundred rows, you can get away with it. Another alternative is to live with the non-updateable problem and use a button or double click to open the selected record in a single record form where it can be updated. This is the method I have used when I needed to work with a recordset that was non-updateable. Using this method lets both forms be bound and will allow you to get rid of all that code.

I have to admit I didn't read the code closely but a couple of things popped out at me.
1. You are saving the current record before editing it. Edits belong in the FORM's BeforeUpdate event or in some cases the individual control's beforeUpdate event so you can cancel the event to prevent the record from being saved if there are errors.
2. Earlier versions of Access defaulted all numeric fields to 0 which as you discovered caused problems with optional foreign keys.
3. I also see code that sets null fields to 0. My preference is to default all optional fields to null even the ones that are numeric. Once you replace the null with 0, you can no longer tell that the user didn't enter 0. If you want the default for these fields to be 0, then make it 0 in the table so your intention is clear and Jet/ACE will enforce the rule for you.