Full Version: Looking For Best Way To List Records
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
dave_access
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
theDBguy
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...
Jeff B.
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.
dave_access
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
dave_access
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
theDBguy
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...
Jeff B.
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...
dave_access
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
dave_access
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
theDBguy
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...
dave_access
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
Jeff B.
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.
dave_access
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
theDBguy
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...
dave_access
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
theDBguy
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...
dave_access
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
theDBguy
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.
dave_access
Hey DB Guy, sorry about that.. I thought I included a few test records. Here's a copy with test records.
theDBguy
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...
dave_access
This works perfectly, it is exactly what I was looking for. You have no idea how much I appreciate all of the help you've given me.

Thanks again!

Dave
theDBguy
Hi Dave,

yw.gif

Glad we could help. Good luck with your project.
dave_access
Now that the Main form and Sub form are working, I just have one final question that should help me to complete the project, which is related to a question I asked earlier in this thread. I was wondering the code to use in a command button so that when I selected a record in a subform, I could click a button in the main form so that it opened a form based on that record. I'm not sure if I described this properly earlier (The subform will be in a datasheet view), here was your response:

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

When you say to the side of the record, do you mean in the subform? Is it possible to have the this command button in the main form so that it opens another form (To view all of the information contained in the record) based on the record selected in the subform?

Thanks again,

Dave
theDBguy
Hi Dave,

Yes, I think that's possible. You would have to modify the code a bit though. For example:

DoCmd.OpenForm "FormName", , , "[ID]=" & Me.SubformContainerName.Form!ID

Hope that helps...
dave_access
Hi DB guy

This is perfect, everything is really coming together thanks to you. I ran into a minor issue with the subform that I overlooked, with some general guidance I should be able to move forward. I have the subform record source being one table, tblTracker. Some of the fields are unique to this table, however, most are related to other tables within the database. For instance, there is a table for "Validators" (employees), the PK autonumber in tblValidators is linked to a FK in the tblTracker to show the validator that was assigned to a record in tblTracker.

The issue that I'm having is that when I view the main form with subform, the subform only shows the FK numerical field associated with that records. I thought that there would be a way so that it displayed the related record in the other table (i.e. it would show the validators name from tblValidators rather than the FK in tblTracker).

Should I recreate the subform recordsource query to contain more tables with their corresponding fields rahter than just having tblTracker?

I have attached a screenshot to make the situation more clear.

Thanks,

Dave
theDBguy
Hi Dave,

No, you wouldn't necessarily need to create a query for your subform to include the other tables because the more tables you add to a query, the more of a possibility that your form will become "read-only." Instead, I would recommend converting the Textbox for the FK into a Combobox with a RowSource that points to the related table.

Hope that helps...
dave_access
I have everything working perfectly now, thanks so much for all of the help.. I'm glad I asked you about the last issue, I would have been going in circles for a while trying to figure that out.

Dave
theDBguy
Hi Dave,

Once again...

yw.gif

Glad we were able to help you. Good luck!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.