Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Using Form Control/Variable Name in SQL statement

Discussion in 'Information Technology' started by Garret, Jul 28, 2006.

  1. Garret

    Garret
    Expand Collapse
    Guest

    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.
     
  2. Loading...


  3. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

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

    Should do it

    Pieter

    "Garret" <garretdoe@hotmail.com> wrote in message
    news:1153225927.114851.203530@m73g2000cwd.googlegroups.com...
    > 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.
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4285 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  4. Nikos Yannacopoulos

    Nikos Yannacopoulos
    Expand Collapse
    Guest

    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
     
  5. Garret

    Garret
    Expand Collapse
    Guest

    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 wrote:
    > 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
     
  6. Nikos Yannacopoulos

    Nikos Yannacopoulos
    Expand Collapse
    Guest

    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
     
  7. Garret

    Garret
    Expand Collapse
    Guest

    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 wrote:
    > 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
     
  8. Nikos Yannacopoulos

    Nikos Yannacopoulos
    Expand Collapse
    Guest

    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
     

Share This Page