Dropdown Lists

[ovadeso]

Hi,
This is my first time on the access board. I hope my question can be answered here. I was able to create a dropdown list for a field in the table view using another table as my source. My question is, why is this dropdown list not active on the form thats linked to that table? Or how can I get a dropdown list on a form?

Thanks for any of your suggestions.

 

[Joe4]

Did you change the table field to have the dropdown selection AFTER you had already added it to the form?

If so, simply delete it from the form, then re-add it using the field list and I believe that Access should automatically make it a combo box.

Otherwise, add an unbound combo box (or list box) to your form, and set the table field as your record source.

 

[ovadeso]

Thanks for you response, thats exactly what I did. That problem is solved. I have one more.
When there are multiple criteria for a query (say 4 or 5) is there a way to have one window where you input all criteria at once instead of one-by-one in separate windows for each criteria. And in this window, could I also have dropdown lists? Thanks again

 

[Joe4]

Are you entering the criteria into a form, or responding to a parameter query?

You can create a criteria selection form, complete with drop down boxes. Then you can use VBA code to build the SQL for the query in background and run the query. How comfortable are you with VBA?

 

[ovadeso]

Sorry for my tardy response. I want to create a criteria selection form with drop down boxes so I can select different parameters. I am not a master at VBA though I'm usually able to follow and understand the code when I see it. I already have a query created with the wizard. I can create a form based on that query but I don't know how to build the SQL. Basically what I have is a correpondence log which I want to query using either date range, subject, sender, etc. I want to be able to select these options on one form. Then I would generate a report from this query.

 

[Joe4]

The good news is that you don't really need to know much SQL, though being comfortable with VBA would be very beneficial.

The reason why you don't need to know much SQL is because you can "steal" the code. Basically, create what you want your query to return in the query builder, then change to SQL view and "steal" the code. This is the code you want to build. Basically, the only thing that should change should be the criteria of the SQL (begins with Where).

I have written a few of these selection forms. Here is some code for a selection form with Check boxes and combo boxes.

This first section of code runs on clicking the command button on the form once the entries are complete. It builds the query, runs it, then opens the report.

Private Sub cmdFindRecords_Click()

    Dim strSQL As String
    Dim AltInv As Integer
    Dim myCount As Long
    
'   Build SQL string, run query, go to first record entry form
    If Not BuildSQLString(strSQL) Then
        MsgBox "There was a problem building the SQL string"
    Else
'   Set AltInv variable equal to check box before coding form
        If chkAltInv = -1 Then
            AltInv = 1
        End If
'   Build SQL string based on user form criteria and run
        CurrentDb.QueryDefs("qry Billing Invoices Select").SQL = strSQL
        DoCmd.Close acForm, Me.Name
'   Count number of records in query
        myCount = DCount("*", "qry Billing Invoices Select")
'   Open Correct Report in Print Preview if there are matching records
        If myCount > 0 Then
            If AltInv = 1 Then
                DoCmd.OpenReport "rpt Billing Invoices 2", acViewPreview, "", ""
            Else
                DoCmd.OpenReport "rpt Billing Invoices", acViewPreview, "", ""
            End If
        Else
            DoCmd.OpenForm "frm Billing Invoices Selection"
            MsgBox "No records match the criteria selected"
        End If
    End If

Here is the actual function that code calls to build the SQL string to run the query:

Function BuildSQLString(strSQL As String) As Boolean

    Dim myLength As Integer
    
    strSQL = "SELECT [qry Billing Invoices].* FROM [qry Billing Invoices]"
    
'   If selection criteria, then add to SQL statement
    If grpSelection.Value = 2 Then
        strSQL = strSQL & " WHERE ("
        If cboInvoiceDate <> "" Then
            strSQL = strSQL & "(([qry Billing Invoices].dteInvoiceDate)=#" & cboInvoiceDate & "#) AND "
        End If
        If cboClientCode <> "" Then
            strSQL = strSQL & "(([qry Billing Invoices].strClientCode)=" & Chr(34) & cboClientCode & Chr(34) & ") AND "
        End If
        If cboEngageNum <> "" Then
            strSQL = strSQL & "(([qry Billing Invoices].strEngageNum)=" & Chr(34) & cboEngageNum & Chr(34) & ") AND "
        End If
        If cboInvoiceLow <> "" Then
            strSQL = strSQL & "(([qry Billing Invoices].lngInvoiceNum)>=" & cboInvoiceLow & ") AND "
        End If
        If cboInvoiceHigh <> "" Then
            strSQL = strSQL & "(([qry Billing Invoices].lngInvoiceNum)<=" & cboInvoiceHigh & ") AND "
        End If

        myLength = Len(strSQL)
        strSQL = Left(strSQL, myLength - 5) & ");"
    End If
    
    BuildSQLString = True
    
End Function

Hope this helps. As you can see, it may appear a little daunting if you are not real comfortable with VBA.

 

[ovadeso]

After trying to follow your code example, I've decided this is way over my head, so I went with a different approach. I just copied some ideas from the "Northwind" database. It would be nice to have one dialog form with dropdown boxes for my 3-criteria searches, but I guess I will have to make do with separate dialog boxes for each criteria. Thanks for your kind assistance.