Problem with subform

[Datathumper]

I have a label on a form that I want to turn the visibility on/off based on a check box condition on a subform. It seems to work fine unless there are no records in a subform, then I get an error message.

Is there a simple way around this. I am a pretty basic user, so I am sure that this is probably a simple fix.

Thanks.

 

[yummy7]

Is label,checkbox on Main form?
Explain

 

[Datathumper]

The label is on the main form and the checkbox is on the subform.

 

[jebry]

Hi!

What is the code you are using and what event is it in?



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[Datathumper]

The error message that I get if there are no records on the subform is

Runtime error 2427

You entered an expression that has no value.

 

[Datathumper]

If [inst dcn tracking].Form![DCN issued] = True And [inst dcn tracking].Form![DCN complete] = False Then Me.Label402.Visible = True

It is in the OnCurrent for the main form.

 

[amarkmac]

I ran into a similar problem earlier this week. Since the subform has no records, calculated and unbound controls in the subform are "undefined." I could not figure out a direct way to deal with this either. There is a .hasData property which would handle this situation, but it is available for subreports only, but not subforms--go figure.

My solution was this:
1) Saved the value of the control on the subform to one of my tables (the table has a default value specified for that field to handle the situation where the subform has no records)
2) On the main form, reference the table value rather than the subform control value for the logic test.

This is working fine for me, but if there is a different way to handle the situation I would be interested too.

 

[Datathumper]

I also researched the .hasdata with no success.

I am trying to avoid having to create fields in the subform table that I dont need just to avoid this problem. There has to be an easy way to ignor my if/then statement if the subform is unpopulated.

I am just learning VBA so my knowledge is limited.

 

[jebry]

Hi!

Just wrap what you have in an if statement:

Dim rst As DAO.Recordset
Dim strSQL as String

strSQL = "Select * From YourTable Where YourID = " & Me!txtID

Set rst = CurrentDb.OpenRecordset(strSQL)

If rst.EOF = True Then
Me!Label402.Visible = False
Else
If [inst dcn tracking].Form![DCN issued] = True And [inst dcn tracking].Form![DCN complete] = False Then
Me!Label402.Visible = True
Else
Me!Label402.Visible = False
End If
End If

Set rst = Nothing

The SQL you use to open the recordset should reflect the record source of the subform filtered for the record current on the main form.

Alternatively you can use a DCount which counts the number of records that would be in the subform based on the ID on the main form. If the DCount is 0 then you know there will be no records in the subform.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[PHV]

you may try something like this:
If [inst dcn tracking].Form.Recordset.RecordCount > 0 Then
If [inst dcn tracking].Form![DCN issued] = True And [inst dcn tracking].Form![DCN complete] = False Then
...
Else
...
End If
Else
...
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Datathumper]

Perfect. That worked better than the temporary fix that I had come up with.

Thanks for the help.