Use form data to generate a report?

[laxster]

Hi all!

I have created an "unbound" form. Basically, people select data that they want from it, and it then needs to spit out a list of labels. I'm not trying to store data, just allow people to input information.

What should happen is they first select the branch/plant from a drop down menu. They then should select the item # -- on the report it will populate both the item # and the item description based off of the number.

How do I get what is displayed on the form to spit directly into a label report? I've tried looking at the other threads I've found, but they haven't been much help to me.

 

[laxster]

Also, do these forms need to update to a table? For instance, can I make it so that if someone enters this data into the form it will update the table with that information so I can easily generate the report?

 

[boblarson]

Check out my sample here. I use a report with just a normal recordsource that pulls everything and then using the code behind the buttons on the selection form it brings back only what I want on the report.

 

[laxster]

How do I make my form fields add new records/data into a table? After some thinking, I'd like to add this data into a table, then use the table to spit out the report.

Does that make sense?

 

[boblarson]

Does that make sense?

Not really why you would want to add this information to a table. But you could I guess just by binding the form's Record Source to the table and then set each control's control source to a field in that table (just like any other bound form). Are you going to try to capture every search that way? If so you would need to also move to a new record in the code for the button that opens the report.

 

[laxster]

Well, I'm not exactly sure what is easiest.

Basically, there are various fields in the form. I've attached a picture of the form.

The first item (Select Plant combo box) refers to a list of our plants in one particular table.
The second item (Select item combo box) refers to a list of all our item numbers.
Enter UoM (this doesn't refer to anything, just needs to display on the form)
Enter number (this doesn't refer to anything, just needs to display on the form)

Month, DD, YY all need to be reformatted together into the format of YYMMMDD along with the plant# and the shift # to generate the lot number on the report.

The item number needs to also pull up the description on the report.

Can all this be done without the use of queries or tables? Really, the only tables that data is pulled from is just for the form input.

 

[boblarson]

Have you checked out my sample yet? What you do is to build your report so that it is not limited by any of those items at all. Then, using the code behind your BUTTON you would create the WHERE clause which then when you open the report you open using that.

So, as a simplified example, if the user selects ONLY the plant drop down you would end up with a WHERE clause at the end of the code that was like:

strWHERE = "[FieldName]=" & Chr(34) & Me.Plant & Chr(34)

DoCmd.OpenReport "ReportNameHere", acViewPreview, , strWHERE

But it is a bit more complex than that as you have to go down and build the where by checking all of the controls. My sample SHOULD show you a better example of how that is done.