Search code examples
ms-accessvbams-access-2003

Required Field's GotFocus Event Won't Set Subform's LinkChildFields Property

I'm having a problem with a database I created in MS Access 2003. I created a form with two combo-boxes (cboCategory & cboSubCategory), a text box (txtDescription) and a sub-form (sbfExistingItems). The cboSubCategory field is the only one which is not required. I added the following code to the GotFocus event of txtDescription:

Private Sub txtDescription_GotFocus()

    Dim sql As String, child As String, master As String

    sql = "SELECT id, description, category, sub_category FROM tblItems"

    If IsNull(Me!cboCategory) Or Me!cboCategory = "" Then 
        ' leave recordsource unfiltered
    ElseIf IsNull(Me!cboSubCategory) Or Me!cboSubCategory = "" Then
        sql = sql & " WHERE [category] = '" & Me!cboCategory & "'"
        child = "category"
        master = "cboCategory"
    Else
        sql = sql & " WHERE [category] = '" & Me!cboCategory & "' AND [sub_category] = '" & Me!cboSubCategory & "'"
        child = "category;sub_category"
        master = "cboCategory;cboSubCategory"
    End If

    sql = sql & " ORDER BY [description];"

    Me!sbfExistingItems.Form.RecordSource = sql

    Me!sbfExistingItems.LinkChildFields = ""
    Me!sbfExistingItems.LinkMasterFields = ""
    Me!sbfExistingItems.LinkChildFields = child
    Me!sbfExistingItems.LinkMasterFields = master

End Sub

If I run this without the last four lines, it works correctly (ie, the subform's RecordSource get's set). But running it with the last four lines results in a run-time error 3314 (description cannot contain a Null value) as soon as txtDescription gets the focus, as if I was trying to leave the main form while the required txtDescription field is empty.

Why would the main form allow me to edit the subform's RecordSource property but not it's LinkChildFields/LinkMasterFields properties?

Solution

  • Because you're setting the recordsource of the subform in your code, there is no need to set the child/master links. You will need to use the code to set the subform's recordsource when the form opens and when the record changes also, but you don't need the links to do this.