Listing data below form (1 Viewer)

[Dave 350z]

Hey folks,
First time posting, I've very basic access skills, so please bare with me.

I have created a pipeline management form, where a handler will record prospective clients, contact details and so on. Each handler has a unique reference, so I want the handler to be able to select their name and at the bottom of the form, all of their pipeline cases will appear, they can then select one of the cases which will populate the fields in the form and then they can update the information, for example they could update the record as moving from prospect to sale and therefore closed.

Any info much appreciated.

Cheers
Dave

 

[Mr. B]

I am not sure exactly where you are in the process of accomplishing your goals.

I also have no idea what the structure of your database is. If your database is "normalized" then you should be able to accomplish what you want quite easily.

It might be a good idea to share the structure of your database with us just to validate that it is correct. The structure or schema for your data is the foundation of your database and just like in building anything, if the foundation is not correct the entire structure is going to fail. You might want to attache a copy of your database an let us take a look before you continue.

With that said, here is a good starting point for creating your form and the controls on it.

The first thing you need is a combo box where you can see a list of the handlers can select their name. It is not critical where this combo box is located on the form, but as general rule, if you should place your controls on your form in the order you expect your uses to use them. In other words, you really should have this combo box at the top of your form. When you have added the combo box to the form, give it a unique and meaningful name like: "cboHandlers". Later when you need to refer to it you will find it easier to do so. Make this combo box an "unbound" control (do not specify a value for its Control Source property). This combo box need to have at least columns, the record ID for handler and the name of the handler. Specify that the control has 2 columns and then put a zero in the "Column Widths" property to hid the first column (the record id column). Try this out and make sure you can select a handler from this combo box.

Next, I would use a list box to display the list of pipeline cases linked to the selected handler.

This should get you started. From here, as you have questions, please post back here and someone will be glad to try to help.

 

[Dave 350z]

Great, thanks, it's just a normal database really, I have combo boxes for a number of fields, such as handler, policy types and so on, but the main thing I want is to have the ability to show the cases for the handler when they select their name, I'll give the list box a go and go from there.

 

[Mr. B]

Ok. Just have your list box to use the value selected in your handler combo box as the criteria for its records.

 

[Dave 350z]

Ok, my combo box has a list of handlers that looks up the detail from a table, the Row Source Type = Table/Query, I've created a table for all of the handlers.

So to create the list of all items input by that handler, what is it exactly I need to do, I've added in a list box, in the control source option, I have selected Handler, but the list box doesn't populate with anything, am I missing something?

 

[Mr. B]

I think it would be a good idea for you to attache a copy of your database. I think you have misunderstood what I was talking about.

 

[Dave 350z]

Cheers Mr.B, I'll take a copy of the database as I do have some client information in there as it is, I'll post it up later on, I presume there's no issues with a "noob" posting an attachment?

Thanks for the help, it's much appreciated!!

Dave

 

[Dave 350z]

Hey,
Attached is the "database", it's only a very basic working draft and filled in with some details.

Let me know if you need any more info as to what I'm looking for....

Cheers
Dave

 

[tezread]

I suggest you look up normalization Dave. As it stands you database needs some work on normalization. The relationships aren't worked out well yet.

There is forum for normalization on here.

As a started you should have a table for clients called tblClients. This should have a unique reference ClientID. You need anthe table Leads called tblLeads, which woukd record all leads relating to clients? tblleads would have a unique idea LeadID but a foreign key in it called ClientID

Basic rule - one client will generate one or more leads so that needs a one to many relationship

 

[Mr. B]

Dave,

After looking at your database, I am afraid that I have more questions than answers for you. LOL

First, you really need to have primary key fields in your tables. Take a look a the AutoNumber field for this. This type of field is designed to provide this type of unique identification for each record. With the use of this type of field for each table, you can then link related data together very easily.

To really help you with the design of your database (the schema) I need to know more about just how your processes work.

I can tell that you have Handlers. There does need to be a table for defining the handlers. A couple of things about this table. You really need to rename this table. It is not a good idea to use characters like the ampersand sign in the name of any objects. I would also suggest that you start now to adopting some sort of naming convention for the objects (tables, forms, reports, controls, etc.) in your database. Personally I use a naming convention that includes a prefix for specific types of objects. For example tables are named with "tbl" as the prefix. Forms have "frm" as the prefix. I think you get the idea. Then I use camel case for the rest of the name. This simply means that I capitalize the first character of each word that is in the object name. You can include the underscore character if you like but I avoid any other special characters.

The Handlers table might also need to have a field where you can flag one record for a handler as Active or not. The can be a field named "Active" and just make it a Yes/No type field.

Do these Handlers have specific clients that they work with or are they subject to contact any client at anytime?

Next, I can tell that there are Clients. There should be a table for identifying the Clients.

Are there multiple contracts at each Client.

It is not really clear to me if multiple leads come from the same client or just how that works in the real world. This would have a direct affect on how the data needs to be designed.

If it is true that there can be multiple leads generated from multiple clients, this may constitute a many-to-many relationship rather than a one-to-many relationship as texread has indicated. I just cannot be sure from your post.

Can you provide more information about the "Renewal" process. I can see the Renewal date but don't understand just how this works in your world.

We must get the structure of the data correct before we get concerned about user interface forms and reports.

 

[Dave 350z]

Cheers for the replies guys, it's really appreciated. (I have also realised I am seriously under trained!! )

Ok, the purpose of the database is for the Handler (our employees), to fill in the detail of the form for a prospective client to provide a quote, all the database is going to be used for is storing the data on the client, there can be multiple clients with multiple potential quotes. I work in the insurance industry and the renewal date gives us the indicator of when best to contact the client.

What we basically want to do is, record the details of a client so we can call them to give them a quote on their insurance, hence the policy types (motor, home ect), we want to show who collected the prospective quote and we also want to show whom did the quote as they can be two different people (generally are).
This is why we want to list the quotes at the bottom of the page, so who ever is allocated the quote can then go in, list all of their quotes and then update them as needs be.

I am confident enough to pull reports on the tables through queries, but I am (obviously) useless on the forms and building a database!

Does any of this make sense?

 

[Mr. B]

Yes, Dave. This does make sense and it does help to get some details about the purpose of the database.

With this info in mind, I will take a look and post back.

 

[Dave 350z]

Massive thank you Mr.B for the help so far!!

 

[Mr. B]

Dave,

I have attached a modified version of your database.

Take a look at the tables that have "tbl" as the prefix. Also look at the relationships that I have setup. See if you think this structure would work.

I am sure that there are going to be additional fields that you will want to add to a couple of the tables but this should get the majority of the structure in place. Once we get the structure correct, then we can start working on the user interface and the output (reports).

 

[Dave 350z]

Hey MrB, looked at the tables and relationships and they all look great to me, seem to actually incorporate pretty much everything I would need, I presume once a form is created, it updates each of the tables rather than the set up I created which created a single table?

 

[Mr. B]

The from that I created only updates and manages records in the "tbkContainers" table. I create this one form to give you an idea of how I would create forms to mange data like that in the containers table.

You can create your forms to accomplish what you need them to do. You definitely need forms to interact with your data.