Passing value from one form to another and generate unique matter id (1 Viewer)

shery1995

Member
Local time
Today, 06:34
Joined
May 29, 2010
Messages
71
Hello everyone

Let me first explain what I want to achieve? I have two different forms based on tables 'Client' and 'Matters'. Client table has the following fields:
1-ClientID (primary key)
2-FirstName etc...

Whereas the Matter table has the following fields:
1-MatterID
2-ClientID
3-Matter details etc...
MatterID and ClientID are combination of primary key. What I want to achieve is when I click the Matter button of Client form Client form should pass the clientid to matter form and generate unique Matterid based on Clientid and Matterid like (clientid=2000 and matter id 1,2,3 for each client. but each client matter should start from 1,2,3 and so on.

Your kind advice will be highly appreciated.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:34
Joined
Oct 29, 2018
Messages
21,358
Hi. What data type is MatterID?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:34
Joined
Oct 29, 2018
Messages
21,358
Both Numbers
So, if they're both just numbers, what is your business rules for creating them? For example, are you trying to create sequential numbers?
 

shery1995

Member
Local time
Today, 06:34
Joined
May 29, 2010
Messages
71
So, if they're both just numbers, what is your business rules for creating them? For example, are you trying to create sequential numbers?
Yes, for clientid I can use autonumber, however for Matterid it should always start from either 0 or 1 for every every client as one client can have many matters, before generating the matteid system should check for clientid and based on that clientid generate matterid, If I use matterid filed as autonumber it does not serve the sequence purpose for every client's matter for instance if clientis is 2001 the matterID should be 1 if same client has another matter matterid should 2. same should be repeated for every client.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:34
Joined
Oct 29, 2018
Messages
21,358
Yes, for clientid I can use autonumber, however for Matterid it should always start from either 0 or 1 for every every client as one client can have many matters, before generating the matteid system should check for clientid and based on that clientid generate matterid, If I use matterid filed as autonumber it does not serve the sequence purpose for every client's matter for instance if clientis is 2001 the matterID should be 1 if same client has another matter matterid should 2. same should be repeated for every client.
In that case, check out the DMax() function. Try to do a search on "custom autonumbers."
 

shery1995

Member
Local time
Today, 06:34
Joined
May 29, 2010
Messages
71
In that case, check out the DMax() function. Try to do a search on "custom autonumbers."
In oracle I can do it, however not good in ms access. in oracle usually issue this command: select max(matterid)+1 from matter where clientid=matterid;

but don't know how to use this command in access???
 

Isaac

Lifelong Learner
Local time
Yesterday, 23:34
Joined
Mar 14, 2017
Messages
8,738
I can't quite tell from reading this thread, exactly when the primary record's ID was inserted/created, so am consequently not sure if this advice is most pertinent or not, but reading this thread may help, not sure.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:34
Joined
Oct 29, 2018
Messages
21,358
In oracle I can do it, however not good in ms access. in oracle usually issue this command: select max(matterid)+1 from matter where clientid=matterid;

but don't know how to use this command in access???
In Access, you can use DMax(). For example:

Code:
Nz(DMax("MatterID","MatterTable","ClientID=" & [ClientID]),0)+1
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:34
Joined
Feb 19, 2002
Messages
42,981
In the popup form, you have to manually set the foreign key. On subforms, Access does this for you automatically if you set the Master/Child links correctly.

You can pass the ClientID in the OpenArgs argument or if the popup is only ever used from one calling form, you can reference the other form. The key is to use the correct form event. You DO NOT want to dirty the form yourself which is what happens if you use the Open or Current events. The best event to use is the BeforeInsert event. This event runs ONCE only for each record and it runs as soon as the user dirties any field. You need tow lines of code.

Me.ClientID = Forms!frmyourform!ClientID
Me.MatterID = Nz(DMax("MatterID","MatterTable","ClientID=" & [ClientID]),0)+1

If you are using the OpenArgs, the first line will be:
Me.ClientID = Me.OpenArgs
 

shery1995

Member
Local time
Today, 06:34
Joined
May 29, 2010
Messages
71
In the popup form, you have to manually set the foreign key. On subforms, Access does this for you automatically if you set the Master/Child links correctly.

You can pass the ClientID in the OpenArgs argument or if the popup is only ever used from one calling form, you can reference the other form. The key is to use the correct form event. You DO NOT want to dirty the form yourself which is what happens if you use the Open or Current events. The best event to use is the BeforeInsert event. This event runs ONCE only for each record and it runs as soon as the user dirties any field. You need tow lines of code.

Me.ClientID = Forms!frmyourform!ClientID
Me.MatterID = Nz(DMax("MatterID","MatterTable","ClientID=" & [ClientID]),0)+1

If you are using the OpenArgs, the first line will be:
Me.ClientID = Me.OpenArgs
Thank you for your reply. Sorry I think I missed something to explain. I have a 'Add Matter' button on Client form. The event procedure to open the form is as follows:

Private Sub Add_Matter_Click()
On Error GoTo Err_Add_Matter_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMatter"

stLinkCriteria = "[ClinetID]=" & Me![ClientID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd
Forms!frmMatter.ClientID.DefaultValue = Me.ClientID

Exit_Add_Matter_Click:
Exit Sub

Err_Add_Matter_Click:
MsgBox Err.Description
Resume Exit_Add_Matter_Click

End Sub

You can advise the changes in above code to to achieve the required result. The target is when I click/press the 'Add Matter Button' it should open up the 'Matte Form' and create 'Matterid' starting from 1,2,3 and repeat the sequence for every client. When ClientID is changed MatterID should start again from 1,2,3 for every client... your help is appreciated
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:34
Joined
Feb 19, 2002
Messages
42,981
Forms!frmMatter.ClientID.DefaultValue = Me.ClientID
When you dirty the form before the user does you can cause confusion and the creation of invalid records if your validation code isn't tight. The method I suggested, does not dirty the record via code. It waits until the user types something and then the code fills in the missing pieces. The user doesn't get confused because he knows he tried to add a record.

Many people attempt to control the data contents from the calling form. My opinion that this method is wrong is based on 30 + years of empirical data and hundreds of Access applications. At least you are setting the DefaultValue property so you are not actually dirtying the record but now you want to generate a sequence number and since that value would not be static, you can't assign it to the DefaultValue property unless you have removed all methods of navigation from the popup form so that there is no possibility that the user can add a second record without first closing the popup form.

Putting all the code that affects a form in the form itself will go a long way toward making the application more robust and easier for your successors to enhance. This hit home pretty hard in one of my earlier assignments. One of my clients asked me to change a report. I ran the app and put it in design view so I "knew" which report I needed to change. I made the changes and ran the report to test the results. No change? Hmmm? Switched to design view and my changes were gone! Hmmm? Made them again, saved because I thought that something happened because I didn't save, ran the code and it still didn't work. A couple of hours later after I had traced all the code and documented it, I determined that the programmer had used one physical report which he modified from the open code. That code created the report I had been trying to modify and saved it which explained how my code kept getting overwritten. The solution was to use separate reports and arguments in the OpenReport method to control the data. Today it would take me less time to find that convoluted code but I was pretty green then, at least with Access, and couldn't believe anyone would write all that code because they didn't understand how to use the OpenReport method to control the criteria for a report.
 
Last edited:

shery1995

Member
Local time
Today, 06:34
Joined
May 29, 2010
Messages
71
When you dirty the form before the user does you can cause confusion and the creation of invalid records if your validation code isn't tight. The method I suggested, does not dirty the record via code. It waits until the user types something and then the code fills in the missing pieces. The user doesn't get confused because he knows he tried to add a record.

Many people attempt to control the data contents from the calling form. My opinion that this method is wrong is based on 30 + years of empirical data and hundreds of Access applications. At least you are setting the DefaultValue property so you are not actually dirtying the record but now you want to generate a sequence number and since that value would not be static, you can't assign it to the DefaultValue property unless you have removed all methods of navigation from the popup form so that there is no possibility that the user can add a second record without first closing the popup form.

Putting all the code that affects a form in the form itself will go a long way toward making the application more robust and easier for your successors to enhance. This hit home pretty hard in one of my earlier assignments. One of my clients asked me to change a report. I ran the app and put it in design view so I "knew" which report I needed to change. I made the changes and ran the report to test the results. No change? Hmmm? Switched to design view and my changes were gone! Hmmm? Made them again, saved because I thought that something happened because I didn't save, ran the code and it still didn't work. A couple of hours later after I had traced all the code and documented it, I determined that the programmer had used one physical report which he modified from the open code. That code created the report I had been trying to modify and saved it which explained how my code kept getting overwritten. The solution was to use separate reports and arguments in the OpenReport method to control the data. Today it would take me less time to find that convoluted code but I was pretty green then, at least with Access, and couldn't believe anyone would write all that code because they didn't understand how to use the OpenReport method to control the criteria for a report.
Thank you for your reply. I can't get my head around it would be great full if you can view the attached file and make necessary/missing changes. Objective is when I click on "Add Matter" button it should open up "Matter Form" populated with current "ClientID" and based on "ClientID" generate "MatterID" like ClientID, 4296, MatterID 1 ClientID, 4296, MatterID 2, ClientID, 4297, MatterID 1... Many thanks for your help..
 

Attachments

  • ClientMatter.accdb
    672 KB · Views: 288

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:34
Joined
Feb 19, 2002
Messages
42,981
I fixed what you have but I don't think you should do this this way. I think matters should be a subform.
 

Attachments

  • ClientMatterPat.accdb
    484 KB · Views: 222

Users who are viewing this thread

Top Bottom