Help Desk database 2

[Rustaferd]

I am a beginner with Access. The best way to learn is to find a project and work through the process. I did this with a Help Desk database I created.

Now I am working on redoing the access database I created on the fly for the Tech Support logs. I've got a spreadsheet that I have the breakdown of all the tables I think I will need. I have a spreadsheet breaking down the forms as well.

I could use some help later in making sure I am linking or joining the tables appropriately. But before I get ahead of myself there are a couple of questions that have come up that I should get answered before moving forward.

I have created a table called RootCause.
I have also created a table called CallResolution.

What I would like to do whenever I open a new issue I have to assign a root cause to the issue from the dropdown menu (linked to RootCause table). When I select this root cause, I want a list (history) of all the resolutions made for this root cause.

I don't know if this is clear but I am not sure of the best way to do this. Do I need to link the RootCauseName of the RootCause table or the RootCauseID of that table to the CallResolutions table so that whichever root cause is selected for the issue, a list of call resolutions linked to the RootCause table will be listed. From that list I can choose which Call Resolution fits the call issue and select that for my call record - or I can simply add to the Call Resolution.

Second, I would like to add images or files to a database call record so that the record will have an image or file that is related to the issue giving a deeper call record. Any idea on how to do this? What kind of table do I need?

I hope this is clear enough. I hope there is someone out there that can help me or direct me.

Thanks,
Russ
russb@lominger.com
952-345-3650

 

[Rustaferd]

Paul,

Just so I understand, when you say go to the Form module, are you talking about selecting the Form Object where all the forms reside? If so, are you suggesting I open each form and look for fields that may have EditCaller_Click?

I don't suppose Access has any type of search tool?

I like your suggestion to try creating a seperate macro to see if this reacts differently.

Thanks for the tips and suggestions,
Russ

 

[PaulBricker]

Sorry Russ, I've been out of the office all day. I saw that you had a Not In List Event Procedure in the Status Properties. You can open that and look there and then close each module window separately by clicking on the inside X button (Access opens all the modules when it opens one so by closing the individual module windows, you can check each on for the problem. What makes me think that's not the issue is because you lose that error if you eliminate the call to the Macro. Just a thought.

Paul

 

[Rustaferd]

I checked and couldn't find this in the status properties. It may be an issue with the macro, but not sure.

SetCallDateOpened
IsNull([CallDateOpened]) SetValue
... Requery
... SetValue

SetCallDateClosedReopened
([CallStatusID]=2) SetValue
[CallStatusID]=2) And [CallDateReopened] Is Not Null

([CallStatusID]=3) And [CallDateClosed] Is Not Null

([CallStatusID]=1) And [CallDateClosed] Is Not Null

...

([CallStatusID]=3) And [CallDateClosed]=Null

Just an example of the macro.

What I really need is an example of what someone else might do in this case, doesn't even have to be the same. I'm learning by tearing apart a database so that I can picture what is is doing and why. Since I'm not a programmer this seems to be the best way to get my mind wrapped around the issue and it's solutions.

Thanks again for your thoughts and suggestions!

 

[PaulBricker]

Here's what happens. When someone wants to run an event procedure, they click on the line next to the Event they want the code for. Then they click on the button with the three dots, select Code Builder and OK. What Access does behind the scenes is create a private subroutine that acts as the event. You can do the same thing manually by opening a Form in Design View and then going to View...Code. This opens the Form module. If you don't have any events built, the module will be blank. You can simply insert something like this

Private Sub ControlName_AfterUpdate()


End Sub

Essentially you are doing the same thing Access would do by clicking all those buttons we mentioned earlier. You still have to go back to the properties box and insert Event Procedure on the line next to the Event inorder for the code to run at the proper time, but it is very possible to have two events of the same name if you don't pay attention to what's going on. If Access runs across that, you will get the kind of error message you got. But, the bit caveat here is that if you get rid of the Macro name, the problem disappears and there doesn't seem to be anything in the macro that would cause this error. The long and short of it is, I don't know why you are getting the error. Did you try creating a separate Macro and see how that performed? That will tell you alot. Don't worry about the other error at this point. See if changing the macro does any good.

Paul

 

[Rustaferd]

Paul,
Thanks for the explanation. The more I work through these problems...the more I am understanding how the software works and with your kind and generous help I have gained a lot!

I did create a new macro and put only that code which is what I need and pointed to the new test macro and still I got the same error message.

I am sure that it is something I am not seeing and I take the blame for this but it would be nice to nail down where this is. Anyway I may have found an alternative way of making this work. I found out how I can create a datestamp and so if I create 3 rado buttons which when selected will stamp the specified fields,I may get the results I want.

Down the road, I may come back to this issue to see if I can clear up these questions.

Thanks again,

Russ