BuildCriteria coding on form

[RG]

Hi.
I'm trying to give a limited 'custom search' facility on a form for DB
users.

My idea needs three elements:
1. A drop down list populated with the Field Names of a specific table
2. A text field into which the user can enter the text or value to search
for
3. A command button that uses data from the first two fields in a
BuildCriteria
function and requeries the form to give the results.

I primarily need help with two parts just now:
Firstly, how to populate a drop down menu with the field names of a specific
table.
Second, how to calculate the field type of the selected field for use in the
BuildCriteria function.

I'm not used to Access programming - I've got a feeling this is reasonably
easy but I don't know how to do it.

Thanks
Ronnie

 

[TonyT]

Hi RG,

Your best bet to *simply* acheive what you are asking is to have the
combobox RowSourceType as the name of a function that you can create to (see
RowSourceType in Help for more info) which will allow you to have as many
columns and rows as required to include the fieldname, datatype and any other
info that may be useful.
You could however just set the RowSourceType to Fieldlist, to display the
field names from the selected table, and then use vba If/Then or Select Case
statement to determine the field's dataType dependant on fieldname selected.
(assumes that number of fields and their datatype won't change over time.)
Thirdly, you could use a Valuelist as the rowsourcetype, then follow the
second approach above, the benefit of this approach is that you can use names
for the fields that may mean more to the useer than the actual fieldname
itself.

Whichever route you go down, it will require vba event coding, most likely
in the AfterUpdate event of said combobox and tetxbox used for search
criteria.

PS. It may not be relevant to what you are trying to acheive, but you can
use the textboxes on change event to continually apply filters based on each
character typed by the user as the search string, thus doing away with the
command button *search* altogether.

hope this helps a little,

TonyT..

 

[RG]

Thanks for your help Tony.

I've set the RowSourceType to FieldList which is what I want.
I had thought about a case statement for calculating the fieldType but
the fields are likely to change so I'd prefer to do it programatically if
possible.

So I know the table, I know the field. Can you/anyone help with the
likely one line of code that I need to retrieve the type of that field?

Ronnie

 

[BruceS]

In ADO, it's fld.Type. It returns a VBA constant. Search for "Field
Object" in VBA help to see a list of all properties, methods, etc.

'Load field name and type into combo box rowsource.
Dim rst as Recordset
Dim fld as Field
Dim str as String

str = ""
Set rst = CurrentDB.OpenRecordset("myTbl")
For each fld in rst.Fields
str = str & ";" & fld.Name & ";" & fld.Type
Next fld
str = Mid(str,2)
rst.Close
Set rst = Nothing
myCbo.RowSource = str

Bruce

 

[BruceS]

Sorry, I was typing too fast. Meant to say "In DAO..."
Bruce

 

[Marshall Barton]

I've set the RowSourceType to FieldList which is what I want.
I had thought about a case statement for calculating the fieldType but
the fields are likely to change so I'd prefer to do it programatically if
possible.

So I know the table, I know the field. Can you/anyone help with the
likely one line of code that I need to retrieve the type of that field?

intType=Currentdb.TableDefs!table.Fields(combo).Type

 

[RG]

Thanks Bruce - looks good.
If I can just take this one step further...

Once an option from the combo box has been selected, how
do I reference the two parts of the selection in my script. Does
the combo.Value (or whatever syntax) return a strong containing
the field name and type or what?

Thanks again.
Ronnie

 

[RG]

intType=Currentdb.TableDefs!table.Fields(combo).Type

Thanks Marsh - perfect!

Ronnie