Access VBADiscuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
I need to create a report that the user can enter a company name and it will show all the part numbers and then job number pertaining to the company.
Sounds pretty simple, but the problem is the job number record has parts in the part field, but parts in 5 other custom text boxes. (This is the way it was setup before me.)
So I am thinking I need to do this:
Enter company name
Display Part number
Job # Description
If Text1 is not null Display
Display Job# Description
If Text2 is not null Display
Display Job# Description
If Text3 is not null
Display Job# Description
If Text4 is not null Display
Display Job# Description
If Text5 is not null Display
Display Job# Description
Can anyone help me get started with some code on how to do this?
I would focus on creating the query that sets up the data for the report.
I'm not clear about what you want to accomplish from your psuedo code. But I think you're trying to display parts if they exist and finally display the job number. So it would be something like:
But if you can't have empty text boxes in the report, try this for each of your fields in the query:
Text#: iif(isnull([Text#]),[Job#],[Text#])
Where Text# is the various text fields. In fact, you can put
Code:
=Iif(isnull([Text#]),[Job#],[Text#])
as the Control Source for the fields in the report instead of building the query. I tend to start with the query because once that is correct I can reformat the report without worrying about getting all of the data right. Plus the query can be used for other purposes (e.g. fixing the database :)).
Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group http://www.pnwadg.org
I am not explaining it correctly. We have an application which keeps track of all our jobs. The reporting capabilities were very limited. So my predecessor created an access database and then linked the tables, so he could create custom reports.
The way the job tracking app works is a user would enter a job number into the application. Then they would enter a part number into a field called Part#.
This is great. I can query the part number and see all the job numbers.
The problem is we are not using this database as intended. Besides the part# filed, there are custom files in the same table that are Text1, Text2, Text3, Text4, and text5.
Users enter part number in those fields too.
The application intended that for every part# there is a job number. We are trying to have multiple part numbers to 1 job number.
So the report I need to create is:
Customer
Part number
Job number
My problem is how to I group by text1-5 and keep the report consistent?
I get the impression that you want us to say you can't do it so you'll have an excuse to get people to use the database the way it was designed and/or redesign the database. :) I say that because the solution I proposed is somewhat applicable.
Ultimately the only thing wrong with the solution I proposed was that I didn't notice that Job # Description was repeating the same information over and over. Odd that you would need to do that? I mean, why not just invert the relationship....
Customer
Job Number
Part Number
Part Number
But whatever...
You with have to have five fields for the Job Numbers on your report. Either build the query to have values for the five job number fields, or set the Control Source using the "Iif" (Immediate If). The syntax will be either:
Code:
JobNo#: Iif(isnull([Text#]),null,[JobNumber])
in the query, or
Code:
=Iif(isnull([Text#]),null,[JobNumber])
in the Control Source.
If you want the report not to use space for empty values, set the "Can Shrink" property to Yes for the Text# fields and the JobNo# fields. This will hide the field if nothing is present.
So, unfortunately, despite the fact that the database should be redesigned as it should be instead of having to come up with a kludge solution, you can still work around the way the database is being used. Sorry about that. But there are ways that you can accomplish almost anything in Access. It just takes time to figure it out.
Now I have a better idea of what you're looking for.
Use a Union like this:
Code:
Select customer, partnumber, jobnumber from table
union
Select customer, text1, jobnumber from table where text1 is not null
union
...
union
Select customer, text5, jobnumber from table where text5 is not null
Order by Customer, Partnumber, JobNumber
When you see the results of that Union I think you'll understand how to build the report.
VBA Script to search fields and display contents
Linear Mode
