Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Create report with multiple combo box selections form form?

    Question: How do I create a Report based off the values/selections of mutliple combo boxes in a form?



    I have tried this several times with several failures and have used multiple
    codes to try this and each has been unsuccesful.

    I will try to explain my database and its contents
    Tabels and Fields ((PK) indicates the primary Key):
    tblAssets: ID(PK), AssetNumber, SerialNumber, ModelName, ComputerName,
    DeploymentDate, Active, UserName, OfficeName,OSName
    tblContacts: ID, Company, LastName, FirstName, Initial, EMailAddress,
    JobTitle, BusinessPhone, Address, City, State, Zip, Country, Username(PK)
    ChargeCode, LocationCode
    tblLocation: LocationCode(PK)

    tblModel: ModelID(PK), ModelName

    tblOfficeVersion: OfficeID(PK), OfficeName

    tblOSVersion: OSName(PK)

    So initial i have tried to create a form with two combo boxes. cboModel and
    cboLocation and with a cmdbutton that open rptModel. Upon opening I want to
    see how many of each model is at each location.

    Exaple: Location: OH01
    Model: Dell 630 = 20
    IBM M50 = 15

    I have tried multiple codes with no success. Please help I have been posting
    this problem for 3 weeks with very little help. Not that people have tried
    but it seems that this is an issue that isnt covered very often. Thank you
    for any help and if there are any questions please ask. I can also provide a
    code if that helps, but like I said I have tried various differant codes with
    no success.Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    3
    Okay the first time i tried this i used the following code "On Click" for cmdApplyFilter:
    Code:
    Option Compare Database
    Option Explicit
    Private Sub cmdApplyFilter_Click()
        Dim strModel As String
        Dim strContactName As String
        Dim strFilter As String
    ' Check that the report is open
      DoCmd.OpenReport "rptContacts", acPreview, , strFilter
    ' Build criteria string for Office field
        If IsNull(Me.cboModel.Value) Then
            strModel = "Like '*'"
        Else
            strModel = "='" & Me.cboModel.Value & "'"
        End If
    ' Build criteria string for Department field
        If IsNull(Me.cboContactName.Value) Then
            strContactName = "Like '*'"
        Else
            strContactName = "='" & Me.cboContactName.Value & "'"
        End If
    ' Combine criteria strings into a WHERE clause for the filter
        strFilter = "[Model] " & strModel & " AND [ContactName] " & strContactName
    ' Apply the filter and switch it on
        With Reports![rptContacts]
            .Filter = strFilter
            .FilterOn = True
        End With
    End Sub
    My two cobo boxes:
    cboModel
    Code:
    SELECT tblModel.[ModelID], tblModel.[ModelName] FROM tblModel ORDER BY [ModelName];
    cboContactName:
    Code:
    SELECT [Query1].[Contact Name] FROM Query1 ORDER BY [Contact Name];
    Query1:
    Code:
    SELECT IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[LastName] & ", " & [FirstName])) AS [File As], IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[FirstName] & " " & [LastName])) AS [Contact Name], tblContacts.*
    FROM tblContacts
    ORDER BY IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[LastName] & ", " & [FirstName])), IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[FirstName] & " " & [LastName]));
    The second cmd button cdmRemoveFilter:
    Code:
     Private Sub cmdRemoveFilter_Click()
        On Error Resume Next
    ' Switch the filter off
        Reports![rptContacts].FilterOn = False
    End Sub
    So I open the form and select in cboModel D630 then I either leave the second one blank or select a Contact Name ( that I know has a Dell 630) and then I click the "Apply Filter" cmdbutton. A small form titled "Enter Parameter Value" open asking for a Model to be inputed and same for Contact name. I input correct values and or leave the contact name blank and I get a blank form.
    This is the first code I tried and this is a horrible example becuase I really do not want to search users. The "Contact Name" combo box should be replaced with something like "OSVersion", "Location", or even OficeName". But i was just trying to get something to work.
    -----------------------------------------------------------------------------------------------------------------
    Form2
    cboModel: row source
    Code:
    SELECT [tblModel].[ModelID], [tblModel].[ModelName] FROM tblModel ORDER BY [ModelName];
    cboLocationCode: row source
    Code:
    SELECT [tblContacts].[UserName], [tblContacts].[LocationCode] FROM tblContacts ORDER BY [LocationCode];
    command button "on click"
    Code:
    Private Sub Command7_Click()
    strWhere = "1=1 "
    If Not IsNull(Me.cboModel) Then
      strWhere = strWhere & " AND [Model] =""" & Me.cboModel & """ "
    End If
    If Not IsNull(Me.cboLocationCode) Then
      strWhere = strWhere & " AND [LocationCode] =""" & Me.cboLocationCode & """ "
    End If
    DoCmd.OpenReport "tblContacts", acPreview, , strWhere
    End Sub
    So with this one there are a couple of problems. The first problem is in the cboLocationCode. The drop down list in the combo box shows mutliple values of the same location. For example there are multiple users that work in Location OH01, but I want the list in the combo box to list only once OH01.
    After selecting values for each combo box I select the cmd button and again iget the small "Enter Parameter Value" but only for the Model and then I get a blank form.

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

Similar Threads

  1. How to create a Password Form
    By heman85 in forum Forms
    Replies: 1
    Last Post: 07-07-2011, 11:49 AM
  2. Replies: 2
    Last Post: 02-10-2009, 12:02 PM
  3. Form Combo problems
    By Honeytree in forum Forms
    Replies: 0
    Last Post: 10-05-2008, 01:32 PM
  4. Replies: 0
    Last Post: 08-17-2008, 12:19 PM
  5. Using combo box to open another form
    By ladyairj23 in forum Forms
    Replies: 0
    Last Post: 06-02-2006, 07:03 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