Add a table record table linked to a sub form to correct table

I have 2 basic tables: clientTbl and MatterTbl. They are joined by the client no. and matter no. fields in both in the tables. The matterTbl contains jobs related to particular clients. A client might have two matters or a hundred.

ClientTbl fields: cCno, cMno (both numeric), client (char). MatterTbl: mCno, mMno, matterName (char).
My primary form has the client table and the sub form contains the matter table.  

I want to have command buttons that will say: add Client, add Matter, delete Client, delete matter. Adding a new client will just create a blank client record but adding a matterTbl record must insert the client no (cCno) displayed in the primary form and the matter no (mMno) can be blank (usually last matter number for the client plus one).  I also want to specify which tables the records are added to. Here is my add client code:

Private Sub ComSaveClient_Click()
' add matter from client form
On Error GoTo Err_ComSaveClient_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    Exit Sub
    MsgBox Err.Description
    Resume Exit_ComSaveClient_click
End Sub

Thank you for any help you can offer.
Who is Participating?
I can't imagine why you need Matter No in client table. May be it is someting specific for your kind of data, but it is not normal.
Normally it is enough to use standard master for adding new record to the main form (Client number should be AutoNUmber). In subform (if it is continuous) you can set default value for cCno field to this value in main form and it will be filled automatically.
It is also better to have AutoNumber for mMno (you will not need to write code for adding new records in this case).
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I agree with als315: There's no need to store MatterNo in the Client table.

Can a "matter" be associated with more than one Client? If not, then you need to structure your tables like this:

etc etc

etc etc

From there, you could build a form/subform setup based on these two tables. tblClient would drive the Master form, and tblMatters would drive the Details (i.e. Subform).

I would also encourage you to move away from using DoMenuItem. As predicted, we're now starting to see issues when people move to 2007/2010 with this sort of code. The menu structures aren't the same in the newer versions, which means either (a) your code will do nothing, which is bad or (b) your code will do the wrong thing, which is far worse.

To Save a record:

If Me.Dirty Then Me.Dirty = False

To add a New record:

DoCmd.RunCommand accmdRecordsGoToNew

To delete a record:

DoCmd.RunCommand accmdDeleteRecord

DavidDF1913Author Commented:
Thank you for your help.  You are both right. The matter no.  is not needed in client table.  However, neither client_no. or matter_no. are always entered in numerical order since this is an old database with missing clients and matters.
I am going to use the auto-number fields for client and matters tables, however, since most additions will be in order even while the database is being updated.
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.