Checking subform contains at least one record

[helenlou]

I have a main form with a button which performs an action. The actio should only be performed if there are records in the subform (i.e. that there is at least one record relating to the record in the main form).

Can anyone help me write the VB to do this check? I've tried all sorts of things - checking the values of various fields aren't null but it doesn't seem to work.

Thanks

H

 

[Howlsta]

Have you tried using DCOUNT?



if DCount("[yourfield]", "yourQuery") > 0 then
perform action

look up Dcount in help or search the forum as it comes up a lot

Rich

 

[helenlou]

DCount doesn't seem to work - I get an "Invalid use of null" error. This is the code I used:

If DCount(Me![Campaign Test Cell subform]![Test Cell ID], "Campaign SubForm_Query") = 0 Then
msgBoxResult = MsgBox("You must specify at least one Test Cell before upload", 48, "USER INFORMATION")
cancelUpload = True
End If

Any other ideas?

H

 

[helenlou]

No worries - have got it to work without using DCount. Must have been a problem in refering to a subform control.

Thanks for your help!

H

If Me![Campaign Test Cell subform]![Test Cell Number] = 0 Then
msgBoxResult = MsgBox("You must specify at least one Test Cell before upload", 48, "USER INFORMATION")
cancelUpload = True
End If

 

[Howlsta]

Hi Helen

I think the syntax is wrong you need to put quotes around the field name as well as [].

if DCount("[fieldname]", "query1") = 0

Also, are you putting in the name of your field as it appears in your query. You have put "Campaign Test Cell subform" this doesn't sound like a field name, as your subform is based on a query (i presume) you only need to reference the query as it will be open as long as your form is.

Good luck!!!

Rich