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?
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
 
hnasrCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
 
hnasrCommented:
@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 MVPCommented:
You're welcome. Glad we could assist.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.