Creating a report for a customer by just typing their name

[dion48064]

Hi all, I have a question on access and I'm hoping you can help.



I am creating an Access Database for my company where we can enter in all of our customers and the loads they have run with us, including the details of the loads. I would like to be able to pull reports for any customer at anytime without having to go in and manual create a query and then a report every time I want a report on a customer.

What I would like to be able to do is whenever I want a report on a specific customer I can have a prompt box pop up and I can just type in the customers name and have it automatically generate a report. I am able to manually do these reports as I previously stated but I want everyone to easily be able to access their customers statistics. I honestly do not know if this is possible but if you do know how to do it I would be very appreciative!

Thank you for your time!

[June7]

Certainly possible but I recommend against popup input prompts - too hard to validate input - use a combobox.

Review http://allenbrowne.com/ser-62.html

[dion48064]

Does that link explain how to do that?

[June7]

I believe it does. It is a commonly referenced source. It even mentions using dynamic parameterized query as an alternative to the VBA procedure - I never use dynamic parameterized queries.

Have you even read through the article?

[Micron]

A combo box approach will work if you'll forever be satisfied with reporting on one customer at a time. If you would like to report on 2 or more, you will need a listbox and have to decide if it is OK to have both customers contained in one report because you can't have 2 instances of it open at the same time (one for Smith and one for Brown) without resorting to complex coding. That means even if using a combo, your procedure should test for the report being open and if so, close it (just invoking close would be OK too) otherwise after running one and going back to the form window to run another, you'll only get the first one back if you don't close it before running the second report.

[dion48064]

I believe it does. It is a commonly referenced source. It even mentions using dynamic parameterized query as an alternative to the VBA procedure - I never use dynamic parameterized queries.

Have you even read through the article?

I have looked through it. I, however, have not used code before so its difficult for me to understand. I really do not understand still how to implement that code to do what I want. Sorry for causing trouble, it just doesn't make much sense to me.

[davegri]

I believe the link in post #2 is for setting search criteria, whereas the OP wants to print a report using the current record shown in the form, as discussed by below link:

http://allenbrowne.com/casu-15.html

[dion48064]

I appreciate all of the help! I've been reading through them both a lot and they are starting to make more sense. Still don't fully understand how to actually just have one box that popup where I can type in a customers name and it can pull it from a table however.

[dion48064]

Not necessarily 'pop up', but just be available to me.

[dion48064]

Also, this may be a dumb question, but are all of these codes written using SQL? If I were to learn to code SQL would I be able to write these types of codes for Access? Or are there any online courses where I can learn to code on Access?

[June7]

As I said, not a popup but a control (textbox or combobox) on a form for user input.

The code is VBA (Visual Basic for Applications). It is the programming language behind all MS Office apps. SQL (Structured Query Language) is the language of queries. Build a query object with query designer then switch to SQLView and you will see SQL code.

I am sure there are online tutorials, most are probably at a cost. Have you searched?

[Micron]

Still don't fully understand how to actually just have one box that popup where I can type in a customers name and it can pull it from a table however.

That is what June7 was saying she doesn't like. They do have their pitfalls (such as accepting any or no input), but I'm not so dead set against them for a novice. We all had to start from some lesser point I guess. In your query design, in the first criteria row, in the customer field type in [Enter Customer] and run it. Try experimenting with valid and invalid data and no entry and see what the outcomes are.

You can also adapt the code shown at the link to your db which requires that you are looking at a record on a form. If not, then it isn't suitable. You'd need either the parametized query or a form from which you can select a customer from a combo box (a common solution) whose AfterUpdate event opens the report for that selection. If you post a zipped copy of your db, I'm sure we can help you overcome your lack of coding experience.