Using VBA to generate report

[aatrv]

Hello. I'm new to this forum and also new to programming/Access. I know basic functions in Access but now i'm trying to learn more as I prepare for my internship. I have a database with about 500 names of people, what computers they own, and what apps they have installed on the computers. I want to know if there's a way to generate a report for each user that lists his/her user id,computer name,and apps. So basiclly i'm going to have 500 different reports. Someone told me I can use a VBA module to do this. Is there a tutorial or anything on this? Please let me know if you can help me out with this. Thanks

(sorry if something similar has already been posted, i searched a lot but could'nt exactly find what I needed)

 

[pbaldy]

One report, either using Sorting and Grouping to break on each user, or a parameter query or this technique:

http://www.baldyweb.com/wherecondition.htm

to restrict to a given user.

 

[aatrv]

Thanks for the quick reply!

So since I'm a beginner, I'm not sure how to sort or group a report. It would be great if you can help me understand that. Also, I clicked on the link. So in my case, "SecondFormName" would be "Inventory". "FieldName = UserID" & Me.ControlName & "myuserID".

Does this sound right?

 

[pbaldy]

Along the lines of:

DoCmd.OpenReport "Inventory", acViewPreview, , "UserID = " & Me.myuserID

As to the sorting and grouping, it would depend on your table structure, but if you build a query that returns the desired data, I would expect you to group first on the user and then on the computer. That's based on the assumption that a user can have more than one computer (desktop, laptop, etc). Then the software would be in the detail section. If you have the query, the report wizard should ask you about the grouping.

 

[aatrv]

That made sense but it didn't seem to do much. Not sure what i'm doing wrong.

I'm actually trying to do this:

-I want to be able to click on the macro so it gives me a message box asking me to input the user id.

-Once I input the ID, a list of computers and programs show up that are associated with that user (displayed as a report)

Hopefully that makes sense

 

[pbaldy]

Not knowing exactly what you're trying, I don't know what you're doing wrong either.

Can you post the db here?

 

[aatrv]

I wasn't able to post the original database, but this one will work. If you just look at the EMPLOYEE table. Let's say that the employee table contained 500 records and some users had multiple products installed on their computers. I want to be able to create a way to just enter the user Id (maybe after clicking the macro) and after I enter the User Id, the list of that users name, dept.,product, new product all shows up in a report. If they have multiple products on their computers, I want all of them to show up in their individual reports. So for example, If you look at the employee table in the attached database, you will see a user named Dale Murray. So I want to use VBA to generate a report that will show me Dale's information only. How can I do that? I hope that's not asking for too much. Thanks again for all your help.

 

[pbaldy]

I'm not a fan of the design, as it's not normalized. What's Dale Murray's employee ID? He's there twice, 1 and 6. The employee table should have a single entry per employee. If they might possibly have more than one computer, their computer(s) should be in a related table. Software should be in another related table, related to the employee table on the employee ID if they can only have one computer, or related to the computers table on computer ID.

 

[aatrv]

sorry for the late reply. But I actually figured it out on my original db. the DoCmd.OpenReport method seems to work good! Thanks again for all your help

 

[pbaldy]

Happy to help.