Using a Combo Box to populate a Field from a Memo Source

[Playfulmutt]

Hi, all.

I'm new here, and actually registered for this problem alone. I'm very new to SQL but I have had my boots on the ground with Access for a while now. I am programming a database for a reorganized group in my office. The boss wants everything highly standardized, which is where I come in.

For whatever reason they want their correspondence letters within the Database instead of in Word templates. This way I can standardize their headings and automatically import data from their tables onto their letters.

For this reason I chose a form. I felt using a report wouldn't give them enough editing power. Almost all of the fields on this form are unbound and set to default values. I want to use a combo box so the worker can select the "letter type" they want from the drop down and it will automatically update the body of the letter with the standard format of that type of letter.

Just one problem--these letters are wordy, so the "content" field had to be a memo. I know you can't set memo fields to combo box columns, so I'm in a jam.

Does anyone have an idea of how I could populate this memo field into the unbound field using some kind of selector? It doesn't necessarily have to be a combo box.

There's a kicker. I want to populate the memo field into the unbound box, but I don't want it to establish a link or control source because I don't want the worker modifying the original record (think of it as a template).

 

[spikepl]

Sounds like confussion to me.

Acces is about records. A record is in table, and the table is made up of records.

The record has fields and almost always a primary key. You put stuff into the fields of a record.

To retrieve your stuff you need the primary key and the columm (field) name. So if you have a memo field in some record of some table you can always use, e.g. Dlookup (look this function up in the documentation).

 

[Playfulmutt]

Access is a dynamic database. There's more to it than records.

I have never used Dlookup before. My two concerns with that are:
1. Can I use it in tandem with a Listbox/ComboBox to allow the workers to select the letter type that they want?
2. Can I use it to populate the unbound field without linking the record source?

Examples of Dlookup and where I would use that event would be greatly appreciated.

 

[Uncle Gizmo]

>>>import data from their tables onto their letters<<<

Are you shifting MS Access Memo field contents to word documents?

 

[vbaInet]

From what you've described your best bet is to still use Word for most of the job. Yes you can use Access for:
>>>I want to use a combo box so the worker can select the "letter type"<<<

But for things like:
>>>automatically update the body of the letter with the standard format of that type of letter.<<<
>>>Just one problem--these letters are wordy, so the "content" field had to be a memo. I know you can't set memo fields to combo box columns, so I'm in a jam.<<<

You should keep it all within Word. Access can be set up to link to Word from there you can automatically open word documents. As for standardisation you open templates, enter text and save it with another name.

As for this comment, "I felt using a report wouldn't give them enough editing power", no Access doesn't have 1/3 of the amount of editing tools that Word has. Access is a data management software whilst Word is a document editing/word processor.

To summarise:
1. Use Access as an interface for providing users with "template selection"
2. Use Access to open the template which already has all the relevant logos, body and signature pre-filled
3. Use Access to add any extra pieces of information the user would want that's not in the template
4. Hand over to Word.

 

[Playfulmutt]

>>>import data from their tables onto their letters<<<

Are you shifting MS Access Memo field contents to word documents?

What I tried to do is use the Listbox to shift the MS Access memo field in one table into an unbound text box on the correspondence form. Because memo fields can't show up on the Listbox columns it won't work. I can change the memo field to a text field, but then it will be limited to 255 characters.

No MS Word is currently implemented. The plan was that workers would print the form. The formatting all works, but the only problem I am having is getting the memo field to load into the unbound text box by selecting an option in the Listbox.

I would really rather avoid exporting the form to a Word document. It doesn't transfer well and the point of the project is to keep all of the workers information within the database.

 

[Uncle Gizmo]

>>>I would really rather avoid exporting the form to a Word document<<<

You wouldn't do it like that you would transfer the Text straight to a "Field" (AN MS WORD FIELD)

 

[Playfulmutt]

>>>I would really rather avoid exporting the form to a Word document<<<

You wouldn't do it like that you would transfer the Text straight to a "Field" (AN MS WORD FIELD)

I see what you're saying now. However that is "pulling resources out" of the database. Bosslady really wants to keep everything that these workers do inside of the database.


Also pulling those Access fields and linking them to Word fields undermines programming that I have done to automatically update a "last correspondence date" field in the tables when the correspondence form is printed.


I would also be concerned that the link may make the database read only while the Word document is open. I cannot allow that because we will have 4 workers with this database open 8 hours a day 5 days a week.

I appreciate all of the feedback and suggestions for alternative methods, but I really need a workaround that will keep the memo field and the correspondence inside of the Access database.

 

[Uncle Gizmo]

>>>However that is "pulling resources out" of the database<<<

No, ALL the code would be in MS Access.

You would open a word *.dot file and generate it with MS Access Code.

 

[Playfulmutt]

>>>However that is "pulling resources out" of the database<<<

No, ALL the code would be in MS Access.

You would open a word *.dot file and generate it with MS Access Code.

Now you're stepping out of my realm of knowledge.

I don't suppose you know of any resources for creating such a document? I can google on my own, but if you know of any resources that apply directly to my current situation I'd like to give them a look.

Thanks for providing a resource that I can investigate.

 

[vbaInet]

All of this was already mentioned in my post but you chose to ignore it because it didn't fit with your expectations. Is "boss lady" technical enough to know that what she's asking for just isn't feasible? You don't reinvent the wheel by using Access as a Word Processor when there's one already.

If you want a proper platform for saving the documents in a database then you should be looking for a SQL Server or Oracle solution. You can save whatever kind of objects with ease and in the knowledge that they are well protected.

 

[Uncle Gizmo]

I don't suppose you know of any resources for creating such a document?

Find Item 55 >>> Moving Large Quantities of Data from Access to Word

On Helen Feddema's Website:-

It's difficult to find the "Field" on the *.dot word template file if you don't know how so have a read HERE:-

Add fields to a word Doc:-
http://theitbros.com/microsoft-office-2007-2010-form-fields/

Save it as a word template:-

Save a Word document as a template

You need to put the template somewhere like this:-

C:\Users\username\AppData\Roaming\Microsoft\Templates