Auto populate form using combo box

Background

Using mysql as the backend and ms access as the frontend. Have a table Client with has {Client_Id, name, etc,etc,etc} Have form for trying to display the clients details. Want to have a combobox which the users selects the "Client_Id" of the client the wish to see. And have the form populate with this users details. Form is called "Existing Client" its record source is a query called "Add_Client_query" the table with the details in is called "Client" the field that identifies each client is "Client_Id".

My latest attempt is in the event "on change" for the combobox insert code

Private Sub Surname_combo_Change()
Dim twClients As ADODB.Recordset
Set twClients = New ADODB.Recordset
twClients.Open "Select * From Add_Client_query where Client_Id = 60", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Set Forms("Existing Client").Recordset = twClients



End Sub

Doesn't error just gives me a blank form(when I say blank I mean the field dissappear and it gives me a grey window) I hardcoded the Client_Id to 60 as I know there is a client in the database with that Id and I was just trying to eliminate whats wrong


Very new to VB coding only know really what this site has taught me(which has been great by the way), need help!!!!!!!

Am I even on the right track?????????

Set LimitToList to YES and do your coding in the NotInList event. The OnChange event occurs for *each* keystroke in the ComboBox.

Don't understand, I want it to load a client that is in the list. The combo box selects things in the list???

If the "List" is the Recordset to which the form is bound then just let the ComboBox wizard create a cbo for you and select #3 option. I've got to run for a few hours, sorry.

I take it the Recordset is *not* bound to the form you are using?

No it wasn't but got it now. I was using a query over three tables because I had fields on two different pages on the form that accessed data from two other tables. I changed those to subforms(as the probably should have benn) and bound the table to the recordset which now allows option three and the wizard done the rest.

Did that make sense ??? hope so

Thanks

Yes it makes sense and was probably where this thread was headed. "You done good!" Thanks for marking the thread as Solved.