Getting a value from one of two different places

[Tara]

I have a form that is used for communication between different users about
specific clients. The user inititating the communication chooses the client
from a combo box on the main form (frmClients) which triggers the opening of
the note form (frmNotes) containing that client's ID number. After writing a
note, the initiator then clicks a command button to send it to the recipient.
I'd like to have the recipient of the note be able to reply using the same
form (frmNotes), but I'm not sure how to code the command button to use the
the clientID from the initial note versus looking for it from frmClients
which won't be open on their end. Any help is greatly appreciated!

 

[Graham Mandeno]

Hi Tara

I'm not sure what you mean by "clicks a command button to send it to the
recipient". Does the note get sent by email, or do you mean something else?

Whatever the mechanism, I'm assuming you have two tables - Clients and
Notes - with a one-to-many relationship. As a client can have many notes,
you need some way to uniquely identify the note that is being replied to,
and the ClientID is not sufficient to do that. You will therefore need a
NoteID which would be the primary key in your Notes table. This could be a
sequential autonumber, or some other unique value that you generate
yourself. If the NoteID is known to the recipient, then that note can be
identified without needing to know the client.

The client detains can easily be retrieved from the NoteID by following the
relationship to the Clients table.

Also, it might be useful to have a ReplyTo field in your Notes table
containing the NoteID that this note is in response to. That way, you can
identify threads of discussion, kind of like the threads in a newsgroup.

I suggest a structure like this for your Notes table:

NoteID (primary key)
NoteClient (ClientID of client)
ReplyTo (NoteID of parent note - if null then this is a new discussion)
Timestamp (date/time of note)
PostedBy (UserID of poster)
Subject (text)
NoteText (memo)

 

[Tara]

Hi Graham. Thanks for getting back with me. Yes, I do have 2 tables. The
Notes table was already set up pretty much the way you suggested, with
NotesID being the primary key. The exception was the ReplyTo field that you
suggested, which I've now added. Adding that helped me clarify my thoughts.
I was looking at in a much more complicated, backward way I think. Thanks so
much!

Hi Tara

I'm not sure what you mean by "clicks a command button to send it to the
recipient". Does the note get sent by email, or do you mean something else?

Whatever the mechanism, I'm assuming you have two tables - Clients and
Notes - with a one-to-many relationship. As a client can have many notes,
you need some way to uniquely identify the note that is being replied to,
and the ClientID is not sufficient to do that. You will therefore need a
NoteID which would be the primary key in your Notes table. This could be a
sequential autonumber, or some other unique value that you generate
yourself. If the NoteID is known to the recipient, then that note can be
identified without needing to know the client.

The client detains can easily be retrieved from the NoteID by following the
relationship to the Clients table.

Also, it might be useful to have a ReplyTo field in your Notes table
containing the NoteID that this note is in response to. That way, you can
identify threads of discussion, kind of like the threads in a newsgroup.

I suggest a structure like this for your Notes table:

NoteID (primary key)
NoteClient (ClientID of client)
ReplyTo (NoteID of parent note - if null then this is a new discussion)
Timestamp (date/time of note)
PostedBy (UserID of poster)
Subject (text)
NoteText (memo)

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a form that is used for communication between different users about
specific clients. The user inititating the communication chooses the
client
from a combo box on the main form (frmClients) which triggers the opening
of
the note form (frmNotes) containing that client's ID number. After
writing a
note, the initiator then clicks a command button to send it to the
recipient.
I'd like to have the recipient of the note be able to reply using the same
form (frmNotes), but I'm not sure how to code the command button to use
the
the clientID from the initial note versus looking for it from frmClients
which won't be open on their end. Any help is greatly appreciated!