Another Filtering Question

[Guest]

Hello,

I'm trying to expand on a filter that I previously created. My filter is
created from a combo box where the user would select a fieldname and then a
corresponding value combo box would show those items - that works great if
all the fields are from the same table

ie this works great

Me.cboVal1.Rowsource = "Select distinct [" _
& Me.cboCrit1 & "]" _
& "From tblevents"

- I'm my case the fields are in different tables.
so I'm trying to dynamically build the after update for the row source by:

Dim strfield1 as string
Dim strfield2 as string

strfield1 = "(select tblfield from sstable where viewname = ' " &
Me.txtCrit1 & " ')"
strfield2 = "(select tbltable from sstable where viewname = ' " &
Me.txtCrit1 & " ')"
Me.txtVal1.RowSource = "select distinct" & strfield1 & " from " & strfield2

tblfield is a list of fields that are to be searched (coloreye)
tbltable is the name of the table to be searched (tblColorEye)
viewname is a friendly name of the field (Eye Color)
txtCrit1 is the name of the combo box that displays the list of the viewname
field

I don't get an error, just no results. I've put single and double quotes
everywhere I could think and didn't get it working.

So my questions are many. Is this a reasonable approach and if so what am I
doing wrong? If not a good approach can you recommend how to do this.

thanks much in advance for your advice... I'm stumped!

 

[Guest]

Jen,

What are you selecting here? - tblfield? have you tried the following:

strfield1 = "select tblfield from sstable where viewname = '" & Me.txtCrit1
& "'"

The second statement should look the same.

Finally you last statement shows a txtValue.rowsource but in your example
you are showing a cboVal1.rowsource are you referring to the correct control?

 

[Guest]

Hi Maurice,

Thanks - close but no cigar. When I take the ( ) out, I get a syntax error.
I've gotten closer however, still not working. Now it is giving a list of
the field name...


So here's what I got now. I eliminated the strfield2 for now.

Dim strfield1 as string
' Dim strfield2 as string

strfield1 = "(select tblfield from sstable where viewname = ' " &
Me.txtCrit1 & " ')"
' strfield2 = "(select tbltable from sstable where viewname = ' " &
' Me.txtCrit1 & " ')"
Me.txtVal1.RowSource = "select distinct" & strfield1 & " from tblevents"

the table looks like

viewname tblfield tblname
Event ID eventid tblevents

So when I select Event ID as txtCrit1 it translates that to eventid, but
then when it hits the rowsource all I see in my list is eventid not the
actual list of eventids....

Jen,

What are you selecting here? - tblfield? have you tried the following:

strfield1 = "select tblfield from sstable where viewname = '" & Me.txtCrit1
& "'"

The second statement should look the same.

Finally you last statement shows a txtValue.rowsource but in your example
you are showing a cboVal1.rowsource are you referring to the correct control?

--
Maurice Ausum

Hello,

I'm trying to expand on a filter that I previously created. My filter is
created from a combo box where the user would select a fieldname and then a
corresponding value combo box would show those items - that works great if
all the fields are from the same table

ie this works great

Me.cboVal1.Rowsource = "Select distinct [" _
& Me.cboCrit1 & "]" _
& "From tblevents"

- I'm my case the fields are in different tables.
so I'm trying to dynamically build the after update for the row source by:

Dim strfield1 as string
Dim strfield2 as string

strfield1 = "(select tblfield from sstable where viewname = ' " &
Me.txtCrit1 & " ')"
strfield2 = "(select tbltable from sstable where viewname = ' " &
Me.txtCrit1 & " ')"
Me.txtVal1.RowSource = "select distinct" & strfield1 & " from " & strfield2

tblfield is a list of fields that are to be searched (coloreye)
tbltable is the name of the table to be searched (tblColorEye)
viewname is a friendly name of the field (Eye Color)
txtCrit1 is the name of the combo box that displays the list of the viewname
field

I don't get an error, just no results. I've put single and double quotes
everywhere I could think and didn't get it working.

So my questions are many. Is this a reasonable approach and if so what am I
doing wrong? If not a good approach can you recommend how to do this.

thanks much in advance for your advice... I'm stumped!

 

[Guest]

Jen,

In that case shouldn't you refer to more fields because now you are only
referring to only one field eg tblField. Why not try something like:

strfield1 = "SELECT * FROM sstable where viewname = ' " & Me.txtCrit1 & "'"

This should give you all the fields from the selection you've made via the
criteria you've entered via strfield1.

hth
--
Maurice Ausum

Hi Maurice,

Thanks - close but no cigar. When I take the ( ) out, I get a syntax error.
I've gotten closer however, still not working. Now it is giving a list of
the field name...


So here's what I got now. I eliminated the strfield2 for now.

Dim strfield1 as string
' Dim strfield2 as string

strfield1 = "(select tblfield from sstable where viewname = ' " &
Me.txtCrit1 & " ')"
' strfield2 = "(select tbltable from sstable where viewname = ' " &
' Me.txtCrit1 & " ')"
Me.txtVal1.RowSource = "select distinct" & strfield1 & " from tblevents"

the table looks like

viewname tblfield tblname
Event ID eventid tblevents

So when I select Event ID as txtCrit1 it translates that to eventid, but
then when it hits the rowsource all I see in my list is eventid not the
actual list of eventids....

Jen,

What are you selecting here? - tblfield? have you tried the following:

strfield1 = "select tblfield from sstable where viewname = '" & Me.txtCrit1
& "'"

The second statement should look the same.

Finally you last statement shows a txtValue.rowsource but in your example
you are showing a cboVal1.rowsource are you referring to the correct control?

--
Maurice Ausum

Hello,

I'm trying to expand on a filter that I previously created. My filter is
created from a combo box where the user would select a fieldname and then a
corresponding value combo box would show those items - that works great if
all the fields are from the same table

ie this works great

Me.cboVal1.Rowsource = "Select distinct [" _
& Me.cboCrit1 & "]" _
& "From tblevents"

- I'm my case the fields are in different tables.
so I'm trying to dynamically build the after update for the row source by:

Dim strfield1 as string
Dim strfield2 as string

strfield1 = "(select tblfield from sstable where viewname = ' " &
Me.txtCrit1 & " ')"
strfield2 = "(select tbltable from sstable where viewname = ' " &
Me.txtCrit1 & " ')"
Me.txtVal1.RowSource = "select distinct" & strfield1 & " from " & strfield2

tblfield is a list of fields that are to be searched (coloreye)
tbltable is the name of the table to be searched (tblColorEye)
viewname is a friendly name of the field (Eye Color)
txtCrit1 is the name of the combo box that displays the list of the viewname
field

I don't get an error, just no results. I've put single and double quotes
everywhere I could think and didn't get it working.

So my questions are many. Is this a reasonable approach and if so what am I
doing wrong? If not a good approach can you recommend how to do this.

thanks much in advance for your advice... I'm stumped!