By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,508 Members | 2,904 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,508 IT Pros & Developers. It's quick & easy.

Populating Memo Field from Combo Box

tdw
100+
P: 206
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub SUB_NAME_AfterUpdate()
  2.  
  3. '   Check to see if this subdivision is already in the subdivisions table
  4.     If DCount("*", "subd", "[subd_name]='" & Me!SUB_NAME & "'") = 0 Then
  5.     '   Yes No Box
  6.         Dim Msg, Style, Title, Response
  7.         Msg = "This Subdivision is not in the list. Do you want to add it?"    ' Define message.
  8.         Style = vbYesNo   ' Define buttons.
  9.         Title = "Add Subdivision"    ' Define title.
  10.         Response = MsgBox(Msg, Style, Title)
  11.             If Response = vbYes Then    ' User chose Yes
  12.                 DoCmd.RunMacro "Open Subdivisions Form"
  13.             End If
  14.  
  15.     Else
  16.         If DLookup("[warn_message]", "subd", "[subd_name] = '" _
  17.                 & Me.SUB_NAME & "'") <> 0 Then
  18.  
  19.             Dim stWarn As String
  20.             stWarn = Me.SUB_NAME.Column(2)
  21.  
  22.         '   Yes No Box
  23.             Msg = stWarn & Chr(13) & "Do you want to add this message into the comments for this order?"    ' Define message.
  24.             Style = vbYesNo   ' Define buttons.
  25.             Title = "Warning"    ' Define title.
  26.             Response = MsgBox(Msg, Style, Title)
  27.                 If Response = vbYes Then    ' User chose Yes
  28.                     Dim stComments As String
  29.                     stComments = Me.COMM_1
  30.                     Me.COMM_1 = stWarn & Chr(13) & stComments
  31.                 End If
  32.         End If
  33.  
  34.     End If
  35. End Sub
  36.  
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
Share this Question
Share on Google+
11 Replies


puppydogbuddy
Expert 100+
P: 1,923
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,

Expand|Select|Wrap|Line Numbers
  1. Private Sub SUB_NAME_AfterUpdate() 
  2.  
  3. '   Check to see if this subdivision is already in the subdivisions table 
  4.     If DCount("*", "subd", "[subd_name]='" & Me!SUB_NAME & "'") = 0 Then 
  5.     '   Yes No Box 
  6.         Dim Msg, Style, Title, Response 
  7.         Msg = "This Subdivision is not in the list. Do you want to add it?"    ' Define message. 
  8.         Style = vbYesNo   ' Define buttons. 
  9.         Title = "Add Subdivision"    ' Define title. 
  10.         Response = MsgBox(Msg, Style, Title) 
  11.             If Response = vbYes Then    ' User chose Yes 
  12.                 DoCmd.RunMacro "Open Subdivisions Form" 
  13.             End If 
  14.  
  15.     Else 
  16.         If DCount("[warn_message]", "subd", "[subd_name] = '" _   '<<<correction
  17.                 & Me.SUB_NAME & "'") > 0 Then 
  18.  
  19.             Dim stWarn As String 
  20.             stWarn = Me.subd_name.Column(2).Value    '<<<<<correction>> 
  21.  
  22.         '   Yes No Box 
  23.             Msg = stWarn & Chr(13) & "Do you want to add this message into the comments for this order?"    ' Define message. 
  24.             Style = vbYesNo   ' Define buttons. 
  25.             Title = "Warning"    ' Define title. 
  26.             Response = MsgBox(Msg, Style, Title) 
  27.                 If Response = vbYes Then    ' User chose Yes 
  28.                     Dim stComments As String 
  29.                     stComments = Me.COMM_1 
  30.                     Me.COMM_1 = stWarn & Chr(13) & stComments 
  31.                 End If 
  32.         End If 
  33.  
  34.     End If 
  35. End Sub 
Nov 11 '08 #2

tdw
100+
P: 206
tdw
those corrections made sense to me, however nothing seems to have changed.
Nov 11 '08 #3

puppydogbuddy
Expert 100+
P: 1,923
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
100+
P: 206
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub SUB_NAME_AfterUpdate()
  2.  
  3. Dim stWarn As String
  4. Dim stComments As String
  5.  
  6. '   Check to see if this subdivision is already in the subdivisions table
  7.     If DCount("*", "subd", "[subd_name]='" & Me!SUB_NAME & "'") = 0 Then
  8.     '   Yes No Box
  9.         Dim Msg, Style, Title, Response
  10.         Msg = "This Subdivision is not in the list. Do you want to add it?"    ' Define message.
  11.         Style = vbYesNo   ' Define buttons.
  12.         Title = "Add Subdivision"    ' Define title.
  13.         Response = MsgBox(Msg, Style, Title)
  14.             If Response = vbYes Then    ' User chose Yes
  15.                 DoCmd.RunMacro "Open Subdivisions Form"
  16.             End If
  17.  
  18.     Else
  19.         If DCount("[warn_message]", "subd", "[subd_name] = '" _
  20.                  & Me.SUB_NAME & "'") > 0 Then
  21.  
  22.             stWarn = DLookup("[warn_message]", "subd", "[subd_name] = '" _
  23.                  & Me.SUB_NAME & "'")
  24.  
  25.         '   Yes No Box
  26.             Msg = stWarn & Chr(13) & "Do you want to add this message into the comments for this order?"    ' Define message.
  27.             Style = vbYesNo   ' Define buttons.
  28.             Title = "Warning"    ' Define title.
  29.             Response = MsgBox(Msg, Style, Title)
  30.                 If Response = vbYes Then    ' User chose Yes
  31.                     stComments = Me.COMM_1
  32.                     Me.COMM_1 = stWarn & Chr(13) & stComments
  33.                 End If
  34.         End If
  35.  
Nov 12 '08 #5

puppydogbuddy
Expert 100+
P: 1,923
I think because you have a memo field involved, you have to trim trailing and leading spaces when copying.
Expand|Select|Wrap|Line Numbers
  1. 'Try changing lines 30 thru 33:
  2.             If Response = vbYes Then    ' User chose Yes 
  3.                     stComments = Me.COMM_1 
  4.                     Me.COMM_1 = stWarn & Chr(13) & stComments 
  5.             End If
  6.  
  7. 'To this:
  8.             If Response = vbYes Then    ' User chose Yes 
  9.                    stComments = Trim(Me.COMM_1)
  10.                    Me.COMM_1 = stWarn & Chr(13) & stComments 
  11.             End If
Nov 12 '08 #6

tdw
100+
P: 206
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
100+
P: 206
tdw
YES!! I figured it out. I had to change it to:
Expand|Select|Wrap|Line Numbers
  1. If Response = vbYes Then    ' User chose Yes
  2.                     stComments = Nz(Me.COMM_1)
  3.                     Me.COMM_1 = stWarn & Chr(13) & stComments
  4.                 End If
  5.  
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
Expert 100+
P: 1,923
Good work! change code as follows:
Expand|Select|Wrap|Line Numbers
  1. If Response = vbYes Then    ' User chose Yes 
  2.                     stComments = Nz(Me.COMM_1) 
  3.                     Me.COMM_1 = stWarn & vbCrLf & stComments 
  4.                 End If 
Nov 12 '08 #9

tdw
100+
P: 206
tdw
It works! Thanks. I ended up deciding it worked better like this:
Expand|Select|Wrap|Line Numbers
  1. If Response = vbYes Then    ' User chose Yes
  2.                     stComments = Nz(vbCrLf & vbCrLf & Me.COMM_1)
  3.                     Me.COMM_1 = stWarn & stComments
  4.                 End If
  5.  
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.
Expand|Select|Wrap|Line Numbers
  1. '   Check to see if this subdivision is already in the subdivisions table
  2.     If DCount("*", "subd", "[subd_name]='" & Me!SUB_NAME & "'") = 0 Then
  3.     '   Yes No Box
  4.         Dim Msg, Style, Title, Response
  5.         Msg = "This Subdivision is not in the list. Do you want to add it?"    ' Define message.
  6.         Style = vbYesNo   ' Define buttons.
  7.         Title = "Add Subdivision"    ' Define title.
  8.         Response = MsgBox(Msg, Style, Title)
  9.             If Response = vbYes Then    ' User chose Yes
  10.                 DoCmd.RunMacro "Open Subdivisions Form"
  11.             End If
  12.  
Nov 12 '08 #10

puppydogbuddy
Expert 100+
P: 1,923
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
100+
P: 206
tdw
Worked beautifully. Thank you!!
Nov 12 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.