Report One Line Of Data

[SteveF]

Hi guys. Probably dumb question time again so apologies in advance if that's the case.

I'm building a risk assessment database and it's going quite well, but I'm struggling to get the reporting to work the way I want.

I have a form that adds data to my table: frm_risktype & tbl_risktype. What I want to do is be able to pick one, or several, 'risk types' out of the table and print only that as a report. Currently, all I can manage to get is a report on the whole table.

My code knowledge/writing ability is zero. Any help appreciated, and I'm running Access 2007 if that makes any difference.

Thanks in advance.

 

[vbaInet]

Have a look at this:

http://baldyweb.com/wherecondition.htm

It's the same idea but you would need to replace OpenForm with OpenReport. The code should go in the CLICK event of a button on your form.

 

[SteveF]

Ok. So the form can open a report that will display the one set of data? I never thought of looking at it that way. I will give it a try and report back, thanks.

 

[vbaInet]

It sure can. All you have to do is pass the ID in the WHERE part of the Docmd.OpenReport method.

For multiple risk types, you would use an OR.

 

[SteveF]

Ok. So I can run the report from a form; I can see that now. But, what I'm getting is a report that has all the data in the table in it, so if I have 5 'Risk Types' they all display in the report.

What I would like to do is select one Risk Type and have that display in a report so that I can print them individually. Is that possible? Or do I have the basic structure wrong?

 

[vbaInet]

Like I mentioned, use the 'WHERE' argument of the OpenReport method. That link explains which part is the WHERE argument and how to send an ID to it, ID in your case would be the risk type. The report will display all the records related to just that risk type.

 

[vbaInet]

I will give you an example:

The structure:
DoCmd.OpenReport "NameOfReport", , , "FieldName = '" & Me.ControlName & "'"

The example:
DoCmd.OpenReport "NameOfReport", , , "[Risk Type] = '" & Me.RiskType & "'"

 

[SteveF]

DoCmd.OpenReport

"rpt_risktype", , , "Risk = '" & Me.Risk & "'"



Ok. This is way over my head but I'm trying to understand.

I've read and re-read the link and the example posted ( thanks for both ) and came up with the above.

The report name is correct, 'Risk' refers to the identifying field I'm trying to use but there is also a primary key: Risk_ID

Any idea where I'm going wrong? Bearing in mind that it could be that I'm going wrong just about anywhere...

 

[vbaInet]

Alrighty. So Risk_ID is the ID per Risk_Type?

What is going wrong at the moment, is there an error?

 

[SteveF]

Yes, Risk_ID is the primary key so each one is one row of data in the table.

It errors at the moment with 'the expression may not result in...'

 

[SteveF]

I would really like to crack this because I'm doing COSHH reports in the same way and I really need to print those one at a time.

Am I doing things the right way by recording them all in one table?

 

[vbaInet]

You're on the right track but I know nothing of your table structure. Risk_ID is a number datatype right?

DoCmd.OpenReport "rpt_risktype", , , "Risk_ID = " & Me.Risk_ID

Where I've highlighted red, check the control where Risk_ID appears and change it to the NAME of that control/textbox.

 

[SteveF]

Risk_ID is the primary key field name.

I will try what you suggest, thanks

 

[SteveF]

DoCmd.OpenReport

"rpt_risktype", , , "Risk_ID = '" & Me.Risk_ID & "'"

Same error. I'm beginning to suspect I'm misunderstanding something embarrassingly basic...

 

[vbaInet]

Risk_ID is the primary key field name.

I will try what you suggest, thanks

Yes you had already said that and I understood. I asked if Risk_ID has a NUMBER datatype or is a TEXT datatype in the table where it resides?

 

[vbaInet]

DoCmd.OpenReport

"rpt_risktype", , , "Risk_ID = '" & Me.Risk_ID & "'"

Same error. I'm beginning to suspect I'm misunderstanding something embarrassingly basic...

You will get it eventually.

Your last two posts always seem to write the code on two lines. The code should be written on one line.

Compare what I wrote in my post with what you've just written. There are things I took off that you re-added. If Risk_ID is a numeric field then you should follow what I gave in post #12.

 

[SteveF]

Sorry, it's a number. I didn't realise a primary key could be anything but a number, every day's a school day

 

[vbaInet]

Oh yes, a Primary could be any of the Access datatypes or even a combination of fields.

Did you see my last post?

 

[SteveF]

Yes. I'm sat puzzling over this and trying that single line of code where I think it ought to be but am still getting the error. As I say, I'm probably making very basic mistakes.

I'm trying to educate myself here as well, so would like to understand why things work and don't work.

 

[SteveF]

I'm trying to make this work through a list box in a form, with the code in the 'on click' section:

Private Sub Combo3_Click()
DoCmd.OpenReport "rpt_risktype", , , "Risk_ID = " & Me.Risk_ID
End Sub