Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 45
  1. #16
    The U is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    25
    OK, thanks. I will try to clarify this. Unfortunately, I am a novice in Access, so I will try to explain this in non-technical terms. This is just an example, not my real DB.
    (A) The AA field is indeed pointless, do not worry about this.
    (B) Where did you find "+Recoveries-Unterbericht"? I see only "DataEntry-Subform".
    (C) This is because it is just an example. I do not use reserved column names in my actual DB.
    (D) I am not quite sure what a Child/Master field actually is. I suspect it is the column that connects two tables/reports? It is the field "Number".

    Let me now try to clarify what the actual purpose will be: In Table1 the column [Number] is unique. There will be other columns associated with it (I guess that is why added [AA]). In Table2 the column [Number] can contain the same value multiple times. However, every value has a match to Table1 [Number]. In Table2, each row can have a different [Name] and multiple other columns. The same name can be associated with multiple [Number]s but does not have to. Is the structure clear to you?

    Report1 gets data from Table1 and Subreport1 data from Table2. Report1 will contain details on each unique [Number] and the Subreport1 will contain [Name]s and other columns from Table2.

    What I want is a Message box or something like that in DataEntry, in which I can enter a name. This should then select all rows from Table2, where the name occurs in column [Name]. Based on this output, Table1 will be filtered, but now the values in column [Number] must match. And all that data will be passed to Report1 (and the Subreport1), generating one report for each value in the filtered [Number] column from Table1. Sorry, if I am still unclear, I hope it becomes clearer to you.

  2. #17
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    B) it is the subform control name
    D) a typical form/subform design involves a field that is on both forms, which ties the 1 side (usually the top, main form) to the subform with the many side (multiple records that relate to the main form's key field. The main form field is the parent. The subform with many records contains the child field. If you navigate to another record, the parent value changes thus so do the subform records if there are any for that new parent value.
    C) OK so if you say a table field is called [Number] for example, I will ignore that.
    Is the structure clear to you?
    I think so. You have a field in a table where [Number] is unique and any value therein may be in table 2 many times or not at all.
    What I want is a Message box or something like that in DataEntry, in which I can enter a name.
    If I pick "John" I should get the ID's from table 1 (since it's the only field you could want other than [Number]) that match John's [Number](s) which is 502 and 503. Thus ID's of 1 and 2 would be returned from table 1 as well as AA field values. Number should not be in table 2 anyway, so forget about getting it from there.

    Does that describe the need?

  3. #18
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    see if this is close to what you want. Original objects are named copy of...
    There is a table with a few notes.
    Database1.zip

  4. #19
    The U is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    25
    Hi Micron, thanks a lot for your effort. Not quite what I wanted though. If I pick "John" then the header of my report (basically the report1) will show the [Number] 502. The subreport should show all entries from table2 that corrspond to the [Number] 502, i.e.
    ID Number AA Name
    9 502 AA John
    11 502 CC John
    12333 502 HH Dirk

    Since "John" is also linked to [Numbers] 503 and 505, two more reports should be appended to the first, namely those for [Numbers] 503 and 505, with the same structure as the first (i.e. report1 reports the [Number] and the subreports show all entries from table 2 corresponding to the numbers. Would that be possible?

  5. #20
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If one could append any random number of sub reports onto a main report, I wouldn't know how, and I really doubt there'd ever be a valid case for doing that. Sometimes what people think they need is driven by a lack of knowledge of how things should be designed, either from an efficiency standpoint, data integrity or just plain avoiding banging one's head against the wall. I think if you group by number instead of name as I did, and add the ID field to the report, you'd have what you seem to be asking for without the need for a sub report.

    Honestly, if you really need the sub report, I can't see it because we're dancing around the real issue due to me not knowing what's actually driving what you say is the need. Sometimes fake data and pseudo requirements are a big road block to quickly arriving at the correct solution for the db at hand. I think this is one of those times as I alluded to in post 15. I regret to say I can't devote more time to designing this because I tend to get occupied with other things leading up to Christmas and because what you've chosen to reveal doesn't support what you're asking for when I look at it from a design perspective. If it did, I might not feel like I'm just spinning my wheels here.

    If altering the report grouping as I suggested doesn't satisfy the need, maybe pbaldy can pick up where I left off.
    In the absence of real and accurate requirements, I wish you good luck with your db.

  6. #21
    The U is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    25
    OK, I actually found a solution, which is almost working, see below. Now my main problem is how I could append all reports that are produced in the loop into one file (best would be PDF, but RTF is also fine). Also, is there a way to have a listbox where I can either select multiple names or type in all names as a string?

    Code:
    Private Sub Befehl2_Click()
    Dim dbs As DAO.Database
    Dim strWhere As String
    Dim myPath As String
    Dim strReportName As String
    Dim strSQL As String
    Dim rs1 As DAO.Recordset
    Dim v As String
    Dim varItem As Variant
    Dim strFilter As String
    
    Set dbs = CurrentDb
    
    strSQL = "DELETE * FROM TEST"
    dbs.Execute strSQL
    
    If SelectName.ItemsSelected.Count = 0 Then
       MsgBox "Must choose one or more names for report."
       Exit Sub
    End If
    
    For Each varItem In SelectFinder.ItemsSelected
       strFilter = strFilter & SelectName.ItemData(varItem) & "','"
    Next
    
    strSQL = "INSERT INTO TEST SELECT Number, Name FROM [+Recoveries] WHERE Name IN('" & strFilter & "');"
    dbs.Execute strSQL
    
    Set rs1 = dbs.OpenRecordset("Select Distinct Number From TEST")
        
        Do While Not rs1.EOF
            v = rs1.Fields(0).Value
            strWhere = "[Number] = """ & v & """"
            DoCmd.OpenReport "Report1", acViewPreview, , strWhere
            myPath = CurrentProject.Path & "\"
            strReportName = v & ".pdf"
            DoCmd.OutputTo acOutputReport, "Report1", acFormatPDF, myPath & strReportName, False
            DoCmd.Close acReport, "Report1"
            rs1.MoveNext
        Loop
        
    End Sub

  7. #22
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Like this?

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

    Rather than export a file with each, you can create a similar string and export a report that includes all items.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #23
    The U is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    25
    This worked, thank you very much!

    I am trying to implement this Search-As-You-Type on my listbox:
    http://www.opengatesw.net/ms-access-...pe-Access.html

    This almost works but constantly throws the error message: Error 438, Line 240. I suspect this to come from the module "modSearch" from the above homepage that I entered (see below). If I understand this correctly, then the error occurs in line 240:
    Code:
    ctlCountLabel.Caption = "Displaying " & Format(ctlFilter.ListCount - 1, "#,##0") & " records"
    Does anyone know how to solve this? I am using a German Access 2010 version.

    Code:
    Option Compare Database
    Option Explicit
    '************* Code Start **************
    ' This code was originally written by OpenGate Software
    ' It is not to be altered or distributed,
    ' except as part of an application.
    ' You are free to use it in any application,
    ' provided the copyright notice is left unchanged.
    ' OpenGate Software    http://www.opengatesw.net
    
    Function fLiveSearch(ctlSearchBox As TextBox, ctlFilter As Control, _
                          strFullSQL As String, strFilteredSQL As String, Optional ctlCountLabel As Control)
    '==================================================================================
    '  THIS FUNCTION ALLOWS YOU TO FILTER A COMBO BOX OR LIST BOX AS THE USER TYPES
    '  ALL YOU NEED TO DO IS PASS IN THE CONTROL REFERENCE TO THE SEARCH BOX ON YOUR
    '  FORM, THE LISTBOX/COMBO BOX YOU WANT TO FILTER, AND WHAT THE FULL AND FILTERED
    '  SQL (ROWSOURCE) SHOULD BE.
    '
    '  ctlSearchBox       THE TEXTBOX THE USER TYPES IN TO SEARCH
    '
    '  ctlFilter          THE LISTBOX OR COMBOBOX ON THE FORM YOU WANT TO FILTER
    '
    '  strFullSQL         THE FULL ROWSOURCE YOU WANT TO DISPLAY AS A DEFAULT IF NO
    '                     RESULTS ARE RETURNED
    '
    '  strFilteredSQL     THE FILTERED ROWSOURCE FOR THE LISTBOX/COMBOBOX; FOR EXAMPLE
    '                     YOU WOULD WANT TO USE '...like ""*" & me.txtsearch.value & "*"""
    '                     TO FILTER THE RESULTS BASED ON THE USER'S SEARCH INPUT
    '
    ' ctlCountLabel       (OPTIONAL) THE LABEL ON YOUR FORM WHERE YOU WANT TO DISPLAY THE
    '                     COUNT OF ROWS DISPLAYED IN THE LISTBOX/COMBOBOX AS THEY SEARCH
    '=====================================================================================
    
    'ADVANCED PARAMETERS - Change these constants to change the behaviour of the search
      Const iSensitivity = 1 'Set to the number of characters the user must enter before the search starts
      Const blnEmptyOnNoMatch = True 'Set to true if you want nothing to appear if nothing matches their search
    
    
    10    On Error GoTo err_handle
      
              'restore the cursor to where they left off
    20        ctlSearchBox.SetFocus
    30        ctlSearchBox.SelStart = Len(ctlSearchBox.Value) + 1
            
    40        If ctlSearchBox.Value <> "" Then
                     'Only fire if they've input more than two characters (otherwise it's wasteful)
    50               If Len(ctlSearchBox.Value) > iSensitivity Then
    60                   ctlFilter.RowSource = strFilteredSQL
    70                   If ctlFilter.ListCount > 0 Then
    80                       ctlSearchBox.SetFocus
    90                       ctlSearchBox.SelStart = Len(ctlSearchBox.Value) + 1
    100                   Else
    110                     If blnEmptyOnNoMatch = True Then
    120                      ctlFilter.RowSource = ""
    130                     Else
    140                      ctlFilter.RowSource = strFullSQL
    150                     End If
    160                   End If
    170             Else
    180               ctlFilter.RowSource = strFullSQL
    190             End If
    
    200        Else
    210           ctlFilter.RowSource = strFullSQL
    220        End If
                
                'if there is a count label, then update it
    230         If IsMissing(ctlCountLabel) = False Then
    240           ctlCountLabel.Caption = "Displaying " & Format(ctlFilter.ListCount - 1, "#,##0") & " records"
    250         End If
                
    260   Exit Function
    err_handle:
    270   Select Case Err.Number
        Case 91 'no ctlCountLabel
           'exit
    280       Case 94 'null string
           'exit
    290       Case Else
    300         MsgBox "An unexpected error has occurred: " & vbCrLf & Err.Description & _
                vbCrLf & "Error " & Err.Number & vbCrLf & "Line: " & Erl
    310   End Select
    
    
    End Function
    '   ***** Code End ******

  9. #24
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    How are you calling it? ctlFilter is a listbox, and ctlCountLabel is a label?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #25
    The U is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    25
    I followed the instructions on:
    http://www.opengatesw.net/ms-access-...pe-Access.html

    ctlFilter is my listbox MyListbox and ctlCountLabel I build as suggested under point 5 on that homepage. That means, I made a textbox named txtCount where I put

    Code:
    ="Displaying " & [MyListbox].
    [ListCount] & " records"
    in the ControlSource. Is that correct? The strange thing is that the Search-As-You-Type works but whenever I enter a value it throws the above error.

  11. #26
    The U is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    25
    If it helps, this is the whole code that I actually just copied from opengatesw.net:

    Code:
    Option Compare Database
    Private blnSpace As Boolean
    
    Private Sub txtSearch_KeyPress(KeyAscii As Integer)
    'NECESSARY TO IDENTIFY IF THE USER IS HITTING THE SPACEBAR
    'IN WHICH CASE WE WANT TO IGNORE THE INPUT
    
    10 On Error GoTo err_handle
    
    20 If KeyAscii = 32 Then
    30 blnSpace = True
    40 Else
    50 blnSpace = False
    60 End If
    
    
    70 Exit Sub
    err_handle:
    80 Select Case Err.Number
    Case Else
    90 MsgBox "An unexpected error has occurred: " & vbCrLf & Err.description & _
    vbCrLf & "Error " & Err.Number & "(" & Erl & ")"
    100 End Select
    End Sub
    
    Private Sub btnClearFilter_Click()
    'CODE FOR THE RED "X" BUTTON TO CLEAR THE FILTER AND SHOW ALL
    On Error Resume Next
    10    Me.txtSearch.Value = ""
    20    txtSearch_Change
    End Sub
    
    Private Sub txtSearch_GotFocus()
    ' USED TO REMOVE THE PROMPT IF THE CONTROL GETS FOCUS
    10 On Error Resume Next
    20 If Me.txtSearch.Value = "(type to search)" Then
    30 Me.txtSearch.Value = ""
    40 End If
    End Sub
    
    Private Sub txtSearch_LostFocus()
    ' USED TO ADD THE PROMPT BACK IN IF THE CONTROL LOSES FOCUS
    10 On Error Resume Next
    20 If Me.txtSearch.Value = "" Then
    30 Me.txtSearch.Value = "(type to search)"
    40 End If
    
    End Sub
    
    Private Sub txtSearch_Change()
    'CODE THAT HANDLES WHAT HAPPENS WHEN THE USER TYPES IN THE SEARCH BOX
    Dim strFullList As String
    Dim strFilteredList As String
    
    
    10 If blnSpace = False Then
    20 Me.Refresh 'refresh to make sure the text box changes are actually available to use
    
    'specify the default/full rowsource for the control
    30 strFullList = "SELECT DISTINCT [+Recoveries].Finder FROM [+Recoveries] ORDER BY [Finder];"
    
    'specify the way you want the rowsource to be filtered based on the user's entry
    40 strFilteredList = "SELECT DISTINCT [Finder] FROM [+Recoveries] WHERE [Finder] LIKE ""*" & Me.txtSearch.Value & _
    "*"" ORDER BY [Finder]"
    
    'run the search
    50 fLiveSearch Me.txtSearch, Me.SelectFinder, strFullList, strFilteredList, Me.txtCount
    60 End If
    
    End Sub

  12. #27
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The code is expecting you to pass a label and it will set the caption (point 5 says label, not textbox). You haven't said how you're calling it, but if you're not using the label they described you shouldn't have anything in that argument.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #28
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try dropping the label argument:

    50 fLiveSearch Me.txtSearch, Me.SelectFinder, strFullList, strFilteredList
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #29
    The U is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    25
    Great, now everything works. Thank you very much!

  15. #30
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 2 of 3 FirstFirst 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