Need assistance with Query Forms & links to reports 3

[HJessen]

Okay, here goes.

I am fairly new to Access, but have been told that I am the most qualified where I work. I am trying to do many things, but the ones I am having the most trouble with are:

(1) Printing a report (call it employee_report) based on the current record I have displayed on a form (call it employee_info). The form is using a field called emply_id that I could link to the report; however, I don't understand how to put a button on the form and have it print the report directly based on the current record.

(2) Having a "query form" where someone can look for an employee record base on any one or more of the following search criteria:
- emply_id
- last name
- first name
- work section
Once a record is located, I want the info to be displayed on my ‘employee_info’ form.

First off, I do not understand what a ‘query form’ is. I see it mentioned, and I have not been able to find a reference to it in my reference book (Running MS Access 2000, Microsoft Press).

I would appreciate any help possible!!

Thank You

 

[johnnymac43]

Your requirements are probably more complex than I can address in the limited time and space available, but this may help to point you in the right direction.

If you create a query, and then create a form based on that query, you have a 'query form'. (The wizard does a reasonable job if your number of fields and field sizes are manageable).

If you then create a report based on the same query, you are ready for the next step.

Open the 'query form' in design view and place a Command Button from the toolbox onto the form. The Command Button Wizard will open. Select Report Operations - Preview (or Print) Report. Select the Report name and you are almost there. Save the changed form. You are now almost ready to get serious about the 'real' user requirement.

If your query returns more than one employee, and you only want to print one, some fine tuning will be required. You will also find that Access asks you to enter any query parameters twice. I read a post on this subject a few minutes ago, but I can't recall its title. I will leave any more detailed help to the programmers amongst us.

Best of luck

John

 

[HJessen]

johnnymac43
This is what I tried, but what happens is that I get all of the employees for the company printed out. It does not limit the record source to the current record.

Let's say I have a form on the screen with emply_id 13579 displayed. What I want to do is click on a button, and have a report for that one emplyee print out. Needless to say, the form and the report are not identical.

The form is using tabs to display sub-forms for different catagories of information (i.e. HR, Emergency Notification, Work Record, etc.), and the report will print this information in a different format.

Does the additional infomation help clarify the situation? Can ANYONE help me?

Thanks.

H. Jessen

 

[annsolomon]

Put this code behind the command button on your form:

DoCmd.OpenReport "employee_report", acViewPreview, , "emply_ID = " & Me!emply_ID

 

[rickgrimes]

annsolomon is correct on the report button, use that and it will operate the way you want.

On the query form, there are a number of ways to accomplish your search. I think the first thing is to have an idea of what kind of form you want to see. Some opt of the 1 form with 4 combo boxes. The operator gets to choose from the boxes (in your case, the employee info) and then cliks a button and is taken to the record.

I have design many of those type of forms, but I have started designing a form with a listbox that displays the information you are searching for, and the listbox is continually updated (narrowed) as each combo box is updated. This seems to make my customers very happy, as they can actively see the records narrow down as more information is given.

Your senario:
1. Create a blank form. Put 4 unbound combo boxes on the form, say at the top
2. Make the row source in combobox1 a query that shows just the employeeIDs. Use the other 3 for the same idea, first name, last name, work section.
3. Add a list box to the form. Base the row source on a query, that shows the employeeID, names, work section, whatever you need. The employeeID is the bound column for the listbox. The criteria for the listbox query should be based on the 4 combo boxes.
Open the rowsource query and under each of the 'search' field (emplyeeID, etc), put the forms' combobox as the criteria. i.e. Forms![YourserchFormName]![Combobox1]
4. Put code in each combobox in the afterupdate event.
Me!Listbox1.requery
5. be sure to include any error handling for you code.
6. Put an button on the form. On the click event, it should be:
Docnd.OpenForm "YourEmployeeForm",,,"[EmployeeID]=" & me!Listbox1
7. You can also put that code under the doubleclick event for the listbox.

Of course, this is just an overview, it you get stuck, I will try and help best as I can.

Good Luck.

 

[HJessen]

First off, let me start by thanking you all for your assistance and saying HAPPY HOLIDAYS to you all.

While the suggestions from annsolomon & rickgrimes both helped it did not work for me. I was able to open a form from a form using this technique, but can not get the form to open a single record report like I wanted. After fighting both a 100 degree temp, and this problem, I think maybe it has something to do with the form I am using.

The form has seven sub-forms all linked via the emply_id field. (Each sub-form is pulling it's information from a different table.) When I use 'Me!Emply_ID' I am not sure that Access knows which emply-ID to use. I have tried to work with the field name, but to now avail. I get either one report with #ERROR at the top, or I get all 300+ records pulled.

How do I tell Access which table to look at? Some table names are:
Data - Basic
Data - HR
Data - Medical
Data - Section

Again THANKS for the help so far. It has been a source of growth for me.

It seems that I will have to work Wednesday - Yes, Christmas Day - to clear this up. The big boys want it done yesterday, so any help would be appreciated! H. Jessen
"Now I know more, and I feel dummer"

 

[rickgrimes]

Try,

Me![TableName.EmplyID]

I have run into this before, were there were multiple field names in the underlying query. Using the syntax of the tablename.fieldname has worked for me several times.

Use the syntax in the docmd.openform statement.
Just a thought, is the field Emply_ID a string or long data type?

Happy Holidays.

 

[annsolomon]

Rick has a good point about your data type. You may need to use the Str function and/or take care with your quotation marks in the code I gave you.

Something else just caught my attention (my mind goes out for walks sometimes). Are your subforms on a Tab control? Tab controls are kinda tricky when it comes to referencing objects. I'm still learning about them the hard way.

I know this hasn't given you the answer but hopefully some ideas of where else to look. We haven't deserted you!

Ann

 

[HJessen]

Rick & Ann;

Thanks for the help so far. Let me give you more details:
Table names are:
Data - Basic
Data - HR
Data - Section
Data - Materials
Each table has the identical field called Emply_ID. This is what links the records in each table together.

The emply_ID field is in the main form (Basic) as well as each sub-form. Each sub-form is related to a different table - i.e. HR has a sub-form, Section has a sub-form, etc. And yes, the sub-forms are accessed via tabs.

The "basic" form and the report use the same query "MERGE - All active files" to gather information.

I want to use the emply_id (DATA - BASIC, emply_ID) field from the main form to call the report.

I wish I could just send you the db, but I have been restricted from doing this, and am probably giving enough info to upset some of the management around here.

Hope you had a great Christmas.

Thanks again for the prior help. H. Jessen
"Now I know more, and I feel dummer"

 

[rickgrimes]

If the Emply_ID is Long data type then,
Try this syntax under the command button for the report:

Docmd.OpenReport "Report_Name", acViewPreview, , "[Data - Basic.Emply_ID]=" & Me![Data - Basic.Emply_ID]

If the Emply_ID is string data type then,

Docmd.OpenReport "Report_Name", acViewPreview, , "[Data - Basic.Emply_ID]='" & Me![Data - Basic.Emply_ID] & "'"
(note the ')

If that doesn't work, try placing the table name in brackets too: [Data - Basic].[Emply_ID], or [Data - Basic]![Emply_ID]

Let us know and good luck.

Thanks Ann for the support also!

 

[HJessen]

Rick,

Is there an email address where I can send you a file? I am at my wits end here!!

Thanks again. H. Jessen
"Now I know more, and I feel dummer"

 

[rickgrimes]

Of course,

Send the file to gher_rick@symet.net

I would be happy to look at it for you.

Regards, Rick.

 

[annsolomon]

H. Jessen, I haven't forgotten you but it seems you're in good hands with Rick. (I'm glad you decided to send the file by the way.)

Ann