How to filter and access parent records when the child records are empty?

[Nick H]

I am having problems accessing full records, (displaying parent and child information) if the child record is empty.

I have managed to fix this when showing records in datasheets using queries, using the Left Join, rather than inner join.

But...
I have a candidate table and a qualification table. Each candidate can have more than one qualification, but can also have no qualifications.

I have a Tab control (form) showing candidate information on one tab and the qualifications in another.
I have combo boxes to find particular users but if they dont have a child (qualification) record they are not found.

Is there any way to either:
-create a blank child record when a new parent record is created?
-show the record, regardless of the child record being empty?

Here is the code from my combobox AfterUpdate:

Dim rs As DAO.Recordset
    If Not IsNull(Me.cmbCandidate) Then
        If Me.Dirty Then
            Me.Dirty = False
        End If
        Set rs = Me.RecordsetClone
        rs.FindFirst "[CandidateID] = " & Me.cmbCandidate
        If rs.NoMatch Then
            MsgBox "Not found: filtered?"
        Else   
            Me.Bookmark = rs.Bookmark
        End If
        Set rs = Nothing
    End If
 

[TheSmileyCoder]

You should be able to make it work using left joins, and I would deffinately recomend against inserting blank records.

However, if you can have multiple qualifications for a single candidate, I would suggest putting the qualifications into a linked subform instead, but that is for you to decide.

If your code is not finding the record, Im guessing there most be something wrong with your joins, so if your still having trouble, try posting the SQL.