Queries is my next step?

[JPP]

Ok, I am using access 2013 and have built several databases to track things like jobs my contractors are doing, orders, etc.

I have the table built, and have gotten to the point to where I can enter a new entry via the form.

My next step I guess would include a query.

What I want to do is for my boss to pull up a report and have a search box she can type in an order number (*there is a field for Order # in this database) and the report shows this order with all the info in it in a nice printable sheet.



I've heard of combo boxes but I do not understand how to create them. I think a report with a search box for a certain field (*Order number in this case) entry that displays the entire "order" associated with it should be easy to create. Help?

[jzwp11]

I have the table built, and

You only have the one table?

If you have jobs, contractors and orders, that implies that you need at least 3 tables and most likely more.

[JPP]

Correct. Sorry about the confusion. I have several databases functional but all have the same end result needed. Searching by a particular field. Each database has only one table with roughly 10 fields.

[jzwp11]

I have several databases

Why do you have several databases rather than just 1?

[JPP]

You'll have to understand I am at level 0 here haha. This is the first time I picked up Access. I have several things I need to keep track of, so I separated the databases. Yes, I see now that I could have all the tables and forms in one database, but it is what it is...

[jzwp11]

It is very easy to copy the tables from the other databases into one or you can have one master database that links to the tables in all of the other databases and then you can develop your forms, queries and reports in 1 database. Out of curiosity, what type of data is in the other tables? If they essentially contain the same type of information, then it would be best to consolidate the data into the appropriate table structure in 1 database. That would make your life a lot easier in the long run.

[JPP]

One is a list of passwords, one is on jobs we do, one is on jobs we do that get adjusted money wise, two are very similar quality control checkups. I think I will keep them separate as my boss is not savvy at all.

[jzwp11]

What I want to do is for my boss to pull up a report and have a search box she can type in an order number (*there is a field for Order # in this database) and the report shows this order with all the info in it in a nice printable sheet.

As long as the information pertinent to the order is in one database, you can do this. This is the process:

1. Create a new form (one that is not bound to a table).
2. Using the combo box wizard, create a combo box based on the order table and include the order number field and any other fields you want your boss or any other user to see in order to help them select the order they want. You want Access to remember the order number (there should be an option for this as you step through the wizard). Make a note of the name of the combo box. Access usually assigns a "combo" box a name of combo with a number. Save the form.
3. Create a query based on the order table. In the criteria section of the query for the order number field/column, you want to reference the form/combo box as follows: forms!formnamehere!comboboxnamehere. Save the query
4. Create your report based on the query you created in step 3.
5. Reopen the form from step 2 in Design View. Using the command button wizard, add a command button. Make sure to select the option to open a report and select the name of the report you created in step 4. Save the form

That should do it. Hopefully I haven't missed anything

[JPP]

Ok, everything LOOKS good but the data is not pulling up... typed in an order #, and it appears to perform a search but no data comes up??

[jzwp11]

typed in an order #,

Are you selecting an order number from the combo box?

When you say no data comes up do you mean that the report opens & it is blank or the report does not open?

[JPP]

Actually, I must have done something wrong... I do not see "Order number" nor a field to type in for the search. I will try again

[jzwp11]

What is the row source of the combo box that you created (property sheet->data tab)? It should start with the word SELECT.

[JPP]

Ok, I got it to show all the order numbers, but when I select one and hit the button it shows no results.

[JPP]

There are no entries in the data tab

[jzwp11]

Can you zip and post a copy of your database (with any sensitive data altered or removed)?