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

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10741-how-do-i-make-search-field.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10741
or is there another way to do this>


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

Exit_cmdSearchCnty_Click:
Exit Sub

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10741
> button, enter the name of the county, and nothing comes up. They have to know
> the county code to make the search work.
> OR,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10741
> 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
>

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10741
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10741
>
>
> "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
> >

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10741
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10741
> > 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
> >

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10741
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10741
> > 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
> > >

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’,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10741
> > ‘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,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10741
> > > > 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
> > > >