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.

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

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.

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

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.

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.

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.
:agree1:
Well Done

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.

P.S. Can a mod remove the attached file from the first post on this thread. Thanks.Done..Thanks for sharing the latest version of the file. :applause:

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.

Thank you very much for sharing this and for your recent assistance on the Access database I am working on. This could not have come at a better time, as I am trying to show Management how powerful a database can be and how it can revolutionise our internal working systems, which are sooooo dated it is untrue!

I am working on a database to log and record the results of product coonformance audits, so your attached database that incorporates customer complaints etc.... has given me some good ideas!

I will keep you posted in my progress, and would like to once again say a BIG thank you, your assistance is much appreciated! :thanks:

:applause: very good job. but if you can add 5WHY or D&W of GM will be best.


thank you.

Just a quick comment while I am looking through the attachment.

I cannot work out how to input a month in the audit scheduler? I can see the format is MMMM but cannot seem to get it to accept any obvious values.

Perhaps have this field as an independant look up or have it look up from a table? I would say the same about the audit status. Give the user just 2-3 options on what the status would be. Make querying the data easier later on.

Just a bit of a pointer.

I should have attached a warning that some of the database is extremely ugly in the way I cobbled it together.

The date issue you are having is solved by inputting the date in the format of dd/mm/yy let the database convert it to the mmmm format.

Dates are handled by Excel and Access as numbers. In order to spot overdue audits then they have to be input in a format that allows it to be given a numerical value. Putting the date in dd/mm/yy and letting the table display it as mmmm format was a work around until I found a solution, but with my absence and the subsequent canning of the DB this never happened.

Audit status should have a choice of "planned" "audited" and "closed". I did clean my companies data out of the DB so may have accidently deleted the options from the tbl_status table, you will need to put the status options I gave above in there as otherwise some of the queries etc may not work.

There is also a mistake in the "auto excel stuff" module. You will need to delete the line ".Font.FontStyle.verdana" to get it to work this is on line 165 of the mentioned module.

Well this database got canned
****, Cherg! There's a lot of work gone into this. It's a great database and I'm confident it will shortcut lots of work I would otherwise have stumbled through. :notme: BTW - the status table is fine in the download version.

Hello Chergh,

where could I find the lastest version of your Database, I would be very interested

Thanx 4 now

Hello Chergh,

where could I find the lastest version of your Database, I would be very interested

Thanx 4 now

Welcome to the cove. Unfortunately, Chergh is no longer on these forums, so the DB shown in post 8 is probably the most upto date version you are going to get. I found this ver useful to develop a system around.

Chances are you won't want a carbon copy of that DB but you can use it to get what you want out of it. Of course, it all depends on how proficient you are on Access? If you need any help just give us a shout and we can try to help you out.