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
- 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
"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