Combo Field List

[DMT Dave]

Hi Guy's, I have 2 combos

1: cboField, this is RowSource Field List

2: cboData RowSource Table/Query

So i am going to update the rowsource of cboData based on the Field name in cboField



There are quite a number of fields in cboField

Is it a matter of setting a load of if cboField statements then the row source of cboData = etc....

[DMT Dave]

Just a quick question, don't suppose i can string the field name based on the value of cboField ?

in this case field selected is Supplier

Can i String the bold text ? and take out any if statements ?

If Me.cboField = "Supplier" ThenMe.cboData.RowSource = "SELECT tblExpenses.Supplier From tblExpenses " _
    & "GROUP BY tblExpenses.Supplier " _
    & "HAVING (((tblExpenses.Supplier) Is Not Null)) " _
    & "ORDER BY tblExpenses.Supplier;"
Me.cboData.Requery
End If

[xps35]

Yes you can.

"SELECT tblExpenses." & Me.cboField & " From tblExpenses "

[Welshgasman]

I would use a Where instead of Having
Also when you change the rowsource I think a requery is automatic?, so no need for yours?

[DMT Dave]

I am getting Undefined function tblExpenses.SupplierWHERE in expression when clicking on cboData

    Me.cboData.RowSource = "SELECT tblExpenses." & Me.cboField & " From tblExpenses " _    & "GROUP BY tblExpenses." & Me.cboField & "" _
    & "WHERE (((tblExpenses." & Me.cboField & ") Is Not Null)) " _
    & "ORDER BY tblExpenses." & Me.cboField & ";"

[Welshgasman]

Put spaces at the start of each new line. Not debug.printing again I see
Put it into a string and use that.

[Micron]

I think your problem is "" vs " " i.e. that is where you probably intended to insert a space. FWIW I prefer this format as it's less cluttered:

str = "SELECT tblExpenses." & Me.cboField & " From tblExpenses GROUP BY"
str = str & " tblExpenses." & Me.cboField & " WHERE (((tblExpenses." & Me.cboField & ")"
str = str & " Is Not Null)) ORDER BY tblExpenses." & Me.cboField & ";"

I don't see how what you posted could work with a line continuation character in the middle of a line, but then again, I don't use them, so...

[DMT Dave]

Thanks guys, will adapt and try your suggestions

[CarlettoFed]

In the presence of grouping
- if you use the WHERE filter expression, it goes before GROUP BY
- if you use the filter expression HAVING it goes after the GROUP BY

so in this case the code is the following:

    Me.cboData.RowSource = "SELECT tblExpenses." & Me.cboField & " From tblExpenses" _
        & " WHERE tblExpenses." & Me.cboField & " Is Not Null " _
        & " GROUP BY tblExpenses." & Me.cboField

the final part & "ORDER BY tblExpenses." & Me.cboField & ";" it can be omitted as GROUP BY already performs the sorting of the data relative to those in the field on which it is carried out.

[DMT Dave]

This done the job nicely, thank you all

    Me.cboData.RowSource = "SELECT tblExpenses." & Me.cboField & " From tblExpenses" _        & " WHERE tblExpenses." & Me.cboField & " Is Not Null " _
        & " GROUP BY tblExpenses." & Me.cboField _
        & " ORDER BY tblExpenses." & Me.cboField & ";"

[DMT Dave]

Hi Guys, just to add this, how can I Run a Sum where the Field Name = etc, having a problem trying to do this bit, this will save many If statements etc..

curTotal = DSum("TotalAmount", "tblExpenses", "[" & Me.cboField & "] = '" & Me.cboData & "' And [Year] Between " & lYearStart & " And " & lYearEnd)

[Welshgasman]

AGAIN! Put the criteria into a string variable and Debug.Print it.
When you get it correct, use that variable in your Domain function.