Accessing Data From Multiple Forms/Tables

[Dennis]

I have a form that opens an opportunity table. There is a unique field for
Opportunity ID. From that form I want to open the contacts table which has
the same unique ID. I am using the macro Open Form where.... If the
opportunity table is on the first record, it will open the first record of
the contacts table. However, if I change the opportunity table to point to
the second record, the contact table still opens on the original record.
What am I doing wrong?

Thanks,

Dennis

 

[Brian]

The key is making sure the current OpportunityID gets passed to the form when
it opens. Post what you have in your Where section of the macro. It should
be something like this:

"OpportunityID = " & [Forms]![[Opportunities]![OpportunityID]

In VBA, the entire command would be like this:

DoCmd.OpenForm "Contacts",acNormal,,"[OpportunityID] = " & OpportunityID

The first OpportunityID refers to the field in the Contacts table that is
the RecordSource of the Contacts form. The second one refers to the
OpportunityID control on the Opportunities form (it can be invisible but
bound to the OpportunityID field in the Opportunities table.

 

[Dennis]

Brian,

This is what the macro generates for me:

Add/Update Contacts, Form, , "[Opportunity ID]=" & [Opportunity ID], , Normal

Dennis

The key is making sure the current OpportunityID gets passed to the form when
it opens. Post what you have in your Where section of the macro. It should
be something like this:

"OpportunityID = " & [Forms]![[Opportunities]![OpportunityID]

In VBA, the entire command would be like this:

DoCmd.OpenForm "Contacts",acNormal,,"[OpportunityID] = " & OpportunityID

The first OpportunityID refers to the field in the Contacts table that is
the RecordSource of the Contacts form. The second one refers to the
OpportunityID control on the Opportunities form (it can be invisible but
bound to the OpportunityID field in the Opportunities table.

I have a form that opens an opportunity table. There is a unique field for
Opportunity ID. From that form I want to open the contacts table which has
the same unique ID. I am using the macro Open Form where.... If the
opportunity table is on the first record, it will open the first record of
the contacts table. However, if I change the opportunity table to point to
the second record, the contact table still opens on the original record.
What am I doing wrong?

Thanks,

Dennis

 

[Brian]

I must admit I'm weak on macros because I put everything into VBA.

I think it should look like this (note the reference to the Opportunities
form)

Add/Update Contacts
Form
[Blank]
"[Opportunity ID]=" & [Forms]![Opportunities]![Opportunity ID]
[Blank]
Normal

I think it needs to have the reference to the OpportunityID control on the
Opportunties form, as above. If that doesn't work, post back with:

1. How do you call the macro? Via a button click?
2. Is there a control called OpportunityID on your Opportunities form?
3. If so, does it show the correct ID for the current OpportunityID when you
run the macro?

I would probably make the control hidden (after testing to make sure it
displays the current ID), since the user probably never needs to know it
(assuming it is the primary key in the Opportunities table.

Brian,

This is what the macro generates for me:

Add/Update Contacts, Form, , "[Opportunity ID]=" & [Opportunity ID], , Normal

Dennis

The key is making sure the current OpportunityID gets passed to the form when
it opens. Post what you have in your Where section of the macro. It should
be something like this:

"OpportunityID = " & [Forms]![[Opportunities]![OpportunityID]

In VBA, the entire command would be like this:

DoCmd.OpenForm "Contacts",acNormal,,"[OpportunityID] = " & OpportunityID

The first OpportunityID refers to the field in the Contacts table that is
the RecordSource of the Contacts form. The second one refers to the
OpportunityID control on the Opportunities form (it can be invisible but
bound to the OpportunityID field in the Opportunities table.

I have a form that opens an opportunity table. There is a unique field for
Opportunity ID. From that form I want to open the contacts table which has
the same unique ID. I am using the macro Open Form where.... If the
opportunity table is on the first record, it will open the first record of
the contacts table. However, if I change the opportunity table to point to
the second record, the contact table still opens on the original record.
What am I doing wrong?

Thanks,

Dennis

 

[Dennis]

Brian,

It is executed with a On Click of a button.. There is a control called
Opportunity ID on the Opportunities Form and it shows correctly. How can I
show these values in a Macro?

Dennis

I must admit I'm weak on macros because I put everything into VBA.

I think it should look like this (note the reference to the Opportunities
form)

Add/Update Contacts
Form
[Blank]
"[Opportunity ID]=" & [Forms]![Opportunities]![Opportunity ID]
[Blank]
Normal

I think it needs to have the reference to the OpportunityID control on the
Opportunties form, as above. If that doesn't work, post back with:

1. How do you call the macro? Via a button click?
2. Is there a control called OpportunityID on your Opportunities form?
3. If so, does it show the correct ID for the current OpportunityID when you
run the macro?

I would probably make the control hidden (after testing to make sure it
displays the current ID), since the user probably never needs to know it
(assuming it is the primary key in the Opportunities table.

Brian,

This is what the macro generates for me:

Add/Update Contacts, Form, , "[Opportunity ID]=" & [Opportunity ID], , Normal

Dennis

The key is making sure the current OpportunityID gets passed to the form when
it opens. Post what you have in your Where section of the macro. It should
be something like this:

"OpportunityID = " & [Forms]![[Opportunities]![OpportunityID]

In VBA, the entire command would be like this:

DoCmd.OpenForm "Contacts",acNormal,,"[OpportunityID] = " & OpportunityID

The first OpportunityID refers to the field in the Contacts table that is
the RecordSource of the Contacts form. The second one refers to the
OpportunityID control on the Opportunities form (it can be invisible but
bound to the OpportunityID field in the Opportunities table.

:

I have a form that opens an opportunity table. There is a unique field for
Opportunity ID. From that form I want to open the contacts table which has
the same unique ID. I am using the macro Open Form where.... If the
opportunity table is on the first record, it will open the first record of
the contacts table. However, if I change the opportunity table to point to
the second record, the contact table still opens on the original record.
What am I doing wrong?

Thanks,

Dennis

 

[Brian]

Change the Where part of the macro from this:

"[Opportunity ID]=" & [Opportunity ID]

to this:

"[Opportunity ID]=" & [Forms]![Opportunities]![Opportunity ID]

It needs to know that it is the specific OpportunityID on the form.


Alternatively, put this code in the button's click event in VBA (all one
line), instead of having the button run the macro:

DoCmd.OpenForm "Add/Update Contacts",acNormal,,"[OpportunityID] = " &
OpportunityID

VBA will already know that the last OpportunityID is the control on the form.

Brian,

It is executed with a On Click of a button.. There is a control called
Opportunity ID on the Opportunities Form and it shows correctly. How can I
show these values in a Macro?

Dennis

I must admit I'm weak on macros because I put everything into VBA.

I think it should look like this (note the reference to the Opportunities
form)

Add/Update Contacts
Form
[Blank]
"[Opportunity ID]=" & [Forms]![Opportunities]![Opportunity ID]
[Blank]
Normal

I think it needs to have the reference to the OpportunityID control on the
Opportunties form, as above. If that doesn't work, post back with:

1. How do you call the macro? Via a button click?
2. Is there a control called OpportunityID on your Opportunities form?
3. If so, does it show the correct ID for the current OpportunityID when you
run the macro?

I would probably make the control hidden (after testing to make sure it
displays the current ID), since the user probably never needs to know it
(assuming it is the primary key in the Opportunities table.

Brian,

This is what the macro generates for me:

Add/Update Contacts, Form, , "[Opportunity ID]=" & [Opportunity ID], , Normal

Dennis

:

The key is making sure the current OpportunityID gets passed to the form when
it opens. Post what you have in your Where section of the macro. It should
be something like this:

"OpportunityID = " & [Forms]![[Opportunities]![OpportunityID]

In VBA, the entire command would be like this:

DoCmd.OpenForm "Contacts",acNormal,,"[OpportunityID] = " & OpportunityID

The first OpportunityID refers to the field in the Contacts table that is
the RecordSource of the Contacts form. The second one refers to the
OpportunityID control on the Opportunities form (it can be invisible but
bound to the OpportunityID field in the Opportunities table.

:

I have a form that opens an opportunity table. There is a unique field for
Opportunity ID. From that form I want to open the contacts table which has
the same unique ID. I am using the macro Open Form where.... If the
opportunity table is on the first record, it will open the first record of
the contacts table. However, if I change the opportunity table to point to
the second record, the contact table still opens on the original record.
What am I doing wrong?

Thanks,

Dennis