By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,211 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

Question about Reports

P: 9
I'm doing an Access inventory program. I already got practically everything done, but there's only one thing that's giving me trouble right now, and it involves the reports. I'm going to try to explain it as general as possible.

Suppose you have item#1, and item#1 has attribute#1, attribute#2, attribute#3 and attribute#4. Now, suppose you have item#2, and item#2 has attribute#2 and attribute#4.

Now, in the table, an item that doesn't have a certain "attribute" will receive a N/A. For example, since item#2 only has attribute#2 and 4, in item#2's column value for attributes #1 and #3 will be N/A.

Now, on to my problem. Using a query of that table, I will be creating a report as a sort of "receipt". The receipt will show the items and their respective attributes. However, what I want is that when it writes an item, the attributes that it doesn't have doesn't write it. For example, if I want to write item#2, I don't want the attributes #1 and #3, since they will display N/A, and I don't think it looks good.

I already have the necessary code for it, my problem is that I don't know when it will execute. I've tried it on report load, but it only takes the value of the last field. I need the code to check every time it writes an item. Does anyone how how to do this? Please this is the only thing I have to do to finish my program.
Mar 8 '10 #1
Share this Question
Share on Google+
22 Replies

P: 109
I might have read this wrong but couldn't you change the query slightly. So in the criteria for each attribute you could put <> "N/A" (so the query will display all fields where it doesn't display "N/A"). That's assuming that every attribute field in your table(s) are attributed with an "N/A" value if the item doesn't have that attribute.

Hope that makes sense!
Mar 8 '10 #2

Expert Mod 100+
P: 2,321
To give a proper answer, you will have to provide your code, as well as your table structure. Are all attributes stored in the main table or in subtables?
Mar 8 '10 #3

P: 9
Ok, my inventory program is for trading cards called Yu-Gi-Oh. These cards are divided in three types: Monster, Spell and Trap. Monsters have monster type, attack and defense. Spells and Traps don't have these, so whenever it is a spell or trap, I have a N/A value in those three fields. In the report, I want to hide those three fields whenever the card type is spell or trap. I hope that makes things easier.

Hedges: I tried what you said but the problem is that it hides the whole card, not just the column.
Mar 8 '10 #4

P: 109
Are all of those fields stored in one single table?
Mar 9 '10 #5

P: 9
Yes, and my key fields are Card Name and the condition of the card.
Mar 9 '10 #6

Expert Mod 100+
P: 2,321
You should put monster type, attack and defense into a seperate table, and then draw the info into your report using a supReport.
Mar 9 '10 #7

P: 9
It still deletes the whole item. The only thing I need right now is a way to check a field of the report everytime it receives new information every time it writes a new item. That's the only thing I need so I can check and delete and put my code to work. If I have 4 cards to display, I need to check the field "CardType" 4 times to check if it's the type I want, that's all. Now, can anyone tell me how to do this? I tried report load and it only checks the last one, and cardtype_afterupdate doesn't work either.
Mar 9 '10 #8

Expert Mod 100+
P: 2,321
You should put it in the Detail_Format.
Mar 9 '10 #9

P: 9
sigh. It didn't work, maybe what I'm trying to do is impossible. I've searched everywhere and tried everything.
Mar 9 '10 #10

Expert Mod 100+
P: 2,321
Ive asked you before to provide the code your using. I can't really help you otherwise.
This would be an example of how it could work.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_Format(Cancel as integer)
  2. If me.tb_CardType=1 then
  3.   me.tb_Attack.Visible=True
  4. else
  5.   Me.tb_Attack.Visible=False
  6. end if
Mar 9 '10 #11

P: 9
Expand|Select|Wrap|Line Numbers
  1. If Me.CardType = "Spell" Or Me.CardType = "Trap" Then
  2.     Me.MonsterType.Visible = False
  3.     Me.MonsterType_Label.Visible = False
  4.     Me.Attack_Label.Visible = False
  5.     Me.Attack.Visible = False
  6.     Me.Defense.Visible = False
  7.     Me.Defense_Label.Visible = False
  8.     Me.Price.Left = 4.3333 * 1440
  9.     Me.Price_Label.Left = 4.3333 * 1440
  10. Else
  11.     Me.MonsterType.Visible = True
  12.     Me.MonsterType_Label.Visible = True
  13.     Me.Attack_Label.Visible = True
  14.     Me.Attack.Visible = True
  15.     Me.Defense.Visible = True
  16.     Me.Defense_Label.Visible = True
  17.     Me.Price.Left = 6.0833 * 1440
  18.     Me.Price_Label.Left = 6.0833 * 1440
  19. End If
Sorry for not posting it before. This is how it goes, whenever it is spell or trap it will hide monster type, attack and defense and move the price to the left so that it looks better. I need this code to be executed everytime a card is added to the report. I tried detail format and it didn't work, like always it takes the information of the last one.
Mar 9 '10 #12

Expert Mod 100+
P: 2,321
Do you have 1 card per "detail" section or are you cramming em all into the same detail section?

These things should work (in the Detail_Format) unless your doing something odd in your report. If you cannot get it to work, attach your DB to this thread (compact before doing so) and I will take a look at it for you.
Mar 9 '10 #13

P: 9
Ok I attached the program here, I think (sorry still new to the website). If you could help me out here I would appreciate it a lot. Also there's a mixture of english and spanish there, so if you have any questions let me know.
Attached Files
File Type: zip Practice 2 (231.1 KB, 41 views)
Mar 9 '10 #14

Expert Mod 100+
P: 2,321
What version of Access are you using?

Nvm. Saw the file format.
Mar 9 '10 #15

Expert Mod 100+
P: 2,321
I looked at your report, and put a Debug.print "Detail is being Formatted" into the detail event. The debug message was never being printed, so the event was simply not firing. I tried:

To make a copy of the report. No luck.
To delete the module, and then create a new module
Compact and Repair on the database.
To tie events into all your groupheaders, which also did not fire.

Sorry, im at a loss as to what is happening. I can seriously understand that you have been baffled as well. I have made extensive use of the Detail_Format event myself (allthough in Access 2003) and never encountered anything like this. Whether its an Access 2007 bug, or related to the project type you have selected (accdb) I really don't know.
Sorry I could not help you more.
Mar 9 '10 #16

P: 9
I see. Well I guess I have no choice but to leave it like that then. Thanks so much for taking your time to check my program!
Mar 9 '10 #17

Expert 100+
P: 378
It's working for me (FYI for those who are using Access 2007, I dont think detail format events work in the Layout/Report view, only in print preview).

The reason your rows arent appearing right is because you're not only formatting objects in the detail block, you're trying to change the objects in the preceeding header as well (your column labels). So it will need to cycle through an entire cardname grouping before it can change the visible properties of the labels. Try to think of a detail format event as commands given while the report is being printed. Once you've passed the header labels, they're printed and cant be altered, only the next time that header appears will your changes visibly take effect.

So, your solution would involve a restructuring of your report.
Mar 9 '10 #18

Expert Mod 100+
P: 2,321
I can try loading your report in print preview. I was not aware there was a difference, as 99.9% of my usual work is done in Access 2003. I will take a look when I get home.
Mar 10 '10 #19

Expert Mod 15k+
P: 31,299
I think Megalog has hit the nail on the head here.

Consider the logic of having a number of records within a grouping. If some of them can be of one type while others are of another, how would it be possible to set the header (title) to reflect the data? If each grouping is consistent within itself, on the other hand, and all records until the next group header are similar, then consider putting the code to handle that in the Format event of the group header instead. Had you explained this in the original question, then Smiley would never have suggested the Detail_Format event.
Mar 10 '10 #20

Expert Mod 15k+
P: 31,299
You could also consider simplifying your code somewhat :
Expand|Select|Wrap|Line Numbers
  1. Private Sub YourGroup_Format()
  2.     Dim blnVisible As Boolean
  3.     Dim dblPriceLeft As Double
  5.     If Me.CardType = "Spell" Or Me.CardType = "Trap" Then
  6.         blnVisible = False
  7.         dblPriceLeft = 4.3333
  8.     Else
  9.         blnVisible = True
  10.         lngPriceLeft = 6.0833
  11.     End If
  12.     With Me
  13.         .MonsterType.Visible = blnVisible
  14.         .MonsterType_Label.Visible = blnVisible
  15.         .Attack_Label.Visible = blnVisible
  16.         .Attack.Visible = blnVisible
  17.         .Defense.Visible = blnVisible
  18.         .Defense_Label.Visible = blnVisible
  19.         .Price.Left = dblPriceLeft * 1440
  20.         .Price_Label.Left = dblPriceLeft * 1440
  21.     End With
  22. End Sub
Mar 10 '10 #21

Expert 100+
P: 378
I think the easiest solution to this is in the placement of the fields. Put the price column to the left to begin with, and remove any code that alters visibility in the header labels. Then the rest should work as the OP intended.
Mar 10 '10 #22

Expert Mod 15k+
P: 31,299
That's only a solution if you're prepared to make the assumption that this fits in with the OP's requirements. I've seen nothing as yet to give me that impression. It may do. It's worth asking.
Mar 10 '10 #23

Post your reply

Sign in to post your reply or Sign up for a free account.