Using a combo box to select multiple records on a form

[Rawb]

That depends.. .

A) Do you want a button that, when clicked, will create a whole new Report from scratch, with a different layout/appearance from any other Report in the system? Or,
B) Do you want a button that, when clicked, will basically show a printer friendly version of the Record you're currently looking at?



If you mean question A, then the answer is "No." Reports have to be pre-made with a specific layout that can't be modified on-the-fly like that. You can control what data shows up in a Report, but not the layout of the Report itself.

If you mean question B, then certainly. That's what Reports are for!

[reverze]

Yes, I want B. I just want it to show a report accross the column for a particular record.

So say ID #4. I want that ID, name, description etc...

And I want it to just create one depending on what is selected in the combo box..

Can I do that together?

I'm thinking I need some extra code to do that because the normal wizard dosen't exactly work that way..

[Rawb]

You will need a little custom Code, but it's actually pretty simple.

First, set up a Report so that it shows information the way you want. I'd suggest not using the Report Wizard for a report like this that only shows information on a single Record.

Create a New Report in Design View. Don't worry about getting the actual data into the Report yet, just focus on how you want it to look. If you've never made a Report before, it's set up almost exactly like a Form is (at least this Report will be).

Once you've got the look down, we can attach it to your data.

[reverze]

See, here's the thing I'm also not sure about.

Right now I have about 40 different entries.

What I want to be able to happen is when the combo box has one of those entries selected, it is possible for the user to click "create report" and no matter what entry it is on, it can generate a report for one of those 40 entries.

I didn't think making a pre-made report would be the best way to go, because I don't know if having 40 some reports is a wise idea?

[Rawb]

Well that might not be necessary. To be sure I'm understanding the situation, would it be possible for you to attach the database so I can take a look at it?

[reverze]

I will reply back here soon with a quick sample one

[reverze]

Okay, here's a quick example..

[reverze]

Hm, well the form is missing...

Why won't it save the form in 2000? haha

[Rawb]

I see 1 Table and 1 Form in the database.

I'm sorry, but I still don't understand what you're trying to do. You keep trying to be too general in your description of what you want. I understand that a lot of the information may be private or confidential, but I really need to know - as exactly as possible - what you're trying to do if I'm going to be able to help.

Let's back up a step . .

First off, what type of information are we going to be dealing with? Geographical (for a company that plans vacations?), info about individuals (like employee information?), or financial (for a billing program?)? Something else?

Then, what do you want to show up in the Form? Is the Form supposed to show all this information too (and the Report is simply a printable version) or is the Form nothing more than a way of selecting what you want to see a Report on? Remember, you can have multiple Forms, each serving a different purpose. You don't have to do everything from just one.

Once we have those things spelled out, then we can try and figure out the Report.

[reverze]

I see 1 Table and 1 Form in the database.

I'm sorry, but I still don't understand what you're trying to do. You keep trying to be too general in your description of what you want. I understand that a lot of the information may be private or confidential, but I really need to know - as exactly as possible - what you're trying to do if I'm going to be able to help.

Let's back up a step . .

First off, what type of information are we going to be dealing with? Geographical (for a company that plans vacations?), info about individuals (like employee information?), or financial (for a billing program?)? Something else?

Then, what do you want to show up in the Form? Is the Form supposed to show all this information too (and the Report is simply a printable version) or is the Form nothing more than a way of selecting what you want to see a Report on? Remember, you can have multiple Forms, each serving a different purpose. You don't have to do everything from just one.

Once we have those things spelled out, then we can try and figure out the Report.

The information is information that is dealing with different entities of an IT organization.

The form is exactly the same as we had it before. I just have it showing about 15 text boxes with information that can be input into those such as leadership, description, etc. It's fitting pretty well on the form right now, so for the time-being I'm going to leave it how it is.

I believe that I figured out the year thing, ( I think... ) so for now I'm not going to worry about that.

Right now I am thinking about the report.

So there's one table that contains all of the data and one form.

One the form there is the combo box where the user selects one of the entities and it displays all the information on the form.

I want a button on the form that can be clicked and when it is clicked, it will see what is selected in the combo box. It will take all of the information currently displayed on that form and turn it into a report that can be viewed..

Soudns complicated I know

[Rawb]

OK, gotcha.

What you're looking at then is a generic Report. It will always have the same layout/appearance, but the data in it will change based on which entity is selected.

This can be done with a single Report. All we'll have to do is, on our button, pass the ID of the entity we want to view in the Report.

1. Go ahead and create a blank Report in Design View.
2. Go to it's Properties (by clicking on the button that looks like a hand pointing at a piece of paper. It will be right next to a "magic Wand" button) and select the Data Tab.
3. In the field labeled Record Source, enter a Query that will show the first entity in your table (something like "SELECT * FROM TableName WHERE [ID]=1").
4. Close the Properties by clicking the "X" in the upper corner (there's no "OK button to click).

Once you've done this, you can build the Report just like you would a Form. Then, when you're done, we'll be able to replace the Record Source
field with something else and use it to view any entity's Record from the Form.

[reverze]

Ok, done.

I'm guessing there's a second part?

[Rawb]

Here's an example of a Report as well as the VBA Code needed to open it from a Form (using your example database).

[Rawb]

Oops, I missed that last post of yours. . .

Once you've made your Report, change that Record Source from the Query to the name of your Table.

Then you can basically just cut and paste the VBA code from my example into your button's On Click Event.

  ' Just in case we run into an error
  On Error GoTo Error_Command12_Click

  ' Make sure the user selects a state
  If IsNull(Me!Combo8) Then
    MsgBox "Please select a state first."
    GoTo FunctionClosing
  End If

  ' Open the Report
  '   Report Name: Report1
  '   View Type: acViewPreview (Show me on the screen instead of printing it)
  '   Filter: None (WHERE clause used instead)
  '   Where Clause: [ID]= the selected state's ID number
  DoCmd.OpenReport "Report1", acViewPreview, , "[ID]=" & Me!Combo8

  ' Exit the function!
  GoTo FunctionClosing

FunctionClosing:
  Exit Sub

Error_Command12_Click:
  ' If we run into an error, alert the user.
  MsgBox "The following error occurred while trying to view the Report:" & _
         vbCrLf & vbCrLf & Err.Description

  ' Exit the function!
  Resume FunctionClosing

[reverze]

Thank you!

Worked perfect in your example, but I am doing something dumb and getting:

"Object dosen't support this method."

at DoCmd.OpenReport "rptEntity", acViewPreview, , "[ID]=" & Me!cmdReport


Trying to figure it out now