Create a sales tool

[Bjorn_DA]

Create a sales tool: search, filter

Hi

I'm new to this forum but I hope its ok that I ask you for a huge faviour.

I have worked a lot with tables and queries in Access, and understand relations. But I have never worked with forms, reports, macros and visual basics.

I need to create like a sales tool. We have 24 sellers that need customer info. They can't handle databases so my idea is to create forms they can use. My plan is to place the db in an internal network destination, "team documents".

I have a total of 17 tables of wich one is the "customer-master" (tbl_Customer), with unique customer id. The other 16 tables contains different products (tbl_Prod01, tbl_Prod02...tbl_Prod16). A customer can have several different products and even many of the same product. The relation between tbl_Customer and the product tables is on the customer id, one-to-many.
It can be up to 40 fields in one table.

Sellers will only be able to read the information, not delete, add or change. (tbl_Customer containing a target group, the sellers will not be able to sell to other companies.)

I have read many threads on this forum and got some good ideas, but unfortunately I have not found anything that completely covers my needs and I have not been able to piece together a solution.

What I want to do:

Select customers
1. Mandatory: The first step for the seller is to filter out their customers, by the SellerID in the tbl_Customer. I want the results in a list box where they can choose a specific customer they want to see the information about.

2. Optional: Refine the filter
a. Select a date (some customers have a "business window" in the tbl_Customer)
b. Search by customer-id (txt) or name (txt).

View information
Since there are many fields in the tables, I think it's best to view the information as data sheets. And since there are many tables, I think it's best to create a Tab control with a tab for each table.
If a customer does not have a product, the tab being there, but the result may be empty.

If possible, I also want the sellers to see only the forms, not the other stuff in the database.


Is this relatively easy to do? Do you think I can do it, even though I never worked with forms, visual basics and macros before?

If I do this, I'm thinking also of trying to create an opportunity for the sellers to register contacts and outcomes. But it is not the most important thing right now.

Now I go on break for a few days, but will take on this after Christmas.

English is not my first language (google translate is my friend ). Hope I was clear anyway and that there is someone here who can help me.

Merry Christmas!

 

[jdraw]

I think you will find many tips and tutorials at Martin Green's site to help you.
http://www.fontstuff.com/access/index.htm

 

[Bjorn_DA]

Thanks for the link, I will look that up.

 

[Bjorn_DA]

Hi again

I have now a basic form that works, but I would like a better filter functionality.

What I have is a form created on a qry with a combo box wich selects customer. Below it I have put in a Tab Control, with tabs for each product (tables as subforms). When I select customer the records in the Tab Control is updated. Fine so far.

The db contains about 12k customers wich - as mensioned in #1 - can have none, one or many of each product.

About 24 persons will use the form. They all have a unique SellerID, and each customer is tagged with a SellerID.

In the form I made, I have filtered the customers by SellerID in the qry, with the intention to duplicate a db for each SellerID (split database). But it is a bad and unnecessary solution, on the contrary, it is an advantage if they are able to select each other's customers in case of illness, vacation, etc.
Therefor I have started a new form from scratch and looked at this solution:
Dynamically search multiple fields (I cant link to it)

It's almost perfect for me; the text boxes I use to "filter" SellerID. I would also like to use more optional filters, eg filter the customers in the same zip-code, etc.

But know my problem: I can not get the Tab Control to update when I choose customer in the list box.

As I understand it, it is because this solution (link above) only works with a unbound form. And to select records from a list box, the form need to be bound to - in this case - the qry.

I suspect this will be solved with VBA coding, but as I wrote in # 1, it is not something I have mastered (yet).

Do you see the problem? Is there any solution?

The sellers will not be able to edit, delete or add any data.
This is a temporary solution, maybe in a year.

 

[Bjorn_DA]

I solved the main issue thx to the thread Dynamically search multiple fields (I can't link to it):

Instead of showing all info in one form, it know works like this:

1. Enter the SellerID in text box, result shown in list box.
2. Select a customer from the list box, hit the button and a new form popup where I got all the customer info (products, etc) in a Tab Control.

It's great, works and looks nice!

But I really would like to put som optional filters in the main form (the SellerID is mandatory), at least two: one number-field and one date-field. In both cases the values are stored in the same table as I built the qry to populate the list box, and both should be chosen from combo boxes (not typed in).

In the Dynamically search multiple fields-db there is Option Buttons doing the thing I want to do, but with combos.
The problem is that I can't found out how they work! I can't find any code to the Option buttons to give me a hint. Feeling really stupid...
I've searched this forum and rest of the web but can't find anything to help me.

The form, the text boxes and the list box are unbounded, just as in the db mentioned above.

I would really appreciate if someone could help me.

 

[Bjorn_DA]

Anyone have a clue how to do?

I saw that the option buttons in the Dynamically search multiple fields actually are frames with On click-code:

Private Sub Frame36_Click()

Me.SearchResults.Requery
Me.SearchResults = Me.SearchResults.ItemData(1)
Me.SearchFor.SetFocus

End Sub

I want the exact thing*, but with combo boxes (and maybe a text field). I have tried using this code on a combo but it didnt work.

* I have first a mandatory search text box, named SellerID. I want extra optional filters to narrow the search result. I have all the fields I want to use in the same qry, but the combos needs to be distinct (eg group zip-code).

 

[jdraw]

Is this the link you are trying to acces
http://www.access-programmers.co.uk/forums/showthread.php?t=188663

 

[Bjorn_DA]

Yep. When I have 10 posts I can link it