Passing data to a new entry in a second table

[zebrafoot]

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

 

[zebrafoot]

Hi,

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

Pete

 

[zebrafoot]

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.

 

[zebrafoot]

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

 

[zebrafoot]

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]

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

 

[zebrafoot]

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

 

[zebrafoot]

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]

Here is also a post to allow you to scramble you data.

Randomizing Data for Posted db's

We often ask Original Poster to upload a copy of their db but the issue might be that it contains sensitive data. Complete data removal may not allow us to troubleshoot the OP's issue because queries, forms, etc. need records in order to observe or trouble shoot the problem. Here's a...

www.access-programmers.co.uk

You can dumb it down and scramble important info.

 

[June7]

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.

 

[zebrafoot]

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

 

[zebrafoot]

That looks logical. So I assume that I could substitute the code you have:

.txtSubjectF2 = me.txtSubjectF1
.txtMessageF2 = me.txtMessageF1

with ContactID/OrderID or whatever I wanted to pass?

 

[zebrafoot]

I would say that if you've got a field that ends in "ID" like "OrderID" then it is very likely that it is an auto number field generated automatically by Microsoft Access, which means you won't be able to update it, if that's what you're trying to do.

Appreciated. I merely want to use it a means of capturing a snapshot - so the ID fields (for example) would be a foreign key in the destination table.

 

[June7]

Might be less confusing if you don't use ID in foreign key field name or maybe suffix field name, such as OrderID_FK.

 

[mike60smart]

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

Hi Pete

Rather than trying to store the same information in different Tables why not have a Control in the Subform that Indicates what Type of Record it is.

It can be Either a "Quote" or "Confirmed Sale" etc...