Copying Field Data from Form A to B (1 Viewer)

[SwmpDnky]

Hi.. I have created a sweet Access Application but have some fundamental issues which i hope are easily fixed.

I have a Form Called "JobInfo" and a second called "ProductInfo" they are linked by a JobID field (which exists as an AutoNumber in "JobInfo").

I have created a Button in "JobInfo" which Opens a New Record in "ProductInfo" but i would like the JobID which has just been created on the previous Form to be carried across to pull the data through..... is this an easy thing to do? At present if you type the appropriate number it pulls the data through but i dont want to rely on the User remembering and entering the correct Number.

Thanks in advance for any help

 

[GPGeorge]

There are different ways to accomplish this task (as there are for many things in Access).
Probably the most straightforward is to create a main form bound to JobInfo and a sub form bound to ProductInfo. Instead of opening ProductInfo as a second form, embed it in a subform control IN the JobInfo form. Use the Master/Child Linking fields of the subform control to link them on JobID.

 

[SwmpDnky]

Thanks

There are different ways to accomplish this task (as there are for many things in Access).
Probably the most straightforward is to create a main form bound to JobInfo and a sub form bound to ProductInfo. Instead of opening ProductInfo as a second form, embed it in a subform control IN the JobInfo form. Use the Master/Child Linking fields of the subform control to link them on JobID.

Thanks for your reply.. i think i may have gone too deep in developing the database to now do this.. is there anyone that can create this without adding additional forms.. happy to pay for development time etc..

 

[MajP]

Does the Button in JobInfo open the ProductInfo form or is the ProductInfo form already open? Are you creating a single record in the ProductInfo form and then closing? You probably want to look at the OpenArgs argument of the DOCMD.OpenForm method. Then you can pass the JobID to the ProdInfo form. Or you can simply set the value of the JobId in the ProdInfo form from the JobInfo form after opening the ProdInfo form.

It may be as simple as the calling form setting the value in the called (prodInfo) form.
Docmd.Openform "frmProdInfo"
forms("frmProdInfo").JobID = Me.JobID

 

[SwmpDnky]

Does the Button in JobInfo open the ProductInfo form or is the ProductInfo form already open? Are you creating a single record in the ProductInfo form and then closing? You probably want to look at the OpenArgs argument of the DOCMD.OpenForm method. Then you can pass the JobID to the ProdInfo form. Or you can simply set the value of the JobId in the ProdInfo form from the JobInfo form after opening the ProdInfo form.

It may be as simple as the calling form setting the value in the called (prodInfo) form.
Docmd.Openform "frmProdInfo"
forms("frmProdInfo").JobID = Me.JobID

Thanks for coming back to me details in the next post below..

 

[SwmpDnky]

So the 1st SS shows the JobInfo with the JobID AutoNumber... When you click the Button in SS1 it Opens the Form Shown in SS2 but leaves the JobInfo Tab open.. its the JobID shown in SS2 that i would like to completed automatically by essentially copying and pasting from the previous form (if that makes sense) in the example above i manually typed it in..

 

[Pat Hartman]

In the OpenForm method, pass the ID in the OpenArgs parameter. THEN, in the popup form's BeforeInsert event, populate the foreign key:

Me.JobID = Me.OpenArgs

I NEVER dirty a popup form with code before the user dirties it by typing. This just leads to confusion. If the user decides to not save the record, he will be confused by error messages if you're the kind of developer who bothers with validation because the user knows he didn't change anything so why are you complaining about missing/bad data.

The BeforeInsert event will run for each record as soon as the user types the first character. That way it works for MULTIPLE records unlike the previously suggested methods. Once you open the popup, you can add as many new records as you want and as long as you use the correct event, all records will have the PK properly populated.

PS, I don't recommend having multiple forms open at one time. If you do it, the best practice is to open the popup as model. That stops code execution in the calling form and keeps focus on the popup until it closes. When control returns to the calling form, the line of code following the OpenForms method is next to execute

 

[MajP]

Is that button only for adding a new drawing or what do you want to happen if a drawing already exists?
There is really no reason for openArgs unless you open the form ACDIALOG. That requires extra steps. Simply set the jobID from the controlling form. If you want to "Add New" then specify this in the openform method

DoCmd.OpenForm "sashInfo", , , , acFormAdd
forms("sashInfo").jobid = me.jobID

But if you do above it will only allow you to add a new drawing and not view the existing drawing. Then you might want to have two buttons
View Drawings and Add New Drawing.

You can combine it where it will open to any existing drawings, but will default to the JobID for subsequent.

 

[MajP]

If it was me, and drawings are something you routinely look at then you have plenty of real estate. I would just have a tab control. Tab 1 would be job info, tab 2 would be drawings. You would not have to build new forms, just drag the drawing form as a subform on to the second tab. This then requires no code and avoids the popping open and closing of forms.

 

[SwmpDnky]

My idea was to use that particular button to add a new drawing only, and create a second button for viewing previously created drawings..

So that my simple brain can understand this..

1. I should edit the Macro on that button to Close the "JobInfo" Form first and then Open the ProductInfo form. (as this as you say is better practice)
2. Can I then use VBA on the "ProductInfo" to insert the value of JobID using..... forms("sashInfo").jobid = me.jobID

 

[MajP]

You can
Open the drawing form
Set the JobID
Close the Job form.

The only problem with that is now you have to code it to return to the Jobinfo for that job. You can add that additional code or just leave the Job form open. You can make your sashInfo form "Pop Up" and "Modal". Then when it opens the only thing you really can do is close it. You cannot move to other forms. Modal means that you cannot move focus off of that form without closing it.

 

[Pat Hartman]

There is really no reason for openArgs unless you open the form ACDIALOG. That requires extra steps. Simply set the jobID from the controlling form. If you want to "Add New" then specify this in the openform method

Are you really going to argue about this? How is the second drawing going to get its PK? are you going to force the user to close the popup so he can open it again for a second drawing?

Modal means that you cannot move focus off of that form without closing it.

That is what I said and that is the point. Unless you have some reason for allowing the user to interact with multiple open forms, all it does is confuse the process. Open the popup. Do what has to be done. Close the popup. Clean. No confusion. No lost, buried forms.

@SwmpDnky If you want to use the one button to open the drawings form for view and add, then you need to use BOTH the where argument and the OpenArgs argument. That way the form will open to existing drawings for that Job. The user can then go to the new record and add a new drawing if he needs to. The OpenArgs value is used to populate the PK. If there are no existing drawings, then the popup opens to a new record directly.

I should edit the Macro on that button to Close the "JobInfo" Form first and then Open the ProductInfo form. (as this as you say is better practice)
2. Can I then use VBA on the "ProductInfo" to insert the value of JobID using..... forms("sashInfo").jobid = me.jobID

Sorry to tell you this but you are going to have to decide whether you are going to do what MajP is telling you to do or what I am telling you to do. Pretty tough first day. Welcome aboard We are not usually this contentious.

The way to decide is - do you want to be able to add more than one drawing when you open the popup form? If you want that ability, then you would use my suggestion because Maj's won't work. His method requires you to close the popup after EACH new drawing. That is why he told you to modify the macro to close the open form before opening it again because if you open an already open form, it doesn't take the new where clause or openargs. And that is exactly why I told you to open the popup form as a dialog. Doing that avoids the problem he is trying to get you to program around.

PS, if you take MajP's advice, you had better remove the navigation bar from the popup because you absolutely do not want it to scroll to a new record because if you try to add one, the PK will not get set. This then ripples down to the view form because the view form requires navigation buttons and you really don't want two different forms because that means that you would have to duplicate any validation code in both forms and that is really poor practice.

 

[Uncle Gizmo]

@Pat Hartman 's method makes a lot more sense to me, she's covered all of the bases I reckon!

 

[SwmpDnky]

Thank you very much for your kind help.. being honest i didn't know that OpenArgs existed!. I will save a few copies of the File and then explore your kind suggestions! Thanks again

 

[MajP]

See demo.
In this design it opens any of the existing drawings. To add a new just scroll to a new drawing record.

 

[SwmpDnky]

Yeah that demo is helpful thank you very much, i have managed to get this to work.. i'm chuffed!

On another note would anyone know if there is the ability to use Access to create scalable SVG's? A few years ago we used Filemaker to create scaled drawings by controlling code through a WEB Viewer. The result is that you create a field (in my example its of a square window) field one say Width and field 2 being height and it would change the Code through the Web Viewer and give you a scaled drawing... has anyone any experience of this?

 

[Pat Hartman]

@MajP,
I see you didn't implement what you suggested You might have mentioned that to @SwmpDnky because he probably didn't notice.

Luckily, with Access, we frequently have alternatives and you found one that worked but was different from my suggestion. Isn't that lucky. I hope I at least opened your eyes to the error of your ways and you will now use a different method to populate the FK on a popup form You're welcome.

@SwmpDnky
The best way to open images using Access is with FollowHyperlink. That opens them in the default Windows viewer. If your images are not all the same size, you will be unhappy with using an image control on a form.

 

[MajP]

Are you really going to argue about this? How is the second drawing going to get its PK? are you going to force the user to close the popup so he can open it again for a second drawing

Sorry. I was not arguing with anyone. I was pointing out to the OP that you really never need to pass a value in with OpenArgs unless you use ACDIALOG as an argument of the Docmd, since that stops code execution in the calling form. If not calling it ACDIALOG you can simply set any property after opening the called form. This way you do not have to add additional code in the called form and tightly couple the called form to the calling form. Adding code in the before insert event tightly couples the drawing form to the job info form. At a minimum you need to validate if OpenArgs.

That is what I said and that is the point. Unless you have some reason for allowing the user to interact with multiple open forms, all it does is confuse the process. Open the popup. Do what has to be done. Close the popup. Clean. No confusion. No lost, buried forms.

I was explaining to the OP what Modal means since you stated it incorrectly.

That stops code execution in the calling form and keeps focus on the popup until it closes.

In Access that is incorrect. Modal does not stop code execution in the calling form. In other applications it does. In access only by calling the form passing the ACDIALOG of the Windowmode argument does. To prove that you can look at the demo I posted. I set the properties from the calling form after opening it Pop up and Modal.

I see you didn't implement what you suggested You might have mentioned that to @SwmpDnky because he probably didn't notice

Not sure what you are talking about here since I implemented it how I suggested, but I explained to the OP would have to do more depending on how they would like this to operate. Do they only want to allow the user to add a single drawing or go to the drawings?

You can combine it where it will open to any existing drawings, but will default to the JobID for subsequent.

I hope I at least opened your eyes to the error of your ways and you will now use a different method to populate the FK on a popup form You're welcome.

Not sure what error you speak of, I do not see any errors. There may be something in my explanation that you misinterpreted or did not understand.

 

[Pat Hartman]

Maj, you posted the same bad solution twice.

It may be as simple as the calling form setting the value in the called (prodInfo) form.
Docmd.Openform "frmProdInfo"
forms("frmProdInfo").JobID = Me.JobID

I pointed out the problem with that method and offered a different method. But you doubled down and reposted the bad method again.
Then you created a demo that used a working solution but it wasn't the solution you had previously recommended, twice That's all.