Access Report question using VB (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

John316 -> Access Report question using VB (8/22/2003 20:11:54)

All,

I have a access report. I want to hide any row if itemA-D = 0 anyone know how I would do that? I was thinking of something like:

If itemA = 0 and itemB = 0 and itemC = 0 and itemD = 0 hide row
Else Show row

But I am not sure how to do this in a access report using VB, can anyone please help me?

John316



webcats -> RE: Access Report question using VB (8/24/2003 16:22:26)

You need to put the code in the Details section of the report. Something like this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim itemA As String
Dim itemB As String
Dim itemC As String
Dim itemD As String

If itemA = 0 Then
If itemB = 0 Then
If itemC = 0 Then
If itemD = 0 Then
Me.Controls!ctl1.Visible = False
Me.Controls!ctl2.Visible = False
Me.Controls!ctl3.Visible = False
Me.Controls!ctl4.Visible = False
Me.Controls!ctl5.Visible = False
Me.Controls!ctl6.Visible = False
End If
End If
End If
End If

End Sub

Substitue your field names for the "ctl1", "ctl2", etc.

HTH



John316 -> RE: Access Report question using VB (8/25/2003 10:43:44)

Would I need to add <% and the begin and the end of the VB script above?



John316 -> RE: Access Report question using VB (8/25/2003 15:46:01)

I am also getting an Run-time error '13': Type mismatch

Do you know how I would fix that? It is pointing to the If statement. Does anyone know how to fix it?

John316



John316 -> RE: Access Report question using VB (8/26/2003 21:45:17)

Can someone please help me

John316



rdouglass -> RE: Access Report question using VB (8/27/2003 9:19:04)

Well, you can do it in ASP, but sometimes it's easier if you build a query in Access that uses expressions and call the query instead of the table.

However, you can do it in the DRW or ASP as well. You can do custom SQL in a DRW and do some of that. You could build a custom query something like:

SELECT * FROM myTable WHERE ([myFirstColumn] - [mySecondColumn] <> 0)

You could do similar stuff in ASP.

Can you explain a little more? Sorry to keep asking questions and not being clear with my answers, but I'm not quite sure of the specifics. Can you give us some code to see what you've attempted?



John316 -> RE: Access Report question using VB (8/29/2003 16:25:49)

rdouglass,

I have a access report. I want to hide any row if itemA, itemB, itemC, and itemD all = 0 rdouglass do you know how I would do that? I was thinking of something like:

If itemA = 0 and itemB = 0 and itemC = 0 and itemD = 0 hide row
Else Show row

I am showing this in a MS access report. So I was thinking of showing this in the footer.

John316



John316 -> RE: Access Report question using VB (9/2/2003 3:06:57)

So rdouglass can you help???

John316



rdouglass -> RE: Access Report question using VB (9/2/2003 10:12:29)

Are you doing this in Access or in an ASP page?



John316 -> RE: Access Report question using VB (9/2/2003 10:53:31)

Access 2000

John316



John316 -> RE: Access Report question using VB (9/3/2003 9:21:47)

Can anyone else help me with this problem?

John316



rdouglass -> RE: Access Report question using VB (9/3/2003 11:11:44)

Sorry, been tied up...

Are you grabbing info (for your report) directly from a table? The way I see your problem, why couldn't you build a query that used those criteria and build a report based on the query and not the table?

Does that make sense?



John316 -> RE: Access Report question using VB (9/3/2003 11:52:03)

rdouglass,

Because there are 10 queries that dump info into this report. It would just be easier to just write a VB script on the report.

John316



mfnickster -> RE: Access Report question using VB (9/3/2003 20:10:14)

Hi John,

I'm new on the forum. I think something like this would work:

- Go to the properties of the Detail section of the report
- Add an event procedure for the "On Format" event
- Enter the following code in the event procedure: 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    If (Me.[ItemA]=0 And Me.[ItemB]=0 And Me.[ItemC]=0 And Me.[ItemD]=0 ) Then
        Me.Section(acDetail).Visible = False
    Else
        Me.Section(acDetail).Visible = True
    End If

End Sub

See if that does the trick!

Best regards,
- Nickster



John316 -> RE: Access Report question using VB (9/4/2003 11:56:32)

mfnickster,

IT did not work, any other idea's?

John316



mfnickster -> RE: Access Report question using VB (9/4/2003 13:20:30)

quote:

ORIGINAL: John316

IT did not work, any other idea's?

John316


Hmmm, I tested it on one of my own reports, and it worked okay, so I'm not sure what's missing in your case. Maybe the field names aren't right?

Check to make sure the "On Format" property of the Detail section shows "[Event Procedure]," so it knows to run when that event occurs.

Also try changing the prodedure a little at a time, starting with the simplest form of expression... do this first: 

If (True) Then
    Me.Section(acDetail).Visible = False
Else
    Me.Section(acDetail).Visible = True
End If

That should hide all the rows. If it does, then changing the "Visible" property works right. Then, change the condition to "If ([itemA]=0)" to see if that is successful. You can hack through it a piece at a time, adding conditions as you go.

Let me know what happens when you try it. If it doesn't work, I'm not sure what to try next until we know why it doesn't work. [8|]

Best regards,
- Nickster



John316 -> RE: Access Report question using VB (9/4/2003 16:19:58)

Nickster,

First off Detail section shows [Event Procedure]. Next when I type

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If (True) Then
Me.Section(acDetail).Visible = False
Else
Me.Section(acDetail).Visible = True
End If
End Sub


Everything was viewable nothing was invisible, why is that? It looks like it has a problem with the If statement. Also does it matter that itemA control source is a formula: =Sum(IIf([RECORDTYPE]="ItemA",+[PRODOLS],0))

Again thanks for helping me out,
John316



mfnickster -> RE: Access Report question using VB (9/4/2003 17:27:32)

quote:


Everything was viewable nothing was invisible, why is that?


I'm not sure why! It should work fine, it's pretty straightforward. Your 'If' statement looks okay, so I don't think that's the problem.

This may seem like an obvious question, but I have to make sure-- are the fields you want to hide actually in the Detail section? Try putting something else into the Detail section (like a colored rectangle) to make sure it is showing.

The next thing to try is to manually set the "Visible" property (in the "Format" tab) of the Detail section to "No," then preview. Make sure that the Detail section can in fact be hidden.



John316 -> RE: Access Report question using VB (9/9/2003 13:01:53)

Well this is what I cameup with

Private Sub GroupFooter3_Print(Cancel As Integer, PrintCount As Integer)
If Me![itemA] = 0 And Me.Controls!itemB = 0 And Me.Controls!itemC = 0 And Me.Controls!itemD = 0 Then

Me.Controls!rowitem1.Visible = False
Me.Controls!rowitem2.Visible = False
Me.Controls!rowitem3.Visible = False
Me.Controls!rowitem4.Visible = False
Me.Controls!rowitem5.Visible = False
Me.Controls!rowitem6.Visible = False
Me.Controls!rowitem7.Visible = False
Me.Controls!rowitem8.Visible = False
Me.Controls!rowitem9.Visible = False
Me.Controls!itemA.Visible = False
Me.Controls!itemB.Visible = False
Me.Controls!itemC.Visible = False
Me.Controls!itemD.Visible = False
Me.Controls!rowitem10.Visible = False
Me.Controls!rowitem11.Visible = False
Me.Controls!rowitem12.Visible = False
Me.Controls!rowitem13.Visible = False
Me.Controls!rowitem14.Visible = False
Me.Controls!rowitem15.Visible = False
Me.Controls!rowitem16.Visible = False
Me.Controls!rowitem17.Visible = False

Else

Me.Controls!rowitem1.Visible = True
Me.Controls!rowitem2.Visible = True
Me.Controls!rowitem3.Visible = True
Me.Controls!rowitem4.Visible = True
Me.Controls!rowitem5.Visible = True
Me.Controls!rowitem6.Visible = True
Me.Controls!rowitem7.Visible = True
Me.Controls!rowitem8.Visible = True
Me.Controls!rowitem9.Visible = True
Me.Controls!itemA.Visible = True
Me.Controls!itemB.Visible = True
Me.Controls!itemC.Visible = True
Me.Controls!itemD.Visible = True
Me.Controls!rowitem10.Visible = True
Me.Controls!rowitem11.Visible = True
Me.Controls!rowitem12.Visible = True
Me.Controls!rowitem13.Visible = True
Me.Controls!rowitem14.Visible = True
Me.Controls!rowitem15.Visible = True
Me.Controls!rowitem16.Visible = True
Me.Controls!rowitem17.Visible = True
End If

End Sub

I modified the code you posted and got it to somewhat work, but I don't think we can do this using the "Visible" property ... I end up with a lot of blank spaces for the records made invisible. Any other idea's?

John316



mfnickster -> RE: Access Report question using VB (9/9/2003 21:03:01)

quote:

ORIGINAL: John316

Well this is what I cameup with

... I end up with a lot of blank spaces for the records made invisible. Any other idea's?

John316

If you turn off the 'Visible' property for the individual controls, then they will still take up the same amount of space, even though you can't see them. You could try setting the 'Height' property for the controls to zero, and the same for the section's 'Height' property.

Note: when you set the height of an object in VB, the unit is 'twips.' You should multiply inches x 1440 twips per inch.

If you make the section invisible, it will not take up any space. You do need to know the identifying constant for the section (it will be like 'acDetail' for the Detail section, but probably something like 'acGroupFooter3').

Try looking in the help file for 'acDetail,' it might have some information about working with sections of reports. If all else fails, you can send me a copy of the database and I'll try to get it to work! [&:]

Best regards,
- Nickster



John316 -> RE: Access Report question using VB (9/11/2003 15:46:50)

Thanks mfnickster it worked

John316



mfnickster -> RE: Access Report question using VB (9/11/2003 17:49:43)

Cool! [8D]

Glad to be of help!
- Nickster



john161 -> RE: Access Report question using VB (9/19/2006 11:50:02)

hi there,

found this thread from a long time ago...but have similar situation.

I want to hide a row in an access report with column heading "VerifiedBy" if there is data in this field. I've been pulling my hair out trying to understand similar threads but I have zero knowledge of VB.

Appreciate any help.

john161



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.046875