Results 1 to 14 of 14
  1. #1
    mangoose is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    8

    Newbie here: Need help running a report based on criteria from 2 combo boxes

    I have looked everywhere, but haven't quite found the answer. I apologize if this is super simple, but I am relatively new to Access.

    I think I am close, but the problem is when I try and use the combo box for "Company" on the following form:
    Click image for larger version. 

Name:	1.PNG 
Views:	6 
Size:	8.7 KB 
ID:	13374

    My report show up blank like so:
    Click image for larger version. 

Name:	2.PNG 
Views:	6 
Size:	14.1 KB 
ID:	13375

    Instead of populated with only the specific Company's records (here is a fully populated report with all the sample data):
    Click image for larger version. 

Name:	3.PNG 
Views:	6 
Size:	23.4 KB 
ID:	13376

    No error is given, so I know the names are at least looking up something, but for some reason it won't populate.

    Here is my code for the EventProcedure on the "Find Records" button:

    Code:
    Private Sub FindRecordsbyCompany_Click()
    On Error GoTo FindRecordsbyCompany_Click_Err
    
    
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.OpenReport "RecordsbyCompanyR", acViewPreview, "", "TenantCompany = '" & Me.TenantCompany & "'"
    
    
    
    
    FindRecordsbyCompany_Click_Exit:
        Exit Sub
    
    
    FindRecordsbyCompany_Click_Err:
        MsgBox Error$
        Resume FindRecordsbyCompany_Click_Exit
    
    
    End Sub

    I am sure this is going to be a very simple problem for someone, but I cant figure it out for the life of me! Any help is appreciated.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,621
    Does the RowSource for company combobox include an ID field? Show combobox properties:

    RowSource
    BoundColumn
    ColumnCount
    ColumnWidths
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    mangoose is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    8
    Rowsource: SELECT [ProposalT].[ProposalNumber], [ProposalT].[TenantCompany] FROM ProposalT ORDER BY [TenantCompany];
    BoundColumn: 1
    ColumnCount: 2
    Column Widths: 0";1.3021"

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,621
    The OpenReport code is using ProposalNumber as filter criteria because that is the actual value held in the combobox since the BoundColumn is referencing that field and BoundColumn determines the value of the combobox.

    Change the VBA code to use the ProposalNumber field.

    Is ProposalNumber a text value? If not, remove the apostrophe delimiters.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    mangoose is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    8
    I see the issue now. Unfortunately it raises another question:

    If I use the ProposalNumber field, the report will only show that individual proposal instead of ALL proposals matching the "company" criteria. Is there a way for the VBA code to take the value of the second column in the Rowsource (in this case the "TenantCompany") field? This would allow the report to show all current records matching that company (the key here is displaying multiple records in the report).

  6. #6
    mangoose is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    8
    As a followup, i suppose the workaround would be to create an entirely new table for the "company" field and then have all the other tables and records point towards that. If the same product can be achieved using the current forms & tables, using some code that would be ideal, but if not, would this work?

  7. #7
    mangoose is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    8
    UPDATE:

    Alright so I think I found a partial solution:
    I changed the Rowsource to SELECT [ProposalT].[TenantCompany] FROM ProposalT ORDER BY [TenantCompany];
    and changed the Column Widths = 1" (so it only shows the column i want)

    Now it appears to work well, and the report operates as it should. I now have two other related items/questions to pose to you:
    1. The dropdown shows all companies in order, but shows duplicate values See picture:
    Attachment 13377
    Is there a way to keep the combo box from displaying duplicate values?

    2. I need to add the "Record Type" criteria to the open report function. Is there a way to add the following criteria to that function?:
    If "Record Type" dropdown has data, match it to the "status" field in the report, similar to above, but if it does NOT have data, display all Status types in the report.


  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,621
    1. SELECT DISTINCT TenantCompany FROM ProposalT ORDER BY TenantCompany;

    Don't you have a table of companies? Could use that as the source for combobox.

    Could have referenced the second column of combobox with (column index begins with 0):
    Me.TenantCompany.Column(1)

    2. Examples of constructing dynamic filter criteria with VBA:
    http://allenbrowne.com/ser-62code.html
    http://allenbrowne.com/ser-50.html

    Alternative would be a parameterized query.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Do you have a table for "TenantCompany"? There should only be one record per tenant company.
    Code:
    SELECT [TenantCompanies].[TenantCompany] FROM TenantCompanies ORDER BY [TenantCompany]; 

    Or try:
    Code:
    SELECT DISTINCT [ProposalT].[TenantCompany] FROM ProposalT ORDER BY [TenantCompany]; 

    --------------------------
    As always, June, you are faster than me.......

  10. #10
    mangoose is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    8
    select distinct worked for me! thanks!

  11. #11
    mangoose is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    8
    I am currently trying to get the second criteria working and have been poking around on this and other forums. This is my code:
    Code:
    Private Sub FindRecordsbyCompany_Click()
    On Error GoTo FindRecordsbyCompany_Click_Err
    
    
        DoCmd.RunCommand acCmdSaveRecord
        
    Dim whereStmt As String
        
        whereStmt = ""
      
        If Not IsNull(Me.TenantCompany) Then
        whereStmt = "[TenantCompany] = & Me.TenantCompany & " And ""
        End If
        
        If Not IsNull(Me.Status) Then
        whereStmt = "[Status] = & Me.Status"
        End If
            
        whereStmt = Left(whereStmt, Len(whereStmt) - 5)
            
        DoCmd.OpenReport "RecordsbyCompanyR", acViewPreview, "", whereStmt
    
    
    
    
    FindRecordsbyCompany_Click_Exit:
        Exit Sub
    
    
    FindRecordsbyCompany_Click_Err:
        MsgBox Error$
        Resume FindRecordsbyCompany_Click_Exit
    
    
    End Sub
    But when I attempt to run it, I get a "type mismatch" error from Access...any ideas?

  12. #12
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Add debug statements or msgbox statements in teh code to see if the result is what you expect..

    Code:
    Private Sub FindRecordsbyCompany_Click()
    On Error GoTo FindRecordsbyCompany_Click_Err
    
    
        DoCmd.RunCommand acCmdSaveRecord
        
    Dim whereStmt As String
        
        whereStmt = ""
      
        If Not IsNull(Me.TenantCompany) Then
        whereStmt = "[TenantCompany] = & Me.TenantCompany & " And ""
        End If
    
    Debug.print  whereStmt 
    '   msgbox  whereStmt             
    
        If Not IsNull(Me.Status) Then
        whereStmt = "[Status] = & Me.Status"
        End If
    
    Debug.print  whereStmt 
    '   msgbox  whereStmt         
            
        whereStmt = Left(whereStmt, Len(whereStmt) - 5)
    
    Debug.print  whereStmt 
    '   msgbox  whereStmt         
            
        DoCmd.OpenReport "RecordsbyCompanyR", acViewPreview, "", whereStmt
    
    
    FindRecordsbyCompany_Click_Exit:
        Exit Sub
    
    
    FindRecordsbyCompany_Click_Err:
        MsgBox Error$
        Resume FindRecordsbyCompany_Click_Exit
    
    
    End Sub
    Single step and look at the immediate window.

  13. #13
    mangoose is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    8
    Problem Solved! Thanks very much!!! Here is the working code:

    Code:
    Private Sub FindRecordsbyCompany_Click()
    On Error GoTo FindRecordsbyCompany_Click_Err
    
    
        DoCmd.RunCommand acCmdSaveRecord
        
    Dim whereStmt As String
    Dim lngLen As Long
        
        whereStmt = ""
      
        If Not IsNull(Me.TenantCompany) Then
        whereStmt = whereStmt & "([TenantCompany] = """ & Me.TenantCompany & """) And "
        End If
        
        If Not IsNull(Me.Status) Then
        whereStmt = whereStmt & "([Status] = """ & Me.Status & """) And "
        End If
                
        whereStmt = Left(whereStmt, Len(whereStmt) - 5)
        
        
        DoCmd.OpenReport "RecordsbyCompanyR", acViewPreview, "", whereStmt
    
    
    
    
    FindRecordsbyCompany_Click_Exit:
        Exit Sub
    
    
    FindRecordsbyCompany_Click_Err:
        MsgBox Error$
        Resume FindRecordsbyCompany_Click_Exit
    
    
    End Sub

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,621
    More guidelines on debug techniques in link at bottom of my post.

    Two things I see right off:

    1. since you want to search on company instead of a numeric ID, need text delmiters; same for status if a text field

    2. you have included the variables Me.TenantCompany and Me.Status within quotes, so you get the literal string "Me.Status" instead of the value in Me.Status

    Review Allen's example again.

    EDIT: And you fixed it while I was posting
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 06-04-2014, 10:45 AM
  2. Newbie questions - combo box criteria, etc.
    By mishraye in forum Access
    Replies: 3
    Last Post: 09-25-2012, 04:11 PM
  3. Combo Boxes Query Criteria help
    By noaccessguru in forum Queries
    Replies: 2
    Last Post: 04-30-2012, 08:09 PM
  4. Replies: 5
    Last Post: 07-01-2011, 11:13 AM
  5. Running a query based on 2 combo boxes.
    By blessoni in forum Queries
    Replies: 4
    Last Post: 12-12-2010, 02:09 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