Hi all,
     I have a combo box that gives a selection of various names of objects. However, when I select and object the cb returns the position in the list of the object. For instance, if the third item down is selected then the value returned will be 3. How can I make this return the object? Any ideas. Here is my code, any help would be greatly appreciated. Thanks!

Private Sub cmdViewFailure_Click()
   ' On Error GoTo cmdViewFailure_ClickError

    Dim fromDate As Date
    Dim toDate As Date
    Dim BusProgIDs As String
    Dim IncAllOpen As Boolean
    Dim strWhere As String
    Dim ctl As Control
    Dim i As Integer

      Dim sqlFR As String
    Dim sqlBP As String
    Dim sqlFactor As String
    Dim sqlDate As String
    Dim sqlTitle As String
    Dim sqlPartNumber As String
    Dim sqlFilter As Variant

    If lstBusinessProgramsPF.ListIndex > 0 Then

        Set ctl = lstBusinessProgramsPF

        fromDate = Format(txtFromProdFail.Value, "mm/dd/yyyy")
        toDate = Format(txtToProdDate.Value, "mm/dd/yyyy")


        'SELECT BusinessPrograms.BusinessProgramID, BusinessPrograms.BusinessProgramCode, BusinessPrograms.BusinessProgramName AS [Program Name]
'FROM BusinessPrograms;

        'strWhere = "SELECT BusinessPrograms.BusinessProgramID, BusinessPrograms.BusinessProgramCode, BusinessPrograms.BusinessProgramName AS [Program Name] FROM BusinessPrograms"


        ' strWhere = ""
        'strWhere = BusProgIDs
        strWhere = ctl & " AND (CAConfirmationDate BETWEEN #" & fromDate & "# AND #" & toDate & "#)"
        If IncAllOpen = True Then
            strWhere = strWhere & " OR (" & BusProgIDs

            strWhere = strWhere & " AND (CAConfirmationDate Is NULL)"
            strWhere = strWhere & " AND (OpeningDate <= #" & toDate & "#))"
        End If

        DoCmd.OpenReport "ProgressReport", acViewPreview, , strWhere, , (fromDate & "," & toDate)
    Else
        MsgBox "One or more Business Programs must be selected to view a report.", vbOKOnly
    End If

Your problem is with the Bound column and column widths. You need to post the query behind the combo box, not the On Click event of a button.

You probably want your bound column to be the first or Primary Key column, which would display a number, but you want meaningful data to remain displayed in the combo box after the user makes a selection.

If your combo is looking up a customer name, for example, it will have at least two columns, CustomerID, and CustomerName.

Set your Bound Column to 1 to get the ID field for subsequent use, and then set your column widths 0";2" so that the ID field is hidden when the combo box is used.

Did that help?


mmcdonal

Hi mmcdonal, thanks for the RE! My combo box is already set up like that, I am wondering how I would get the data from the form using the number. Right now I am getting an error because the strWhere variable give a number followed by the date criteria. Any thoughts? Thanks again!

Take the query you currently have to run this report, and create a new copy just for this report.

Instead of passing all this criteria, put the control names in the criteria lines in the query designer like:

For Column BusinessProgramID:

[Forms]![frmMyForm].[BusinessProgramID]

etc.

Then when you click on the button to open the report, the query comes back and gets all the criteria from what is on the form.

Does that make sense?

Otherwise, refer to the specific column like this:

cboMyComboBox.Column(0)

Remember to also leave out parens in you code, and use the proper syntax:

strWhere = strWhere & " AND (OpeningDate <= #" & toDate & "#))"

should be

strWhere = strWhere & " AND [OpeningDate] <= #" & toDate & "#"

Did that help any?



mmcdonal

Sorry, I accidentally unsubscribed.

mmcdonal

Everything is being entered in ok, but the BusinessProgramID is being returned as a number instead of referencing the BusinessProgramID in the query. I've switched the action to change instead of onCLick as well. I'm still getting the same problem. I think the major problem lies within the report. I think I may hvae to pass in the ID number by value instead of by reference. Any ideas on how to do this?

I am not sure what the problem is here. Normally you want to pass the PK, and you would do it like this, assuming it is an autonumber field:

Dim iPK As Integer
Dim sLink As String

iPK = Me.BusinessProgramID

sLink = "[BusinessProgramID] = " & iPK

This should be what you want. However, it looks like you are trying to pull a range of data instead of a single record. If that is the case, and you have a text string in the combo box, then you would do this:

Dim sString, sLink As String

sString = Me.ComboBox.Column(1) 'I can never remember if they start with 0 or 1

sLink = "[TextField] = '" & sString & "'"

or

sLink = "[TextField] Like *" & sString & "*"

This should give you fields that contain the string value somewhere in the string.

Is that what you want?



mmcdonal

    Hi,
      I've switched my code a little, here is the new code:
    Private Sub cmdViewFailure_Click()

    Dim fromDate As Date
    Dim toDate As Date
    Dim BusProgIDs As String
    Dim IncAllOpen As Boolean
    Dim strWhere As String
    Dim ctl As Control
    Dim i As Integer

    If ckAllOpen.Value = True Then
        IncAllOpen = True
    Else
        IncAllOpen = False
    End If

    If lstBusinessProgramsPF.ItemsSelected.Count > 0 Then

        Set ctl = lstBusinessProgramsPF

        fromDate = Format(txtFromProdFail.Value, "mm/dd/yyyy")
        toDate = Format(txtToProdDate.Value, "mm/dd/yyyy")
        BusProgIDs = GetSqlBusinessProgram(ctl)

        strWhere = ""
        strWhere = strWhere & BusProgIDs
        strWhere = strWhere & " AND CAConfirmationDate BETWEEN #" & fromDate & "# AND #" & toDate & "#"
        If IncAllOpen = True Then
            strWhere = strWhere & " OR (" & BusProgIDs

            strWhere = strWhere & " AND (CAConfirmationDate Is NULL)"
            strWhere = strWhere & " AND (OpeningDate <= #" & toDate & "#))"
        End If

        DoCmd.OpenReport "ProgressReport", acViewPreview, , strWhere, , (fromDate & "," & toDate)
    Else
        MsgBox "One or more Business Programs must be selected to view a report.", vbOKOnly
    End If

ExitcmdViewFailure_Click:
    Exit Sub

End Sub


    I have found that I'm getting a "missing operator error: 3075" on the following line:
             DoCmd.OpenReport "ProgressReport", acViewPreview, , strWhere, , (fromDate & "," & toDate)

  When I highlight the parameters everything looks fine, any ideas of the problem? THANKS!