combobox lookup for column names

W

woody_woodz

I am trying to do a search form....something like

select column u want to search (combobox), - >
enter text to search for (textbox), ->
SearchButton

I use a form linked to a query which has a condition on a field "lik
textboxNameInForm"

I want to search each time in different fields using the combobox t
decide which field has the condition ...

Hope someone could understand :d

How can I make the combobox to show the column names and not the value
in column (using code I supose) ?

Or maybe someone knows a better way to do this kind of searches ...

Thank
 
K

Klatuu

One of the row source types of a combo is Field List. When you set the row
source type to Field List and Identify the table or query as the row source,
you get a list of the field names in the recordset to choose from.

There is also the consideration of the field type for the chosen field so
when build ing the Criteria string, you know what delimiter to use.

Dim intCtlType As Integer
Dim strDelimiter As String
Dim intCtr As Integer
Dim strSearchFor As string
Dim strWhere As String

'Determine the Delimiter to use
intCtlType = Me.Recordset(Me.MyCombo).Type
Select Case intCtlType
Case dbText
strDelimiter = "'"
Case dbDate
strDelimiter = "#"
Case Else
strDelimiter = ""
End Select

'Detemine which field to use for the compare vaule

For intCtr = 0 To Me.Controls.Count -1
If Me.Controls(intCtr) = acTextBox Or Me.Controls(intCtr) =
acComboBox Then
If Me.Controls(intCtr).ControlSource = Me.MyCombo Then
strSearchFor = Me.Controls(intCtr)
Exit For
End If
End If
Next intCtr

'Create the Where Clause
strWhere = "[" & Me.MyCombo & "] = " & strDelimiter & strSearchFor &
strDelimiter

The above will create a criteria string you can use for filtering your form
or filtering a form or report you are about to open. It can also be used to
postition the forms recordset to the record found.

If you are going to use it for a query, you will want to take all the
criteria out of the query and use a different approach to running the query.
The way I prefer to do this is to have the query saved under two names. One
being the name I will actually use, and one that has the word Template on the
end of it. The trick is to keep the copy with no criteria under the Template
name. Then to add the criteria, retrieve the SQL, add the Where condition to
it, and save it back to the production name.

Dim strSQL As String

strSQL = Currentdb.QueryDefs("MyQueryTemplate").SQL
strSQL = Replace(strSQL,";", strWhere & ";")
Currentdb.Querydefs("MyQuery").SQL = strSQL
 
F

fredg

I am trying to do a search form....something like

select column u want to search (combobox), - >
enter text to search for (textbox), ->
SearchButton

I use a form linked to a query which has a condition on a field "like
textboxNameInForm"

I want to search each time in different fields using the combobox to
decide which field has the condition ...

Hope someone could understand :d

How can I make the combobox to show the column names and not the values
in column (using code I supose) ?

Or maybe someone knows a better way to do this kind of searches ...

Thanks
Click to expand...

To search for a value in a Text datatype field:

Add an unbound text control to the form.
Name this control "txtSearch"

Add a Combo Box to your form.
Set the Combo Box RowSource Type property to Field List.
Enter the name of the query/table from which you want to show the
field names in the RowSource property line.
Set the Column Size property to 1"
Name this combo box "cboFilter"

Code the Combo box AfterUpdate event:

Me.Filter = "[" & Me.cboFilter & "] = '" & txtSearch & "'"
Me.FilterOn = True

The above will only find the exact text entered in the txtSearch box.

If you wish to search for a phrase within a larger text field, you
need to use the Like keyword with the "*" wildcards instead of the
above.

Me.Filter = "[" & Me.cboFilter & "]" & "Like '*" & txtSearch & "*'"

First enter the text in the txtSearch text box, then select the field
from the combo box.

Searching a Date or Number datatype field also requires a different
syntax.
Any reason you don't simply use the built-in Filter by Selection of
Filter by Form tool buttons?
 
W

woody_woodz

SOOO Thank you !!! My bigest problem was "Field list" It was sooo
clear for me to notice... :( soory

but when I run the code above I get this error:

"Object doesn't support this property or method ." (error '438')

at this line :
......
If Me.Controls(intCtr) = acTextBox Or Me.Controls(intCtr)
acComboBox Then

.......

I have access 2003, and VB 6.
 
K

Klatuu

oops, my bad. Thats what I get for writing untested air code. It should be:

If Me.Controls(intCtr).ControlType = acTextBox Or
Me.Controls(intCtr).ControlType = acComboBox Then
 
W

woody_woodz

Thank you , Fred .

I will use my original code :


'---------------------
Dim Crit As String

Me.txt_Test.SetFocus

Crit = "[Column2] Like '*" & Me.txt_Test.Text & "*'"

Me.Filter = Crit
Me.FilterOn = True
'---------------------

Plus the combobox value and the delimiters code from Klatuu all in th
Crit variable

Why not use the filter buttons?
Because the person who uses this database said it will be much easie
for him and asked me to try to do this thing -- choose also the colum
 
W

woody_woodz

The new code works beautifull with simple forms...but, when I trye
something similar for a subform.... :( nothing seems do work again.
I am now trying to filter a subform using a button on the mainform
criteria is from a combobox (column name) and a txtbox (text to searc
for) both on main form)
I changed this code so many times I realy don't know what to try an
more :(
But I have one good news : the code generates no errors :p

Here is the code that does nothing :

Code
-------------------


Me.txt_find.SetFocus

temp = "Forms![frm_Struct]![sfrm_St_I].Form![" & Me.cmb_colls.Value & "]"
'Debug.Print temp

Crit = temp & " Like '" & Me.txt_find.Text & "'" & " AND " & "Forms![frm_Structl]![sfrm_St_I].Form![IDStruct]=" & Me.IDStruct

'Debug.Print Crit

Forms![frm_struct]![sfrm_St_I].Form.Filter =Crit
Forms![frm_struct]![sfrm_St_I].Form.FilterOn = True
 
D

Douglas J. Steele

What does "nothing seems to work" mean? Does nothing happen at all, or do
you not see anything? What does crit contain? You're using Like in
conjunction with your text field: are you entering the entire string that
contains in the field, or are you hoping (incorrectly) that you can just
type part of the string into the text box? (For that to work, you need to
include a wildcard character)
 
W

woody_woodz

"Nothing seems to work" because some filter is applyed - I can se
that "(Filtered) " apears near the navigation buttons (record 1 of 1
(Filtered) and initialy there are 15 records) - but it is just not wha
i wanted for my subform...

I thought I will add the wildcard char when it's going to work wit
the exact word in field ... so now i am testing only with exact words

It must be something about applying the filter on the subform ...

Should I send also the code that i'm using for simple forms? (this i
the one I tried to modify by replacing "Me." with "Me!Subform1.Form!" o
someting like this)

Maybe I am not thinking right about this filter... I have to verif
again ... after this I will send the code also, just in case ;) ...

If you don't understand someting please ask again :
 
Top