Microsoft Access Database Report help needed...

[roughdraft274]

If there are any MS Access gurus around these parts that could help me out it would be greatly appreciated. I know just enough to get in trouble, lol. Was hoping to not have to join a forum simply to ask one question. Everything so far I've been able to learn from instructional videos or a quick google.

I work at an engineering firm that is trying to automate some stuff, as we already keep alot of info in an internal access database. It works great and has many reports already built in. I'm trying to add on to it and alter it a bit.

Is there a way to add an if statement to an object like a line? something like "if value of AAA is BBB, then line is visible, else it is not visible"? I've heard I could do it using VBA, which I'm open to but don't want to do it because the guy who does alot of the database stuff doesn't know VBA and doesn't want to learn. So I will get some griping but I'm ok with that if it's the only way.

This seems like it should be very easy. I need to do it with some text boxes in the report as well as lines if possible.

Thanks in advance for any advice

 

[Carjosse]

If there are any MS Access gurus around these parts that could help me out it would be greatly appreciated. I know just enough to get in trouble, lol. Was hoping to not have to join a forum simply to ask one question. Everything so far I've been able to learn from instructional videos or a quick google.

I work at an engineering firm that is trying to automate some stuff, as we already keep alot of info in an internal access database. It works great and has many reports already built in. I'm trying to add on to it and alter it a bit.

Is there a way to add an if statement to an object like a line? something like "if value of AAA is BBB, then line is visible, else it is not visible"? I've heard I could do it using VBA, which I'm open to but don't want to do it because the guy who does alot of the database stuff doesn't know VBA and doesn't want to learn. So I will get some griping but I'm ok with that if it's the only way.

This seems like it should be very easy. I need to do it with some text boxes in the report as well as lines if possible.

Thanks in advance for any advice

I think you are out of luck, from what I can tell you need VBA. Can you not implement it and then just tell him not to mess with it? Event hen VBA is one of the easiest languages to learn.

 

[Cisero]

If there are any MS Access gurus around these parts that could help me out it would be greatly appreciated. I know just enough to get in trouble, lol. Was hoping to not have to join a forum simply to ask one question. Everything so far I've been able to learn from instructional videos or a quick google.

I work at an engineering firm that is trying to automate some stuff, as we already keep alot of info in an internal access database. It works great and has many reports already built in. I'm trying to add on to it and alter it a bit.

Is there a way to add an if statement to an object like a line? something like "if value of AAA is BBB, then line is visible, else it is not visible"? I've heard I could do it using VBA, which I'm open to but don't want to do it because the guy who does alot of the database stuff doesn't know VBA and doesn't want to learn. So I will get some griping but I'm ok with that if it's the only way.

This seems like it should be very easy. I need to do it with some text boxes in the report as well as lines if possible.

Thanks in advance for any advice

Usually a report is filled out with an SQL query. Can't you just add a "WHERE" statement in your SQL query and fill the TextField only with the result ? Or do you absolutely need to hide the information ?

VBA isn't really hard, especially if you just have to hide something. But SQL is also an option.

 

[Taylor]

Is there a way to add an if statement to an object like a line? something like "if value of AAA is BBB, then line is visible, else it is not visible"? I've heard I could do it using VBA, which I'm open to but don't want to do it because the guy who does alot of the database stuff doesn't know VBA and doesn't want to learn. So I will get some griping but I'm ok with that if it's the only way.

Yes, should be fairly straightforward:

https://docs.microsoft.com/en-us/office/vba/api/access.line.visible

 

[Taylor]

You just need to find out what your line is called (e.g. "Line01") then make the property conditional on your value, something like:

Line01.Visible = Forms![Orders]![Qty] <> 0

(if order quantity is greater than zero, line appears)

 

[roughdraft274]

I think you are out of luck, from what I can tell you need VBA. Can you not implement it and then just tell him not to mess with it? Event hen VBA is one of the easiest languages to learn.

I'm more than open to VBA. I just don't want to do VBA and then be told later on that I didn't need to. I'll do whatever is needed to get the job done, but at the same time we like to keep these things as simple as possible just in case I up and leave and hand it to someone else they should be able to study it and understand it.

The only VBA i've really used is copy and pasted stuff off the internet where I just change variable names etc. But familiar enough to where if someone gave me a command I could probably make it work.

 

[roughdraft274]

Usually a report is filled out with an SQL query. Can't you just add a "WHERE" statement in your SQL query and fill the TextField only with the result ? Or do you absolutely need to hide the information ?

VBA isn't really hard, especially if you just have to hide something. But SQL is also an option.

Yea, the actual text is essentially straight out of a query, but I have the text in an outlined box along with a line next to it. I want the outlined box and line to go away if it isn't needed on that exact line of the report.

 

[roughdraft274]

You just need to find out what your line is called (e.g. "Line01") then make the property conditional on your value, something like:

Line01.Visible = Forms![Orders]![Qty] <> 0

(if order quantity is greater than zero, line appears)

Thanks!

Quick question, If I do this, how do I do it so that as the report is going through every return, it figures if the line is needed on every single line? Do I enter it in a certain segment of the report?

For example, the below picture is a report thats similar to what I'm planning. The textbox "TagNo" (In Detail section, on the very left) and the line directly to the right of it should not appear if TagNo = null. And I guess I need it evaluated for all 1000 "TagNo"s that I will be running through the report. How do I take that VBA and make that work? I'm scared I will make it evaluate the first line of the report and then either have it visible or invisible for the next 1000 "TagNo"s?

 

[Taylor]

Thanks!

Quick question, If I do this, how do I do it so that as the report is going through every return, it figures if the line is needed on every single line? Do I enter it in a certain segment of the report?

For example, the below picture is a report thats similar to what I'm planning. The textbox "TagNo" (In Detail section, on the very left) and the line directly to the right of it should not appear if TagNo = null. And I guess I need it evaluated for all 1000 "TagNo"s that I will be running through the report. How do I take that VBA and make that work? I'm scared I will make it evaluate the first line of the report and then either have it visible or invisible for the next 1000 "TagNo"s?

View attachment 67242891

The fields are located in the detail section of the form, so it would go in the detail format section:

Sub Detail_Format(Cancel as Integer, FormatCount as Integer)

****CODE****

End Sub

 

[roughdraft274]

The fields are located in the detail section of the form, so it would go in the detail format section:

Sub Detail_Format(Cancel as Integer, FormatCount as Integer)

****CODE****

End Sub

You're an angel. I'll try it as soon as I get a free minute and see what happens.

Thanks