combo box wont auto complete tried various methods in here 1

[steve1033]

Hi there folks im new to this and could be doing with a little guidance if possible , i am using access 2010 and i need a combo box to autofill and filter as i type n to it , when i then select the item from the list i want it to open the clients details in the clientdetails form, is this very difficult the table names i have are clients, categories, the forms i have are Splash, search , contact details, the search box has 1 combo box on it and i would really like it to search both tables when im typing in for instance if i type a name of a category it should bring up what is in the tables. sounds really complicated to me
thanks in advance

 

[dhookom]

I'm not sure I understand your question since we really don't know much about your table, fields, primary keys, what fields you want to display in the combo box etc. You could create a union query from "both tables":

SELECT [ClientLastName] & ", " & [ClientFirstname], "frmClients" as Source
FROM Clients
UNION ALL
SELECT [Category], "frmCategories"
FROM Categories
ORDER BY 1;

Then in the after update of the combo box, you can use the first column to create a WHERE CONDITION and the second column to open the correct form.


Duane
Hook'D on Access
MS Access MVP

 

[steve1033]

Hi there the two table are basic table client is orimary key on client id as it category table . the category table only has a single column with the category in it . the clients table has all name fields ie first name surname title etc and address details 1,2,3 and post code . lets say there is a surname "ford" and a category "forth" i would want to type f both names will auto fill the combo box at this point until i type fort where ford is no longer the option then it will show forth only and if i then select the field shown ,it opens the full details of that client that i have selected. sorry if i have gone nto too much detail i dont know how else to explain it
thanks

 

[dhookom]

Did you attempt to create a combo box based on the SQL statement I provided?

Duane
Hook'D on Access
MS Access MVP

 

[steve1033]

Hi there i hadnt done that when i posted before as i am not sure where to put the code i am at a very basic level here sorry for that i am keen to learn and have spent about a week trying to work it out if you could advise where i put the code. i have deleted the combo box and will create a new one on demand

 

[dhookom]

Create a union query with the SQL and save it with a name of "quniClientCats".

SELECT [Client ID], [Surname] & ", " & [First Name], "Clients" as Source
FROM Client
UNION ALL
SELECT [Category], [Category],"Cats"
FROM Category
ORDER BY 1;

Make sure it displays the informat as expected.
Then create a combo box on your form and set:
Name: cboClientCats
Column Count: 3
Bound Column: 1
Column Widths: 0,1,0

Then add code to the After Update event of the combo box:

  Dim strWhere as String
  Dim strFormName as String
  Select Case Me.cboClientCats.Column(2)
    Case "Clients"
       strFormName = "Your Client Form Name Here"
       strWhere = "[Client ID] = Int(" & Me.cboClientCats & ")"
    Case "Cats"
       strFormName = "Your Categories Form Name Here"
       strWhere = "[Category] = '" & Me.cboClientCats & "'"
  End Select
  DoCmd.OpenForm strFormName , , , strWhere

Duane
Hook'D on Access
MS Access MVP

 

[steve1033]

is there any way to show you what i have done in here

 

[steve1033]

i have zipped it and attached it

 

[dhookom]

There is a way that you can show us what you have done and it is much the same as how I have shown you. It's copy and paste into the "Reply To This Thread" box and hit submit.

Duane
Hook'D on Access
MS Access MVP