Question Help With Search Results Being Clickable and Opening up a Form

[j.razz1]

Thanks a lot! I'll check this out once I get back to the office and see what I can glean from it. Hopefully it will help clear some things up as I know what I need (end result) but I am foggy about how to make an efficient means to that end. This may just be the thing I need to help put 2 and 2 together in addition to the help I have already received on this forum.

Also, I don't see a need to have CaseOpen and CaseClosed in your Cases table. They're mutually exclusive. If a case is not open then it is closed. You only need one boolean (Yes/No) field for this.

These are date fields too. The supervisors will need to be able to know opened dates (this gives a time reference for when the first response would need to happen by) and they need to know the closed day too for reporting purposes (x number of cases were closed within this date range- usually month by month. Same goes for opened cases as well).

Thanks again to both of you. I was getting bogged down in all the reading and I was feeling lost in all the options and terms... this should be a breath of fresh air when I get back to the office!

j razz

 

[Beetle]

These are date fields too.

Ahh. In that case disregard my previous comment.

 

[j.razz1]

Okay, so if I'm understanding your example and I wanted to add a subform showing assigned case managers, I would need to do the following:

1. Set up a new table (EmployeeTable) with a list of all employees.
1a. In that table I would rename the ID field with the autonumber attribute to EmployeeID for clarity sake(this would be the primary key).

2. Create a subform with an employee combo box that is based off the EmployeeTable.

(The next step is foggy to me as I don't know if it is needed or not, or exactly how to implement it.)

3. I would set up a Query that pulls the data from the EmployeeTable and populate the combo box and date stamp the employee's modification.

This employee would always be tied to this case, but if that employee was terminated or quit, then a new employee (or existing) would take over those cases. So maybe #3 above would be needed as it would allow for a running record of what employee did what to each case.


Also, in your example database I changed the phone number field to CaseID field and subsequently changed the phone column in the tblClients table to CaseID and changed it from text to number. Once I did that I got an error where it was asking for a reference for tblClients.Phone. I went into the qryClients query and changed the phone column to CaseID as well. That resolved the error. (before seeing this database, I did not understand how to change references- this is very beneficial)

In delving around in the database, I went into relationships and saw CaseID was already added as an autonumber under tblCases. Will my addition (changing the phone field to the CaseID field in tblClients) conflict with this? This is a field that they will need to be able to enter the Case ID and it will be tied to the client forever (it does not change as it is the client's master case number). They will also need to be able to search by this number as well as the name.

I assume I can replicate what you did with the name combo box, but just set it up so that you can select the CaseID from it right?

Thanks again to both of you for the help. I've only been learning about Access for 2 days and this type of one-on-one interaction really speeds up the learning process!

j razz

 

[Beetle]

1. Set up a new table (EmployeeTable) with a list of all employees.
1a. In that table I would rename the ID field with the autonumber attribute to EmployeeID for clarity sake(this would be the primary key).

So far you're on the right track. However, if a Case can (over time) be managed by more than one Employee, and an Employee can manage more than one Case (which, I would think, would be true in your application) then you have a many-to-many relationship, so you need a junction table. Let's say you name it tblCaseManagers. This table would have it's own PK field, like CaseEmpID, plus two Foreign Key fields, CaseID and EmployeeID (each a FK to the corresponding table). It would also include any other fields that are specific to the relationship. By that I mean any other fields that are necessary to describe each instance of a given Employees management of a given Case (if that makes sense). This could be something like the date they were assigned to the case, etc.

There is also another possible scenario that might work here as well. That is, your Requests table might be expanded a bit to become more of a CaseActions table. This table would then track the following;

• The type of Action (1st Request, 2nd Request, etc.)
• The Employee assigned to this Action (so you need an EmpoyeeID field)
• The date the Action took place
• Any comments the Employee might need to enter for this Action
• The result of the Action (Approved, etc.)

In this scenario you wouldn't need the junction table, just an EmpoyeeID field added to the Requests (Actions) table as a Foreign Key, plus any other additional fields that might be necessary. The current Case Manager would be whichever Employee was involved with the most recent Action on a Case.

You would have to decide which of these scenarios might work best for your situation.

2. Create a subform with an employee combo box that is based off the EmployeeTable.

Putting the cart before the horse here. First you have to decide how you're going to handle the relationship between Employees and Cases. In scenario 1 above, the additional subform would be based on the junction table. In scenario 2 you would just be expanding the existing subform.

3. I would set up a Query that pulls the data from the EmployeeTable and populate the combo box

Depends on what you mean by "set up a Query". You don't need to (and normally wouldn't) define a saved query (meaning a query that you create and save in your Query objects) to populate a combo box. In most cases the query exists only in the Row Source of the combo box. Combo boxes (and list boxes) have a few important properties you should be aware of;

Control Source - this defines what field in the underlying Record Source the combo/list box is bound to (if it is bound - it could be unbound as well, which means it has no Control Source, or the Control Source is some type of expression and not a field, although an expression in the Control Source would typically be used in a text box, not a combo box). If it has a field as its Control Source then any value that is selected in the combo/list box will be stored in that field. This property applies to several other types of controls as well (text boxes, check boxes, etc.).

Row Source - this defines what values are displayed in the combo/list box. It can be a Value List that you define yourself, or it can be a query that returns values from a table.

Bound Column - if the Row Source has more than one column (field) then this defines which of those columns is bound to the combo/list box. A combo/list box can have many displayed columns, but only one bound column.

Column Count - this defines how many columns are displayed in the combo/list box.

Column Widths - this defines the width of each column. In many cases the first column (which is often a PK value) will be hidden by setting the width of the first column to 0. You can see that in the example db.

Back to creating Row Source queries for combo/list boxes. If you open the property sheet for a combo/list box and go to the Data tab you will see the Row Source property. If you click the build button (...) at the right of that property it will open the query design grid (same as when designing any other query). After you have added the appropriate tables(s) and selected the columns you want for your combo/list box, you close the query designer (you do not save it). When you close it you will get a message saying "blah, blah, blah...do you want to update the property?". Click Yes and the query will be saved only in the Row Source of the combo/list box, not in your Query objects list (this way you don't clutter up your objects list with a bunch of combo box queries).

I'm getting off track a bit I guess, so back to your questions.

Also, in your example database I changed the phone number field to CaseID field and subsequently changed the phone column in the tblClients table to CaseID and changed it from text to number.

Ummm.....huh?

I'm a bit lost as to why you would change the Phone field to CaseID, or what you're trying to accomplish by doing so. Maybe you can elaborate on your reasoning here?

Meanwhile, here are a few more terminology tips that may be helpful to know as you try to get a handle on all of this.

Tables and Queries have Fields (not cells, in case you're coming from Excel. Calling them cells around here will result in a quick smack down ).

Forms and reports have Controls (text boxes, labels, combo/list boxes, check boxes, etc. - these are Controls).

Forms and reports have Record Sources.This is the query or table that the form/report is base on.

Controls (some - not all) have Row Sources. As mentioned above with combo/list boxes, this defines what values are displayed in the Control.

Time to sign off. Hopefully I haven't confused you even more.

 

[j.razz1]

Quote:
Also, in your example database I changed the phone number field to CaseID field and subsequently changed the phone column in the tblClients table to CaseID and changed it from text to number.
Ummm.....huh?

I'm a bit lost as to why you would change the Phone field to CaseID, or what you're trying to accomplish by doing so. Maybe you can elaborate on your reasoning here?

We don't need a phone field, but the supervisors will be given cases that have preassigned Case ID's from another system. This Case ID will need to be entered into the database manually and from that point forward stick with the client as that Case ID is how they are known in the other system. (We could get a phone call asking about Case ID #3098547. We would need to pull up that Case ID and see all the history attached to that client. We could also get a call from the client themselves and have to look up the same information based on their first and last name. Hopefully that makes sense).

In regards to the other information, thanks for clarifying and giving me some pointers in some places and some hands on help in others I did read in the book link from the first reply here that the form is the last thing you should develop. I will see what I can accomplish today.

j razz

 

[Beetle]

We don't need a phone field, but the supervisors will be given cases that have preassigned Case ID's from another system. This Case ID will need to be entered into the database manually and from that point forward stick with the client as that Case ID is how they are known in the other system. (We could get a phone call asking about Case ID #3098547.

I had already assumed, and accounted for, that scenario in the example. The field is CaseNumber. CaseID is the PK (which is Autonumber), CaseNumber is where you would enter the real world case number.

 

[cpberg1]

J Razz

Seems like you've been making some really good progress! Now that you have a table Cases and a field in that table CaseID (PK) that is in many other tables as a CaseID (FK) you shouldn't use it for another application such as manually typing in a previously assigned CaseID from another database.

Worst case access will assume that the CaseID field you just created should be a foreign key and it will cause errors because it will not find matches in your Cases table. Best case if you manage all your relationships and joins very carefully it will become confusing for you later when you are designing forms and such.

Suggest amending the title of that field to PreviousCaseID or similar so that it is less confusing. Also you need to consider carefully which table you put this field in. What entity is the PreviousCaseID a description of? Does it describe an employee?

Also during designing you'll find it better to delete a field from a table and add a new one instead of renaming an existing, just so you don't retain any inappropriate properties.

C

 

[j.razz1]

I will have to go back and look at how that was implemented in your example as I didn't connect all the dots.

I was attempting to add a combobox for employees (we call them advocates for this program). Long story short, I got it added using the 2nd method you listed above (additional field in the sfrmRequests form). I made an additional table called "tbleRequestAdvocates" with 2 columns: AdvocateID and AdvocateName. I linked this up in Relationships to the tblRequests table. I set "AdvocateID" in tbleRequestAdvocates as the primary key and linked it to "AdvocateID" in tblRequests.

It works! But there is one caveat. It does not show the employee's name. It shows the AdvocateID (autonumber).

So, I looked at what you did with the cboRequestType and saw this:
SELECT tblRequestTypes.RequestTypeID, tblRequestTypes.Description FROM tblRequestTypes;

With that, I made a similar line of code and plugged it in under cboAdvocate (combobox for advocates). Still the combobox only shows the autonumber. Here is what I changed: SELECT tblRequestAdvocates.AdvocateID, tblRequestAdvocates.AdvocateName FROM tblRequestAdvocates;

It's pulling from the right table as it shows the proper autonumber options if I add or take away from the tblRequestAdvocates table. (as an aside, I also updated qryRequests to account for the change I made).

Any ideas where I am going wrong on that?

Thanks again for the help!

j razz

 

[Beetle]

Any ideas where I am going wrong on that?

Remember when I said there are several important combo/list box properties you should be aware of? The Column Count and Column Width properties will get you to the promised land here.

@cpberg1

Beat me to it on my Ipad. Hi Beetle!

BTW - I meant to respond to your greeting earlier, but didn't, so greetings!

Didn't mean to take over the thread or anything, just throwing a few more thoughts.

 

[j.razz1]

I found the places you were talking about and made the changes (I don't quiet understand how they relate (column width seems like it would be relational to size and not affect what is shown) but I will look into it and figure it out. Thanks for the pointer! With both of your help, I now have a working database and understand a ton more than what I did prior to two days ago (which was nothing)!

I have attached two pictures showing the frmClients form. One is of the design view and the other, the form view. I see that you have a reference in design view to CaseID, but it does not show on the Form view. It is also an autonumber field (it needs to be a regular number field that the employee can fill in once and be retained with the client's records. The phone number field seemed easy enough to change and make that relevant. However, I am concerned about both of you all's concerns but I want to make sure we are both talking about the Case ID that is given to us by a referring agency. We can't just auto populate it. We have to type it in as it is given to us and then retain it from there on out. Even though it is called Case ID, it is not tied to the case- it is tied to the client. I hope that makes sense and clarifies a few things.

If I am right and the terms got confusing, would I then just need to rename the ClientID field name in tblClients to something other than ClientID to take care of the concern of duplicate names?

Here is a link to the two images in a zip file: http://ge.tt/9NPsYYH/v/0?c

As you can see, I have added a few more tables to add some additional functionality to the database. The example database that was provided was very, very helpful and it helped me to visualize some concepts I couldn't see by simply reading about it. Thanks again to both of you guys. Now I have to set up reports and separate out the database so that it can be housed on the server with the front end on employee computers.

j razz

 

[Beetle]

You;

I have attached two pictures showing the frmClients form. One is of the design view and the other, the form view. I see that you have a reference in design view to CaseID, but it does not show on the Form view. It is also an autonumber field (it needs to be a regular number field that the employee can fill in once and be retained with the client's records.

Me, from post #20

Another note. The main form (frmClients) has two subforms, one for Cases and one for Requests. When you select a Case in the first subform the related Requests are displayed in the second subform. This method uses a hidden text box on the main form that holds the CaseID from the first subform and is referenced in the Master/Child link of the second subform.

What you're seeing in design view is that hidden text box, and no, it's not an "autonumber field", it is a calculated Control (text box) with an expression as its Control Source. It's only purpose is to store the CaseID value from the selected record in the first subform so the correct related records (Requests) can be displayed in the second subform. A user would never interact with this control. They should not even know it's there, which is why it is hidden.

I want to make sure we are both talking about the Case ID that is given to us by a referring agency. We can't just auto populate it. We have to type it in as it is given to us and then retain it from there on out. Even though it is called Case ID, it is not tied to the case- it is tied to the client. I hope that makes sense and clarifies a few things.

If this Case ID is tied only to the Client, and not related to an actual Case in any way, then it should just be a field in your Clients table and not related to any other table. It really doesn't matter what you name it as long as the name makes sense to you, so don't get hung up on naming the field CaseID just because that's what you normally call it. As Chris said, this would just be confusing because you already have a CaseID field in another table. Name it ClientCaseNumber or something. Now, on your form, you can still put Case ID in the label for that text box, so to the user it will still appear to be named Case ID. You can put whatever text you want in a label, this has no bearing or affect on anything else in your application. Don't base your field names on what you think the users will want to see, because they will never see the actual names of the fields, only the labels you put on your forms.

If I am right and the terms got confusing, would I then just need to rename the ClientID field name in tblClients to something other than ClientID to take care of the concern of duplicate names?

We were talking about a new field for your Case ID, now you're talking about renaming the ClientID field? You're losing me again.

 

[j.razz1]

Sorry about that- I meant CaseID, not ClientID.

Thanks for the clarification and for helping me to understand naming doesn't matter to the user- only me.

One other question... and this was my original purpose for posting here to begin with; I need to make search form that will pull from either qryClients or tbleClients (I would think the query but I don't know enough to know why I wouldn't use the table). The results would need to populate in the frmClients form.

I have been trying to dissect your "Select a Client" combobox on the frmClients form to see how it populates the subforms by the name selected. I wouldn't need a search page if I could add a Last name search box and a CaseID search box to the header on the frmClients form that functioned similarly to your "Select a Client" combobox. I think either way would be good but the second option would be one less screen someone would have to use.

I see that you used parent/child relationships with the sub forms. What I don't see, is how I would do that with an input field (where the user could type in the last name or CaseID).

Thanks again guys!

j razz

 

[Beetle]

I wouldn't need a search page if I could add a Last name search box and a CaseID search box to the header on the frmClients form that functioned similarly to your "Select a Client" combobox. I think either way would be good but the second option would be one less screen someone would have to use.

This would not be difficult (relatively speaking). You would use an unbound text box where the user could enter a value. The code would be similar to the code in the After Update event of the combo box, but you would be searching the LastName field instead of the ClientID field, and you would want an extra line of code to display a message box in case the name entered wasn't found. As far as searching for the CaseID, I'm not sure if you mean the new Case ID you've been talking about that is a Client attribute, or the CaseID PK value of the Cases table.

I see that you used parent/child relationships with the sub forms. What I don't see, is how I would do that with an input field (where the user could type in the last name or CaseID).

If you're talking about the Master/Child link of the Main form/subform, that would have nothing to do with any unbound search box you might have on your form.

 

[j.razz1]

You would use an unbound text box where the user could enter a value.

I couldn't get it figured out so I resorted to a Combobox. It works fine and displays the results needed by either typing the CaseID number (the one I changed from the phone field) or by drilling down through the dropdown.

However, I do have a question about this. In the "Select A Client" box, cboSelectClientID when in form view, if you clear out the field, you get a runtime error as it seemingly has to have something in the combo box. The same is true for the one I modeled after it. Is there a way to clear them out or at the very least, to avoid the runtime error pop up?

The next thing I am going to attempt is to setup caseload page where it will be filtered by the advocate who has been assigned to the case.

I envision it would have the following parts:

Switchboard: Advocate Case Load
frmAdvocateCaseLoad
CboAdvocateSearch (I already have a ComboBox named AdvocateID in the sfrmRequests form)
sfrmCaseLoad: Once an advocate is selected, it would show all cases assigned to that advocate.
Sort by: Open Date(Descending/Ascending)/Closed Date(Descending/Ascending)

I think it would need a query set up to pull from the tables the following:
Client Name (first and last), Case ID (the one I made from the phone field), Open date, Closed date.

Somehow, I would need for the Case ID to be clickable and take them to the frmClients form where they can update that client's records.

I am going to try my hand at setting up what I described above. I think I may have a chance at everything but the cilckable (hyperlink) Case ID... but I am rather optimistic

Thanks again for the help and for guiding me in this.

j razz