Make a Bound Control Editable

[jjaly]

I have an Access 2007 database in which I have a combo box showing one column. The data from this column is linked to a table "tblparagraphs" with two fields, Title and Paragraph. When the user clicks on a title in the combo box, a separate text box fills in a corresponding paragraph.

This is all related to a mail merge file which has been created and works flawlessly. Here is my problem:

Is there a way to make the paragraph text box available for editing without the edit changing the tblparagraphs?

We have a paragraph that looks like this:

Enclosed is/are computer image(s) of your Corporation tax return(s) filed for tax year(s) _______.

I want the end user to be able to put a year in the blank of the paragraphs text box, so that when we merge the data in Word, the year or years will be filled in.

Is there some way to do this without changing the paragraphs in the source table?

 

[lameid]

No.

However you could add another paragraph column to your table for editing (or not editing), and use a combobox to navigate the records.... Do not display the uneditable field, and populate the editable field after moving to the appropritate record.

You might also be able to do this using some sort of query on top of the table.... Since this is a merge, I am having a hard time thinking of a way for the data to be passed to the query.

 

[jjaly]

lameid,

I already use a query for the mail merge. I am just going crazy trying to make that field that pulls the paragraph from a table, editable. Its a bound control, therefore it won't let me make an edit. And if I could edit the paragraph, I don't want it to alter the original paragraph on the table.

Thank you.

 

[dhookom]

jjaly,
Can you explain "a separate text box fills in a corresponding paragraph"? Have you considered using code to fill the text box so there is nothing in the control source?

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

I think Duane has hit the nail on the head, we need more information...

I assumed that tblparagrahps is used in you mailmerge but maybe not. Maybe you are populating some other table with it's contents?

In any case it would likely be helpful to walk through where the data is, how it is populated, what your screen does and what the source of your mailmerge is (post the SQL of the query please).

BTW a control being bound has nothing to do with whether you can edit it... Often locked or enabled properites are a culprit but your recordsource could not be updatable because it is based on a query that is not.

If you blanks always have the same number of characters, you could try something like this in your query, substituting Field1 for your field name. You many need to tinker with the year formula or change that altogether.

Replace(Field1, "_______", Year(Date)))

 

[jjaly]

Sure. I have a table with two fields, paratitle and paragraph. This table is linked to the front end of the database.

The user when working on the form, clicks the drop down box of the paratitle and selects the paragraph title that corresponds to the paragraph they need to use. The drop down box is a combo box with only the first column visible.

As an example:
Paratitle would equal - Returns Not Found
Paragraph would equal - A search of our records has failed to locate returns for tax years ____.

Once they click the title, a separate text box populates with the corresponding paragraph, which it pulls from the linked table.

This is done by setting the control source of the paragraph box equal to the paratitle hidden column.

If I try to change that text box by adding a year in place of the blank, it says: "Control can't be edited; it's bound to the expression '[paragraphtitle1].[column](1)'"

I am attaching a small screen print of the form.

 

[jjaly]

Duane,
Being fairly new and teaching this to myself, I have not attempted to code it.

If that is something that can be done, I am all ears.

Thank you guys for any assistance. It is much appreciated.

 

[dhookom]

I would remove the control source from the unnamed text box and use code in the after update event of the combobox to place the value of the hidden column into the text box.

Me.txtParagraph = Me.paragraphtitle1.column(1)

This depends on the name of your text box.



Duane
Hook'D on Access
MS Access MVP

 

[jjaly]

Duane,
How will that be any different? Will it allow me to make edits to that paragraph? Won't it still be a bound control? Or is setting it equal to "me.txtparagraph" going to allow editing?

 

[jjaly]

Duane,

That works perfectly and does what I need it to do. Just one other issue, For the query I use for mail merge it pulls the data from the table, not the form. Is there have a query pull data from a form instead?

Below is my query

SELECT tblnameaddress.firstname, tblnameaddress.lastname, tblnameaddress.address1, tblnameaddress.address2, tblnameaddress.city, tblnameaddress.state, tblnameaddress.zipcode, tblnameaddress.requestedtaxtype, tblnameaddress.creationdate, tblnameaddress.modifieddate, tblnameaddress.paragraphtitle1, tblnameaddress.paragraphtitle2, tblnameaddress.ParagraphTitle3 AS [Name Address_ParagraphTitle3], tblparagraphs.paragraphtitle1, tblparagraphs.paragraph1, tblparagraphs2.paragraphtitle2, tblparagraphs2.paragraph2, tblparagraphs3.paragraphtitle3, tblparagraphs3.paragraph3
FROM tblparagraphs3 RIGHT JOIN (tblparagraphs2 RIGHT JOIN (tblparagraphs RIGHT JOIN tblnameaddress ON tblparagraphs.PARAGRAPHTITLE1 = tblnameaddress.ParagraphTitle1) ON tblparagraphs2.PARAGRAPHTITLE2 = tblnameaddress.ParagraphTItle2) ON tblparagraphs3.PARAGRAPHTITLE3 = tblnameaddress.ParagraphTitle3
WHERE (((tblnameaddress.modifieddate)=[Date Modified]));

 

[dhookom]

Is the merge from only a single record? If so, your query can reference the form/control value in place of the paragraph.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

As an expression in your query you can use the below syntax to reference an item on your form...

Forms!<Formname>!<ControlName>

Just use that expression. You may want to use the NZ function as well, but I'll let you look that up in help.

I am a little wary of Merging based on an open database though... But if Duane says it will work, I expect it will.

 

[jjaly]

Duane,
No, it is not a single record result from the query.

lameid,
I will try that.

 

[dhookom]

Every record in your query will display the same value if you are referencing the control on the form. Is this what you want?

Duane
Hook'D on Access
MS Access MVP

 

[jjaly]

No Duane. Each record will have different paragraphs.

 

[dhookom]

I don't understand how you can have an unbound text box with a value that isn't stored anywhere appear different for each record. If you want different paragraphs for each record, you must store different paragraphs for each record.

Duane
Hook'D on Access
MS Access MVP

 

[jjaly]

I agree. The current text box is a bound control. I pull the paragraph title and that in turn populates the paragraph so that each record has it's own paragraphs associated with it. what I wanted to do was be able to make changes to the paragraph on the form for each record individually without changing the original paragraph table.

Then have the mail merge pull the data related to each individual record in the query.

 

[dhookom]

Again, if each record has its own unique paragraph, then each record needs to store its own unique paragraph.

Duane
Hook'D on Access
MS Access MVP