Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    ninachopper is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    25

    vba question from newbie

    Hello,


    I am trying to figure out the code and am having some trouble. I have a form with a drop down parameter list that opens up a report. The code I am using is

    Private Sub openFacType_Click()
    DoCmd.OpenReport "Noise Report", acViewReport, , "[Facility Type]='" & cboFacType & "'"

    End Sub

    This is working great but I have another field with a "2nd facility type" because some of the facilities are more than one type.
    I need to know how to make this code grab the records from both of the fields and not just the "Facility Type" field.

    I am sorry if this sounds confusing but any bit of advice would be greatly appreciated.

    Thanks! Lauren

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If they're both text:

    DoCmd.OpenReport "Noise Report", acViewReport, , "[Facility Type]='" & cboFacType & "' AND OtherField = '" & OtherCombo & "'"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ninachopper is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    25
    sorry for the delayed response and thanks for the help. I am still a little unsure though. What I really was trying to do was run a combo box parameter but since I was unsure of how to do that, I tried to set the facility type up into two different fields just incase any facility was more than one type. I only have one drop down list, but I want it to search through the records of the two different fields for the results.
    Am I on the right track, or should I definitely be using the combo box? I was having trouble going that way and every time I did a search online, it seemed like the ways to run it that way were a little too advanced for me.
    Thanks again for any input!

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If I'm understanding correctly, that may be a design error (normalization, repeating fields) but does this work?

    DoCmd.OpenReport "Noise Report", acViewReport, , "[Facility Type]='" & cboFacType & "' OR [OtherFieldName] = '" & cboFacType & "'"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    ninachopper is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    25
    Thanks so much! That was it. I figured it was just a mix up in the typing I had. Fantastic.

    Just one more quick question though...

    When I run that statement it brings up the report. Is there an easy way to combine the two fields on the report if there is data in both of them?

    For example:

    On some records, there is only one facility type. But, On the one's where they are more than one type, I would like to be able to view them as A,B (combine the two fields on the report with a comma only if there is a second option)


    Thanks again! That saved me so much aggrevation!!

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try this as a textbox control source:

    =[FirstFieldName] & IIf(Len([SecondFieldName] & "") > 0, ", " & [SecondFieldName], "")
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    ninachopper is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    25
    Again, that is exactly what I needed! But how would I incorporate that into my report? Do I have to add it to my table as a new field? Or is there a way that I can just add it to my report to bring up the fields after I run the parameter?

    Again, thanks so much for your time!!!

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by pbaldy View Post
    Try this as a textbox control source:
    Put a new textbox on the report, and copy that to the control source property.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    ninachopper is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    25
    Awesome!! Thanks so much! I've taught myself access and have been struggling through these last few things for a while. Looks like I'm good to go!

    Thanks again!!

  10. #10
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problemo, and welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    ninachopper is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    25
    Hey there,
    Just one more quick snag.
    I had a characteristic table (I think that's what it is called) for the facility types and they were numbered for each type. On my reports it was set up where the numbers would automatically turn into the names of the facility types. Once I used your code for the text box to combine the two fields together, everything worked great but It is only bringing up the numbers, not the names.
    After this I promise, I'm really good to go

    Thanks for any input!!

  12. #12
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I would expect the report to be based on a query that joins the tables together, which would let you have the names as fields available for use in the textbox. Is that not the case?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    ninachopper is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    25
    got it figured out. It's really a lot going through your head at one time. whhhew! Thanks again!

  14. #14
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    There's a lot to learn, and usually 5 ways to accomplish 1 goal. It's a lot to grasp. I'm still learning too. Good luck and post back if you get stuck!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    ninachopper is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    25
    Hi Paul,

    wondering if you could help me.
    Is it possible to have an attachment pdf file that is attatched to multiple records? I am going to have about 200 pdf files, and there is approximately 1000 records. Is there a way I can link them together?
    Thanks for any advice!

    Lauren

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Newbie has a Query Question
    By Bruce in forum Queries
    Replies: 2
    Last Post: 11-20-2009, 10:38 AM
  2. Easy newbie question
    By pontuse in forum Access
    Replies: 4
    Last Post: 11-08-2009, 08:02 AM
  3. Question from a newbie
    By scarps626 in forum Access
    Replies: 2
    Last Post: 10-02-2009, 03:40 AM
  4. Newbie question
    By The_Dude in forum Programming
    Replies: 2
    Last Post: 12-23-2007, 07:11 PM
  5. Newbie question
    By benplace in forum Queries
    Replies: 1
    Last Post: 12-13-2005, 06:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums