Display conbined information from multiple records in textbox

[cableguy47905 via AccessMonster.com]

First off, this site has been a tremendous tool for me in the past. I am not
all that much of a novice with databases, but I have never been that great
with forms.

These are the fields that I have in my main table that I will be referring to:

Call_ID AutoNumber
Ref_ID will = a specific Call_ID in some cases
EDS_ID User specific
Product_ID Specific to Client, but not a unique number in this case
Client_Name
CSA_Name
Notes Memo field
Date
Time

Some other fields not related to this question.

Basically when someone needs to start a new record that is referencing a
specific call, then I want to be able to Search the Product_ID field for that
Product. The results would display all of the records related to that
Product_ID. Then the user can copy the Call_ID and start the new record with
the Call_ID in the Ref_ID field.
The Summary of that new record should contain all information from previous
calls containing that have the Call_ID= Call_ID or additional records that
reference that Call_ID.

Previously I was the only one to use this database and I made it so that I
could edit the records. However, now others in my group have caught wind of
the database and would like to also use it. I figured it would be beneficial
for everyone to be able to access each others records, but not be able to
edit them. So now I have the form for additions only and want to make it so
that we can create new records that reference previous calls.

I believe I have figured out all that is needed to make the additions, and
reference calls, but I would like for the representative on the call to be
able to view all of the conversations on previous calls dealing with the
current issue.

Right now in the current form I also have a Summary field that is calculated
for each record so that it can easily be copied and pasted into another
application. That Summary field is formatted to display the following data
just as shown here:
ER – [Client_Name]
CSA – [CSA_Name]
[Notes]

What I would like it to display the following in the format shown:
Call ID - [Call_ID] [Date] (of that particular record)
ER – [Client_Name] [Time] (of that particular record)
CSA – [CSA_Name]
[Notes]

(plus all the same information referencing that particular Call_ID)

This is sort of the pseudocode for that field, but I am really confused as to
how to actually implement my idea.


If Ref_ID ISNull Then
Summary = ER - Client_Name
CSA - CSA_Name
Else
Summary =
Select EDS_ID, DATE, Time, Client_Name, CSA_Name, Notes
Where Call_ID = Ref_ID OR Ref_ID = Ref_ID
Sort By Date, Time
EndIf

 

[TonyT]

You might find it useful to play around with a query, until you get it to
display the formats and information you want, and then copy and paste the SQL
into vba, ammending it as required as the source for a listbox or memo field,
there you can get to see immediately what formats and field combinations work
for you.

sorry not to be more specific,

TonyT..

First off, this site has been a tremendous tool for me in the past. I am not
all that much of a novice with databases, but I have never been that great
with forms.

These are the fields that I have in my main table that I will be referring to:

Call_ID AutoNumber
Ref_ID will = a specific Call_ID in some cases
EDS_ID User specific
Product_ID Specific to Client, but not a unique number in this case
Client_Name
CSA_Name
Notes Memo field
Date
Time

Some other fields not related to this question.

Basically when someone needs to start a new record that is referencing a
specific call, then I want to be able to Search the Product_ID field for that
Product. The results would display all of the records related to that
Product_ID. Then the user can copy the Call_ID and start the new record with
the Call_ID in the Ref_ID field.
The Summary of that new record should contain all information from previous
calls containing that have the Call_ID= Call_ID or additional records that
reference that Call_ID.

Previously I was the only one to use this database and I made it so that I
could edit the records. However, now others in my group have caught wind of
the database and would like to also use it. I figured it would be beneficial
for everyone to be able to access each others records, but not be able to
edit them. So now I have the form for additions only and want to make it so
that we can create new records that reference previous calls.

I believe I have figured out all that is needed to make the additions, and
reference calls, but I would like for the representative on the call to be
able to view all of the conversations on previous calls dealing with the
current issue.

Right now in the current form I also have a Summary field that is calculated
for each record so that it can easily be copied and pasted into another
application. That Summary field is formatted to display the following data
just as shown here:
ER – [Client_Name]
CSA – [CSA_Name]
[Notes]

What I would like it to display the following in the format shown:
Call ID - [Call_ID] [Date] (of that particular record)
ER – [Client_Name] [Time] (of that particular record)
CSA – [CSA_Name]
[Notes]

(plus all the same information referencing that particular Call_ID)

This is sort of the pseudocode for that field, but I am really confused as to
how to actually implement my idea.


If Ref_ID ISNull Then
Summary = ER - Client_Name
CSA - CSA_Name
Else
Summary =
Select EDS_ID, DATE, Time, Client_Name, CSA_Name, Notes
Where Call_ID = Ref_ID OR Ref_ID = Ref_ID
Sort By Date, Time
EndIf

 

[cableguy47905 via AccessMonster.com]

Thanks Tony.

I don't really have a problem getting the actual data that I want to be
displayed, I am just having a problem getting it to display in the format
that I want it in. It is strictly to be displayed, not to be input into a
table again. It needs to be displayed in a single text box, I guess so that
I can it can be easily copied and pasted into another application. I just
though, I wonder if a subreport would work?

 

[TonyT]

Have you thought about displaying it in a listbox with a command button to
export it to word or maybe excel for further copy/paste work?

tonyT..

 

[cableguy47905 via AccessMonster.com]

Thanks TonyT,

I decided just to go with a continuous form. It looks great, I will use the
txtbox for the copy/paste that I need, but use the continuous form for the
"history" of the record.
I am having trbl with opening the form now, using the query. See:
"Parameter Query based on field in current form".

Thanks again,
Lee

Have you thought about displaying it in a listbox with a command button to
export it to word or maybe excel for further copy/paste work?

tonyT..

Thanks Tony.

[quoted text clipped - 4 lines]

I can it can be easily copied and pasted into another application. I just
though, I wonder if a subreport would work?