Populating Memo Field from Combo Box

tdw

Hi all,

I have tried a few different methods to accomplish this, but with no luck. I will post the code for the latest attempt at the end of this post.

I work at a land surveying company. This database is for tracking survey orders.
One of the fields is for entering the Subdivision that the parcel of land is in. That field on the New Orders entry form is called "SUB_NAME". On that form it is a combo box that uses a seperate table called "subd". In that table are two fields only: "subd_name" (not to be confused with "SUB_NAME" in the form) and "warn_message". The purpose for "warn_message" is because some subdivisions have special notes or instructions for the field crew conducting the survey, relevant to that particular subdivision (such as "Note: The only access to back yards is through the houses", "Note: None of the lots in this subdivision are staked", etc.)

On my New Orders form I have a Memo field for special instructions or comments called "COMM_1". This memo field serves a variety of purposes, including special instructions for the field crew.

What I am trying to do is as follows:
When a subdivision is selected or typed into the "SUB_NAME" field, I want the code to look to see if a warning message exists for that subdivision on the "subd" table. If one does, I want it to pop up a message box displaying that warning message. I want the person entering the records to have the option (via the yes/no message box) to automatically enter that warning message in the the "COMM_1" memo field. Now, there may already be something in that memo field, and I don't want the new warning message to replace whatever already exists there. I just want it inserted before the existing text.

So far, some of my various attempts work fine for the message box, but will not insert the warning message text into the memo field. My latest attempt neither displays the warning message in the message box, nor inserts it into the memo field. My combo box has two columns: one for "subd_name" and the second for "warn_message". The second column is hidden (width = 0 in)

What am I doing wrong?

Again, to help avoid confusion:
"SUB_NAME" is the field in the main table and in the form for entering the subdivision.
"subd_name" is the field on the subdivisions table ("subd") that the combo box looks up.

Thanks

Nov 11 '08 #1

puppydogbuddy

Try this with the corrections (corrected code lines are noted as such) . I am not certain about this, but you may also have to change the width of the [warn_message] column (bound) in the combobox to .1 (point one tenth of an inch) which will be enough to keep it hidden, yet make its value accessible for passing to the textbox. I seem to remember that the column width can't be zero if you intend to copy and pass the value of the column,

Nov 11 '08 #2

tdw

those corrections made sense to me, however nothing seems to have changed.

Nov 11 '08 #3

puppydogbuddy

1. Did you try changing the width of the bound column of the combobox from zero to .1, as suggested?
2. Did your test data have a warning message?
3. You need to place control breaks and look at the values of your variables at the breakpoints. Which variables do not show any values where there should be values?

Nov 11 '08 #4

tdw

I have column 2 of the combo box set at 0.1". Strangely, the combo box has a horizontal scroll bar at the bottom when open, and if I scroll right the second column appears and most of the warning message is shown. That doesn't seem to me like it's only 0.1".

I am not very good at figuring out the watches and breakpoints. I don't know if I'm doing them right. What I did is selected both lines that are supposed to give a value to the strings (stWarn and stComments). For each line I selected "Add Watch" from the Debug menu. I told it to break when value changes. Then I went back to my form and typed in a subdivision that I know has a warning message. When nothing happened, I rewrote a little bit of the code. I also tried "Toggle Breakpoint" and that didn't seem to do anything different.

After the slight rewrite the yes/no msgbox part works. It does display the box with the warning message in it. But if I click yes, it does nothing. Going back to look at the watch window, all it has are two lines (one for each expression). On both lines the Value column says "<out of context>" and the Type column says "empty".

I must not be doing it right, because obviously stWarn DID have the value of the warning message because it displayed it in the yes/no box.

Here is how my code looks right now:

Nov 12 '08 #5

puppydogbuddy

I think because you have a memo field involved, you have to trim trailing and leading spaces when copying.

Nov 12 '08 #6

tdw

Unfortunately it didn't work. I also tried just modifying it altogether so that it was just being told to to make COMM_1 equal the warning message without worrying about including the existing data in COMM_1. Still nothing.
So then I tried changing the COMM_1 in both lines to a different "text" field to see if it was a memo field problem, using only stWarn and not stComments. That worked. So then I tried changing it to still use the other "text" field, but to include stComments but it didn't work.

Nov 12 '08 #7

tdw

YES!! I figured it out. I had to change it to:
The only problem now is that Chr(13) puts a vertical line in there instead of skipping to the next line.

Nov 12 '08 #8

puppydogbuddy

Good work! change code as follows:

Nov 12 '08 #9

tdw

It works! Thanks. I ended up deciding it worked better like this:
Now, a similar question that I can't seem to figure out:
In the following code, how do I get it to copy the text I just typed over to the Subdivisions form that opens, so that I don't have to type it twice?
This would only apply to when the subdivision I type is not already in the subd table.

Nov 12 '08 #10

puppydogbuddy

I see you open the subdivision add form using a macro. I don't use macros very often, but I think you can use a SetValue action in the macro, following the OpenForm action, to pass the value you want.

There are two Arguments to the SetValue Action:

Item - this will be the field in the form you just opened for which you want to set the value.

Expression - This is where you stipulate the field to be copied (provided that you have the source form open).

Nov 12 '08 #11

tdw

Worked beautifully. Thank you!!

Nov 12 '08 #12