How to pass value from one table to another one

[Mitrich]

Dear Friend, need your help...

I have two tables "Partners" and "Contacts" (Both linked by Partner ID) and four forms "Partners", "New Partner", "Contacts" and "New Contact".
In the form "Partners" I have a command button "New Partner" that opens "New Partner" form in a data-entry mode. "New Partner" form in its turn has two buttons "Save" and "Create Contact".

After I enter an information of the new partner into "New Partner" form and save it (by clicking "Save" button) I want to create a new contact related to this specific partner. When I click "Create Contact" button the "New Contact" form is opened in a data-entry mode. What I want is that the Partner ID (of just created partner in "New Partner" form ) is automatically entered into corresponding field on "New Contact" form.

Please advise how I can do it.

Thanks in advance!

PS: I'm using Navigation table "Main" so switching b/w all the forms are happening with the help of BrowseTo command.

[June7]

I don't understand table structure. Can a partner have more than one contact record?

Perhaps you should use a form/subform arrangement.

Otherwise, need code and there are several methods to accomplish.

[Mitrich]

Thanks for your reply!

"Can a partner have more than one contact record?" - yes, one partner can have a more than one contact.
"Perhaps you should use a form/subform arrangement." - for some reasons I don't want to use this arrangement. Otherwise I already do it.
"Otherwise, need code and there are several methods to accomplish." - yes, this is exactly what I need Could you please give me at least one example on how I can make it?

Thanks in advance!

[Mitrich]

Anyone? Please help!!!

[ItsMe]

You could pass the value to your "New Contact" form via Open Args in a Docmd. Something like
DoCmd.OpenForm "New Contact", acNormal, , , , acWindowNormal, Me!IDFieldName

Then in the other form you can access the value of OpenArgs.
Me.TextBox = Me.OpenArgs

You could also reference textbox controls from the first form before it closes. Something like
Forms![New Contact].[IDFieldName].Value = me.TextBox
if Forms![New Contact].[IDFieldName].dirty = true then
Forms![New Contact].[IDFieldName].dirty = false
end if

[June7]

But you only want to populate the field if it is empty. So code in the form's Open event:

If IsNull(Me!fieldname) Then Me!fieldname = Me.OpenArgs

Can make the form open to new record.

DoCmd.OpenForm "New Contact", , , , acFormAdd

[Mitrich]

ItsMe and June7, thanks a lot for your answers!

However I still have some problems that I do hope you’ll kindly help me to solve…

1. First I tried to use Openargs method (Despite the fact it’s not preferable for my case since DoCmd.OpenForm opens a form as a standalone, while I wanted it to be loaded into the NavigationSubform space. For that reason I had to use BrowseTo command instead. But anyway, I tried to follow your advice and test how it works)

So, on the click event on my button “Create Contact” I put the code that you've gave me:

Private Sub cmdNewContact_Click()
DoCmd.OpenForm "frm_NewContact", acNormal, , , , acWindowNormal, Me!PID
If IsNull(Me!PID) Then Me!PID = Me.OpenArgs
End Sub

Unfortunately, it just helps to open “frm_NewContact” form, while the PID textbox still remains to be empty. Please have a look at the screenshots:



In this example I expect PID textbox in the "frm_NewContact" form will get the value - 2198.
Please advise what I did wrong...

2. As for the second method advised by ItsMe – frankly I don’t quite understand how to use it . Could you please describe in more details what exactly should I do so that I could use it?

Thanks in advance for your time!

[ItsMe]

The Me keyword is a shortcut that you can use in place of the Fully Qualified Name of Objects like Forms and Reports. If you are writing code in a form's module and that form is named Form1 you could reference a control named Combo1 like this

Forms!Form1.Combo1

or you can use the shortcut
Me.Combo1

If you see code like
Forms![New Contact].[IDFieldName].Value = me.TextBox

You can assume the control TextBox is in the current form and you are trying to pass its value to a control named IDFieldName. Where IDFieldName resides in another form named New Contact. This will work as long as both forms are open simultaneously.

[June7]

If IsNull(Me!PID) Then Me!PID = Me.OpenArgs

goes in the Open or Current event of the NewContact form, not in the button Click event of Partner form.

If you are loading NewContact form into a navigation form subspace, I don't know if either of these suggestions will work. I don't use navigation form, don't like it.

[Mitrich]

It works!!! Thanks a lot guys for your angel patience and for such a detailed explanations!

[Mitrich]

Hi again ItsMe!
Sorry to bother you one more time but could you please help me once again?

Your Forms![New Contact].[IDFieldName].Value = me.TextBox works perfectly but only with DoCmd.OpenForm. Once I change it to DoCmd.BrowseTo it gives me Run-time error '2467': The expression your entered refers to an object that is closed or doesn't exist.

I suspect it happens because BrowsTo closes the previous (frm_NewPartner) form and that's why me.TextBox is not available any more...

Do you know any method how I can pass the value by suing DoCmd.BrowseTo?

Thanks in advance!

[June7]

Maybe with TempVars or globally declared variable.

[Mitrich]

Hi June7, thanks
Could you please provide some sample code that I can use as an example for my situation?
Thanks in advance!

[June7]

I've never used TempVars so will let you research if you are interested.

For global variable, review http://www.worldbestlearningcenter.c...-variables.htm

[ItsMe]

Once you use .BrowseTo, the form's module is no longer open/available. VBA within a form's module is private to the form. It is not available on the public interface. It is not available to other form's and modules (there is always an exception but, if the form is closed it is not available).

In essence, you are asking Access to execute a line of code after you tell it to close. The .BrowseTo is (kinda) closing the first form and opening a second, all in one line of code. When using BrowseTo, you are limited to the parameters provided. For instance, BrowseTo does not have an OpenArgs. Where OpenForm does have an OpenArgs argument within its parameters.

What June is suggesting is to place your variable in the Public Interface before closing your form or using BrowseTo. I do not use TempVars either. Just the name, Vars, sounds expensive so I have chosen to avoid them. My preference is to use global variables declared in a standard module. However, you have to be careful with this because a RunTime error will erase the values of global variables (as well as other variables) held in memory.

I will say, in short, you are experiencing some of the frustrations that have caused many to avoid Navigation Controls. Not to say Navigation Controls are a bad thing. It is just that many have avoided them. Avoiding them has limited my exposure to their nuances and understanding solutions needed to employ them.