|
| |
|
|
John316
Posts: 214
Joined: 9/25/2002
Status: offline
 |
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
Posts: 5
Joined: 8/23/2003
Status: offline
|
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
Posts: 214
Joined: 9/25/2002
Status: offline
|
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
Posts: 214
Joined: 9/25/2002
Status: offline
|
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
< Message edited by John316 -- 8/25/2003 5:18:34 PM >
| |
|
|
|
John316
Posts: 214
Joined: 9/25/2002
Status: offline
|
RE: Access Report question using VB - 8/26/2003 21:45:17
Can someone please help me
John316
| |
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
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?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
John316
Posts: 214
Joined: 9/25/2002
Status: offline
|
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
Posts: 214
Joined: 9/25/2002
Status: offline
|
RE: Access Report question using VB - 9/2/2003 3:06:57
So rdouglass can you help???
John316
| |
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: Access Report question using VB - 9/2/2003 10:12:29
Are you doing this in Access or in an ASP page?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
John316
Posts: 214
Joined: 9/25/2002
Status: offline
|
RE: Access Report question using VB - 9/2/2003 10:53:31
Access 2000
John316
| |
|
|
|
John316
Posts: 214
Joined: 9/25/2002
Status: offline
|
RE: Access Report question using VB - 9/3/2003 9:21:47
Can anyone else help me with this problem?
John316
| |
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
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?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
John316
Posts: 214
Joined: 9/25/2002
Status: offline
|
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
Posts: 15
Joined: 9/3/2003
From: San Diego, CA
Status: offline
|
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
Posts: 214
Joined: 9/25/2002
Status: offline
|
RE: Access Report question using VB - 9/4/2003 11:56:32
mfnickster,
IT did not work, any other idea's?
John316
| |
|
|
|
mfnickster
Posts: 15
Joined: 9/3/2003
From: San Diego, CA
Status: offline
|
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. 
Best regards,
- Nickster
| |
|
|
|
John316
Posts: 214
Joined: 9/25/2002
Status: offline
|
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
< Message edited by John316 -- 9/4/2003 4:35:29 PM >
| |
|
|
|
mfnickster
Posts: 15
Joined: 9/3/2003
From: San Diego, CA
Status: offline
|
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
Posts: 214
Joined: 9/25/2002
Status: offline
|
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
Posts: 15
Joined: 9/3/2003
From: San Diego, CA
Status: offline
|
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
Posts: 214
Joined: 9/25/2002
Status: offline
|
RE: Access Report question using VB - 9/11/2003 15:46:50
Thanks mfnickster it worked
John316
| |
|
|
|
john161
Posts: 1
Joined: 9/19/2006
Status: offline
|
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
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
