Welcome to SPN

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

Sign Up Now!

How do I make the “Search Field As Formatted†the default?

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

  1. DHEC

    DHEC
    Expand Collapse
    Guest

    I have a database that uses a combo table as a look-up table.
    Users select a county name from a drop-down list but what is stored in the
    database is the county code.
    The problem comes when they want to search by county, they click the find
    button, enter the name of the county, and nothing comes up. They have to know
    the county code to make the search work.
    OR,
    They can click “More†in the ‘Find and replace’ dialog window then check the
    “Search Field As Formatted†box, which allows them to type the name instead
    of the code for the county.
    I would like the “Search Field As Formatted†box to be checked by default.
    I use a command button for the search (code below), can this be done there
    or is there another way to do this>


    County.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

    Exit_cmdSearchCnty_Click:
    Exit Sub
     
  2. Loading...


  3. Jerry Whittle

    Jerry Whittle
    Expand Collapse
    Guest

    1. Those lookup fields are a very bad idea. Check out Commandment #2 at this
    address:
    http://www.mvps.org/access/tencommandments.htm

    Allowing users to access Access at table level is also a very bad idea.
    Users can easily do things like delete columns, rows, and even entire tables.
    Keep the users away from the tables by setting up forms and reports. You'll
    be glad that you did.
    --
    Jerry Whittle
    Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


    "DHEC" wrote:

    > I have a database that uses a combo table as a look-up table.
    > Users select a county name from a drop-down list but what is stored in the
    > database is the county code.
    > The problem comes when they want to search by county, they click the find
    > button, enter the name of the county, and nothing comes up. They have to know
    > the county code to make the search work.
    > OR,
    > They can click “More†in the ‘Find and replace’ dialog window then check the
    > “Search Field As Formatted†box, which allows them to type the name instead
    > of the code for the county.
    > I would like the “Search Field As Formatted†box to be checked by default.
    > I use a command button for the search (code below), can this be done there
    > or is there another way to do this>
    >
    >
    > County.SetFocus
    > DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
    >
    > Exit_cmdSearchCnty_Click:
    > Exit Sub
    >
     
  4. Jerry Whittle

    Jerry Whittle
    Expand Collapse
    Guest

    I suggest that you dump the DoCmd.DoMenuItem for the much better

    DoCmd.RunCommand acCmdFind

    --
    Jerry Whittle
    Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


    "Jerry Whittle" wrote:

    > 1. Those lookup fields are a very bad idea. Check out Commandment #2 at this
    > address:
    > http://www.mvps.org/access/tencommandments.htm
    >
    > Allowing users to access Access at table level is also a very bad idea.
    > Users can easily do things like delete columns, rows, and even entire tables.
    > Keep the users away from the tables by setting up forms and reports. You'll
    > be glad that you did.
    > --
    > Jerry Whittle
    > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
    >
    >
    > "DHEC" wrote:
    >
    > > I have a database that uses a combo table as a look-up table.
    > > Users select a county name from a drop-down list but what is stored in the
    > > database is the county code.
    > > The problem comes when they want to search by county, they click the find
    > > button, enter the name of the county, and nothing comes up. They have to know
    > > the county code to make the search work.
    > > OR,
    > > They can click “More†in the ‘Find and replace’ dialog window then check the
    > > “Search Field As Formatted†box, which allows them to type the name instead
    > > of the code for the county.
    > > I would like the “Search Field As Formatted†box to be checked by default.
    > > I use a command button for the search (code below), can this be done there
    > > or is there another way to do this>
    > >
    > >
    > > County.SetFocus
    > > DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
    > >
    > > Exit_cmdSearchCnty_Click:
    > > Exit Sub
    > >
     
  5. DHEC

    DHEC
    Expand Collapse
    Guest

    Jerry,
    This is my first time posting so I may not be providing the correct
    information.
    Users can only access data through a form.
    The “look-up table†is one big table with ‘Value’, ‘Description’,
    ‘SelectKey’, and SortOrder’; this is used to populate combo boxes in the form
    using the following code:

    SELECT [tblCombos].[Value], [tblCombos].[Description] FROM tblCombos WHERE
    ((([tblCombos].[SelectKey])="County")) ORDER BY [tblCombos].[SortOrder];

    To limit to options to what is appropriate for the data field.
    This allows me to let the user see something they are familiar with (i.e.
    the counties name) while storing numerical values in the database that I can
    use for queries or statistics.

    Is there a better way to do this?

    Thanks for your help.

    "Jerry Whittle" wrote:

    > 1. Those lookup fields are a very bad idea. Check out Commandment #2 at this
    > address:
    > http://www.mvps.org/access/tencommandments.htm
    >
    > Allowing users to access Access at table level is also a very bad idea.
    > Users can easily do things like delete columns, rows, and even entire tables.
    > Keep the users away from the tables by setting up forms and reports. You'll
    > be glad that you did.
    > --
    > Jerry Whittle
    > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
    >
    >
    > "DHEC" wrote:
    >
    > > I have a database that uses a combo table as a look-up table.
    > > Users select a county name from a drop-down list but what is stored in the
    > > database is the county code.
    > > The problem comes when they want to search by county, they click the find
    > > button, enter the name of the county, and nothing comes up. They have to know
    > > the county code to make the search work.
    > > OR,
    > > They can click “More†in the ‘Find and replace’ dialog window then check the
    > > “Search Field As Formatted†box, which allows them to type the name instead
    > > of the code for the county.
    > > I would like the “Search Field As Formatted†box to be checked by default.
    > > I use a command button for the search (code below), can this be done there
    > > or is there another way to do this>
    > >
    > >
    > > County.SetFocus
    > > DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
    > >
    > > Exit_cmdSearchCnty_Click:
    > > Exit Sub
    > >
     
  6. Jerry Whittle

    Jerry Whittle
    Expand Collapse
    Guest

    AAAAH! That is different and much better. Go to the design view of the form
    and open the properties for the combo box. Make the Column Widths something
    like:

    0";1.5"

    The 0 will hide the first column. It also make it look into the second
    column when a user types in England for example. The numerical identifier for
    England should still be used if the Bound Column is left at 1.

    While I've seen people use one big 'lookups' table to store such data, I
    don't like it myself. Personally I'd rather have a Country table, Company
    table, etc.
    --
    Jerry Whittle
    Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


    "DHEC" wrote:

    > Jerry,
    > This is my first time posting so I may not be providing the correct
    > information.
    > Users can only access data through a form.
    > The “look-up table†is one big table with ‘Value’, ‘Description’,
    > ‘SelectKey’, and SortOrder’; this is used to populate combo boxes in the form
    > using the following code:
    >
    > SELECT [tblCombos].[Value], [tblCombos].[Description] FROM tblCombos WHERE
    > ((([tblCombos].[SelectKey])="County")) ORDER BY [tblCombos].[SortOrder];
    >
    > To limit to options to what is appropriate for the data field.
    > This allows me to let the user see something they are familiar with (i.e.
    > the counties name) while storing numerical values in the database that I can
    > use for queries or statistics.
    >
    > Is there a better way to do this?
    >
    > Thanks for your help.
    >
    > "Jerry Whittle" wrote:
    >
    > > 1. Those lookup fields are a very bad idea. Check out Commandment #2 at this
    > > address:
    > > http://www.mvps.org/access/tencommandments.htm
    > >
    > > Allowing users to access Access at table level is also a very bad idea.
    > > Users can easily do things like delete columns, rows, and even entire tables.
    > > Keep the users away from the tables by setting up forms and reports. You'll
    > > be glad that you did.
    > > --
    > > Jerry Whittle
    > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
    > >
    > >
    > > "DHEC" wrote:
    > >
    > > > I have a database that uses a combo table as a look-up table.
    > > > Users select a county name from a drop-down list but what is stored in the
    > > > database is the county code.
    > > > The problem comes when they want to search by county, they click the find
    > > > button, enter the name of the county, and nothing comes up. They have to know
    > > > the county code to make the search work.
    > > > OR,
    > > > They can click “More†in the ‘Find and replace’ dialog window then check the
    > > > “Search Field As Formatted†box, which allows them to type the name instead
    > > > of the code for the county.
    > > > I would like the “Search Field As Formatted†box to be checked by default.
    > > > I use a command button for the search (code below), can this be done there
    > > > or is there another way to do this>
    > > >
    > > >
    > > > County.SetFocus
    > > > DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
    > > >
    > > > Exit_cmdSearchCnty_Click:
    > > > Exit Sub
    > > >
     
  7. DHEC

    DHEC
    Expand Collapse
    Guest

    Thanks Jerry, I will make those changes and see if that works.

    "Jerry Whittle" wrote:

    > AAAAH! That is different and much better. Go to the design view of the form
    > and open the properties for the combo box. Make the Column Widths something
    > like:
    >
    > 0";1.5"
    >
    > The 0 will hide the first column. It also make it look into the second
    > column when a user types in England for example. The numerical identifier for
    > England should still be used if the Bound Column is left at 1.
    >
    > While I've seen people use one big 'lookups' table to store such data, I
    > don't like it myself. Personally I'd rather have a Country table, Company
    > table, etc.
    > --
    > Jerry Whittle
    > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
    >
    >
    > "DHEC" wrote:
    >
    > > Jerry,
    > > This is my first time posting so I may not be providing the correct
    > > information.
    > > Users can only access data through a form.
    > > The “look-up table†is one big table with ‘Value’, ‘Description’,
    > > ‘SelectKey’, and SortOrder’; this is used to populate combo boxes in the form
    > > using the following code:
    > >
    > > SELECT [tblCombos].[Value], [tblCombos].[Description] FROM tblCombos WHERE
    > > ((([tblCombos].[SelectKey])="County")) ORDER BY [tblCombos].[SortOrder];
    > >
    > > To limit to options to what is appropriate for the data field.
    > > This allows me to let the user see something they are familiar with (i.e.
    > > the counties name) while storing numerical values in the database that I can
    > > use for queries or statistics.
    > >
    > > Is there a better way to do this?
    > >
    > > Thanks for your help.
    > >
    > > "Jerry Whittle" wrote:
    > >
    > > > 1. Those lookup fields are a very bad idea. Check out Commandment #2 at this
    > > > address:
    > > > http://www.mvps.org/access/tencommandments.htm
    > > >
    > > > Allowing users to access Access at table level is also a very bad idea.
    > > > Users can easily do things like delete columns, rows, and even entire tables.
    > > > Keep the users away from the tables by setting up forms and reports. You'll
    > > > be glad that you did.
    > > > --
    > > > Jerry Whittle
    > > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
    > > >
    > > >
    > > > "DHEC" wrote:
    > > >
    > > > > I have a database that uses a combo table as a look-up table.
    > > > > Users select a county name from a drop-down list but what is stored in the
    > > > > database is the county code.
    > > > > The problem comes when they want to search by county, they click the find
    > > > > button, enter the name of the county, and nothing comes up. They have to know
    > > > > the county code to make the search work.
    > > > > OR,
    > > > > They can click “More†in the ‘Find and replace’ dialog window then check the
    > > > > “Search Field As Formatted†box, which allows them to type the name instead
    > > > > of the code for the county.
    > > > > I would like the “Search Field As Formatted†box to be checked by default.
    > > > > I use a command button for the search (code below), can this be done there
    > > > > or is there another way to do this>
    > > > >
    > > > >
    > > > > County.SetFocus
    > > > > DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
    > > > >
    > > > > Exit_cmdSearchCnty_Click:
    > > > > Exit Sub
    > > > >
     

Share This Page