Page 1 of 3 123 LastLast
Results 1 to 15 of 45
  1. #1
    The U is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    25

    Pass value from Input Box to Open Report

    Hi,
    I am very new to Access and especiall VBA. My database consists of two tables: Table1 contains unique entries in column "ID". Table2 can contain several entries for "ID". Table2 also contains a column "Name" which covers one or more IDs in column "ID".
    On a form which has as the main form Table1 and a subform Table2, I am trying to do the following after adding a button:

    1. On Click, open Input Box
    Code:
    Private Sub XXX_Click()
    Dim ToDoName As String
    ToDoName = InputBox("Enter name")
    2. Take that name and use it in an SQL query, getting all IDs that are linked to that name in Table2.
    Code:
    Dim strcmd As String
    strcmd = "SELECT ID FROM Table2 WHERE Name = """ & ToDoName & """"
    3. Open a report using the IDs in "strcmd". Something like:


    Code:
    DoCmd.OpenReport "NAMEID", acViewPreview, , strWhere=strcmd
    This does not work. As far as I can see, the SQL statement is not executed and nothing is passed to DoCmd.OpenReport. Any help from your side would be highly appreciated. I have to say, I cannot change the structure of the database.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You can do this from a form without any code...
    the query would read the form for the input.
    select * from table where [id]=forms!myForm!txtID

    the report can open with a filter or using the query above to filter...
    DoCmd.OpenReport "NAMEID", acViewPreview, , strCmd

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    But the string just has the bit after WHERE:

    http://www.baldyweb.com/wherecondition.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    The U is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    25
    Quote Originally Posted by ranman256 View Post
    You can do this from a form without any code...
    the query would read the form for the input.
    select * from table where [id]=forms!myForm!txtID

    the report can open with a filter or using the query above to filter...
    DoCmd.OpenReport "NAMEID", acViewPreview, , strCmd

    I think this does not give what I want or I do not understand it properly. I want the text box and the user needs to enter a name there (this name might not even be displayed on the opened form). This name is stored in Table2, which is a subform in the above form. The name is needed to actually find the corresponding IDs in Table2 (each name can be associated with multiple IDs). And these IDs should be passed to the report, which is based on both Table1 and Table2 (which are connected via ID). In the above I do not see where the name that the user entered is used as a filter. Is this description more helpful?

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Presuming both fields are in the report's source query, the method in post 3 should work for you.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    The U is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    25
    Quote Originally Posted by pbaldy View Post
    Presuming both fields are in the report's source query, the method in post 3 should work for you.
    Ok, I tried to transfer this to my problem:

    Code:
    ToDoName = InputBox("Enter name")
    
    DoCmd.OpenReport "NameOfSubreport", , , "Name = '" & ToDoName & "'"
    This actually produces a report, but the report only shows the Subreport, not the header(s) from the main report. It further contains only the entries for the specific name that was entered in the input box and not for all "ID"s that are associated with the name entered in the input box (i.e. in the report, there should be all names listed that are associated with the IDs with which the name entered in the input box is associated). I think column "ID" should select the data, but how?

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Oh, and I would expect the name of the report, not the subreport.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    The U is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    25
    Hi Paul, many thanks for your help. Indeed, when I use:

    Code:
    ToDoName = InputBox("Enter name")
    DoCmd.OpenReport "NameOfReport", , , "ID = '" & ToDoName & "'"
    My report opens for the correct ID. That is cool, but I need it a bit differently. The table my form is based on, looks like this:
    ID Column1
    5346502 AA
    5346503 BB
    5346504 CC
    5346505 DD
    5346506 EE

    The table my subform is based on, looks like:
    ID Name
    5346502 Uli
    5346503 Uli
    5346502 Uli
    5346503 Uli
    5346503 Hans
    5346504 Hans
    5346506 Andreas
    5346502 Dirk

    Now I want to enter in the input box for example "Hans" and I want the reports for ID 5346503 and 534604. In the report for 5346503 I also want to see the two rows with name "Uli".

    If that doesn't help you, how can I send you my DB?

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You should be able to attach it here, after compact/repair and zipping. It's in the "Go Advanced" area.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sorry, I've been unable to circle back to this. Attached is the db if somebody can help. More info on the goal is:

    I uploaded two example outcomes for the entering the names "John" or "Dirk". Basically, when opening the "DataEntry" form there should be a button on top which upon click either shows me all names (from Table2), such that I can select one or more, or that allows me to enter a name. The name(s) I entered should be from Table2 (column "Name"). Then I want to select all "Numbers" from Table2 that are associated with the name and pass all these numbers to the report "Report1". Report1, however, is based on both Table1 and Table2 and so I think there must be a way to first filter Table1 according the numbers in Table2. Report1 usually gets produced by clicking on the "Report1" button in "DataEntry" and produces output for only the active number in column "Number".
    Attached Files Attached Files
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    The U is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    25
    Hi, I just wanted to bring this up again because I did not find any solution yet. It would be great if somebody could help me with this. Many thanks!

  13. #13
    The U is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    25
    Hi, is there by any chance someone who could help me with this problem? In post #11 a summary of the problem and an example DB is posted.

  14. #14
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    will take a look and post back after digesting the new requirement from post 11
    such that I can select one or more, or that allows me to enter a name.
    I fail to see how this can be. If the name isn't in the table, what's the point of being able to enter one? There's no data for that name. Besides, the first part sounds like a multi select combo or list box. You can't enter data in a list box AFAIK, and if it was a combo, then you have to deal with the entry not being in the source - again, which makes no sense seeing as how the name isn't in the records.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe I'm way off here, but I think you threw this together using sample data and a goal that you think mimics the real thing, which you're not showing. After looking at the sample data, I don't get why there's a sub report involved at all, and that's a big worry for me. Plus I see or wonder about the following:
    - why repeat the AA field in tbl2? It's not necessary
    - special characters in object names (save for underscore) = bad; e.g. +Recoveries-Unterbericht
    - reserved words used for object names; e.g. Number, Name
    - the specified Child/Master field for the report/sub report doesn't exist on the sub report

    So even if a multi select combo (if that's what it ends up being) is added to the form, the report won't work without the sub report child field. I'll wait for replies on all of this before going further, because in the past I've crafted solutions that solved the problem only to find out that the structure and data given don't really match the real thing, thus the solutions don't work and it ends up being a waste of time and effort.

    Additional Edits:
    I don't get why there's a subform either, given the data provided.
    When I mentioned a multi-select combo, I should have mentioned that this requires a multi-value field, which as I know, neither I or many of my esteemed fellow posters here would not entertain the idea of.
    Last edited by Micron; 12-07-2017 at 10:36 PM. Reason: clarification

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

Similar Threads

  1. Replies: 3
    Last Post: 05-17-2016, 11:20 AM
  2. how to open pass through query with recordset
    By adnancanada in forum Queries
    Replies: 7
    Last Post: 01-13-2016, 11:25 AM
  3. Replies: 6
    Last Post: 10-27-2014, 08:05 PM
  4. Replies: 9
    Last Post: 06-27-2014, 08:23 PM
  5. Pass date and open outlook
    By jaykappy in forum Programming
    Replies: 3
    Last Post: 10-04-2012, 07:06 AM

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