I am in the process of creating a ticketing system in MS Access that will allow employees to track their interaction with clients (create a ticket for each problem/issue that the client has). I already have a form created so that they can enter new tickets when required. I am now looking to create a form so that employees can view all tickets in a list view (similar to a table view) and they can apply filters to this list.

The attachment shows how I would like the form to look (Taken from a screenshot of a similar system, I've changed the info/titles to match my database).

I would also like have the functionality to select a record within the list (highlight it) and then click a button to edit that record (Essentially just taking the employee back to the entry form).

I've seen a few different methods on how to create these types of forms (the most similar to what I'm looking for appears to be the Multiple Items form)

Additional Question:
Is it possible to have a maximum of 20 records per form page, it would be more managable without having a massive of every record on the form.


I am just wondering the best way that I can create this type of form, I figured that I should ask the experts for the most efficient / best way before began to put a great deal of work into creating this.

Thanks for any help!

Dave

This post has been edited by dave_access: Jan 4 2011, 12:57 PM

Hi Dave,


What do you mean by this? If you are using a subform, which is what it appears to be in your screenshots, then you can resize the subform container to only display as much records as you want to show on the form. If you meant that you only want to show a total of 20 records, no matter what, you might modify the query of the form to use the TOP predicate to limit the number of records returned.

Just my 2 cents...

JOPO - just one person's opinion ...

The "best" way is the one that most helps the folks who use the application get their REAL job done.

I like the thought you've already put into your description. Now, tell us a bit more about the skills and experience of the folks who'll be using this.

That's exactly what I meant by the best way, making it as easy and fast as possible for the user. I'm not quite sure what you mean when you say REAL job, part of their real job requires them to review these tickets and edit them accordingly. I've chosen to do the form like this for the exact reason you've stated in your second line, they have a good skill set for using database applications. The structure/layout of the form in the screenshot will exactly match another system already being used by the employees.

Also, thanks for the help DB Guy, thats exactly what I meant by that additional question.

Thanks,

Dave

Hey DB Guy, based on the example I have.. I have 3 dropdowns in my main form that I want to use to filter my subform, each of these dropdowns are from a different table. What should I put as the recordsource for my main form?

Also, the subform is comprised of fields from multiple tables.

Should I create a query to list all of these tables for my recordsource?

Thanks again

Dave

Hi Dave,

From what I can see in your screenshot, I don't think you need any Record Source for the main form. You can have different table sources for your comboboxes but nothing in their Control Source properties.

For your subform, you probably have to use a query that filters the records using those comboboxes as the criteria.

Hope that helps...

If I have folks who've been using Access for a dozen years, who understand relational database design, and who do NOT need their application "user-proofed", I'll build something quite different than I'd build for someone (or several someones) who know nothing about Access, relational databases, tables, etc., and against whom I REALLY need to provide some user-proofing, lest they destroy both the application and data.

That's what I was referring to...

Thanks so much for all of the help DB Guy, you always answer my questions perfectly and allow me to move forward. I just have two more questios, I hope I'm not asking too many questions.

I've taken your advice and now have a form with no record source, three combo boxes that are unbound and seem to be filtering the subform properly. One concern that I've noticed is related to a field that I have called "TicketTime" (You actually helped me earlier by giving me advice to put Now() as the default value). When I make a combo box selection, then press a button called Search (Requiries the subform), it puts the current time into the TicketTime field. I've tried locking and disabling this field without any luck. Do you have any suggestions?

Also, one last question (I won't bother you anymore lol).. I would not like the users to be able to edit information directly in the subform (as they are not advanced users, I would like them to use the form that I have created for this), I would only like them to be able to make a record selection then press an Edit button. Would simply locking all fields in the subform meet this objective.

Thanks again.

Dave

Ohh I see Jeff, in that case.. they know very little to nothing about Access databases and design. They are capable of using interfaces relatively well, but nothing to do with the structure of a database. It will need to be user-proofed for sure.

Dave

I don't think you are.



Yes, and this is related to the one below. Try setting your subform's AllowAdditions property to "No."



First, I suggest that you also set the AllowEdits property of the subform to "No" and then add a command button to the side of the record with the following code in its Click event:

DoCmd.OpenForm "FormName", , , "ID = " & Me.ID

Hope that helps...

Hi DB Guy, thanks again for the help.

I can't seem to get the filters working for the subform.

In the recordsource for the subform, I have a query built with all of the fields in the subform. I have also put in the criteria for Status, Applicant (Company), and Validator (Employee) referencing the combo boxes on the main form. (ex. Status criteria in subform is: [Forms]![frmMAINview]![cboVIEWstatus] )

This doesnt seem to be working. It will not even work if I only do it to one field, i.e. Status field.

Am I doing this correctly?

Dave

In that case, "easy ... is HARD!"

You will need to do much more work to ensure that the users never see the underlying bones of your database, and only work via the user interaces you create/manage.

I've tried the following code, which seems like it should do be able to do what I'm looking for. However, I receive a message "Data type mismatch" and then in the debugger it sends me to the line of code: Me.frmSUBview.Form.FilterOn = True (near the end of the code listed below)

Any ideas? (If there is an easier way to do this please let me know)


Option Compare Database
Option Explicit

Private Sub cboVIEWstatus_AfterUpdate()
Call RunFilter
End Sub

Private Sub cboVIEWapplicant_AfterUpdate()
Call RunFilter
End Sub

Private Sub cboVIEWvalidator_AfterUpdate()
Call RunFilter
End Sub

Private Sub Form_Load()
Call RunFilter
End Sub

Private Sub RunFilter()

Dim strFilter As String
Dim bFilter As Boolean

bFilter = False
strFilter = ""


If Nz(Me.cboVIEWStatus, "<All>") > "<All>" Then 'Status
If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
strFilter = strFilter & "TicStatus = '" & Me.cboVIEWStatus & "'"
bFilter = True
End If


If Nz(Me.cboVIEWapplicant, "<All>") > "<All>" Then 'Applicant
If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
strFilter = strFilter & "TicApplicant = '" & Me.cboVIEWapplicant & "'"
bFilter = True
End If


If Nz(Me.cboVIEWvalidator, "<All>") > "<All>" Then 'Validator
If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
strFilter = strFilter & "ticValidator = '" & Me.cboVIEWvalidator & "'"
bFilter = True
End If


If bFilter Then
Me.frmSUBview.Form.OrderBy = ""
Me.frmSUBview.Form.Filter = strFilter
Me.frmSUBview.Form.FilterOn = True
Else
Me.frmSUBview.Form.FilterOn = False
End If



End Sub

Hi Dave,


Your screenshot doesn't give me much to go on. What is in the code for your Search button? I also need to see the SQL for your query.

My thoughts are simply, the query should have a WHERE condition that points to the unbound comboxes on the main form, and the button should requery the subform.

Just my 2 cents...

Private Sub btnSearch_Click()
Me.frmSUBview.Requery
End Sub


Record Source for subform (frmSUBview):
SELECT tblTracker.pkTicketID, tblTracker.DocumentID, tblTracker.UnregisteredApplicant, tblTracker.TicketTime, tblTracker.ResolvedTime, tblTracker.ticSystem, tblTracker.ticProblem, tblTracker.ticStatus, tblTracker.ticValidator, tblTracker.ticResponsibleValidator, tblTracker.ticApplicant, tblTracker.ticUser, tblTracker.ticInteraction FROM tblTracker;


For the ticStatus, ticApplicant, and ticValidator fields within this, I have under their conditions the following:

[Forms]![frmMAINview]![cboStatus]
[Forms]![frmMAINview]![cboApplicant]
[Forms]![frmMAINview]![cboValidator]

This is what I had interpreted your initial recommendation to be, althought I don't think the code/reasoning that I put in is correct. Thanks for the help

Dave

Hi Dave,

Try changing your Subform's Record Source to the following SQL:

SELECT pkTicketID, DocumentID, UnregisteredApplicant, TicketTime, ResolvedTime, ticSystem, ticProblem, ticStatus, ticValidator, ticResponsibleValidator, ticApplicant, ticUser, ticInteraction
FROM tblTracker
WHERE (ticStatus = Forms!frmMAINView.cboStatus OR Forms!frmMAINView.cboStatus Is Null) AND (ticApplicant = Forms!frmMAINView.cboApplicant OR Forms!frmMAINView.cboApplicant Is Null) AND (ticValidator = Forms!frmMAINView.cboValidator OR Forms!frmMAINView.cboValidator Is Null)

Hope that helps...

Hi DBguy, sorry for the delayed response.. I was away the last few days. I tried this code that you've provided me and when I click the "Submit" button that requeries the subform, it fills all fields in the subform as #Name?. I've attached a copy of the database so you can see the issue.

Thanks again for all of your help.

Dave

Hi,

Thanks for posting a copy of your db. Unfortunately, I cannot test it because the table is empty. I tried to enter some records but I just get errors because I really don't know what types of values I should put in each field.

Sorry.

Hey DB Guy, sorry about that.. I thought I included a few test records. Here's a copy with test records.

Hi Dave,

Thanks for posting an updated version. Check out the attached modified db and let me know if you have any questions.

Hope that helps...