I want a search text box! (I think) or a filter?

[Guest]

Just like the one up the top of this page, where the user types in the
information, and then presses the go button. What I am building (when I get
the time) is a database of information on both jobs completed at particular
sites, under programs either maintenance or construction, and jobs currently
being worked on. The project manager also has information management of how
many hours in a week is he working on particular jobs. The company is
contracted to work/maintain on over 4000 sites.

So I have a table which describes all the sites, some may be very close in
description, so I want to be able to get the user to select the exact one.
eg High St 100 metres from x intersection; High St (200 metres from x
intersection) (This table came from a preexisting spreadsheet) Each of them
has a unique Asset number, (which I am using as the PK), but I need them to
do the search on the location, as they never know the Asset number.

I have been able to do it using a combo box, and then populating the fields
of the current jobs form using the column(1)...but the users find the use of
the combo box annoying.

I am learning heaps from you guys and reading the posts, your support is
greatly appreciated.

 

[Guest]

What do your users find annoying about the way you have implemented your
combo box?

I suspect what you really need to do is provide a method to 'drill down' on
your data and shrink the size of the list users have to select from?

You have stated that you want users to go to a specific record. Setting up
'search' functionality would really be to provide you with multiple options
for supplied keywords etc. What other fields would you search on anyway?

Steve

 

[Guest]

Because the combo box needs to be so wide. There are 4 fields they want to
search on. Most frequently the Location. There is a High St, or Station Rd
in almost every suburb, so they also need the descriptor field about the
location, which is very wide too. This then gives them pretty much of an
idea, however if they have contracts in two locations very close together, or
on opposite sides of the road, then they need to have the km notation, and
the side of the road notation there as well.

Just typing in the combo box aint helping, because they are finding there
are still too many possibilities.

What I have been trying to do on and off all day is the following:


Private Sub btnSearch_Click()
Dim strFindLocation As String
Dim strSearch As String
Dim intLXNumber As Integer
Dim lngSngkm As Long


'Has the user entered anything?.

If IsNull(Me![txtsearch]) Or (Me![txtsearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Put some text in the box!"
Me![txtsearch].SetFocus
Exit Sub

'Performs the search based upon the input into txtSearch

Set strSearch = (Me![txtsearch])

If Not IsNull(Me![txtsearch]) Then

'select records like txtSearch from
'location name in location table


'and put them into a subform for the user to
'select the correct one (include intAssetNumber and sngkm in subform)
'correct location = strFindLocation, correct asset number = intAssNum
'correct km = lngSngKm

'from strFindLocation then find Asset Number and km location
'and populate new job details form

but can't get SQL to work in the procedure, and am too new to this to
completely understand the limitations of using SQL in a VBA procedure

 

[banem2]

Because the combo box needs to be so wide. There are 4 fields they want to
search on. Most frequently the Location. There is a High St, or Station Rd
in almost every suburb, so they also need the descriptor field about the
location, which is very wide too. This then gives them pretty much of an
idea, however if they have contracts in two locations very close together, or
on opposite sides of the road, then they need to have the km notation, and
the side of the road notation there as well.

Just typing in the combo box aint helping, because they are finding there
are still too many possibilities.

What I have been trying to do on and off all day is the following:

Private Sub btnSearch_Click()
Dim strFindLocation As String
Dim strSearch As String
Dim intLXNumber As Integer
Dim lngSngkm As Long

'Has the user entered anything?.

If IsNull(Me![txtsearch]) Or (Me![txtsearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Put some text in the box!"
Me![txtsearch].SetFocus
Exit Sub

'Performs the search based upon the input into txtSearch

Set strSearch = (Me![txtsearch])

If Not IsNull(Me![txtsearch]) Then

'select records like txtSearch from
'location name in location table

'and put them into a subform for the user to
'select the correct one (include intAssetNumber and sngkm in subform)
'correct location = strFindLocation, correct asset number = intAssNum
'correct km = lngSngKm

'from strFindLocation then find Asset Number and km location
'and populate new job details form

but can't get SQL to work in the procedure, and am too new to this to
completely understand the limitations of using SQL in a VBA procedure

What do your users find annoying about the way you have implemented your
combo box?

I suspect what you really need to do is provide a method to 'drill down' on
your data and shrink the size of the list users have to select from?

You have stated that you want users to go to a specific record. Setting up
'search' functionality would really be to provide you with multiple options
for supplied keywords etc. What other fields would you search on anyway?

"suek" wrote:

Hi,

If I understand your need, I will go with different solution.

As I understand, you need one text box where user can type almost
anything and program will find all matches stored in any of the
fields?

I will create query with all fields and one additional field which
will contain all fields you will search:

Expr1: Site & High & Width & Etc

Use the criteria to filter this field as:

Like "*" & Forms!frmFormName!fldSearchField & "*"

When user types anything in Search field just do the Requery of form
with results.

If you have data in several tables, use Union Query to join results in
one recordset and use the same technique.

Regards,
Branislav Mihaljev

 

[Guest]

You could use a series of combos that allow you drill down on the data. Each
containing a specific part of the string...

It would have been better if your database was designed with these parts in
separate fields, you could then combine them when required. This would allow
you to interrogate the individual parts and show progressively less options
in each combo.

Another alternative is to make your search functionality return records to a
continuous form. A user can then select the right record from there and you
can use the selected item to open your main form using the associated Primary
key field.

Steve

Because the combo box needs to be so wide. There are 4 fields they want to
search on. Most frequently the Location. There is a High St, or Station Rd
in almost every suburb, so they also need the descriptor field about the
location, which is very wide too. This then gives them pretty much of an
idea, however if they have contracts in two locations very close together, or
on opposite sides of the road, then they need to have the km notation, and
the side of the road notation there as well.

Just typing in the combo box aint helping, because they are finding there
are still too many possibilities.

What I have been trying to do on and off all day is the following:


Private Sub btnSearch_Click()
Dim strFindLocation As String
Dim strSearch As String
Dim intLXNumber As Integer
Dim lngSngkm As Long


'Has the user entered anything?.

If IsNull(Me![txtsearch]) Or (Me![txtsearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Put some text in the box!"
Me![txtsearch].SetFocus
Exit Sub

'Performs the search based upon the input into txtSearch

Set strSearch = (Me![txtsearch])

If Not IsNull(Me![txtsearch]) Then

'select records like txtSearch from
'location name in location table


'and put them into a subform for the user to
'select the correct one (include intAssetNumber and sngkm in subform)
'correct location = strFindLocation, correct asset number = intAssNum
'correct km = lngSngKm

'from strFindLocation then find Asset Number and km location
'and populate new job details form

but can't get SQL to work in the procedure, and am too new to this to
completely understand the limitations of using SQL in a VBA procedure

What do your users find annoying about the way you have implemented your
combo box?

I suspect what you really need to do is provide a method to 'drill down' on
your data and shrink the size of the list users have to select from?

You have stated that you want users to go to a specific record. Setting up
'search' functionality would really be to provide you with multiple options
for supplied keywords etc. What other fields would you search on anyway?

Steve

 

[Guest]

They are in separate fields, but there is the problem have a 4 column combo
box of asset number (5) location char 40. sngkm is long, and usually runs to
10 digits and identifier roadside is 15 characters long - so therein lies the
problem of fitting all this information in one combo box.
I was hoping to do it in one search if possible ?or a filter, where they
could put in a key word - they usually search on the location or identifier
roadside fields, although rarely they will have an asset number from the
surveys of this sites.

You could use a series of combos that allow you drill down on the data. Each
containing a specific part of the string...

It would have been better if your database was designed with these parts in
separate fields, you could then combine them when required. This would allow
you to interrogate the individual parts and show progressively less options
in each combo.

Another alternative is to make your search functionality return records to a
continuous form. A user can then select the right record from there and you
can use the selected item to open your main form using the associated Primary
key field.

Steve

Because the combo box needs to be so wide. There are 4 fields they want to
search on. Most frequently the Location. There is a High St, or Station Rd
in almost every suburb, so they also need the descriptor field about the
location, which is very wide too. This then gives them pretty much of an
idea, however if they have contracts in two locations very close together, or
on opposite sides of the road, then they need to have the km notation, and
the side of the road notation there as well.

Just typing in the combo box aint helping, because they are finding there
are still too many possibilities.

What I have been trying to do on and off all day is the following:


Private Sub btnSearch_Click()
Dim strFindLocation As String
Dim strSearch As String
Dim intLXNumber As Integer
Dim lngSngkm As Long


'Has the user entered anything?.

If IsNull(Me![txtsearch]) Or (Me![txtsearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Put some text in the box!"
Me![txtsearch].SetFocus
Exit Sub

'Performs the search based upon the input into txtSearch

Set strSearch = (Me![txtsearch])

If Not IsNull(Me![txtsearch]) Then

'select records like txtSearch from
'location name in location table


'and put them into a subform for the user to
'select the correct one (include intAssetNumber and sngkm in subform)
'correct location = strFindLocation, correct asset number = intAssNum
'correct km = lngSngKm

'from strFindLocation then find Asset Number and km location
'and populate new job details form

but can't get SQL to work in the procedure, and am too new to this to
completely understand the limitations of using SQL in a VBA procedure

What do your users find annoying about the way you have implemented your
combo box?

I suspect what you really need to do is provide a method to 'drill down' on
your data and shrink the size of the list users have to select from?

You have stated that you want users to go to a specific record. Setting up
'search' functionality would really be to provide you with multiple options
for supplied keywords etc. What other fields would you search on anyway?

Steve

:

Just like the one up the top of this page, where the user types in the
information, and then presses the go button. What I am building (when I get
the time) is a database of information on both jobs completed at particular
sites, under programs either maintenance or construction, and jobs currently
being worked on. The project manager also has information management of how
many hours in a week is he working on particular jobs. The company is
contracted to work/maintain on over 4000 sites.

So I have a table which describes all the sites, some may be very close in
description, so I want to be able to get the user to select the exact one.
eg High St 100 metres from x intersection; High St (200 metres from x
intersection) (This table came from a preexisting spreadsheet) Each of them
has a unique Asset number, (which I am using as the PK), but I need them to
do the search on the location, as they never know the Asset number.

I have been able to do it using a combo box, and then populating the fields
of the current jobs form using the column(1)...but the users find the use of
the combo box annoying.

I am learning heaps from you guys and reading the posts, your support is
greatly appreciated.