Updating Table Field Values From A Form

Greetings! This is my first post.

I have two tables: 1) tblClient, 2) tblCase.

Client records from tblClient contain a field called Client_CID (Primary Key), as the Client ID. There are also fields Client_HIGH_FILE_NO, a numeric value of the last case number assigned to the specific client and Client_PREFIX that contain a unique three letter prefix that identifies the client.

Case records from tblCase contain a field called Case_CFN (Primary Key), as the Case File Number. tblCase also contains a field called Client_CID that contains the Client ID associated with the case (obtained from a combo box lookup from tblClient).

My form is frmCase bound to tblCase.

The Case_CFN is constructed by combining the value of the selected Client_CID’s Client_PREFIX with the value of Client_HIGH_FILE_NO plus 1.

I am constructing the Case_CFN on the before update event of the combo box for selecting the client. The resulting Case_CFN may appear as follows

ABC10001

Where Client_PREFIX = “ABC” and Client_HIGH_FILE_NO = 10000

Now, I need to increment Client_HIGH_FILE_NO in tblClient by 1, meaning I need to set the value of Client_HIGH_FILE_NO for the selected Client_CID to, in this example, to 10001.

Questions:

1) Is anyone familiar with this type of number scheme generally and if so any ideas?

2) Can anyone tell me how to update the value Client_HIGH_FILE_NO for the selected Client_CID?