Access - How to add a record to a table using a form with combo box tied to another table

Why are you not using a bound form?  

If you will use a bound form then you can use the full power of Access to do everything you need with little or no VBA code.

I updated your example to be a bound form. See Attached
Clients-HiTechCoach.accdb

Try this by adding other field to the update statement.

I used VBA instead of a Macro.

Check the form "Copy Of frmClient"

I included 2 text fields and 3 numeric fields required for the example to work.
Clients-2.accdb

That added the record, and I assume I would just add the other fields to the VBA to get them to load into the table?

Not sure if you saw the secondary piece of this from above, but I would also like to be able to use this form to cycle thru tblClients to edit the records.  Is that possible?

I assume I would just add the other fields to the VBA to get them to load into the table?

Probably not.  You do NOT want to store the Broker information  again in the client table  It is a better design to always look up the Broker data as needed. That way you will always get the current email and phone.

I removed the storing of the broker data into the client table. It goes again the "Best Practice" for a properly normalized (designed) database.

... I would also like to be able to use this form to cycle thru tblClients to edit the records.  Is that possible?

Yes.
I changed the form's Data Entry Property to NO so you can use the same form to add or edit records.

I assume I would just add the other fields to the VBA to get them to load into the table

No, that's not how relational databases work.  Only the BrokerID is stored in tblClients so remove Broker_Contact_Name, email and phone from tblClients.  When you need information about the broker and you want to show it on a form that is showing the client record, then you use a query that joins the two tables.  You would use a left join if the brokerID might be blank in the client record.  The same advice goes for a lot of other fields.  You only need to store the Foreign key.  You NEVER need to, nor should you store the other data fields.  How do you think broker_phone would get updated in all the client records if you changed it in tblBroker?  It won't, unless you force it with an update query and this is absolutely the wrong approach.  So, remove the "lookup fields" of the joined tables and just keep the ID's.

And finally, ALL tables need to have primary keys.  They can be natural keys or surrogate keys (autonumber) but each row should have a unique identifier.  When you use an autonumber surrogate key, consider adding a unique index to enforce business rules.   For example, tblAM defines Account Manager.  It has an autonumber which should be defined as the primary key.  The Account_Manager field should be defined as unique to prevent the same AM from being assigned more than one ID.

@EMB_Joe

"I would also like to be able to use this form to cycle thru tblClients to edit the records"

What do you mean by this statement?

Thanks Boyd Trimmell.  I may have another one for you soon.

You're welcome. Glad we could assist.