Form/Subform check for data

[CosmaL]

Hello everybody!


I need your valuable help once more!


I've got a product database with a main product form and 10 sub-forms.
Each sub-form loads in a new window, by pressing its button.


Main ID is Record_ID and it's unique (no duplicates) for every product.
Second ID is Product_ID which i use it to load the sub-forms and the final report (which also contains the additional sub-reports).


I've also got a button to export the current product in pdf file.


My problem/question is:
Is there any way to check if each of the sub-forms are containing data by checking the product_id?


I'd like to do that, so when i press the export button, i''ll be able to check if all sections are OK and the final report will not be empty, before exporting it.



Thanks in advance!!!!


Best regards,
Costas

 

[June7]

So each subform/subreport is bound to a separate table? You could use DLookup in VBA to determine if records exist. What should happen if even only one section does not have data?

 

[CosmaL]

Yes, a separate table for each section.


When there's no data, i'm thinking of the following solutions:


1st option: A case statement which checks (with dlookup) for records in every section.

A message will appear everytime there's no data and user will be prompted to enter the data.


2nd option: A general message with all the empty sections and prompt to enter the data (i think that this one is difficult).


Generally, if there's no data, the event will be cancelled until all sections are completed.

 

[Mark_]

Rather than DLookup, I'd use DCount. Avoids needing to check for nulls.

 

[June7]

Why 10 tables? If structure is identical, have 1 table with another field for section identifier.

Second option not too much more difficult, just concatenating a string, and more 'user-friendly' because there would be only 1 popup requiring response.

Attempt code and when you encounter specific issue, post question.

 

[missinglinq]

Why 10 tables?

This was the first thing that entered my mind, on reading about the ten Subforms! Cannot imagine a situation, dealing with products, that would require this design.

Linq ;0)>

 

[CosmaL]

@June7
-Structure unfortunately isn't identical and i need to enter a lot of data and calculations! (Each product's report will be about 15 pages. Each sub-form contains about 10-20 fields with page control etc...)
The only field that is the same in each table is the product_id.
-My knowledge isn't enough to go on with 2nd option.

@Mark_
Thanks, i will try it!

@Uncle Gizmo
It didn't help.

@missinglinq
I've designed it )), 20MB only for the design, without data.
Do you think i'll face problems?

 

[June7]

"It didn't help" means what - error message, wrong results, nothing happens.

Gizmo's code looks reasonable to me.

 

[CosmaL]

Nothing happened!

 

[CosmaL]

I'm sorry Uncle Gizmo,


I did use the code you've suggested in the main form.


My subforms load by button, that's why propably nothing happened.

 

[CosmaL]

Still nothing....

 

[CosmaL]

I did as you request, i had all the sub-forms open (even a new one with missing information) but it's like button doesn't work.


I'm familiar with access, but you never know what you may find! Thanks!



For this project i cannot see any other way of designing it It contains a lot of sub-sections which we need them for the final report (I estimate that there are about of 120-150 fields per product).

 

[CosmaL]

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


The last request works fine "Message ok"

 

[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

 

[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