Recordcounts in subform

[lachesis]

I have a kind of general question on recordcounts.

The situation is my main form: Company has a subform: Contacts. In a global variable I am passing the company ID, so that the mainform will load with the company details as well as any associated contacts in the subform.

In the Form Open event of the mainform, I want to make the subform invisible if there are no contacts for the company.

I seem to be having difficulty getting the recordcount from the subform in the form open event on the mainform. I was thinking that the form open event is too early to get recordcounts from subforms (#??@). Anyone come up against this problem, or is familiar with object/data availability in the sequence of events?

Here's the code used to determine the subform recordcount:

[blue]subContact.Form.Requery
subContact.Form.Recordset.Recordcount[/blue]

tks
L.

 

[lachesis]

I've checked the Forms() collection in the form_open and form_load events and the subform is not even there. (The subform must load at a later stage...)
Guess that's why I can't do a recordcount!

Anyone got a workaround for subform recordcounts at the mainform loading stage??

L.

 

[gol4]

sure there are other solutions as well but why not set visible property from the subforms on current event


If Me.Recordset.RecordCount = 0 Then Forms.form1.subform.Form.Visible = False
else
Forms.form1.subform.Form.Visible = true
end if

 

[Edski]

lachesis,
It should work but you may need to reference the subform using the Me keyword:

Me.subContact.Form.Recordset.Recordcount

Also, I think the subform must be in Datasheet or Continuous Form mode for the RecordCount to work.

You maght also want to consider putting the code in the OnCurrent Event instead, especially if the user will navigate through the records of the main form.
Hope that helps.

 

[lachesis]

Sorry gol, that dont seem to work. Got the error message about the "object not being in the collection", which kinda was my original problem with the mainform. Something to do with the availability of the objects as event load up. hmmm.

L. !?

 

[dafyddg]

How about a slightly different approach.

I assume that you are launching your Company form from another form. How about getting the launching form to work out if there is a need to open the contacts form i.e. run a DLookup for contacts and if it returns null for the selected provider.

You could then launch the Company form like this

DoCmd.OpenForm "FormName, , , "[CompanyID] = x", , 1

I've used company ID as a filter in this case assuming that the Company form is set to a Company table. This way it should filter otu al records bar the company you are after. you can then set openargs to say 1 for enable sub form and 0 for disable subform

Then in the on open or onload even you should be able to read the value of open args and set the visible status of the contracts sub form.

Hope this helps

 

[gol4]

sorry the other did not work for you. It did in a quick test I did here
did you try the recordset clone

If subContact.Form.RecordsetClone.RecordCount = 0 Then
subContact.Visible = False
Else
subContact.Visible = True
End If

the above code works in the on current event
as far as when forms load this is from access help

Working with subforms
When you open a form containing a subform, the subform and its records are loaded before the main form. Thus, the events for the subform and its controls (such as Open, Current, Enter, and GotFocus) occur before the events for the form. The Activate event doesn't occur for subforms, however, so opening a main form triggers an Activate event only for the main form.

 

[lachesis]

Thanks gol.
I used the Me reference in front of your code, on the subform and it worked fine.

[blue]If Me.Form.Recordset.RecordCount = 0 Then
...
End If[/blue]

 

[gol4]

Edski (Programmer) was the one who pointed out the me.ref so appears a star may be in order for him ????

 

[Edski]

gol, you beat me by about 5 minutes if I recall correctly.

I wonder if the webmasters for this site could add the time to the date of a post. Sometimes I get pipped at the post (pun not intended) while I'm typing my response!

I think lachesis should at least give a star to you, gol.