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

I have the following tables: (See Attachment)
tblClients
tblBroker
tblEMB_AM
tbl_EMB_EDI
tbl_Enrollment_Type
tbl_Renewal_Date

I am trying to add a new record to the tblClients using a form(frmClient).  Most of the fields will be data entry, however some fields will be pulled from other tables using ComboBoxes.  (cboBrokerName, cboEnrType, cboRenewalDate, cboEDIFF, cboEDI, cboAM)

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


Thanks,
Clients.accdb
EMB_JoeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
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
0
Hamed NasrRetired IT ProfessionalCommented:
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
0
EMB_JoeAuthor Commented:
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?
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
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.
0
PatHartmanCommented:
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.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Here is a example with the client table cleaned up with the duplicate broker fields removed.

I would also urge you to use the learn to use built in abilities of Access before you try to recreate the same functionality in VBA code. So I turned back on the navigation buttons on the Client Form.
Clients-HiTechCoach.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hamed NasrRetired IT ProfessionalCommented:
@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?
0
EMB_JoeAuthor Commented:
Thanks Boyd Trimmell.  I may have another one for you soon.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
You're welcome. Glad we could assist.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.