Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss 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 .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old May 10th, 2005, 07:36 AM
Registered User
 
Join Date: May 2005
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default VBA Script to search fields and display contents

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?

Thanks,

Bill


Reply With Quote
  #2 (permalink)  
Old May 15th, 2005, 10:19 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 0 Times in 0 Posts
Default

How about just fixing the database? :D

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:
Code:
part, Job
part, part, job
part, part, part, job
part, part, job
I don't know why it can't be:

part, empty, empty, empty, empty, job

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
Reply With Quote
  #3 (permalink)  
Old May 16th, 2005, 01:03 PM
Registered User
 
Join Date: May 2005
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?






Can this even be accomplished?




Reply With Quote
  #4 (permalink)  
Old May 16th, 2005, 10:32 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #5 (permalink)  
Old May 18th, 2005, 02:27 PM
Registered User
 
Join Date: May 2005
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It does not work.

If I run the query, this is what you will see


Customer Part# Text1 Text2 Text3 Text4 Text5 Job Description

Mycustomer 848 848B 848C 848D 111 This is the job
Mycustomer 848 222 Test


Now I can group by customer, then part number which will give me


Mycustomer

          Part # 848
        Job# 111 This is the job
                Job# 222 Test


But what I can’t figure out is how to make it look like the following


Mycustomer

           Part # 848
        Job# 111 This is the job
                Job# 222 Test

           Part# 848B
                Job#111 This is the job

           Part# 848C
            Job#111 This is the job

           Part# 848D
            Job# 111 This is the job



I tried creating 5 individual queries and them added them to a master query, but it…lol crashed.



Reply With Quote
  #6 (permalink)  
Old May 25th, 2005, 07:33 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #7 (permalink)  
Old May 25th, 2005, 07:44 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 0 Times in 0 Posts
Default

BTW, sorry for the delayed response. Big week for me last week. I got hired at Microsoft and it was my birthday!
Reply With Quote
  #8 (permalink)  
Old June 2nd, 2005, 07:22 AM
Registered User
 
Join Date: May 2005
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

IT WORKED!!!!!!

Thank you so much for your help!!! I have heard of a union query, never needed to create one before.

Happy Birthday!!
Congratulations on your new job!

Reply With Quote
  #9 (permalink)  
Old June 2nd, 2005, 07:33 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Congrats to you too!

And thank you.
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA code to Search All Text Fields on a form dezmond2 BOOK: Access 2007 VBA Programmer's Reference ISBN: 978-0-470-04703-3 1 July 7th, 2007 11:58 AM
display contents from aspx in IE thas123 ASP.NET 2.0 Basics 0 March 25th, 2007 02:54 AM
Search the contents of a node azbij XSLT 1 August 18th, 2005 04:03 AM
Search and display fields in XML file kts_33 XML 5 September 28th, 2004 08:17 AM
Search and display fields in XML file kts_33 Classic ASP XML 1 September 22nd, 2004 05:16 AM



All times are GMT -4. The time now is 03:43 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.