form records to report

[mdemarte]

I have a database of vendors and have created a form to find records using the filter from form option. For example, all vendors that are in the city of Rochester or all service vendors. Now, I want to see a report of JUST those vendors. I have created an unbound report where the text box shows the vendor name based on the form, however it only shows the first name, no others. How do I get it to show the other 211 Rochester vendors or the 19 service vendors? If possible, can it be done without using VBA?

The unbound text box Control Source is set to this now:

=[Forms]![frmVendorFind]![VENDOR_NAM]

Thanks in advance!

 

[dhoffman]

I read your problem perhaps a little too quickly so I may have missed something, but here's my suggestion:
Build a report based on the exact same record set as the form. (the report should display the same records as the form when first opened) Put a button on the form that will open the report. Now remember that filter by form you were talking about? (By the way , you must use VBA for this) Look at the code for the button that opens the form. What you want in there is a couple of lines like this:
If Me.FilterON Then
DoCmd.OpenReport "NAME", acPreview, , Me.Filter

Notice how the the third argument is left blank? The argument is optional and you don't need to use it. The fourth option will make the Report open with the same restrictions on it's data as the Form has with the filter applied. If you look up OpenReport in help it will tell you the third arg is the filter and the fourth is the WHERE clause. Well, the filter here is different from the filter for the form, trust me you want to use the fourth argument and skip the third.

 

[dhoffman]

If you need a little more help let me know.

 

[dhoffman]

did that work at all?

 

[mdemarte]

That worked like a charm! Sorry it took me so long to get back to you, got your message this morning, but then our Internet connection was down.

Just one thing that I did not think of, I have some duplicate records (the query combines two tables, so I have some vendors listed more than once for different services). So, after I filter, but before my report, how do I remove duplicates? I am going to try a query on a query, but will it keep the filter?

 

[dhoffman]

I'm not sure I understand why you are getting duplicates. What does you original query look like? I don't think you need to worry about the filter not working as long as the recordsets for the form and report are the same. Filter by Form will not let you choose an invalid filter.

Oh, and I really think you would want to remove the duplicates before applying any filter. Really, you underlying query/record source should be responsible for making sure there are no duplicates. If you gave me a detailed description of your tables and the query I think I could help.

[This message has been edited by dhoffman (edited 07-17-2001).]

 

[mdemarte]

This database is being converted from Approach and was developed by someone else. There are three tables: VENDINFO, VENDTP, & VENDTYPE.

VENDTYPE has the vendor type and abbreviation (this is the key). For example: Printer Supplies and PRINTERSUP.

VENDINFO has the vendor name, street address, city, state, zip, contact, phone #, fax #. This is linked by vendor name (which is the key) to VENDTP. For example: IBM Corporation, 123 First Avenue, Washington, DC, 12345, Martha Brown, 123-456-7890, none.

VENDTP is linked to VENDTYPE, so the user can select the abbreviation, and also another field that is a memo field for listing brand names.

What the user needs is to be able to perform a find of all vendors in Rochester, or all VENDTYPE PRINTERSUP vendors. Doing a Filter on the form that I already had did not work. So, I created a Form with a Query as the source, combining the table of VENDORINFO with the VENDTP. This created duplicate Vendor names, but was necessary to show IBM Corporation as both a VENDTYPE of PRINTERSUP and COMPUTER.

Now, see where this is headed? If I search for VENDTYPE COMPUTER, I am fine. I get just one IBM Corporation. If I search for all of the vendors in Washington, IBM Corporation comes up twice.

 

[dhoffman]

I'll try and get back to you later, I'm really in a hurry to finish something this week, hope you can wait a day or two.