Replies

Thank you so much – that was exactly the problem.

I suppose you have Access 2007
Go to Access Options, Trust Center, Trust Center Settings… , Macro Settings.
What is the Macro Setting ? Mine is set to Enable all macros. If you have to change it, you have to restart Acces for activate the new setting.

You can use the WORKDAY function – see WORKDAY – Excel – Microsoft Office Online.

Thanks Hans – just what I needed.

For the first half of your question, search this board and the VBA board for userform which is the object you will need to create and program. For the action, since that method is new to me, I think I would start by using the macro recorder and see what kind of code is created. Sometimes it’s very useful, sometimes not. (I’m on Word 2003 at the moment, so I can’t try it myself.)

Thanks, I will look up userform. I guess I always thought that with forms, the final result shows all the form controls and boxes, etc… that’s why I was thinking macros.. a lot to learn. Thanks again.

I originally thought of Autotext but some of the boilerplate text is several pages long and contains images. However, as you say… it changed in 2007 so probably worth another look.

What you describe would be better done by using the Autotext functionality rather than separate files. In Word 2007 this functionality has changed its name to Quick Parts or Building Blocks. If you do some research on these items then you may find that you don’t need macros or separate source files at all.

Thanks also – I never knew that ‘insert text from file’ existed. How would I write code for a macro that would give me choices (like checkboxes) that I could select then click ‘GO’ and have the macro execute the insert text from file using a filename based on the checkbox choice? That way the user could use the same macro as many times as needed through the document inserting appropriate text.

I think Word 2007’s Insert > Object > Text of file is supposed to work the same as Word 2003’s Insert > File. When I use that, in “Draft” view where section breaks are visible, I don’t see anything unusual.

After inserting, do you get any little “smart tag” menus letting you choose options for the inserted text?

I very much appreciate the sample DB you attached. I have studied it and it will launch me on a total redesign of my DB. However, as I look at the forms & subforms, I don’t see where the information in the subforms come from. For example, in looking the Courses form and the subform (sbfStudents), I cannot find how the students’ names appears in the subform. The subform has but one field and that is the StudentID field. I see that it uses the query (qryStudents) as the row source but how is the student name retrieved? The query has two columns but the subform field is bound to column 1. I changed it to ‘0’ to see what effect, if any, and it had no effect.

Thanks you.

General recommendations:
– Turn off “Track Name AutoCorrect Info” in the General tab of Tools | Options… in each database you create.
– Set the Subdatasheet Name property of all tables to [None].
– Avoid creating duplicate indexes; Courses, for example, had two indexes on CourseID.

I have created the subforms for you and placed them on the main forms. They are very simple continuous subforms, with a combo box to let the user select a course or student. See attached.

Cool! Thanks again.

No! If you set the Record Source property of the form to the name of the query, the form will automatically run the query when it opens. You don’t have to do or set anything extra.

Thank you. Does this mean I will need to run the query each time I open the form in question? If so, how can I automate that so that new record entries will be correctly sorted when I next open that form?

Create a query based on the table that sorts the records the way you want, and use the query as record source for the form.

BTW, unlike forms, reports ignore the sort order of their record source. For a report, you must specify the sort order in the Sorting and Grouping dialog.

Thank you Hans & John,

I am now rebuilding my DB from scratch and trying to normalize as much as possible. I think I have the concepts down although I am intrigued by John’s reference to inserting seven reords using code… That is a great idea and I would love to see the code that will automatically insert seven records.

If you design the db with a normaized design, you can usually find some way to get the look you want.
The screen shot below shows information about a doctor’s working times. The information about each day is in a subform, and comes from a related table, but data entry is still by ticking boxes. In this case, whenever a doctor record is created, then 7 records are created in the availability table, using code in the “after insert” event.

Hans, is there a way to do this without the user being able to see the query code (URL)? For example, if I construct a valid Google Maps query but I want the spreadsheet link to simply be a button or a link that simply says “Click here for map” then, when clicked, all the query parameter is passed on correctly.

I find that some of the links I construct are very long since I put comments in them that display on the map (for example, “park on South side and enter though rear entrance”). Rather than show all this on the spreadsheet, I would like a cleaner link.

See Google Map Parameters for info about the search string Google Map uses.

Sample code:

Dim strAddress As String
‘ Assemble string of the form “25 Palm Court, LouisVille, CA 90123”
strAddress = Range(“B12”) & “, ” & Range(“B13”) & “, ” & Range(“B14″) & ” ” & Range(“B15″)
‘ Replace spaces with +
strAddress = Replace(strAddress, ” “, “+”)
‘ Go to Google Maps with search string
ActiveWorkbook.FollowHyperlink “http://maps.google.com?q=” & strAddress

You are probably right. Thanks for the lead – I looked up scenario manager and that may meet my need.

Maybe you bumped into the scenario manager? (In the tools menu)

Thank you, that is helpful.

Or this one ??

Thank you, that is helpful.

Does this YouTube tutorial tell you what you need to know?

Ian

Thank you Andrew,

What you suggest seems more than I am seeking. I was thinking of the occasional need to generate a proposal or contract that uses much of the same text but with some variations depending on whether it is a proposal or a contract, whether it is for a nonprofit organization, and a few other choices – some of which are strict choices (A, B, or C) and others that can be multiple choices (A, B, and C).

I thought there might be a way to have check boxes or option buttons that when checked, would insert the appropriate text.

What I do now is to use a document that contains everything and I selectively remove what I don’t need and modify what remains as needed for that particular document. I am finding, however, that it is easy to make mistakes this way and I would like to avoid that.

Automated document generation is generally what this is called and there are multiple ways to go about this. You can create templates yourself just using the tools you already have such as Word 2007 and tie this together with a spreadsheet/database/accounting package/CRM software where your customer details are located. The coding can be simple or complex depending on how far you want to go with it. If you don’t have the expertise yourself to create the template(s) required then you can pay a developer to create a bespoke solution that fits your requirements exactly. Such a solution could again be simple or complex – it just depends on how far you want to go in terms of money and functionality.

An alternative would be to evaluate some of the software available to do the task. Some possible examples are listed on this page Quote-and-Proposal-Management Software. Of those, years ago I tested the Sant ProposalMaster and also supported this software for an Australian reseller. It was pretty slick at the time and I would certainly have a good look at it again if I needed to produce customised proposals and contracts on a rapid churn basis.

Thanks Hans, I did think of that but was hoping there was some way to use the ROW() method. I have seen this used in VBA – especially in loops where something like “M”&row(i) is used. Is this restricted to VBA or can it be used in a formula? Not the row(i) but the Row() plus or minus some number.

In I7 (the first row where you could possibly add 7 rows):

=IF(WEEKDAY(B7)6,””,SUM(H1:H7))

and fill down as far as needed. The B7 and H1:H7 in the formula will automatically be adjusted.