Using Form Control/Variable Name in SQL statement

[Garret]

Hello,

I have a hopefully simple question. This is the SQL statement that I
have presently:

[lstSearch].RowSource = _
"SELECT tblGages.GageNo, tblGages.GageDesc, tblGages.GageType,
tblGages.GageStatus, tblGages.EngDrawNo " & _
"FROM tblGages " & _
"WHERE (((tblGages.GageNo) Like " & "'" & [txtSearch] & "*'" &
")) " & _
"ORDER BY tblGages.GageNo;"

You can see now that I'm limiting the the SQL statement to certain
values in the tblGages.GageNo field with the text box I have on the
form, txtSearch. What I want to do is have a ComboBox on the Form that
contains the name of a few of the Fields, and instead of
tblGages.GageNo, I could replace it with tblGages.(cboSearchBy) or
something (I know that doesn't work, just trying to get my point
across). Or could a variable be substituted in there too, like
tbl.Gages.(strSearchby) - a String.
Many thanks for any help.

 

[Pieter Wijnen]

"SELECT .... FROM .... WHERE " & Me.cboSearchBy.Value & " Like '" &
Me.txtSearch.Value & "*'"

Should do it

Pieter

Hello,

I have a hopefully simple question. This is the SQL statement that I
have presently:

[lstSearch].RowSource = _
"SELECT tblGages.GageNo, tblGages.GageDesc, tblGages.GageType,
tblGages.GageStatus, tblGages.EngDrawNo " & _
"FROM tblGages " & _
"WHERE (((tblGages.GageNo) Like " & "'" & [txtSearch] & "*'" &
")) " & _
"ORDER BY tblGages.GageNo;"

You can see now that I'm limiting the the SQL statement to certain
values in the tblGages.GageNo field with the text box I have on the
form, txtSearch. What I want to do is have a ComboBox on the Form that
contains the name of a few of the Fields, and instead of
tblGages.GageNo, I could replace it with tblGages.(cboSearchBy) or
something (I know that doesn't work, just trying to get my point
across). Or could a variable be substituted in there too, like
tbl.Gages.(strSearchby) - a String.
Many thanks for any help.

--

 

[Nikos Yannacopoulos]

Garret,

The .RowSource property is assigned a string, so what you are doing on
the RHS of the equal sign is merely constructing a text string. The
method to "read" a field name from a combo instead of hardcoding it is
the exact same as you do for reading the search criterion string, so
your code would be:

[lstSearch].RowSource = _
"SELECT tblGages.GageNo, tblGages.GageDesc, tblGages.GageType,
tblGages.GageStatus, tblGages.EngDrawNo " & _
"FROM tblGages " & _
"WHERE tblGages." & Me.cboSearchBy & " Like '" & _
[txtSearch] & "*' " & _
"ORDER BY tblGages.GageNo;"

Of course, this will only work with text fields; if you need to be able
to filter on either text or numeric, your field combo might have a
second column with the field type, reading which you could manipulate
your string accordingly (Like vs. =, quotes+wildcards or not.)

HTH,
Nikos

 

[Garret]

Thanks Nikos, your statement worked great. I did not need the .Value
afterward that Pieter suggested.
This brings me to a normalization question. Since it takes the field
name directly, should the field name be something user-friendly? Right
now my fields like GageNo, GageDesc, EngDrawNo seem pretty normalized,
but since the user is the only seeing it, would it be better to have
fields with the name Gage Number, Gage Description, etc.? I suppose
it's a personal choice but I'd like to hear your opinion on this (or
anyones).

 

[Nikos Yannacopoulos]

Garret,

On your first point, .Value is the control property referenced by
default if none specified, which is why it makes no difference whether
you include it or not.

On your second one: to begin with, this has nothing to do with
normalization (which has to do with organizing data in related tables so
you have no redundancies). Re. table field names, I suppose it's a
personal style issue; I would stick with what you've got already, for
I'm too lazy to type long names all the time, let alone have to enclose
them in square brackets to deal with the spaces, plus it makes
expressions long so harder to read - but that's just me. Now, the user
interface is a different story! One thing you could do is make a small
table with two fields, one for actual table field name and one for a
description which makes sense to the user, then use that table for
populating the combo box such that while the user sees the second field,
the combo actually returns the first one, which would keep both you and
the user happy.
Alternatively, you can use the fields' Caption property in table design
to store a description for the user, and use some code in the form's
Load event to populate the combo by reading the fields' names and
captions from there (so the combo returns the field names but shows the
captions) without the need for an extra table. The code would look
something like:

Private Sub Form_Load()
Dim str1 As String
Dim db As DAO.Database
Set db = CurrentDb
For i = 0 To db.TableDefs("tblGages").Fields.Count - 1
str1 = db.TableDefs("tblGages").Fields(i).Name & _
";" & db.TableDefs("tblGages").Fields(i).Properties("Caption")
Me.cboSearchBy.AddItem str1
Next
Set db = Nothing
End Sub

(watch out for line wrapping in your newsreader).

Not necessarily the best solution, but my preferred one. Just remember
to set the combo's properties: Column Count 2, Bound Column 1 (default),
Column Widths 0;5 (or whatever, as long as the first one is 0, so it
doesn't show).

HTH,
Nikos

 

[Garret]

Thanks Nikos. What I ended up doing was just using your advice here:

to set the combo's properties: Column Count 2, Bound Column 1 (default),
Column Widths 0;5 (or whatever, as long as the first one is 0, so it
doesn't show).

And I wrote in my own captions in the RowSource property of the combo
box. Like "GageNo";"Gage Number";"GageDesc"; "Description"; etc, so
that the bound column is the one still being used, but the second
column is the one being shown. I found it simpler this way. Thanks
for all your help!

 

[Nikos Yannacopoulos]

Glad I could help! The way you did it is simpler to do but static; the
code method would adapt automatically to changes in the table design, so
would come in handy during development, and potentially save you some
frustration in the event of a future change, when you might not remember
to manually change the list.

Regards,
Nikos