Code to select and insert data into a form

[ss3373]

I have two tables - Table A has client details and Table B has product details.

I have made table C that combines the primary keys from Table A Client# and Table B Product code and have a relationship between them all.

I have a single form "Form 1" that has the products on with a subform that has Client details on.

I have over 8000 clients so I cant use a combo box to select the client# so i have a search form made with a button on each record in a continuous form.

I need some code so that when i press the button on the record that i want it will insert the primary key "Client #" into the subform of the products form "form 1" and thus insert the client # "primary key" into Table C.

I then want to auto populate the rest of the client details in the subform on Form 1

Any direct would be appreciated

[June7]

I don't think a subform is required but need some info. What is the RecordSource for the form - Table C? Does this form use a combobox to select product?

[ss3373]

Thanks for you reply - I do not want to use a combo box as I have 8000 clients in the database. I have a search tool that have built that can find any client and has a button on the row of the continuous form that selects the record and opens the client form at that record. I have duplicated the search tool for this search and at the moment the button has no code. I want to be able to click the button on the record selected and insert the data from that record into the new subform in the products table - The record source for the form is the product table. The record source for the Cleints is not set yet bu I think it should be Table C. The reason i have used a subform is that two people can buy the same product (we are selling property and two clients can be connected to to the property)

[June7]

Linking subform to main form should work. I would have combobox to select the client. If you don't think combobox will work with 8000 records (I have used it on a table with 10,000 records without problem by relying on the 'Auto Expand' property) then a search procedure may be the answer. Will have to save a record in Table C for each co-buyer. Or you can go with less normalized structure and have fields for Buyer1, Buyer2. Problem with this is knowing how many buyers to allow for. This will result in lot of blanks in the table and can make some queries more difficult.

You can get the client details by joining to clients table. The client detail should not be saved to table C. Set RecordSource of subform as a query joining table C and clients on client ID (jointype 'Show all records from tableC...'). Bind textboxes to the client field (except for the ID, leave it out). Set textboxes as Locked and TabStop No. Once the value is in the table C client ID field, the related data will display. Bind a textbox to the client ID field of table C. Code in the search procedure could set the value of this box, like: Forms.formname.boxname = search result ID