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?

Private Sub SUB_NAME_AfterUpdate()
 
'   Check to see if this subdivision is already in the subdivisions table
    If DCount("*", "subd", "[subd_name]='" & Me!SUB_NAME & "'") = 0 Then
    '   Yes No Box
        Dim Msg, Style, Title, Response
        Msg = "This Subdivision is not in the list. Do you want to add it?"    ' Define message.
        Style = vbYesNo   ' Define buttons.
        Title = "Add Subdivision"    ' Define title.
        Response = MsgBox(Msg, Style, Title)
            If Response = vbYes Then    ' User chose Yes
                DoCmd.RunMacro "Open Subdivisions Form"
            End If
 
    Else
        If DLookup("[warn_message]", "subd", "[subd_name] = '" _
                & Me.SUB_NAME & "'") <> 0 Then
 
            Dim stWarn As String
            stWarn = Me.SUB_NAME.Column(2)
 
        '   Yes No Box
            Msg = stWarn & Chr(13) & "Do you want to add this message into the comments for this order?"    ' Define message.
            Style = vbYesNo   ' Define buttons.
            Title = "Warning"    ' Define title.
            Response = MsgBox(Msg, Style, Title)
                If Response = vbYes Then    ' User chose Yes
                    Dim stComments As String
                    stComments = Me.COMM_1
                    Me.COMM_1 = stWarn & Chr(13) & stComments
                End If
        End If
 
    End If
End Sub
 

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

[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,

Private Sub SUB_NAME_AfterUpdate() 
 
'   Check to see if this subdivision is already in the subdivisions table 
    If DCount("*", "subd", "[subd_name]='" & Me!SUB_NAME & "'") = 0 Then 
    '   Yes No Box 
        Dim Msg, Style, Title, Response 
        Msg = "This Subdivision is not in the list. Do you want to add it?"    ' Define message. 
        Style = vbYesNo   ' Define buttons. 
        Title = "Add Subdivision"    ' Define title. 
        Response = MsgBox(Msg, Style, Title) 
            If Response = vbYes Then    ' User chose Yes 
                DoCmd.RunMacro "Open Subdivisions Form" 
            End If 
 
    Else 
        If DCount("[warn_message]", "subd", "[subd_name] = '" _   '<<<correction
                & Me.SUB_NAME & "'") > 0 Then 
 
            Dim stWarn As String 
            stWarn = Me.subd_name.Column(2).Value    '<<<<<correction>> 
 
        '   Yes No Box 
            Msg = stWarn & Chr(13) & "Do you want to add this message into the comments for this order?"    ' Define message. 
            Style = vbYesNo   ' Define buttons. 
            Title = "Warning"    ' Define title. 
            Response = MsgBox(Msg, Style, Title) 
                If Response = vbYes Then    ' User chose Yes 
                    Dim stComments As String 
                    stComments = Me.COMM_1 
                    Me.COMM_1 = stWarn & Chr(13) & stComments 
                End If 
        End If 
 
    End If 
End Sub 

[tdw]

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

[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?

[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:

Private Sub SUB_NAME_AfterUpdate()
 
Dim stWarn As String
Dim stComments As String
 
'   Check to see if this subdivision is already in the subdivisions table
    If DCount("*", "subd", "[subd_name]='" & Me!SUB_NAME & "'") = 0 Then
    '   Yes No Box
        Dim Msg, Style, Title, Response
        Msg = "This Subdivision is not in the list. Do you want to add it?"    ' Define message.
        Style = vbYesNo   ' Define buttons.
        Title = "Add Subdivision"    ' Define title.
        Response = MsgBox(Msg, Style, Title)
            If Response = vbYes Then    ' User chose Yes
                DoCmd.RunMacro "Open Subdivisions Form"
            End If
 
    Else
        If DCount("[warn_message]", "subd", "[subd_name] = '" _
                 & Me.SUB_NAME & "'") > 0 Then
 
            stWarn = DLookup("[warn_message]", "subd", "[subd_name] = '" _
                 & Me.SUB_NAME & "'")
 
        '   Yes No Box
            Msg = stWarn & Chr(13) & "Do you want to add this message into the comments for this order?"    ' Define message.
            Style = vbYesNo   ' Define buttons.
            Title = "Warning"    ' Define title.
            Response = MsgBox(Msg, Style, Title)
                If Response = vbYes Then    ' User chose Yes
                    stComments = Me.COMM_1
                    Me.COMM_1 = stWarn & Chr(13) & stComments
                End If
        End If
 

[puppydogbuddy]

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

'Try changing lines 30 thru 33:
            If Response = vbYes Then    ' User chose Yes 
                    stComments = Me.COMM_1 
                    Me.COMM_1 = stWarn & Chr(13) & stComments 
            End If
 
'To this:
            If Response = vbYes Then    ' User chose Yes 
                   stComments = Trim(Me.COMM_1)
                   Me.COMM_1 = stWarn & Chr(13) & stComments 
            End If

[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.

[tdw]

YES!! I figured it out. I had to change it to:

If Response = vbYes Then    ' User chose Yes
                    stComments = Nz(Me.COMM_1)
                    Me.COMM_1 = stWarn & Chr(13) & stComments
                End If
 

The only problem now is that Chr(13) puts a vertical line in there instead of skipping to the next line.

[puppydogbuddy]

Good work! change code as follows:

If Response = vbYes Then    ' User chose Yes 
                    stComments = Nz(Me.COMM_1) 
                    Me.COMM_1 = stWarn & vbCrLf & stComments 
                End If 

[tdw]

It works! Thanks. I ended up deciding it worked better like this:

If Response = vbYes Then    ' User chose Yes
                    stComments = Nz(vbCrLf & vbCrLf & Me.COMM_1)
                    Me.COMM_1 = stWarn & stComments
                End If
 

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.

'   Check to see if this subdivision is already in the subdivisions table
    If DCount("*", "subd", "[subd_name]='" & Me!SUB_NAME & "'") = 0 Then
    '   Yes No Box
        Dim Msg, Style, Title, Response
        Msg = "This Subdivision is not in the list. Do you want to add it?"    ' Define message.
        Style = vbYesNo   ' Define buttons.
        Title = "Add Subdivision"    ' Define title.
        Response = MsgBox(Msg, Style, Title)
            If Response = vbYes Then    ' User chose Yes
                DoCmd.RunMacro "Open Subdivisions Form"
            End If
 

[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).

[tdw]

Worked beautifully. Thank you!!