How to Open new form and insert ID from first form, Access

[Siw]

I see there is a lot of questions that sniff close to what I want to accomplish, but as a real newbie to coding - i have a hard time translating the solutions provided to what I am building. So I try my luck to see if explaining my built can get me the solutions I seek.

I have a customerform with "KundeID" as primary key. On this form I have a button that I am trying to make do as follows: Open a new Orderform and insert "KundeID" from customerform in "KundeID" on the orderform. (and then autofill the other customer information but this I have already figured out, I think)

 

[Gasman]

Use the OpenArgs argument of the OpenForm command?

 

[theDBguy]

Hi. Welcome to AWF!

Even if you want a separate popup form for the orders, consider using a form/subform setup for it, so you can easily transfer the primary key to the orders.

 

[Siw]

Use the OpenArgs argument of the OpenForm command?

Dont even know what this means, thats how green I am..

 

[Siw]

Hi. Welcome to AWF!

Even if you want a separate popup form for the orders, consider using a form/subform setup for it, so you can easily transfer the primary key to the orders.

Thank you
And how do I do that?

 

[CJ_London]

And how do I do that?

1. open your customer form in design view
2. from the navigation window, drag the orderform on to the customer form - typically into the header or footer section if your customer form is a continuous form, otherwise wherever works for you.
3. resize to suit
4. in your newly created subform control go to properties data tab and put KundeID in both linkchild and linkmaster properties. If you have already defined the relationship in the relationships window this should happen automatically.

 

[plog]

(and then autofill the other customer information but this I have already figured out, I think

This data--is it just displaying on the subform, or are you actually saving it in a table? It's fine to copy data so it shows on forms, but in a relational database you don't copy data from table to table.

 

[Pat Hartman]

I agree that the subform solution is probably best but opening a popup is sometimes preferred especially if the form you are opening should be in single view AND have a subform of its own so here's the method for getting the FK set correctly in that case.

1. Save the current record if it is dirty using DoCmd.RunCommand acCmdSaveRecord BEFORE you open the popup form.
2. Use the OpenArgs of the Open form method to pass the PK to the form you are opening.
3. Open the second form in dialog mode so you have to close it to go back to the original form. Users get very confused when you allow multiple forms to be open at the same time and using Dialog prevents that. They can still see the calling form if they move the popup but they can't go back to it until they close the popup.
4. In the BeforeInsert event of the popup form, populate the FK:

Me.MyFKName = Me.OpenArgs


The reason for using the BeforeInsert event is two-fold:
1. It runs for every new record so if it is possible to add multiple records, ALL of them will get the correct FK
2. It doesn't run until the user has typed something into any control. That means that you won't be dirtying the record, the user will. You will understand the import of this someday. Take it on faith today.

 

[Siw]

I agree that the subform solution is probably best but opening a popup is sometimes preferred especially if the form you are opening should be in single view AND have a subform of its own so here's the method for getting the FK set correctly in that case.

1. Save the current record if it is dirty using DoCmd.RunCommand acCmdSaveRecord BEFORE you open the popup form.
2. Use the OpenArgs of the Open form method to pass the PK to the form you are opening.
3. Open the second form in dialog mode so you have to close it to go back to the original form. Users get very confused when you allow multiple forms to be open at the same time and using Dialog prevents that. They can still see the calling form if they move the popup but they can't go back to it until they close the popup.
4. In the BeforeInsert event of the popup form, populate the FK:

Me.MyFKName = Me.OpenArgs


The reason for using the BeforeInsert event is two-fold:
1. It runs for every new record so if it is possible to add multiple records, ALL of them will get the correct FK
2. It doesn't run until the user has typed something into any control. That means that you won't be dirtying the record, the user will. You will understand the import of this someday. Take it on faith today.

This seem to explain what I need, but I dont understand how to write the complete VBA code. Are you able to write me an example?

These are my forms:
KundekortS, singelform, (Primary key "KundeID")- first form where the customer is created
Here is the button I would like to open the second form with.

OrdreskjemaS, singelform with a continuous subform, (holds a field named "KundeID")- second form where the order is created
As you guessed holds a subform of its own. And as this form is one of 3 forms that I wish to relate to from the first form, putting the second form as a subform is not an option for what I have been asked to build.

 

[arnelgp]

this is a demo of Main form and Subform.

 

[Pat Hartman]

It's three lines of code and I gave you TWO of them


To open the form:
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm ...... finish with Intellisense. Last argument will be the PK you are passing


Then in the popup form's BeforeInsert event

Me.MyFKName = Me.OpenArgs '' if you want to get fancy, you can validate the OpenArgs

Arnelgp gave you the subform method. The master/child link settings allows Access to automatically populate the foreign key to link the records. The code in the subform's Beforeinsert event simply prevents you from trying to add a record to a subform before you create the main form record.

 

[Siw]

Thanks for all the replies.

Pat, I might be blond as f.. as I am not able to get this to work.
Specially "Me.MyFKName = Me.OpenArgs".

But I have figured it out with another way

Code on the button in first form:
If Me.Dirty = True Then Me.Dirty = False
DoCmd.OpenForm "OrdreSkjemaS", , , , acFormAdd, acDialog, Me!KundeID

On load in the second form;
If Me.NewRecord Then Me!KundeID = Me.OpenArgs

This solved the problem, however now the second form is allways dirty when I open it. Even if its an old record and har not been chenged - only opened and reviewed.

 

[Gasman]

What Pat was trying to imply, is you set your PK after the user has dirtied the form?
So her Me.MyFKName is meant to be the FK (PK?) of your record, which appears to be Me.KundeID

As we do not know your control/field names, we give a generic name which you are meant to change to your respective name?

Too many people just copy code as it is without even knowing what it is doing?

 

[Siw]

What Pat was trying to imply, is you set your PK after the user has dirtied the form?
So her Me.MyFKName is meant to be the FK (PK?) of your record, which appears to be Me.KundeID

As we do not know your control/field names, we give a generic name which you are meant to change to your respective name?

Too many people just copy code as it is without even knowing what it is doing?

Thank you for clarifying

I really tried to understand the code, but couldnt figure out if I was supposed to change something or not. (really REALLY new to VBA and access, but I am trying to understand and get a grasp of this new world).

 

[Pat Hartman]

On load in the second form;
If Me.NewRecord Then Me!KundeID = Me.OpenArgs

Remove the code from the load event and put it in the BeforeInsert event. I get that you didn't work out the generic name but I did explain why it was correct to use the BeforeInsert event.

It is incorrect to use the Load event because the load event runs only ONCE. Therefore, if you add a second record using the open form, the KundeID will NOT get populated. Test it if you don't believe me.

You could use the Current event with your If Me.NewRecord code BUT that means that YOU would be dirtying the next record before the user entered data into it and Access ALWAYS tries to save a dirtied record. If you have no validation code to prevent that, and I'd bet the ranch that you don't, then you will be creating "empty" records which is always a bad thing.

 

[Siw]

Remove the code from the load event and put it in the BeforeInsert event. I get that you didn't work out the generic name but I did explain why it was correct to use the BeforeInsert event.

It is incorrect to use the Load event because the load event runs only ONCE. Therefore, if you add a second record using the open form, the KundeID will NOT get populated. Test it if you don't believe me.

You could use the Current event with your If Me.NewRecord code BUT that means that YOU would be dirtying the next record before the user entered data into it and Access ALWAYS tries to save a dirtied record. If you have no validation code to prevent that, and I'd bet the ranch that you don't, then you will be creating "empty" records which is always a bad thing.

Thanks, I heard you and believed you but as I didnt get it to work I tried another way.
I now made the corrections (moved the code to before insert) and it is finally working as I want it to.

 

[Pat Hartman]

I tried another way.

Trying another way is a good tactic when you can't get something to work. However, you need to control your experimentation to ONE change at a time or you won't learn anything. You'll just flail around. And if you happen on something that appears to work, you won't understand why. Based on the specific warning about subsequent records, you might have tested that with your solution and you would have found that it didn't work.

If you had believed the extended description regarding the BeforeInsert event, the obvious first change would have been the control name and that would have worked.

You even changed my specific code that used the standard save a record method to a non-standard method. Why? Who knows? Did anyone ever tell you why to use a non-standard method rather than a standard method? To grow as a developer, you need to understand why. Some "experts" use the "try this" with no explanation approach. If you read my posts, I frequently get very long winded about the why because that is what you really need to know. If you give a man a fish, you feed him for a day. If you teach him how to fish, you feed him for a lifetime.