Grace wrote:
> On Aug 23, 2:33 pm, KC-Mass <connear...@comcast.net> wrote:
>
>>On Aug 23, 1:59 pm, Grace <vi.qu...@wisconsin.gov> wrote:
>>
>>
>>>Hi,
>>>I am working on a database to keep track all the caller's info in the
>>>database.
>>
>>>For example:
>>>After the user entered a caller's information in the database, and
>>>when that same caller called back again the second time and then some
>>>of their information and also the client id automatically appeared on
>>>the screen so that the user don't need to retype them again. I want
>>>to keep track of each record as new records. The Id and first name
>>>will be the same. When I run a report I can link them all together
>>>and knowing that this person had called in twice!
>>
>>>Any suggestion, please advice! Thanks. Grace
>>
>>Fundamentally you need three things:
>>
>>1. A table to hold the clients information - minimally - name and ID.
>>(the table can hold other, non repeating data such as phone number,
>>email, etc)
>>
>>2. A second table to hold the call information - minimally - the ID
>>of the caller, the date and time of the call, and what the call was
>>about
>>
>>3 A query that links the two tables on the client ID
>>
>>You can then get a list, report or what ever that will list each
>>caller listing separately each call received from them.
>>
>>Regards
>>
>>Kevin
>
>
> Thanks Kevin for your response!
> Very appreciate it!
>
> Basically what I try to do is to have to links/choices:
> #1. Add a new caller with no prior service with us
> #2. List recent caller info
>
> if #1 is clicked - a new black screen/form will pop-up for enter new
> info ( I got that working!)
>
> if #2 is clicked - these info will pop-up:
> Caller ID Name Date Call (option links as shows below)
> 7575 Bob 8/23/2010 View/Edit/New Call
> Please note the above's option links, when New Call was clicked
> the form pup-up contains previous/same caller info such as:
> Caller ID Name Date Call (New date! and many new info need to
> enter!)
> 7575 Bob 8/24/2010
>
> In the table it should contains the info as follows:
> Caller ID Name Date Call
> 7575 Bob 8/24/2010
> 7575 Bob 8/23/2010
>
> This is what I need to accomplish and hope is more clear.
> Thanks for your help or anyone willing. Very appreciate it!
> G
>
This sounds like an excellent use of a form/subform. You would have a
main form (caller info) and a subform that that would contain the call
records.
Table1 (main) would contain the id, name, etc.
Table2 (sub) would contain the call info
Create the main form. In properties set as Single (under format). You
can use a wizard to generate the form. Set navigation buttons to true so
you can skip thru the list or add/goto a new record.
Create the call info form (sub). Set as continuous or datasheet in
properties (under format). I recommend you sort the calls in descending
order by call date so you get the most recent data first.
Open the main form In design mode. Select the toolbox, the subform
button, and drag it onto the main form. Resize it. It will ask you to
link the two. I recommend you have a field like "CallingPersonID" in
both tables. Than way there is a link between the main form (contact
info, etc) and the calling detail (subform).
In the header of the form you can put a dropdown/combobox. This would
list all of the callers. The SQL might me
Select CallingPersonID, CallingPersonName from MainTable
Order By CallingPersonName
as the rowsource for the combo. A wizard will ask, when you put it on
the form, if you want to it to search for records. Say yes.
Now a person can enter a person's name in the dropdown to instantly find
and goto a record. There you see the call records. In the subform you
could highlight all for the fields and in the OnDblClick event in the
propertysheet something like
=GoToCallDetail
Then in your subform create a function like
Private Function GoToCallDetail()
Docmd.OpenForm "CallDetail",,,"CallID = " & Me.CallID
End Function
Now this will open the form with even more call information. This
assumes you have a notes field and other fields not displayed on the
subform.
By having an ID that you can link to between both main and sub tables,
creating a form/subform will be a piece of cake.
Here's a couple of tutorial links on forms/subforms
http://www.youtube.com/watch?v=_7ZtNyQ_fmc
http://www.youtube.com/watch?v=YZKN_-P6wck