ms access - Listing Records with Zero-Length Field in Sub-Form -

i'm having problem database created in ms access 2003.

there table (let's call tblitems) 4 fields: id, description, category, sub_category. sub_category field 1 not required , allows zero-length data.

i created form based on table (frmadditems) has 2 combo-boxes (cbocategory & cbosubcategory), text box (txtdescription) , sub-form (sbfexistingitems) supposed list existing items based on selected in 2 combo-boxes. works long sub-category selected, if there's no sub-category, sbfexistingitems empty.

the record source sbfexistingitems is: "select tblitems.id, tblitems.description, tblitems.category, tblitems.sub_category tblitems order [description];"

frmadditems , sbfexistingitems linked follows:

• child fields: category;sub_category
• master fields: cbocategory;cbosubcategory

the problem presumably related how access handles zero-length data, don't know how make want do. appreciated.

update

as suggested jhtuppeny, have removed child/master links on subform , added following code:

private sub txtdescription_gotfocus()  dim sql string  sql = "select id, description, category, sub_category tblitems"  if isnull(me!cbocategory) or me!cbocategory = ""     ' leave recordsource unfiltered elseif isnull(me!cbosubcategory) or me!cbosubcategory= ""     sql = sql & " [category] = '" & me!cbocategory & "'" else     sql = sql & " [category] = '" & me!cbocategory & "' , [sub_category] = '" & me!cbosubcategory & "'" end if  sql = sql & " order [description];"  me!sbfexistingitems.form.recordsource = sql  end sub 

but subform acts child/master fields linked on "id", listing current item.

i guess comes down need way of altering values of child/master fields in subform's form without main form thinking i'm trying leave while required txtdescription field empty.

your problem have specified link between sub category , subform. when there no sub category there no link regardless of category.

you need create query subform return records based on category , sub category not reliant on there being sub category supplied. can set recordsource of subform using this;

dim sql string  sql = "select tblitems.id, tblitems.description, tblitems.category, tblitems.sub_category " sql = sql & "from tblitems " if cbosubcategory.value = ""     sql = sql & "where (category = cbocategory.value) " else     sql = sql & "where (category = cbocategory.value) , (sub_category = cbosubcategory.value)" end if sql = sql & "order [description];"  sbfexistingitems.form.recordsource = sql