Subforms and Queries

[Hammondo]

I have two SubFormS (SubA and SubB) SubB has a query as a record source.

I call the SubB from SubA and tell in to requery as follows:

Me.Parent![SubB].Requery

this is fine.

I need to detect if no matching records are found when SubB requeries, so that I can send a "Null" value to a text box. How can I detect (from SubA or SubB) if SubB returns no record matches when it requeries?

Something simlar to the following is what I'm looking at:

If Me.[QueryID] = ???? Then
Me.Parent!TxtBox = "null"
Else
Me.Parent!TxtBox = Me.[QueryID]
End If

Help!

 

[hsitz]

A form's currentrecord property will be 0 only if there are no records in its recordset. So I think you shold be able to set your textbox this way:

If forms![mainformname]![subB].form.currentrecord = 0 then
me.parent!txtbox = "null"
else
me.parent!txtbox = me.[fieldname]
end if

-- Herb

 

[Hammondo]

I've given this a go and it doesn't work.... I change the condition to '> 0' to make sure the function was working and it returned the "null" string as expected.. might the currentrecord statement be returning something else for zero record counts?

 

[hsitz]

Actually I checked it and it does return 0 for empty recordsets. I'm not sure it should ever return Null for an open form bound to a record source. You can fool around with it in the VBA IDE immediate view window on an open form and you should find that it returns 0 if the form has no records, and a number > 0 if the form does have records.

It's possible that subA's "On Currrent" event is triggered before subB form has been loaded with a recordset. That sounds to me like the most likely problem. You could try making sure that the subform control holding subB is before the subA subform control in the main form's tab order. That will fix it, I think, if that indeed is the problem. -- Herb

 

[hsitz]

If for some reason you can't get the 'CurrentRecord' property method to work, you might try using the form's recordset's 'RecordCount' property.

You reference it like this:

forms![formname].Recordset.RecordCount

The RecordCount property will be 0 only if there are no records in the form's recordset. Will be some number greater than 0 if there are records (although not guaranteed to give you actual number of records unless you do some other stuff.)

For your needs the CurrentRecord property of the form should work, but accessing the recordset RecordCount property directly is probably a better choice. You could end up having the same problems with it that you do with CurrentRecord, though.

If setting the main form textbox from subB is an option, that's what I'd do. That way you avoid any problems with accessing it from subA before subB has loaded its recordset. You also can use the simpler 'Me.' syntax. -- Herb

 

[Guest_imported]

Thanks for all the help...