Reports and multiple complex template options - a plan (1 Viewer)

Newbie!

New member
Local time
Today, 12:55
Joined
Apr 19, 2020
Messages
13
Hi all

If anyone has time to look at this plan and give their 2 cents I would much appreciate it. I am expecting there will be lots of problems with what I plan to do!


Background
I am looking to make a database (in part) to make creating word documents easier and quicker. Our current system involves data kept on spreadsheets and several MS word templates that we can fill in the blanks on. I am very new to access and partly using this project as a learning opportunity.

Aim
Make a system that can churn out individual reports based on the information selected on a form and make the system user friendly.

Proposed method
Form will be in tabular or datasheet design. Each row is a separate case and needs its own command button(s) to generate report based on individual row of data. A series of combo boxes on each row will pick the correct parts and paragraphs of text to added to the report (in a modular manner) and then the other data from the row such as data’s, text fields and numbers are then inserted into the newly made template....all with the click of a command button. Report then gets exported to word.

Alternative
Similar but with one command button. Tick boxes on each row select the items I wish to make individual reports on and then the command button generates all of them at once.

Less complex alternative
Using first method but aforementioned combo boxes are just for information and do not generate template. Only single combo box at the end will choose entire template but it will be a longer list.

Issues I know need to be figured out
1. I need to insert data between data that is already being inserted, e.g. if a drop down inserted "The quick brown ____ jumped over the lazy ____" I would need to find a way to insert data from text fields stating "fox" and "dog". In insert inserted into an insert if you like.

2. Exporting reports to word is slow and not friendly to new users, (particularly those who prefer pen and paper to computers. )I will need to design a way this can be carried out with a single button press.

Potential issues I think could come up
1. Complex task for someone new to this

2. Complicated combo system may be resource hungry and slow

3. Maintenance could be difficult and if I leave no one else will have to know how to fix it



My questions for everyone!

As new I'm expecting my plan is filled with holes..... Where am I going horribly wrong and if so, what are the better alternatives?
If this plan is viable how much of a problem are the issues I thought of or are they imagined?
And finally anything I have totally failed to consider?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:55
Joined
Oct 29, 2018
Messages
14,098
Hi. Sorry, short on time and can't read the whole post, but have you looked into doing a Word Mail Merge from Access? I'll try to find a link for you.
 

Newbie!

New member
Local time
Today, 12:55
Joined
Apr 19, 2020
Messages
13
@theDBguy

Thanks I really appreciate the responce, especially given how general an issue this is

My organisation has frowned on mail merge from excel in the past as it tends to confuse half our users who just don't like using computers, especially with all the options in the wizard.
I have tried once or twice using excel to word mail merge with mixed results. I found its good when you want to make lots of documents from data in excel but less so if you have a single document to make

However... if there is a way to use it from access with the click of a button... and no one had to know they were carrying out sneaky mail merge.... :unsure:
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:55
Joined
Oct 29, 2018
Messages
14,098
@theDBguy

Thanks I really appreciate the responce, especially given how general an issue this is

My organisation has frowned on mail merge from excel in the past as it tends to confuse half our users who just don't like using computers, especially with all the options in the wizard.
I have tried once or twice using excel to word mail merge with mixed results. I found its good when you want to make lots of documents from data in excel but less so if you have a single document to make

However... if there is a way to use it from access with the click of a button... and no one had to know they were carrying out sneaky mail merge.... :unsure:
Hi. Go to this link and scroll down until you see the section on "Super Easy Word Merge."
 

Newbie!

New member
Local time
Today, 12:55
Joined
Apr 19, 2020
Messages
13
@theDBguy

Thanks for this! I'l give it try over the next couple of days and let you know the results :D
 

zeroaccess

Active member
Local time
Today, 06:55
Joined
Jan 30, 2020
Messages
658
I understand your goal and will say that there is a limitation within Access in doing this the way you describe.

You can not "cascade" combo boxes in a datasheet or continuous form. That means you can't have a combo box filter a second combo box.

The workaround would be to have all the combo boxes on a single form (not datasheet or continuous), and have the screen only show that one record at a time. A record list would be a separate form that could be clicked to open any previously entered record.

Must your output be editable in Word? If not, an Access Report would be the next step to display the results. It could be saved as .pdf, emailed through Outlook, or printed.
 

Newbie!

New member
Local time
Today, 12:55
Joined
Apr 19, 2020
Messages
13
@zeroaccess

Thanks for the info!

Darn I was hoping to cascade, but there are other ways round this... if I can get each field combo box to represent a single independent paragraph of the letter/document (though long text). The difficulty would then be inserting a field into an inserted paragraph and I'm not sure that can be done?

Yeah, I am begining to wonder if I should just have all 16 or so report templates and just let them be picked be chosen by a single combo box, though I suppose the question is what is more of a problem.... too many reports to choose from or having more options, tables and possibly code.

In any case your work around does seem a good one so I will look into it!

I'm afraid it does need to be editable in word.... unless access allows users to edit reports?
Producing the word document is just the start of the process and it gets further fancy stuff done to it
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:55
Joined
Oct 29, 2018
Messages
14,098
@zeroaccess

Thanks for the info!

Darn I was hoping to cascade, but there are other ways round this... if I can get each field combo box to represent a single independent paragraph of the letter/document (though long text). The difficulty would then be inserting a field into an inserted paragraph and I'm not sure that can be done?

Yeah, I am begining to wonder if I should just have all 16 or so report templates and just let them be picked be chosen by a single combo box, though I suppose the question is what is more of a problem.... too many reports to choose from or having more options, tables and possibly code.

In any case your work around does seem a good one so I will look into it!

I'm afraid it does need to be editable in word.... unless access allows users to edit reports?
Producing the word document is just the start of the process and it gets further fancy stuff done to it
Hi. I'll just be quick to clarify it for others. Cascading Comboboxes are possible in a Continuous Form. In fact, I've seen at least 3 ways it can be done. Cheers!
 

zeroaccess

Active member
Local time
Today, 06:55
Joined
Jan 30, 2020
Messages
658
Hi. I'll just be quick to clarify it for others. Cascading Comboboxes are possible in a Continuous Form. In fact, I've seen at least 3 ways it can be done. Cheers!
Well to be clear, it's only possible with a "hack"...like the text box on top of a combo box trick.

But the thing is a continuous form isn't really necessary for this design. He only needs to be working on one form at a time, so use a record list, then click one and have it pop up to work on it.
 

zeroaccess

Active member
Local time
Today, 06:55
Joined
Jan 30, 2020
Messages
658
@zeroaccess
I'm afraid it does need to be editable in word.... unless access allows users to edit reports?
Producing the word document is just the start of the process and it gets further fancy stuff done to it
Reports are not editable. I think you are going to need to make a Word template, and use VBA code to instruct Access what to do with it. I have no experience with that, though others might.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:55
Joined
Oct 29, 2018
Messages
14,098
Well to be clear, it's only possible with a "hack"...like the text box on top of a combo box trick.

But the thing is a continuous form isn't really necessary for this design. He only needs to be working on one form at a time, so use a record list, then click one and have it pop up to work on it.
Hi. It wasn't a dig at you. Some people might be happy with a hack. I was just saying it wasn't an absolute "can't do" to not give others who may read this thread in the future the wrong impression.
 

Newbie!

New member
Local time
Today, 12:55
Joined
Apr 19, 2020
Messages
13
Well folks, it seems I need to do a bit more learning (particularly about normalisation) before I start tackling this problem. With this quest of mine its allways 2 steps forward and 1 step back, and though I do learn best from having a project inevitably this approach leaves gaps in my knowledge that can only be solved by good ol reading!

Anyway thank you all for your advice, when I get back to this issue in a couple of weeks you have given me a few methods to try out!
I'l report back for more help or, if I'm successful post my solution, when I get back to this stage
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:55
Joined
Oct 29, 2018
Messages
14,098
Well folks, it seems I need to do a bit more learning (particularly about normalisation) before I start tackling this problem. With this quest of mine its allways 2 steps forward and 1 step back, and though I do learn best from having a project inevitably this approach leaves gaps in my knowledge that can only be solved by good ol reading!

Anyway thank you all for your advice, when I get back to this issue in a couple of weeks you have given me a few methods to try out!
I'l report back for more help or, if I'm successful post my solution, when I get back to this stage
Hi. Good luck!
 

Users who are viewing this thread

Top Bottom