User selection determines field appended?

[geotalk2bs]

I have a form called Main that has three different dialog fields
displayed, I'll call them Local Dialog, Regional Dialog, and National
Dialog. The Main form has an Add Comment button and on click I'm
popping up a new form called AddComment. This form has a drop down
list pre-populated with Local, Regional, and National and is single
select. It also has a text entry field to capture comments. After
picking one of the three identifiers the user then enters their
comment.

My problem - after entering their comments they can hit an Add button
that I created but I don't know how to control the destination of
their comments. I would like to append the new comments onto the
existing data contained in either the Local Dialog, Regional Dialog,
or National Dialog fields depending on which the user chose.

Example:
1. User is on the Main form
2. Local Dialog field currently says "This customer terminated their
agreement last year - Tom 3/5/07."
3. User wants to add new comment to the Local Dialog so they hit the
Add Comment button
4. They select Local from the drop down list.
5. They enter comment "Customer signed new agreement and is now active
- Jeff 4/1/07."
6. They hit add.
7. I'd like the Add Comment window to go away, the user is returned to
the Main form, same record, and the Local Dialog field now says "This
customer terminated their agreement last year - Tom 3/5/07. |
Customer signed new agreement and is now active - Jeff 4/1/07."

Is this possible? It may not be pretty but given my current
restrictions this solution can work if I can pull it off. Please
advise.

 

[Dale Fye]

In this situation, I would make the text box on the main form unbound and
locked.
I would use the forms Current event to run some code to create a string that
contains all of the comments associated with the Local Dialog for this user.

The Add Comment form should be storing your data in a table (tbl_Comments)
that contains a Customer_ID, Dialog_Type_ID, Comment (probably a memo field),
MadeBy, and CommentDate.

If you setup the main form so that when they hit the Add Comment button, it
opens the Add Comment form in dialog mode, then you can rerun the Current
event of the main form when the user closes the Add Comment Form. It might
look something like:

Private sub cmd_Add_Comment_Click

docmd.openform "frm_Add_Comment", , , , , acDialog
Call Form_Current

end sub

The code in the current event might look something like:

Private sub Form_Current
Dim strSQL as string
Dim rs as DAO.Recordset
Dim strComment as string

strSQL = "SELECT Comment, MadeBy, CommentDate " _
& "FROM yourTable " _
& "WHERE CustomerID = " & me.txt_Customer_ID
& " ORDER BY CommentDate"
Set rs = currentdb.openrecordset (strsql, dbfailonerror)

While not rs.eof
strComment = strComment _
& rs("Comment") & " " & rs("MadeBy") & " " _
& rs("CommentDate") & vbcrlf & vbcrlf
rs.movenext
Wend
rs.close
set rs = nothing

me.txt_Comments = strComment

End Sub

HTH
Dale