Re: Help creating a database

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: lbrinkman (lbrinkman_at_rcn.com)
Date: 06/09/04 

Date: Wed, 9 Jun 2004 18:57:24 -0400

I agree with "Cranky": In the table ("tblCALLS"), I would set rigid criteria
for certain fields.
For example: LINE should have: (Between 1 and 5) or null.

I would also ADD another field to the TABLE: "DateOfCall" (as a Date type
field) with a default of =Date() in the field's properties, with a format of
"m/d/yyyy"; and the DateOfCall control "locked" on the data entry form,
forecolor of 128 (to let you know it's locked and cannot be changed). (I
would also split the caller's name into CallerFirstName and CallerLast Name,
but that is your call.)

I would also have COMBO BOXES for ALL of the controls (fields) on the FORM,
EXCEPT the "DateOfCall" field/control. You can use the Wizard to set up
lists for
Line, TakenBy, and CallStatus. (Be sure to set their "Limit To List"
properties to "Yes"; and possibly write code for each's "Not-In-List"
event.)

Personally, I would set up a lookup table for the "Company" field - I would
call the table tblCompany_Lookup. (The "Company" field in the table should
NOT allow duplicates!!)
I would then create a query, sorted by "Company" based on tblCompany_Lookup:
qryCompanyForCbo. Then create on the data entry form a combo box based on
that query. The tblCompany_Lookup table will have no records at first, but
the cboCompany combo box will have it's "Limit-To-List" property set to
"Yes" and have code for the "Not-In-List" event.
================
Private Sub cboCompany_NotInList(NewData As String, Response As Integer)
On Error GoTo YIKES
'
Dim rs As Recordset
Dim strMsg As String
Dim strTitle As String
Dim intUserResponse As Integer
'
strMsg = NewData & " is not on the list of Companies." & vbCr & "Would you
like to add it?"
strTitle = "Please Verify"
intUserResponse = MsgBox(sMsg, vbYesNo + vbDefaultButton2 + vbQuestion,
strTitle)
'
If intUserResponse = vbYes Then 'user wants to add a NEW company
    Set rs = CurrentDb.OpenRecordset("tblCompany_Lookup")
    With rs
        .AddNew
            ![Company] = NewData
        .Update
    End With
    Response = acDataErrAdded
    rs.Close
    Set rs = Nothing
    Set db = Nothing
Else 'user does NOT want to add a new company
    Response = acDataErrContinue 'same as: Response = 0
    Me![cboCompany].Undo
    Me![cboCompany] = Null
End If
'
'
'========= Error Handling ==============
Exit_YIKES:
    DoCmd.Hourglass False
    DoCmd.Echo True
    Exit Sub
'
'
YIKES:
    DoCmd.Hourglass False
    DoCmd.Echo True
    MsgBox Err.Description
    Resume Exit_YIKES
'
End Sub
================ ==============================
I would also add this code to that field/control on the form

Private Sub cboCompany_GotFocus()
    DoCmd.Requery "cboCompany"
End Sub
=======================================
YOUR QUESTION: Why do all this for the "Company" field in "tblCALLS" with
the control called "cboCompany" on the form, but whose Control Source
property is still the field
"Company" in tblCALLS?

Because data entry people make mistakes, even putting in the same data. For
example: WITHOUT such a combo box, various users could type in Verity
Software,
Verity Software, Inc. and Verity -- all for the SAME company. When you go to
print
out reports, this would not look good!! WITH such a combo box, as soon as
the user
typed in "ver", the "Verity Software, Inc." would appear. No errors, faster,
and no duplicates for the same company. When a customer from a NEW company
calls, as
you type in the company's name, it will NOT appear in full. When you hit the
enter key,
it will ask if you want to add that company name. If you answer "Yes", then
it will
be added to tblCompany_Lookup and thus will be on the list in the future.

---Phil Szlyk

"Cranky" <anonymous@discussions.microsoft.com> wrote in message
news:242EAAC7-D86C-4C99-B32A-510075D25399@microsoft.com...
> You should be able to do this with one table (with the fields you listed),
one data-entry form and then whatever reports you want. Use the form to
ensure you get good data (ie: only let a 1, 2, 3, 4 or 5 be entered the line
number) and you should be able to use one form to enter all the calls (far
easier than jumping between 5 similar forms!).
>
> Use the wizards to help you build your table, form, queries and reports.
>
>
>
> ----- Carolyn Schultze wrote: -----
>
> Hi,
>
> I'm having trouble and have been trying to create this database for
over a
> week now. I'm not very up to speed with creating databases and have
been
> trying to figure this out on my own. But I'm not doing a very good
job at
> it.
>
> I need to create a database for our receptionist.Currently she has to
record
> all calls on a *** of paper that has the following fields
>
> LINE / NAME / COMPANY / TAKEN BY / CALL STATUS
>
> Beneath each field would be the line that the customer is calling on
(1 thru
> 5), their name, their company name, the employee or sales person
taking the
> call, and the status of the call (if the sales person took the call
(check
> mark or "x"), if the call went to voicemail (vm), if the customer
hung up
> (hu), if the customer will call back (wcb), if the customer asked for
the
> sales persons cell phone (cp), or if if the message went to email
(em).
>
> At the end of the day, a count is done of how many calls were either
taken
> by each sales person and how many went to voicemail. All of the other
call
> statuses are not counted.
>
> Basically I need a database that will allow me to enter up to 5
records at
> once (because of the 5 line capability on the phone switchboard)
including
> all of the fields listed above. I would like this to look like a form
(not a
> spread***). I also need to be able to create a report at the end
of the
> day with the total call count (mentioned above).
>
> Any help would be GREATLY appreciated. I really would like to present
this
> to my supervisor when he comes back from his vacation.
>
>
> Best Regards,
> Carolyn
>
>
>

Quantcast