Search Query within Dropdown Question

[Scyclone]

Hello everyone,



I have a small dilema that is a little odd.

I have two means of pulling up customer information (typing in Phone Number into txtPhone or selecting the customer themselves off of a dropdown).

Now, I am wanting to retrieve information from the dropdown if possible when entering in a phone number, as it is already stored within CustDrop1.Column(0-7) though on the dropdown I am only displaying the name when selected.

Is there any way to select the correct dropdown row where txtPhone = CustDrop1.Column(7)?

This



returns all of this




Thanks,
Steven

[June7]

Yes. Use code in VBA procedure. But why?

When selecting customer, the search should be done on the CustomerID that should be in the RowSource for the Customer dropdown.

[Scyclone]

Thing is, they are wanting to search by phone number, specifically. All I am simply trying to do on this is.

When a phone number is entered into txtPhone, I want to search the already queried/loaded values of the dropdown for a matching phone number. If a match is found, I want to select that item off the dropdown. Once I have selected that item on the dropdown, my scripts handle the rest to populate the rest of the information, the issue I am having is getting the dropdown item to be selected.

[June7]

Searching the combobox RowSource list would require VBA loop code to read each line of the RowSource.

Easier to just filter the form's recordset on the given phone number or use a DLookup on the source table.

The phone number control could also be a combobox with a RowSource of all unique numbers in the table. With AutoExpand Yes as users type in a number, the control will display matching value.

[Scyclone]

Hmm, I am confused on what you mean.

[June7]

Which statement confuses you?

Finding the Row of combobox that corresponds to the phone number given in the textbox is not easy unless you make the combobox dependent on the textbox as filter criteria. This would defeat the combobox's use as a tool to search through all data.

If you want to determine the CustomerID associated with the phone number given in the textbox, use DLookup function to search the source table.

If you want to make it easier for users to enter a valid existing phone number, then make the textbox a combobox with a RowSource of valid existing phone numbers.

[Scyclone]

Hmm so if I understand this correctly, what I should just do is have them enter the phone number. Then run a query to pull the record set where phone number is equal to the text box, then just display its record set in the customer information area below.

I could always put in a text value for the dropdown, it doesn't need to reference anything and could just be visual.

[June7]

Have them select/enter phone number. No need to run a query, apply a filter or GoTo record (the filter is easier).

I don't understand your last statement.

[Scyclone]

I guess I just am not following what you are talking on.

So I have 2 dropdowns, 1 for phone, 1 just normal.

Both have all the information, though naturally they only display their respective value (Phone and Customer)

They can type in the phone number into the dropdown, which is set to filter down based on that. Once they complete the number, it has already selected the customer row that contains that phone number. Then I use an after update script to populate the information below?

[June7]

You changed the phone textbox to a combobox? Both comboboxes are multi-column and have the CustomerID? Good.

If I understand what you are wanting to do is: user provides input criteria and then triggers a search of the form's records. You can either filter the form's recordset or go to a record that meets the criteria. Here is one filter method http://www.datapigtechnologies.com/f...tomfilter.html

[Scyclone]

Hmm...

Just tried to give it a shot but had no luck on it unfortunately.

I have in the dropdown all the information, and I have the criteria for Phone Number set to 'like whatever value is in the dropdown text' but it returns an error of 'the text you entered isn't on the list'.

if I enter 222 for phone, I want rows consisting of 2223334444 2224446666 to show up, but not 8886663333. It doesn't seem to filter down and I get the error message above, thoughts?

====
SQL

SELECT CustList.ID, CustList.CustName, CustList.[Address 1], CustList.[Address 2], CustList.City, CustList.State, CustList.Zip, CustList.PhoneNo
FROM CustList
WHERE (((CustList.PhoneNo) Like [Forms]![Create Order]![PhoneDrop1] & "*"));


====

Thanks

[June7]

Show the combobox RowSource. If it includes CustListID then don't set the parameter to the phone number, filter on the CustListID. Users make their selection using phone number but do the search with unique identifier. Then instead of Like use =.

If the combobox is multi-column, what are the settings for ColumnCount, ColumnWidths, BoundColumn, AutoExpand, LimitToList properties?

[Scyclone]

Hmm the issue of going that route, unfortunately, is that to enter in the information that makes it searachable, could only be done with the unique identifier. I am going to just change up the methodology and add a new form specifically to bring up information and they key in the unique identifier it gives them.

As tech people, we'd love to use the ID as often as possible. Unfortunately, everyone else goes by their own fickle requirements hehe.