Another Filtering Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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?
 
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....

Maurice said:
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


Jen Fields said:
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!
Click to expand...
Click to expand...
 
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


Jen Fields said:
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....

Maurice said:
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


Jen Fields said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top