Search Box/Lookup

[Guest]

I have a combo box created in a form for users to search by the "ID" field.
The ID field is unique and never duplicated. For example, user types "71" in
the ID field and the record is populated.

Fields in Combo Box:

Id Name of Group Group No. Effective Date
71 Deer Park 555555 12/01/07
68 Deer Park 555555 12/01/07
74 Deer Park 555555 06/08/07

My problem is the user wants another Combo Box to search by the Name of the
Group. The fields "Name of Group" "Group No." and "Effective Date" may have
the same data. The user wants to be able to type the first letter of the
name of the group in the lookup.

When I create my combo box using the "Name of Group" field Access only
returns the first "Name of Group" even if it is listed more than once. I do
know why Access is doing this because I have no unique field identified. Is
possible to create this kind of combo box for the user? I have the ID in the
lookup but it still doesn't return the correct record. I was told maybe a
lookup query would help but found no luck with that.

If someone could help, I would appreciate!
JS

 

[John W. Vinson]

I have a combo box created in a form for users to search by the "ID" field.
The ID field is unique and never duplicated. For example, user types "71" in
the ID field and the record is populated.

Fields in Combo Box:

Id Name of Group Group No. Effective Date
71 Deer Park 555555 12/01/07
68 Deer Park 555555 12/01/07
74 Deer Park 555555 06/08/07

My problem is the user wants another Combo Box to search by the Name of the
Group. The fields "Name of Group" "Group No." and "Effective Date" may have
the same data. The user wants to be able to type the first letter of the
name of the group in the lookup.

When I create my combo box using the "Name of Group" field Access only
returns the first "Name of Group" even if it is listed more than once. I do
know why Access is doing this because I have no unique field identified. Is
possible to create this kind of combo box for the user? I have the ID in the
lookup but it still doesn't return the correct record. I was told maybe a
lookup query would help but found no luck with that.

If someone could help, I would appreciate!
JS

Which ID do you want returned if "Deer Park" is selected? How can Access
determine whether the user intends it to be 68, or 71, or 74? Do you want the
latest date - if so, how can the records with ID 71 and 68 be distinguished
from one another?

John W. Vinson [MVP]

 

[Guest]

I have a combo box created in a form for users to search by the "ID" field.
The ID field is unique and never duplicated. For example, user types "71" in
the ID field and the record is populated.

Fields in Combo Box:

Id Name of Group Group No. Effective Date
71 Deer Park 555555 12/01/07
68 Deer Park 555555 12/01/07
74 Deer Park 555555 06/08/07

My problem is the user wants another Combo Box to search by the Name of the
Group. The fields "Name of Group" "Group No." and "Effective Date" may have
the same data. The user wants to be able to type the first letter of the
name of the group in the lookup.

When I create my combo box using the "Name of Group" field Access only
returns the first "Name of Group" even if it is listed more than once. I do
know why Access is doing this because I have no unique field identified. Is
possible to create this kind of combo box for the user? I have the ID in the
lookup but it still doesn't return the correct record. I was told maybe a
lookup query would help but found no luck with that.

If someone could help, I would appreciate!
JS

The way i would do this would be create a text box with an appropriate name
for e.g. SearchNOG
On the after update property of this box have it requery the combo box
Me!Combobox.requery

In the query behind the the combo box, use the criteria for Name Of Group
and type something like

ALike Forms!NameOf Form!SearchNOG & "%"

This will narrow down the combo box to show only entries that start with the
letters entered int he box. You can type the whole word of just the first few
letters adn it will still work.

Hope this helps with your problem

Falty

 

[Guest]

I have a combo box created in a form for users to search by the "ID" field.
The ID field is unique and never duplicated. For example, user types "71" in
the ID field and the record is populated.

Fields in Combo Box:

Id Name of Group Group No. Effective Date
71 Deer Park 555555 12/01/07
68 Deer Park 555555 12/01/07
74 Deer Park 555555 06/08/07

My problem is the user wants another Combo Box to search by the Name of the
Group. The fields "Name of Group" "Group No." and "Effective Date" may have
the same data. The user wants to be able to type the first letter of the
name of the group in the lookup.

When I create my combo box using the "Name of Group" field Access only
returns the first "Name of Group" even if it is listed more than once. I do
know why Access is doing this because I have no unique field identified. Is
possible to create this kind of combo box for the user? I have the ID in the
lookup but it still doesn't return the correct record. I was told maybe a
lookup query would help but found no luck with that.

If someone could help, I would appreciate!
JS

The way i would do this would be create a text box with an appropriate name
for e.g. SearchNOG
On the after update property of this box have it requery the combo box
Me!Combobox.requery

In the query behind the the combo box, use the criteria for Name Of Group
and type something like

ALike Forms!NameOf Form!SearchNOG & "%"

This will narrow down the combo box to show only entries that start with the
letters entered int he box. You can type the whole word of just the first few
letters adn it will still work.

Hope this helps with your problem

Falty

 

[Guest]

Hi JS/John,

I have a similar problem. I'm using a combo box to look up the
"NameofGroup" with a lookup query using: Select Distinct Table.NameofGroup,
From Table
because I do not want the combo box to show me the duplicates.
I just want to see one of the records so that when I search by that record
it will direct me to a new form (NOG Results) that will show me the rest of
the information pertaining to that selection. In other words, if I select
Deer Park, the form will show me the Group No., and Effective Date(s) for
Deer Park.
My problem is getting the search code to work and take me to the NOG Results
form.
I'm using the following code that gives me a syntax error when I run it:

stDocName = "NOG Results"
stlinkcriteria = "[Name of Group]=" & Me![Combo35]
DoCmd.OpenForm stDocName, , , stlinkcriteria

Please help
Thank you,
JRG