Passing data to a new entry in a second table (1 Viewer)

zebrafoot

Member
Local time
Today, 11:08
Joined
May 15, 2020
Messages
61
Hi all,

I know lots of people have had similar questions, but I've been unable to find a solution that works in my instance, so please be gentle!

I'm developing a database in Access 2016. I have a form called frmContacts, which contains contact info (plus a sub-table with a snapshot view of the opportunities generated from the contact being viewed). I wish to link all new Opportunities to the originating contact, so I want to be able to do the following:

- click a button in the Contact form

- open the Opportunities form

- make a new record in Opportunities, using the ContactID taken from the Contact form

I just require a single numerical value (ContactID) to be copied across.

Any assistance would be very gratefully received.

Best wishes,
Pete
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:08
Joined
Jul 9, 2003
Messages
16,244
Hi zebra foot and welcome to access world forums (AWF)... Can you post your database?
 

zebrafoot

Member
Local time
Today, 11:08
Joined
May 15, 2020
Messages
61
Hi,

I'm afraid it contains confidential information, so I'm unable to do that.

Pete
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:08
Joined
Jul 9, 2003
Messages
16,244
I have a form called frmContacts, which contains contact info (plus a sub-table with a snapshot view of the opp

I wish to link all new Opportunities to the originating contact,

Well I'm thinking that if you have a sub-table displayed then it's probably in a subform/subreport control. A subform/subreport control will automatically provide a link between your contact and your Opportunities.
 

zebrafoot

Member
Local time
Today, 11:08
Joined
May 15, 2020
Messages
61
Hi,

Sorry, yes I mean subform - that's a bit of a red herring though - essentially I just want understand how I can select a contact from one table and using a button control, copy that contactID through to a new form (opportunity). The tables are linked already via contactID in the database relationships - that part of it works fine.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:08
Joined
Jul 9, 2003
Messages
16,244
As your contact form is already displaying opportunities then your contact is already linked to the opportunities table by the subform/subreport control. All you need to do is create a new record in your contact form, enter in the contact name, and then enter the opportunities associated with that contact in the now empty subform/subreport control displayed on the contact form.
 

zebrafoot

Member
Local time
Today, 11:08
Joined
May 15, 2020
Messages
61
Many thanks for your quick response.

To clarify what I'm trying to do, I think I need to give a bit more info. The subform is to give me an overview of the opportunities raised by a contact, so as such does not contain all the fields within the opportunity table - I've put control buttons within the subform, so if I want to go to the full opportunity I could open the full-size form easily - that bit works ok, however, when opening a new Opportunity record, the ContactID field is not automatically populated with the ContactID from the originating form - I have to enter a contact ID before I save the record and would like this to be automated to avoid user input errors.

Another factor that's driving this question is that I know that I'm going to want to copy data from one form into another for other parts of the database, so it would be helpful if I can understand the code I'd need to do that.

Hope that makes sense,

Pete
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:08
Joined
Jul 9, 2003
Messages
16,244
Another factor that's driving this question is that I know that I'm going to want to copy data from one form into another for other parts of the database, so it would be helpful if I can understand the code I'd need to do that.

One of the the most common ways to do this is to use the "OpenArgs" property. There are many examples of this within the forum try this link:-

Search for - "openargs"

Although you can do a lot with the "OpenArgs" it seems to be a bit of a fudge to me and I've developed my own system which I describe on my website here:-

 

zebrafoot

Member
Local time
Today, 11:08
Joined
May 15, 2020
Messages
61
Thank you once again for taking the time to reply - I'll take a look through the links you've shared and report back with any further questions.

Have a good weekend.
Pete
 

June7

AWF VIP
Local time
Today, 03:08
Joined
Mar 9, 2014
Messages
5,423
If ContactID is primary key on main form, and subform container Master/Child Links are properly set, ContactID should automatically populate to foreign key field of subform when new Opportunity record is created. But if you are not using subform for a entering new record then can use OpenArgs to pass primary key to independent form.

DoCmd.OpenForm "formname", , , , acFormAdd, , Me.Parent!ContactID

Then code behind second form in Current event.

If Me.NewRecord Then Me.ContactID_FK = Me.OpenArgs
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:08
Joined
Jul 9, 2003
Messages
16,244
I'll take a look through the links you've shared and report back with any further questions.

Actually, looking through that webpage, I'm not sure there's an example that suits your problem.

You might be better off using one of the OpenArgs methods.

I'm sure I've done a video about it somewhere, but I have yet added it to that webpage!
 

zebrafoot

Member
Local time
Today, 11:08
Joined
May 15, 2020
Messages
61
Hi,

Thanks once again. Is the OpenArgs method suitable for use where I wish to pass more than one piece of data to the new table?

Best wishes,
Pete
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:08
Joined
Jul 9, 2003
Messages
16,244
Is the OpenArgs method suitable for use where I wish to pass more than one piece of data to the new table?

Well yes, it is, however it's a bit of a fudge if you ask me...
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:08
Joined
Jul 9, 2003
Messages
16,244
I've developed my own system

The method I use requires more code which seems to upset some people. But I think the most important aspect of writing code is clarity, and to get the clarity you are better off writing more code...
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:08
Joined
Jul 9, 2003
Messages
16,244
I wish to pass more than one piece of data to the new table

If you post a dumbed Down version of your database, just the basic components where you want to apply this to, (a couple of tables and a couple of forms) replace any confidential table data with fictitious data, I can have a look and see if I can set up my method for you to have a look at.

A good way of getting some useful data for creating a sample database that you want to share with people is from the Northwind MS Access database which you can download:-

Setup the Northwind Sample dB

In fact you will find the North-wind MS Access database very handy! It contains examples of some useful tricks you can employ...

I'm sure there are some good examples of doing it with open-args, you might want to explore those. May be others that use openargs frequently might point you in the right direction...
 

zebrafoot

Member
Local time
Today, 11:08
Joined
May 15, 2020
Messages
61
If you post a dumbed Down version of your database, just the basic components where you want to apply this to, (a couple of tables and a couple of forms) replace any confidential table data with fictitious data, I can have a look and see if I can set up my method for you to have a look at.

That's a very kind offer. I will see if I can do that tomorrow morning.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:08
Joined
May 21, 2018
Messages
8,463
Here is also a post to allow you to scramble you data.
You can dumb it down and scramble important info.
 

June7

AWF VIP
Local time
Today, 03:08
Joined
Mar 9, 2014
Messages
5,423
I have used OpenArgs to pass multiple pieces of data. The data is a string separated by some character such as a semi-colon then code has to parse the string or use LIKE and * wildcard to test for particular content.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:08
Joined
Feb 19, 2002
Messages
42,970
Passing a single value or even multiple value to a pop up form using OpenArgs is certainly doable as others have explained. To pass multiple arguments, use a separator such as colon or semi-colon or something that would NEVER be a part of the data. Then at the other end, you can use the Split() function to separate out the string of variables.

Your explanation and example are very confusing. We can't understand why the subform records don't already have the ID of the parent record as a FK. If the subform isn't connected to the main form using the master/child links and is showing only UNASSIGNED opportunities, that makes a little more sense but in that case the solution would be to add a double-click event to the subform. In this event you can reference the parent form and copy the ID to the subform:

Me.ForeignKeyID = Me.Parent!PrimaryKeyID

Then when you save the record, the updated subform record will be removed when you requery the subform but will continue to show if you don't requery. This would be very confusing to the users.

I would suggest trying again to explain if the process you want and ditching the description of the form if it isn't actually involved in the process.
 

zebrafoot

Member
Local time
Today, 11:08
Joined
May 15, 2020
Messages
61
Hi again,

Thanks for taking the time to respond again. Apologies if I wasn't clear enough in my original question - I am a new access user so I really want to understand the underlying methodology for passing data between tables.

Let me try to explain the thinking behind what I want my database to do - the example I gave initially seems to have led to some confusion, so can we pretend for a moment that I never mentioned subtables! subforms!

I have a database of customers/opportunities/quotes/products etc. When a customer quote is made, I wish to make a FIXED record in a new table of the relevant details (e.g. price, applicable taxes, discounts etc) so that changes to the underlying tables will not affect the record I have made in the new table. However, the example I give here is irrelevant really; I just want to know if a form contains >1 piece of information that I wish to replicate elsewhere, how would I code a command to record the relevant variables temporarily, open a different form and drop the passed information in. I can see that OpenArgs may be a way to do this, but I'm struggling to find a good reference on the process.

I hope that's a clearer explanation, but if not, please say.

Best wishes,
Pete
 
Last edited:

Users who are viewing this thread

Top Bottom