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?
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.