using combo box to search and got record

[holysepulchre]

I am abit confused. I have a table with a field called family. I also have a form based on the table which uses the field. I created a combo box to search for the family name which I have a paramenter box come up and ask for the family In the results I display tow fields the name and id. on the after update i have a macro set up to search for record. on the condition line I have "[id] = " & Str(Nz([Screen].[ActiveControl],0)) which when I click on the name in the search box it does not move to that record. It does not matter if I select the family or id field. I have used this in another databse and works fine. Here is the what i am selecting as a row source. I also tried make a query to pull the name and id and refernce that which didn;t also seem to work. Any suggestions

SELECT FOUND11.FAMILY, FOUND11.id, *
FROM FOUND11
WHERE (((FOUND11.FAMILY)=[Family Name]));

[June7]

Why does the RowSource sql show the asterisk (*) wildcard?

I never use popup parameter inputs and I don't use macros. So not sure what the issue is. If you want to provide db for analysis, follow instructions at bottom of my post.

[holysepulchre]

Attached is the zipped database

[holysepulchre]

* was inserted but makes sure to look at all records. I have used this type of find in another database with no issue. I find this better than the standard access find of using the binoculars

[June7]

Don't understand "* was inserted but makes sure to look at all records". All the wildcard does in that context is retrieve all fields.

Why do you have Dealer and Dealer Info fields in FOUND11? Both of those fields are linked to MONDEA11 Dealer_Nam field in Relationships builder. What field should be primary key in MONDEA11 - Dealer_ID, Dealer_Code, or Dealer_Nam? Appear to be saving Dealer_Code as foreign key in FOUND11.

Why a WHERE clause in the combobox RowSource? It references a name that doesn't exist.

The form is on a navigation form. Referencing controls on a navigation form from code (VBA or macro) seems to be trickier than a conventional form/subform arrangement. I have seen several threads on this. https://www.accessforums.net/forms/n...ers-28983.html

After selecting item in combobox, be sure to Tab or Enter from the combobox to trigger the AfterUpdate event. Can't use generic Str(Nz([Screen].[ActiveControl],0)) because the combobox is not the active control after Tab or Enter.

[holysepulchre]

I am using a parameter on the search to pop up as box asking them to type a name in which then finds a name from the found11 table. the results come back with smith and the id number. I know that it is looking at the table and finding the record. I hadnt realized that there is an extra reference in the relationships the mondea11_1 is extra. I look at the mondea table and pull in the basic info for each record in the found11. That way i have a one to many relationship and can display all records for a dealer. I have used this type of arrangement in another database no problem. You search by a name it displays a list of name click on the name and jums to that record. Yes I am missing a primary key in the mondea table

[June7]

I have never seen a combobox with a popup parameter. Have you made this work elsewhere? I really don't see how it can and I don't see any benefit to it. If user must type name in the popup prompt, could just type it directly in the combobox box.

[holysepulchre]

I have used this in another database and works. It is also a more complicated database with some vba that may be driving things. I need a way for a user to input a name to search and then jump to that record. Right now if i put smith in the search box it comes back with smith and id 34 so i know its searching and finding informatin in the database. What its not doing is moving to that record when I click on the results

[June7]

Well, I tried entering a name in the popup, think it was Hart, and the combobox does not show anything and form does not move to record. I still see no reason for the popup input. All that popup can possibly do is restrict the RowSource list, it doesn't actually populate the combobox. Value is needed in the combobox to serve as search/filter criteria and that isn't happening. I suspect what you have in the other database is not quite the same as this.

As I said earlier: After selecting item in combobox, be sure to Tab or Enter from the combobox to trigger the AfterUpdate event. Can't use generic Str(Nz([Screen].[ActiveControl],0)) because the combobox is not the active control after Tab or Enter.

But then, I could certainly be wrong about that.

[holysepulchre]

You were right. I reworked the whole idea and gave up on that approach. I reran combo box wizard took out parameter box and now works.

[Missinglinq]

I've never seen a popup box with a Combobox, either, and there's really no reason for one! What you're describing is what a Combobox does by default, using its AutoExpand feature! As you type into the Combobox, Access takes you to a matching Record, narrowing the list down as add each character, until you get to the Record you want. Hit <Enter> and the Record will be retrieved. Here's a step-by-step:

If you haven't already done so, create a Form based on your Table or Query, including all the Fields you want displayed.

Then simply:

• Add a Combobox to your Form.
• The Combobox Wizard will pop up
• Select "Find a record based on the value I selected in my combobox."
• From the Table or Query the Form is based on, click on the Field you're searching by (the Family Name, in your case) that is unique for each Record) to move it to the right side. Next move the ID Number over, so you can use it to verify that you have the correct Family Name
  
• Hit Next.
• Size the column appropriately.
• Hit Next.
• Name the Combobox something appropriate.
• Hit Finish.
  
  

Now you can drop the Combobox down and scroll down to the item to search by, or you can start to enter the Family Name, and the Combobox will "autofill" as you type. Hit <Enter> and the Record will be retrieved.

Linq ;0)>