Form/Subform check for data

[CosmaL]

Dear friends,
I've tried to create a message box with dlookup, to check for empty sub forms, before exporting to pdf.
The results i get is only 1 line in the message with only one section.
I've tried it with 3 sections (with and without data) but i get always the message for section 1 (containing data or not), like its always empty.

There's something wrong in case statement! :banghead:

Private Sub Command56_Click()
On Error GoTo Err_Command56_click

Dim MsgResults As String
Dim MsgResultsContent As String
Dim CheckSection As Integer

MsgResults = "Cannot export the file, the following section(s) don't contain data: " & vbCrLf
MsgResultsContent=""

Select Case CheckSection
Case IsNull(DLookup("[product_id]", "tblsection01", "product_ID =" & Me.product_ID))
MsgResultsContent = MsgResultsContent & "- Section 1" & vbCrLf
Case IsNull(DLookup("[product_id]", "tblsection02", "product_ID =" & Me.product_ID))
MsgResultsContent = MsgResultsContent & "- Section 2" & vbCrLf
Case IsNull(DLookup("[product_id]", "tblsection03", "product_ID =" & Me.product_ID))
MsgResultsContent = MsgResultsContent & "- Section 3" & vbCrLf
Case Else
MsgResults = "OK"
End Select

MsgBox MsgResults & MsgResultsContent


Exit_Err_Command56_click:
Exit Sub

Err_Command56_click:
MsgBox "Error, please inform administrator: (" & Err.Number & ") " & Err.Description, vbCritical
Resume Exit_Err_Command56_click

 

[Uncle Gizmo]

I'm sorry, i've tried the code you prompt for the control check.


The last request works fine "Message ok"

Oh good I'm glad that's working! I thought we had something seriously wrong, or at least were completely on the wrong wavelengths.

What I would suggest is if you can create a new Microsoft Access database and just create a dummy form and place some dummy subforms on it. This will make it much clearer what do you've got and what the problem could well be...


You could create this sample form within the Microsoft Office sample database "Northwind" which is very easy to install and will give you access to tables of data you can use. This avoids the problem of sharing confidential information that some people have.

I blogged about how to install the northwind database here:-

http://www.niftyaccess.com/setup-the-northwind-db/

 

[Mark_]

Dear friends,
I've tried to create a message box with dlookup, to check for empty sub forms, before exporting to pdf.
The results i get is only 1 line in the message with only one section.
I've tried it with 3 sections (with and without data) but i get always the message for section 1 (containing data or not), like its always empty.

There's something wrong in case statement! :banghead:

Private Sub Command56_Click()
On Error GoTo Err_Command56_click

Dim MsgResults As String
Dim MsgResultsContent As String
Dim CheckSection As Integer

MsgResults = "Cannot export the file, the following section(s) don't contain data: " & vbCrLf
MsgResultsContent=""

Select Case CheckSection
Case IsNull(DLookup("[product_id]", "tblsection01", "product_ID =" & Me.product_ID))
MsgResultsContent = MsgResultsContent & "- Section 1" & vbCrLf
Case IsNull(DLookup("[product_id]", "tblsection02", "product_ID =" & Me.product_ID))
MsgResultsContent = MsgResultsContent & "- Section 2" & vbCrLf
Case IsNull(DLookup("[product_id]", "tblsection03", "product_ID =" & Me.product_ID))
MsgResultsContent = MsgResultsContent & "- Section 3" & vbCrLf
Case Else
MsgResults = "OK"
End Select

MsgBox MsgResults & MsgResultsContent


Exit_Err_Command56_click:
Exit Sub

Err_Command56_click:
MsgBox "Error, please inform administrator: (" & Err.Number & ") " & Err.Description, vbCritical
Resume Exit_Err_Command56_click

If you only get ONE notice, this is working exactly as intended. A CASE will only return ONE value.

What you may want to do instead is a set of individual checks that each append their result.

' I'm replacing MsgResultContent with something more programmer friendly

Dim asRet as String  'Use asRet as the Added String for Return Message

asRet = "Cannot export the file, the following section(s) don't contain data: " 

IF DCount("product_id","tblsection01","product_ID =" & Me.product_ID) = 0 THEN asRet = asRet  & vbCrLf & "- Section 1"
IF DCount("product_id","tblsection02","product_ID =" & Me.product_ID) = 0 THEN asRet = asRet  & vbCrLf & "- Section 2"
IF DCount("product_id","tblsection03","product_ID =" & Me.product_ID) = 0 THEN asRet = asRet  & vbCrLf & "- Section 3"

IF asRet = "Cannot export the file, the following section(s) don't contain data: " THEN asRet = "OK"

 

[CosmaL]

@ Uncle Gizmo
This last one with an empty database is working fine!



@ Mark_
Thank you for the corrections, it's working!

 

[Cronk]

If the main form is showing only one record and the sub forms are linked to the main form by the product ID, another way is

for each ctl in me.controls
   if ctl.controltype = acform then
      set rst = me.SubForm1.form.recordsetclone
      rst.movelast
      debug.print ctl,name & " has " rst.recordcount & " records"
   endif
next

 

[CosmaL]

Thank you all (one more time ) for the help!

I kept Mark_'s solution!


Best regards,
Costas