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

[mangoose]

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:


My report show up blank like so:


Instead of populated with only the specific Company's records (here is a fully populated report with all the sample data):


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:

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.

[June7]

Does the RowSource for company combobox include an ID field? Show combobox properties:

RowSource
BoundColumn
ColumnCount
ColumnWidths

[mangoose]

Rowsource: SELECT [ProposalT].[ProposalNumber], [ProposalT].[TenantCompany] FROM ProposalT ORDER BY [TenantCompany];
BoundColumn: 1
ColumnCount: 2
Column Widths: 0";1.3021"

[June7]

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.

[mangoose]

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).

[mangoose]

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?

[mangoose]

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.

[June7]

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.

[ssanfu]

Do you have a table for "TenantCompany"? There should only be one record per tenant company.

SELECT [TenantCompanies].[TenantCompany] FROM TenantCompanies ORDER BY [TenantCompany]; 

Or try:

SELECT DISTINCT [ProposalT].[TenantCompany] FROM ProposalT ORDER BY [TenantCompany]; 

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

[mangoose]

select distinct worked for me! thanks!

[mangoose]

I am currently trying to get the second criteria working and have been poking around on this and other forums. This is my 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?

[ssanfu]

Add debug statements or msgbox statements in teh code to see if the result is what you expect..

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.

[mangoose]

Problem Solved! Thanks very much!!! Here is the working 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

[June7]

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