Form/Subform check for data (1 Viewer)

C

CosmaL

Registered User.
Local time
Today, 18:11
Joined
Jan 14, 2010
Messages
92
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

June7

AWF VIP
Local time
Today, 07:11
Joined
Mar 9, 2014
Messages
5,470
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?
 
C

CosmaL

Registered User.
Local time
Today, 18:11
Joined
Jan 14, 2010
Messages
92
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.
 
M

Mark_

Longboard on the internet
Local time
Today, 08:11
Joined
Sep 12, 2017
Messages
2,111
Rather than DLookup, I'd use DCount. Avoids needing to check for nulls.
 
June7

June7

AWF VIP
Local time
Today, 07:11
Joined
Mar 9, 2014
Messages
5,470
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.
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 16:11
Joined
Jul 9, 2003
Messages
16,280
What about something like this? Place a command button on the main form and call this code with the button. It will report the name of every subform/subreport Control and the number of records in it's form.

Code: 
Dim Ctrl As Control

    For Each Ctrl In Me.Controls
        Select Case Ctrl.ControlType
            Case acSubform   'acCheckBox, acComboBox, acLabel, acListBox, acOptionButton, acOptionGroup, acTextBox, acToggleButton
                
                MsgBox " >>> " & Ctrl.Name
                MsgBox " >> " & Ctrl.Form.RecordsetClone.RecordCount
        End Select
    Next Ctrl

The above code could be easily adapted to only report subforms that have no records.
 
missinglinq

missinglinq

AWF VIP
Local time
Today, 11:11
Joined
Jun 20, 2003
Messages
6,423
June7 said:
Why 10 tables?
Click to expand...
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)>
 
C

CosmaL

Registered User.
Local time
Today, 18:11
Joined
Jan 14, 2010
Messages
92
@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

June7

AWF VIP
Local time
Today, 07:11
Joined
Mar 9, 2014
Messages
5,470
"It didn't help" means what - error message, wrong results, nothing happens.

Gizmo's code looks reasonable to me.
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 16:11
Joined
Jul 9, 2003
Messages
16,280
CosmaL said:
Nothing happened!
Click to expand...
Do you have a main form and on that main form are displayed 10 subforms?

If yes, then did you put the command button on the main form or on a subform?

If the on the Main Form,, then place the following code in the command button VBA...

MsgBox " >>> OK <<<"

And report what happens.

Sent from my SM-G925F using Tapatalk
 
C

CosmaL

Registered User.
Local time
Today, 18:11
Joined
Jan 14, 2010
Messages
92
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.
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 16:11
Joined
Jul 9, 2003
Messages
16,280
CosmaL said:
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.
Click to expand...
So what about running my code after you have loaded the subforms?

Sent from my SM-G925F using Tapatalk
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 16:11
Joined
Jul 9, 2003
Messages
16,280
CosmaL said:
Still nothing....
Click to expand...



Well that's very odd. The message box should pop up when you press the command button. Sometimes you can lose connection between a button and the code, especially if you copy and paste code. To avoid the possibility of this being part of the problem I would suggest you create a new button and put the message box in the code stub of the new button and see if it works there. Please report back the results...
 
C

CosmaL

Registered User.
Local time
Today, 18:11
Joined
Jan 14, 2010
Messages
92
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).
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 16:11
Joined
Jul 9, 2003
Messages
16,280
CosmaL said:
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).
Click to expand...
Please please can you confirm that you have created a command button and you have put the following code in the command button but nothing happens?

MsgBox " >>> OK <<<"

Sent from my SM-G925F using Tapatalk
 
C

CosmaL

Registered User.
Local time
Today, 18:11
Joined
Jan 14, 2010
Messages
92
I'm sorry, i've tried the code you prompt for the control check.


The last request works fine "Message ok"
 
You must log in or register to reply here.

Similar threads

S
Copying Check boxes from previous record
Replies
4
Views
284
gemma-the-husky
gemma-the-husky
oxicottin
Check/Uncheck boxes on subform based on main form
Replies
5
Views
293
pbaldy
pbaldy
oxicottin
Solved Export search form data to excel
Replies
11
Views
597
oxicottin
oxicottin
N
Solved How to changes thousands of products prices in a retail business (The backed is SQL Server AZURE)
Replies
3
Views
339
nector
N
zelarra821
Solved Detect the active field and its value
2
Replies
25
Views
723
zelarra821
zelarra821

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom