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_ComSaveClient_click:
    Exit Sub
Err_Command34_Click:
    MsgBox Err.Description
    Resume Exit_ComSaveClient_click
End Sub

Thank you for any help you can offer.
David
DavidDF1913Asked:
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.

als315Commented:
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).
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
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:

tblClient
-----------------
lnClientID
txClientName
etc etc

tblMatters
--------------------
lnMatterID
lnClientID
txMatterDesc
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

0
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.
David
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 Applications

From novice to tech pro — start learning today.