Customer Complaints 'Access' Database - See attached for your comments

[chergh]

Just finished the initial version of my customer complaints database so thought I would share it.

Sorry about the colours, but they are the company colours and easy to change.

If you find any faults please let me know.

[Douglas E. Purdy]

Originally Posted by chergh

Just finished the initial version of my customer complaints database so thought I would share it.

Sorry about the colours, but they are the company colours and easy to change.

If you find any faults please let me know.

Chergh,

Looking good! I suppose you will be working on a report format for sharing information from the database. When you do, it would be nice to see the finished product.

Doug

[chergh]

I will be getting round to adding reporting functions at some point in the next few months, certainly before my next surveillance audit anyway. Will also be another update coming a lot quicker seeing as I forgot to add a text box for the corrective action field.

Reports are likely to just be queries as MS Access reports are usually quite poor. The only reports I currently intend to implement are complaints by customer, complaints by product, average and max time till closure and complaints by cause category (which is also to be added in my next revision as I havn't included it in the version posted here).

If anyone has any requests for specific reports feel free to post them, I'm not going to make any promises to include them but if they are helpful to myself I shall.

[sbickley]

Originally Posted by chergh

Just finished the initial version of my customer complaints database so thought I would share it.

Sorry about the colours, but they are the company colours and easy to change.

If you find any faults please let me know.

Hi Chergh,

How did you add the file attachment option? Is that a built in option within access to add to a form control? If you could advise that would be great.

Thanks,
Scott

[chergh]

It's not a built in function of Access you need to build the form and functionality itself, but is not particularly hard to do.

First you need to create a table to contain the file information. My table simply consists of 3 fields "complaint_ref", "file_ref", and "date" which has a default value of "Date()"

Based on this table use the form wizard to create an "Autoform: Tabular". Save this form. The only field you need on this form is the "file_ref" field. You will also want to create buttons for "attach" and "view". Nothing is done with these yet.

On the form you want to use file attachment handler on create a subform, this is a button on the toolbar, and when the wizard appears select the "use an existing form" option then select the form you used previously.

Once this is done open up the properties list for the subform. You will want to link the "complaint_ref" field as the "Link Child Fields" option and the unique identifier on your master form as the "Link master fields" option.

Now you need some code to go behind the buttons. First save and then open the files I have attached. Goto access and click on the "modules" tab. Then click new and copy and paste the file text into the VB module window that has opened. Then save this with same file name as the text files have, minus the .txt extension i.e. "basShellAPI" and "browse button code".

Now right click the browse button and bring up its properties. Goto the event tab and locate the "on click" line for this. Where it displays "..." click here and select code builder. Paste the following into the codebuilder between the "private sub...." and "end sub" lines.

Dim strfilter As String
Dim strInputFileName As String

strfilter = ahtAddFilterItem(strfilter, "All Files (*.*)", "*.*")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strfilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)


Me.File_ref = strInputFileName

Now for the view button you need to call the windows api function ShellExecute. Right click on the view button select properties, then event and then the "..." button on the "on click" property. Once again post the following code between the "private sub..." and "end sub lines"

Dim intRetval As Integer
Dim strBuffer As String

intRetval = acbFindExecutable(Me.File_ref, ".", strBuffer)

If intRetval <= acbHinstanceErr Then
MsgBox "Unable to find executable. Error " & intRetval & "."
Else

intRetval = acb_apiShellExecute(Me.hWnd, "open", strBuffer, _
"" & Chr$(34) & Me.File_ref & Chr$(34) & "", "c:\", acbSW_SHOWMAXIMIZE)
If intRetval <= acbHinstanceErr Then
MsgBox "Unable to load application. Error " & intRetval & "."
End If
End If

And that is pretty much it. Any further questions just let me know and I will do what I can to help.

[tazng00se]

That's really a great idea. We have an Access wiz in one of our departments. She is moving all paper tracking of suppliers, PO/PR and such into a database.

If your employer doesn't mind...wouldn't it be more helpful (at a glance) to see all of your yes/no fields as radio buttons instead of pulldowns. I know it would be easier to populate the records as well.

I like the "upload attachment" feature as well.

[Greg B]

Cherg,

Thanks for the DB it looks good but I will have to review it a bit more. And NOW I also know where I got the Audit database because there could not be two people that use the same backgorund colour. I'm still evaluatiung that also.

Well Done

[chergh]

Well this database got canned due to a period of extended absence for myself and something else had been introduced in this time. Given the amount of work I put into this I would be gutted to see it all go to waste so hopefully some people here will be able to get some use for it.

This particular version of the db has NC/PA, customer complaints and an internal audit module. There is also some code that exports metrics to excel and draws up some nice graphs for you.

You will probably need some help getting it set up initially if you want to use it so feel free to ask and will do my best to answer your questions.

Please also feel free to scavenge anything you want from it yourself.

Hope it helps someone out there.

P.S. Can a mod remove the attached file from the first post on this thread. Thanks.