Editing Foreign IDs in cboboxes causing ophan records

[NBRJ]

Not sure if this is best here or in Theory and practice of database design? It's causing me massive problems on forms so here unless mods think otherwise.

------------------------------

I have had an issue with orphaned records today and I'm, first, massively panicked, and then I'm hoping feverantly that I haven't missed out a very basic chunk of my understanding of database design. Please could you give me some advice.

Here's some very basic example tables:

tFaculty
IDFaculty
FacultyName

tSchool
IDSchool
SchoolName
FIDFaculty (enforced referential intergrity: On)

tSubject
IDSchool
SubjectName
FIDSchool (enforced referential intergrity: On)

tStaff
IDStaff
FName
SName
FIDFaculty (enforced referential intergrity: On)
FIDSchool
FIDSubject

Form for tStaff is called fStaff (continuous). For data entry, all the FIDs, become comboboxes, bacause users can't work with just IDs, obviously.

fStaff
IDStaff (autonumber)
FName (text)
SName (text)
Cascading combo boxes:
FIDFaculty (cboFaculty, bound to tFaculty.IDFaculty, showing FacultyName)
FIDSchool (cboSchool, bound to tSchool.IDSchool, showing SchoolName)
FIDSubject (cboSubject, bound to tSubject.IDSubject, showing SubjectName)

If I go to a record entry on fStaff, and because a staff member isn't affiliated to a subject anymore (i.e. it's an optional field), and want to remove the subject value, I just backspace to remove the value.

This changes the value from it's tStaff.FIDSubject to NULL. I checked with this:

Private Sub Command1_Click()

If (FIDNullZeroTest) = 0 Then
    Debug.Print ; "Field is 0, check: "; FIDNullZeroTest
    
ElseIf Not IsNull(FIDNullZeroTest) Then
    Debug.Print ; "Field is not null, check: "; FIDNullZeroTest

ElseIf (FIDNullZeroTest) = "" Then
    Debug.Print ; "Field is empty, check: "; FIDNullZeroTest
    
ElseIf IsNull(FIDNullZeroTest) Then
    
    Debug.Print ; "Field is null, check: "; FIDNullZeroTest
    FIDNullZeroTest = 0
    Debug.Print ; "Field had been put back to 0, check : "; FIDNullZeroTest
    
End If
End Sub

Firstly I thought Null was the value if a field had never held data and since the FIDs all have a default value of 0, that shouldn't be the case? I thought backspace/delete changes it to Empty not null.

Anyway, the underlying table will have tStaff.FIDSubject values:

• 0 if nothing has ever been selected
• NULL if the data's been removed
• a relevent FID if a selection has been made.

This inconsitency then causes me to lose records with my filter functions I have made.

strFilterStart = "SELECT * FROM tStaff WHERE ("
strFilterEnd = ");"

strFilterFirstname = "((tStaff.Firstname) Like Nz(Forms![fNavigation].[NavigationSubform].Form.[txtFilterFirstname]) & ""*"")"
strFilterSurname = "((tStaff.Surname) Like Nz(Forms![fNavigation].[NavigationSubform].Form.[txtFilterSurname]) & ""*"")"
strFitlerFaculty = "((tStaff.FIDFaculty) Like Nz(Forms![fNavigation].[NavigationSubform].Form.[cboFilterFIDFaculty],'*'))"
strFilterSchool = "((tStaff.FIDSchool) Like Nz(Forms![fNavigation].[NavigationSubform].Form.[cboFilterFIDSchool],'*'))"
strFilterSubject = "((tStaff.FIDSubject) Like Nz(Forms![fNavigation].[NavigationSubform].Form.[cboFilterFIDSubject],'*'))"
strFilterSpecialism = "((tStaff.FIDSpecialism) Like Nz(Forms![fNavigation].[NavigationSubform].Form.[cboFilterFIDSpecialism]) & ""*"")"

Me.RecordSource = strFilterStart & strFilterFirstname & _
    " AND " & strFilterSurname & _
    " AND " & strFitlerFaculty & _
    " AND " & strFilterSchool & _
    " AND " & strFilterSubject & _
    " AND " & strFilterSpecialism & _
    strFilterEnd

Note: the filterboxes are set to NULL on form load and at filter reset.

If the record FIDs have zeros, works like a dream. If any have had of the cbo selections deleted (i.e. NULL) then they don't show on subsequent filters (they'll show when I reset the form but will still, obviously, fail to show up in filter results).All my form filters are based off this

I could for every damn cbo that is talking to a FID (which, to be honest, that's how all my cbo's behave) do this as an AfterUpdate event:

If IsNull(FIDSubject) Then
   FIDSubject = 0
End If

Should I have my FIDs not have 0 set as default. I tried that on a test table and not having default set to 0 means they're NULL. But then my filter doesn't work and I couldn't figure out how to change it so it ignores NULL. I think my preference is to set FID NULL entries to 0. That is, if a default value of a FID to 0 is the normal convention, and not a mistake.

Can someone guide me what is good practice with all this? Have I made a fundamental error? I can't find any articles anywhere that say, when creating an FID and using a Long Integer switch the default value from 0 to nothing (which then reads as NULL).

This seems so basic. I feel such an idiot.

 

[NBRJ]

I've just tried this type of thing after an update:

Private Sub cboFIDSubject_AfterUpdate()

If IsNull(cboFIDSubject) Then
    cboFIDSubject = 0
    cboFIDSpecialism = 0 'child table (tSpecialism) of tSubject.

ElseIf (cboFIDSubject) = 0 Then
    cboFIDSpecialism = 0

ElseIf (cboFIDSubject) > 0 Then
    ' Set the Specialism combo box to be limited by the selected Subject
    Me.cboFIDSpecialism.RowSource = "SELECT tSpecialism.[IDSpecialism], tSpecialism.[SpecialismName] " & _
        "FROM tSpecialism " & _
        "WHERE [FIDSubject] = " & Nz(Me.cboFIDSubject)

End If

End Sub

(tSpecialism is a related child of tSubject (just as tSubject is a child of tSchool,etc)... So I need to do this for every cbo?

This feels like a workaround to bad design. But solves the missing zero palarva. But I'd like to remove bad design as far as my inexperiance allows anyway....

 

[plog]

I stopped reading after this:

tFaculty
IDFaculty
FacultyName

tSchool
IDSchool
SchoolName
FIDFaculty (enforced referential intergrity: On)

tSubject
IDSchool
SubjectName
FIDSchool (enforced referential intergrity: On)

tStaff
IDStaff
FName
SName
FIDFaculty (enforced referential intergrity: On)
FIDSchool
FIDSubject

You've created multiple paths among your tables. For example, tStaff is directly linked to tFaculty; it is also indirectly linked to tFaculty via tSchool; and indirectly linked again via tSubject via tSchool. If you set up the relationship tool you will see its a spider web. That is incorrect. You should only have 1 path between tables.

I also think you've done a poor job naming your tables. I don't think tSubject is a of all subjects, its a junction table to hold what schools teach what subjects, correct? A better name for that table would be SchoolsSubjects. tSchool suffers from something similar, it's not a list of schools but also you are trying to tie staff to schools in the same table.

The more I look at it and try and guess the purpose the more confused I become. Take a step back, put down all database thinking and write a plain english (no database jargon) explanation of the real world process this is to model. From there you need to properly set up your tables, then move on to forms (actually, reports come next; forms are last).

 

[NBRJ]

Well the relationship diagram doesn't look that complicated for those tables (attached) - I can't see the confusion. The database design is documented and all tables, fields, etc have a description next to them plus example logic.

A staff member must be a member of a faculty. School and subject is optional, depending on their job (Head of Faculty has no school/subject field entry, for example).

My question was about cbo's and Null value after deletion on the form, I'm happy with the design, but thanks for taking your time though.

 

[plog]

So the FIDSchool field in tstaff is not a foreign key to tSchool? Likewise for the FIDSubject field in tStaff?

I'm pretty sure your tables are not correctly related. I think tFaculty is a hack table used to make things work, staff should be directly related to schools, and subjects could be related in a variety of ways depending on the purpose it is to serve. Again, a plain-english explanation would help me and others a lot.

 

[NBRJ]

It's ok, I'm happy with the design based on what the database required to do. It was just there as an example regarding cbo's and the Null value after deletion on the form, which was the problem I was asking about, but I've worked around that now.