How to have new added field appear automatically on the report in Ms Access

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
on
SELECT tblSupplier. *
FROM tblSupplier;

Open in new window


If I create an Ms access query like that it means that whatever field I create will be on the query no matter what, the same goes for deleting on the opposite.

Question 1

What should I do to achieve the same behavior on the report, example I DO NOT want to be adding the new fields on the report manually whenever a new field is created, it should be automatically appear on the report without being added manually as it were on the query?

Question 2

Since such type of a query design does not allow filters such that blank fields should NOT TURN UP on the report, is there a way to do the filters at report level to avoid blank field & Zeros?

Sorry to bother you I’m not a full-time trained IT person, I’m a qualified Chartered Certified Accountant, I have only talk myself programming because of the nature of my job. For example, its not possible to competently audit an IT project if one has no clue about simple IT design & programming, that is the reason why I’m here.

Kindly take the above into account.

However thank you to all of you , you contributed a lot to my learning process.

Regards

Chris
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer
Commented:
If I create an Ms access query like that it means that whatever field I create will be on the query no matter what, the same goes for deleting on the opposite.
Don't use such queries, don't use the asterisk. This a thing which should be avoided when ever it is possible.

Q1: The only generic thing is: You can use a query or table as sub-report source. But this means: not control over the format.
Q2: This means that you would need to create your query in VBA.

Why do you want to happen this? What is your use-case?
Based on your question here, I can't help but wonder if your database design is properly normalized (which is paramount to the success of your project). So to give you guidance on that, I urge you to post what your table looks like in design view, so we can feedback on that.

Regarding 1)
No there is no way to automatically add the field to the report. You must open it in design view, and manually add it.

Regarding 2) I think this might depend a bit on your database design, which brings me back to my first comment. Lets see what the table looks like.
Software & Systems Engineer
Commented:
Well its a yes and a no...
You can create a report that dynamically adapts by two ways
1. Create more controls than the one you already have...e.g 20 textboxes when you have only 10 fields from the query (show only 10)...in case you have an 11 fields then an 11th textbox gets visible and gets assigned the Controlsource....check this for start : http://www.utteraccess.com/forum/index.php?showtopic=1976652
based on the excellent code by Ken Getz
2. for this probably you need to create controls on the fly...its a bit more elaborate....
As for the lack of IT training...i must say....you are trying to fly when you simple need first to walk.
I know its hard if someone throws you in the pit and expects from you miracles...but  you need first time and then you need to spend some money.. My suggestion to get you going is :
Buy these books : https://www.amazon.com/Access-Developers-Handbook-Litwin-2002-01-23/dp/B01JXQFRMC along with this book : https://www.amazon.com/VBA-Developers-Handbook-2nd-Getz/dp/0782129781/ref=sr_1_1?keywords=vba+getz&qid=1574243630&s=books&sr=1-1
Study them thoroughly from cover to cover ...start with the VBA book and then Volume 1...Volume 2 is a bit of overkill but you never know when you might need it.
Also....don't argue that they are old....these books are pretty much like gold....when you blow the dust from them the shine will come glowing...just buy them and we will talk again in a few months.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial