Confussed as to which control to reference subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 4 subforms on my Main form and I'm trying to get the IDNumber to come
up on all 4 subforms. When the user clicks on the combo box and finds the
company they want to see info from, the IDNumber shows up on the main form.
This same IDNUmber also should show up on the other forms as well so that the
user will know that additional info is available with that company.

The 4 subforms are from 4 different queries & tables. The Primary Key is
IDNumber.

I'm confused as to where this should be located. Right now I'm trying to
put it in the "Link Master Fields" within the subform.

Here are the different ways I've tried but none work:

Me!RecordSource
=Forms![CTAMainView]![CTAContactsView].Form![IDNumber]
Me!Parent.CTAContactsView!IDnumber
Forms!CTAMainView!CTAContactsView!IDNumber

When I try to run the main form I get a box which says"Enter Parameter
Value" "OK or Cancel". Have not a clue what to put in.

Also tried "=[CTAName_Combo].[Column](1)
Which came up with a syntax error of "
'([__=[CTAName_Combo]].[[Column](1))=IDNumber]

Please help and tell me the correct way of doing this and where it should go.

Thank you.
 
Hi, melwester.

I'm not entirely clear, but it sounds like you're using an unbound combo box
on your main form to select a customer whose record you would like to view.
Is this correct?

I am assuming that the main form is based on a table or query that includes
the IDNumber. All that is necessary then to display related records in each
of the subforms is to set the LinkMasterFields and LinkChildFields properties
of each subform to the name of the field in the main form's recordsource and
the subforms recordsource, respectively.

To use the combo box to navigate to the selected record, place the following
code in its AfterUpdate event:

DoCmd.OpenForm "YourFormName", , , "IDNumber = " & Me!YourComboBox

changing YourFormName and YourComboBox to the names of your main form and
combo box. Once the code is executed, you will be looking at the selected
main form record and the related records in each subform will display.

If this does not resolve your problem, then my assumptions are incorrect.
In that case, please post the table or query structures of the main and each
subform.

Hope that helps.
Sprinks
 
I'm not entirely clear, but it sounds like you're using an unbound combo box
on your main form to select a customer whose record you would like to view.
Is this correct?
YES

I am assuming that the main form is based on a table or query that includes
the IDNumber. All that is necessary then to display related records in each
of the subforms is to set the LinkMasterFields and LinkChildFields properties
of each subform to the name of the field in the main form's recordsource and
the subforms recordsource, respectively.
Click to expand...

Based on a query, Subform1 is based on it's table, sub2 on a query, sub3 & 4
I haven't gotten to yet. Trying to get sub1 working first. Then will do the
same for 2-4.

I do have the Master & Child field properties in place for each subform.
But its not working.
To use the combo box to navigate to the selected record, place the following
code in its AfterUpdate event:

DoCmd.OpenForm "YourFormName", , , "IDNumber = " & Me!YourComboBox

changing YourFormName and YourComboBox to the names of your main form and combo box. Once the code is executed, you will be looking at the selected
main form record and the related records in each subform will display.
Click to expand...

I've tried this. Still won't work.

DoCmd.Openform "frm_CTAMainView",,,"IDNumber="& Me!CTAName_Combo
I've even tried it without the " "
I put this inthe "after event on the main form - Didn't work and tried it on
the subform -- No Go -- It still prompts me with "Enter IDNumber Number"

Here is the Main Table:
IDNumber
CTAName
Address
City
State
Zip
Email
Phone
Fax
Contact Name
Contact Phone
Contact Fax
Contact Email

AdditionalContacts Table:
IDNumber
Contact Name2
Contact Phone2
Contact Fax2
Contact Email2
Same for Contacts 3 & 4

As a CTAName (Company) could have up to 4 contacts.



Sprinks said:
Hi, melwester.

I'm not entirely clear, but it sounds like you're using an unbound combo box
on your main form to select a customer whose record you would like to view.
Is this correct?

I am assuming that the main form is based on a table or query that includes
the IDNumber. All that is necessary then to display related records in each
of the subforms is to set the LinkMasterFields and LinkChildFields properties
of each subform to the name of the field in the main form's recordsource and
the subforms recordsource, respectively.

To use the combo box to navigate to the selected record, place the following
code in its AfterUpdate event:

DoCmd.OpenForm "YourFormName", , , "IDNumber = " & Me!YourComboBox

changing YourFormName and YourComboBox to the names of your main form and
combo box. Once the code is executed, you will be looking at the selected
main form record and the related records in each subform will display.

If this does not resolve your problem, then my assumptions are incorrect.
In that case, please post the table or query structures of the main and each
subform.

Hope that helps.
Sprinks


melwester said:
I have 4 subforms on my Main form and I'm trying to get the IDNumber to come
up on all 4 subforms. When the user clicks on the combo box and finds the
company they want to see info from, the IDNumber shows up on the main form.
This same IDNUmber also should show up on the other forms as well so that the
user will know that additional info is available with that company.

The 4 subforms are from 4 different queries & tables. The Primary Key is
IDNumber.

I'm confused as to where this should be located. Right now I'm trying to
put it in the "Link Master Fields" within the subform.

Here are the different ways I've tried but none work:

Me!RecordSource
=Forms![CTAMainView]![CTAContactsView].Form![IDNumber]
Me!Parent.CTAContactsView!IDnumber
Forms!CTAMainView!CTAContactsView!IDNumber

When I try to run the main form I get a box which says"Enter Parameter
Value" "OK or Cancel". Have not a clue what to put in.

Also tried "=[CTAName_Combo].[Column](1)
Which came up with a syntax error of "
'([__=[CTAName_Combo]].[[Column](1))=IDNumber]

Please help and tell me the correct way of doing this and where it should go.

Thank you.
Click to expand...
Click to expand...
 
I'm not sure what you mean by "after event of the main form". The code
belongs in the AfterUpdate event of the unbound combo box.

However, you have a table normalization issue. Perhaps correcting this
first will help to an easier solution.

The AdditionalContacts table does not appear to have a primary key, and you
are attempting to implement a natural one-to-many relationship by having
multiple contacts in a single record. A Contacts table that has a single
contact per record is much easier to implement with a single subform. For a
given main table record, you could have 1, 2, or any other arbitrary number
of Contacts:

Contacts
ContactID AutoNumber (Primary Key)
IDNumber Number (Foreign Key to Main Table)
ContactName Text
Phone Text
Fax Text
Email Hyperlink

The syntax of your code looks correct. Check:

- That the AfterUpdate property shows (Event Procedure), and that when you
open this procedure it contains the code you've typed. I don't think you cut
and pasted it because Access would put a space between the " and the &
symbols.
- That the Field name in the main table matches the name on the left side of
the equal sign exactly.
- That the name of your unbound combo box control matches the name on the
right hand side of the equal sign exactly.
- That the main form's RecordSource property is what you think it is.
- That the main form's RecordSource contains the IDNumber field.
- Same previous two for the subform.
- That the subform's LinkMasterFields and LinkChildFields are set to the
IDNumber.

The cut-and-pasted code I posted was tested and works as described, so I
know the approach is correct.

Hope that helps.
Sprinks

melwester said:
I'm not entirely clear, but it sounds like you're using an unbound combo box
on your main form to select a customer whose record you would like to view.
Is this correct?
YES

I am assuming that the main form is based on a table or query that includes
the IDNumber. All that is necessary then to display related records in each
of the subforms is to set the LinkMasterFields and LinkChildFields properties
of each subform to the name of the field in the main form's recordsource and
the subforms recordsource, respectively.
Click to expand...

Based on a query, Subform1 is based on it's table, sub2 on a query, sub3 & 4
I haven't gotten to yet. Trying to get sub1 working first. Then will do the
same for 2-4.

I do have the Master & Child field properties in place for each subform.
But its not working.
To use the combo box to navigate to the selected record, place the following
code in its AfterUpdate event:

DoCmd.OpenForm "YourFormName", , , "IDNumber = " & Me!YourComboBox

changing YourFormName and YourComboBox to the names of your main form and combo box. Once the code is executed, you will be looking at the selected
main form record and the related records in each subform will display.
Click to expand...

I've tried this. Still won't work.

DoCmd.Openform "frm_CTAMainView",,,"IDNumber="& Me!CTAName_Combo
I've even tried it without the " "
I put this inthe "after event on the main form - Didn't work and tried it on
the subform -- No Go -- It still prompts me with "Enter IDNumber Number"

Here is the Main Table:
IDNumber
CTAName
Address
City
State
Zip
Email
Phone
Fax
Contact Name
Contact Phone
Contact Fax
Contact Email

AdditionalContacts Table:
IDNumber
Contact Name2
Contact Phone2
Contact Fax2
Contact Email2
Same for Contacts 3 & 4

As a CTAName (Company) could have up to 4 contacts.



Sprinks said:
Hi, melwester.

I'm not entirely clear, but it sounds like you're using an unbound combo box
on your main form to select a customer whose record you would like to view.
Is this correct?

I am assuming that the main form is based on a table or query that includes
the IDNumber. All that is necessary then to display related records in each
of the subforms is to set the LinkMasterFields and LinkChildFields properties
of each subform to the name of the field in the main form's recordsource and
the subforms recordsource, respectively.

To use the combo box to navigate to the selected record, place the following
code in its AfterUpdate event:

DoCmd.OpenForm "YourFormName", , , "IDNumber = " & Me!YourComboBox

changing YourFormName and YourComboBox to the names of your main form and
combo box. Once the code is executed, you will be looking at the selected
main form record and the related records in each subform will display.

If this does not resolve your problem, then my assumptions are incorrect.
In that case, please post the table or query structures of the main and each
subform.

Hope that helps.
Sprinks


melwester said:
I have 4 subforms on my Main form and I'm trying to get the IDNumber to come
up on all 4 subforms. When the user clicks on the combo box and finds the
company they want to see info from, the IDNumber shows up on the main form.
This same IDNUmber also should show up on the other forms as well so that the
user will know that additional info is available with that company.

The 4 subforms are from 4 different queries & tables. The Primary Key is
IDNumber.

I'm confused as to where this should be located. Right now I'm trying to
put it in the "Link Master Fields" within the subform.

Here are the different ways I've tried but none work:

Me!RecordSource
=Forms![CTAMainView]![CTAContactsView].Form![IDNumber]
Me!Parent.CTAContactsView!IDnumber
Forms!CTAMainView!CTAContactsView!IDNumber

When I try to run the main form I get a box which says"Enter Parameter
Value" "OK or Cancel". Have not a clue what to put in.

Also tried "=[CTAName_Combo].[Column](1)
Which came up with a syntax error of "
'([__=[CTAName_Combo]].[[Column](1))=IDNumber]

Please help and tell me the correct way of doing this and where it should go.

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
The IDNumber IS the Primary Key on all tables.

I'm still getting "Enter Parameter Value" Wants me to enter a CTANumber.
Should just come up to a blank Form. For the user to click on the combo and
the combo will fill in the form in.

I had this working last week. Can't remember what I did to get it working.
I do have this VBA code that someone had mentioned:

Private Sub Additional_Contacts_Click()
On Error GoTo Err_Additional_Contacts_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim stWhere As String

stDocName = "sfrm_CTAContactsView"
stWhere = "[IDNumber] = '" & Me!Number_Text & "'"
DoCmd.OpenForm stDocName, acNormal, stLinkCriteria, stWhere

Exit_Additional_Contacts_Click:
Exit Sub

Err_Additional_Contacts_Click:
MsgBox Err.Description
Resume Exit_Additional_Contacts_Click

End Sub

This was working.
When I tested it I would click on the Combo Box and the name of the company
and the info for the main form filled. If I wanted more info such as
"Additional Contacts, Notes, Products, etc" (Subforms) I would click on the
subform and the info for that IDNumber was displayed. Why won't this work
now - It worked LAst week!!!!!!


Sprinks said:
I'm not sure what you mean by "after event of the main form". The code
belongs in the AfterUpdate event of the unbound combo box.

However, you have a table normalization issue. Perhaps correcting this
first will help to an easier solution.

The AdditionalContacts table does not appear to have a primary key, and you
are attempting to implement a natural one-to-many relationship by having
multiple contacts in a single record. A Contacts table that has a single
contact per record is much easier to implement with a single subform. For a
given main table record, you could have 1, 2, or any other arbitrary number
of Contacts:

Contacts
ContactID AutoNumber (Primary Key)
IDNumber Number (Foreign Key to Main Table)
ContactName Text
Phone Text
Fax Text
Email Hyperlink

The syntax of your code looks correct. Check:

- That the AfterUpdate property shows (Event Procedure), and that when you
open this procedure it contains the code you've typed. I don't think you cut
and pasted it because Access would put a space between the " and the &
symbols.
- That the Field name in the main table matches the name on the left side of
the equal sign exactly.
- That the name of your unbound combo box control matches the name on the
right hand side of the equal sign exactly.
- That the main form's RecordSource property is what you think it is.
- That the main form's RecordSource contains the IDNumber field.
- Same previous two for the subform.
- That the subform's LinkMasterFields and LinkChildFields are set to the
IDNumber.

The cut-and-pasted code I posted was tested and works as described, so I
know the approach is correct.

Hope that helps.
Sprinks

melwester said:
I'm not entirely clear, but it sounds like you're using an unbound combo box
on your main form to select a customer whose record you would like to view.
Is this correct?
YES

I am assuming that the main form is based on a table or query that includes
the IDNumber. All that is necessary then to display related records in each
of the subforms is to set the LinkMasterFields and LinkChildFields properties
of each subform to the name of the field in the main form's recordsource and
the subforms recordsource, respectively.
Click to expand...

Based on a query, Subform1 is based on it's table, sub2 on a query, sub3 & 4
I haven't gotten to yet. Trying to get sub1 working first. Then will do the
same for 2-4.

I do have the Master & Child field properties in place for each subform.
But its not working.
To use the combo box to navigate to the selected record, place the following
code in its AfterUpdate event:

DoCmd.OpenForm "YourFormName", , , "IDNumber = " & Me!YourComboBox

changing YourFormName and YourComboBox to the names of your main form and combo box. Once the code is executed, you will be looking at the selected
main form record and the related records in each subform will display.
Click to expand...

I've tried this. Still won't work.

DoCmd.Openform "frm_CTAMainView",,,"IDNumber="& Me!CTAName_Combo
I've even tried it without the " "
I put this inthe "after event on the main form - Didn't work and tried it on
the subform -- No Go -- It still prompts me with "Enter IDNumber Number"

Here is the Main Table:
IDNumber
CTAName
Address
City
State
Zip
Email
Phone
Fax
Contact Name
Contact Phone
Contact Fax
Contact Email

AdditionalContacts Table:
IDNumber
Contact Name2
Contact Phone2
Contact Fax2
Contact Email2
Same for Contacts 3 & 4

As a CTAName (Company) could have up to 4 contacts.



Sprinks said:
Hi, melwester.

I'm not entirely clear, but it sounds like you're using an unbound combo box
on your main form to select a customer whose record you would like to view.
Is this correct?

I am assuming that the main form is based on a table or query that includes
the IDNumber. All that is necessary then to display related records in each
of the subforms is to set the LinkMasterFields and LinkChildFields properties
of each subform to the name of the field in the main form's recordsource and
the subforms recordsource, respectively.

To use the combo box to navigate to the selected record, place the following
code in its AfterUpdate event:

DoCmd.OpenForm "YourFormName", , , "IDNumber = " & Me!YourComboBox

changing YourFormName and YourComboBox to the names of your main form and
combo box. Once the code is executed, you will be looking at the selected
main form record and the related records in each subform will display.

If this does not resolve your problem, then my assumptions are incorrect.
In that case, please post the table or query structures of the main and each
subform.

Hope that helps.
Sprinks


:

I have 4 subforms on my Main form and I'm trying to get the IDNumber to come
up on all 4 subforms. When the user clicks on the combo box and finds the
company they want to see info from, the IDNumber shows up on the main form.
This same IDNUmber also should show up on the other forms as well so that the
user will know that additional info is available with that company.

The 4 subforms are from 4 different queries & tables. The Primary Key is
IDNumber.

I'm confused as to where this should be located. Right now I'm trying to
put it in the "Link Master Fields" within the subform.

Here are the different ways I've tried but none work:

Me!RecordSource
=Forms![CTAMainView]![CTAContactsView].Form![IDNumber]
Me!Parent.CTAContactsView!IDnumber
Forms!CTAMainView!CTAContactsView!IDNumber

When I try to run the main form I get a box which says"Enter Parameter
Value" "OK or Cancel". Have not a clue what to put in.

Also tried "=[CTAName_Combo].[Column](1)
Which came up with a syntax error of "
'([__=[CTAName_Combo]].[[Column](1))=IDNumber]

Please help and tell me the correct way of doing this and where it should go.

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Melwester,

The Contacts table should have its own primary key, unrelated to the
IDNumber of your main table, that uniquely identifies each record. To relate
these records to a main form record, include a "foreign" key field whose data
matches the primary key of the main table.

Perhaps the confusion is because you've called both keys IDNumber. Let's
presume you changed them to the following:

Main Table
CTA_ID AutoNumber (PK)
CTAName Text
etc.

Contacts Table
ContactsID AutoNumber (PK)
CTA_ID Number (Foreign Key to Main Table)
ContactName Text
etc.

With a subform based on Contacts, and a main form based on the main table,
the subform properties would be:

LinkMasterFields: CTA_ID (of the main table)
LinkChildFields: CTA_ID (of the contacts table)

And the code I gave you should work.

The code you posted was attached to a command button that is designed to
open a new form. It has a flaw in it, in that stLinkCriteria is never
assigned, nor is it needed, as its placement is for a FilterName, whereas the
code is utilizing a Where condition assigned to stWhere. So you are passing
OpenForm a parameter that has no value. If you are using this code, it may
be causing the error.

Hope that makes my meaning clear. Good luck.
Sprinks

melwester said:
The IDNumber IS the Primary Key on all tables.

I'm still getting "Enter Parameter Value" Wants me to enter a CTANumber.
Should just come up to a blank Form. For the user to click on the combo and
the combo will fill in the form in.

I had this working last week. Can't remember what I did to get it working.
I do have this VBA code that someone had mentioned:

Private Sub Additional_Contacts_Click()
On Error GoTo Err_Additional_Contacts_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim stWhere As String

stDocName = "sfrm_CTAContactsView"
stWhere = "[IDNumber] = '" & Me!Number_Text & "'"
DoCmd.OpenForm stDocName, acNormal, stLinkCriteria, stWhere

Exit_Additional_Contacts_Click:
Exit Sub

Err_Additional_Contacts_Click:
MsgBox Err.Description
Resume Exit_Additional_Contacts_Click

End Sub

This was working.
When I tested it I would click on the Combo Box and the name of the company
and the info for the main form filled. If I wanted more info such as
"Additional Contacts, Notes, Products, etc" (Subforms) I would click on the
subform and the info for that IDNumber was displayed. Why won't this work
now - It worked LAst week!!!!!!


Sprinks said:
I'm not sure what you mean by "after event of the main form". The code
belongs in the AfterUpdate event of the unbound combo box.

However, you have a table normalization issue. Perhaps correcting this
first will help to an easier solution.

The AdditionalContacts table does not appear to have a primary key, and you
are attempting to implement a natural one-to-many relationship by having
multiple contacts in a single record. A Contacts table that has a single
contact per record is much easier to implement with a single subform. For a
given main table record, you could have 1, 2, or any other arbitrary number
of Contacts:

Contacts
ContactID AutoNumber (Primary Key)
IDNumber Number (Foreign Key to Main Table)
ContactName Text
Phone Text
Fax Text
Email Hyperlink

The syntax of your code looks correct. Check:

- That the AfterUpdate property shows (Event Procedure), and that when you
open this procedure it contains the code you've typed. I don't think you cut
and pasted it because Access would put a space between the " and the &
symbols.
- That the Field name in the main table matches the name on the left side of
the equal sign exactly.
- That the name of your unbound combo box control matches the name on the
right hand side of the equal sign exactly.
- That the main form's RecordSource property is what you think it is.
- That the main form's RecordSource contains the IDNumber field.
- Same previous two for the subform.
- That the subform's LinkMasterFields and LinkChildFields are set to the
IDNumber.

The cut-and-pasted code I posted was tested and works as described, so I
know the approach is correct.

Hope that helps.
Sprinks

melwester said:
I'm not entirely clear, but it sounds like you're using an unbound combo box
on your main form to select a customer whose record you would like to view.
Is this correct?

YES

I am assuming that the main form is based on a table or query that includes
the IDNumber. All that is necessary then to display related records in each
of the subforms is to set the LinkMasterFields and LinkChildFields properties
of each subform to the name of the field in the main form's recordsource and
the subforms recordsource, respectively.

Based on a query, Subform1 is based on it's table, sub2 on a query, sub3 & 4
I haven't gotten to yet. Trying to get sub1 working first. Then will do the
same for 2-4.

I do have the Master & Child field properties in place for each subform.
But its not working.

To use the combo box to navigate to the selected record, place the following
code in its AfterUpdate event:

DoCmd.OpenForm "YourFormName", , , "IDNumber = " & Me!YourComboBox

changing YourFormName and YourComboBox to the names of your main form and combo box. Once the code is executed, you will be looking at the selected
main form record and the related records in each subform will display.

I've tried this. Still won't work.

DoCmd.Openform "frm_CTAMainView",,,"IDNumber="& Me!CTAName_Combo
I've even tried it without the " "
I put this inthe "after event on the main form - Didn't work and tried it on
the subform -- No Go -- It still prompts me with "Enter IDNumber Number"

Here is the Main Table:
IDNumber
CTAName
Address
City
State
Zip
Email
Phone
Fax
Contact Name
Contact Phone
Contact Fax
Contact Email

AdditionalContacts Table:
IDNumber
Contact Name2
Contact Phone2
Contact Fax2
Contact Email2
Same for Contacts 3 & 4

As a CTAName (Company) could have up to 4 contacts.



:

Hi, melwester.

I'm not entirely clear, but it sounds like you're using an unbound combo box
on your main form to select a customer whose record you would like to view.
Is this correct?

I am assuming that the main form is based on a table or query that includes
the IDNumber. All that is necessary then to display related records in each
of the subforms is to set the LinkMasterFields and LinkChildFields properties
of each subform to the name of the field in the main form's recordsource and
the subforms recordsource, respectively.

To use the combo box to navigate to the selected record, place the following
code in its AfterUpdate event:

DoCmd.OpenForm "YourFormName", , , "IDNumber = " & Me!YourComboBox

changing YourFormName and YourComboBox to the names of your main form and
combo box. Once the code is executed, you will be looking at the selected
main form record and the related records in each subform will display.

If this does not resolve your problem, then my assumptions are incorrect.
In that case, please post the table or query structures of the main and each
subform.

Hope that helps.
Sprinks


:

I have 4 subforms on my Main form and I'm trying to get the IDNumber to come
up on all 4 subforms. When the user clicks on the combo box and finds the
company they want to see info from, the IDNumber shows up on the main form.
This same IDNUmber also should show up on the other forms as well so that the
user will know that additional info is available with that company.

The 4 subforms are from 4 different queries & tables. The Primary Key is
IDNumber.

I'm confused as to where this should be located. Right now I'm trying to
put it in the "Link Master Fields" within the subform.

Here are the different ways I've tried but none work:

Me!RecordSource
=Forms![CTAMainView]![CTAContactsView].Form![IDNumber]
Me!Parent.CTAContactsView!IDnumber
Forms!CTAMainView!CTAContactsView!IDNumber

When I try to run the main form I get a box which says"Enter Parameter
Value" "OK or Cancel". Have not a clue what to put in.

Also tried "=[CTAName_Combo].[Column](1)
Which came up with a syntax error of "
'([__=[CTAName_Combo]].[[Column](1))=IDNumber]

Please help and tell me the correct way of doing this and where it should go.

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Sprinks said:
Melwester,

The Contacts table should have its own primary key, unrelated to the
IDNumber of your main table, that uniquely identifies each record. To relate
these records to a main form record, include a "foreign" key field whose data
matches the primary key of the main table.

Perhaps the confusion is because you've called both keys IDNumber. Let's
presume you changed them to the following:

Main Table
CTA_ID AutoNumber (PK)
CTAName Text
etc.

Contacts Table
ContactsID AutoNumber (PK)
CTA_ID Number (Foreign Key to Main Table)
ContactName Text
etc.

With a subform based on Contacts, and a main form based on the main table,
the subform properties would be:

LinkMasterFields: CTA_ID (of the main table)
LinkChildFields: CTA_ID (of the contacts table)

And the code I gave you should work.

The code you posted was attached to a command button that is designed to
open a new form. It has a flaw in it, in that stLinkCriteria is never
assigned, nor is it needed, as its placement is for a FilterName, whereas the
code is utilizing a Where condition assigned to stWhere. So you are passing
OpenForm a parameter that has no value. If you are using this code, it may
be causing the error.

Hope that makes my meaning clear. Good luck.
Sprinks

melwester said:
The IDNumber IS the Primary Key on all tables.

I'm still getting "Enter Parameter Value" Wants me to enter a CTANumber.
Should just come up to a blank Form. For the user to click on the combo and
the combo will fill in the form in.

I had this working last week. Can't remember what I did to get it working.
I do have this VBA code that someone had mentioned:

Private Sub Additional_Contacts_Click()
On Error GoTo Err_Additional_Contacts_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim stWhere As String

stDocName = "sfrm_CTAContactsView"
stWhere = "[IDNumber] = '" & Me!Number_Text & "'"
DoCmd.OpenForm stDocName, acNormal, stLinkCriteria, stWhere

Exit_Additional_Contacts_Click:
Exit Sub

Err_Additional_Contacts_Click:
MsgBox Err.Description
Resume Exit_Additional_Contacts_Click

End Sub

This was working.
When I tested it I would click on the Combo Box and the name of the company
and the info for the main form filled. If I wanted more info such as
"Additional Contacts, Notes, Products, etc" (Subforms) I would click on the
subform and the info for that IDNumber was displayed. Why won't this work
now - It worked LAst week!!!!!!


Sprinks said:
I'm not sure what you mean by "after event of the main form". The code
belongs in the AfterUpdate event of the unbound combo box.

However, you have a table normalization issue. Perhaps correcting this
first will help to an easier solution.

The AdditionalContacts table does not appear to have a primary key, and you
are attempting to implement a natural one-to-many relationship by having
multiple contacts in a single record. A Contacts table that has a single
contact per record is much easier to implement with a single subform. For a
given main table record, you could have 1, 2, or any other arbitrary number
of Contacts:

Contacts
ContactID AutoNumber (Primary Key)
IDNumber Number (Foreign Key to Main Table)
ContactName Text
Phone Text
Fax Text
Email Hyperlink

The syntax of your code looks correct. Check:

- That the AfterUpdate property shows (Event Procedure), and that when you
open this procedure it contains the code you've typed. I don't think you cut
and pasted it because Access would put a space between the " and the &
symbols.
- That the Field name in the main table matches the name on the left side of
the equal sign exactly.
- That the name of your unbound combo box control matches the name on the
right hand side of the equal sign exactly.
- That the main form's RecordSource property is what you think it is.
- That the main form's RecordSource contains the IDNumber field.
- Same previous two for the subform.
- That the subform's LinkMasterFields and LinkChildFields are set to the
IDNumber.

The cut-and-pasted code I posted was tested and works as described, so I
know the approach is correct.

Hope that helps.
Sprinks

:

I'm not entirely clear, but it sounds like you're using an unbound combo box
on your main form to select a customer whose record you would like to view.
Is this correct?

YES

I am assuming that the main form is based on a table or query that includes
the IDNumber. All that is necessary then to display related records in each
of the subforms is to set the LinkMasterFields and LinkChildFields properties
of each subform to the name of the field in the main form's recordsource and
the subforms recordsource, respectively.

Based on a query, Subform1 is based on it's table, sub2 on a query, sub3 & 4
I haven't gotten to yet. Trying to get sub1 working first. Then will do the
same for 2-4.

I do have the Master & Child field properties in place for each subform.
But its not working.

To use the combo box to navigate to the selected record, place the following
code in its AfterUpdate event:

DoCmd.OpenForm "YourFormName", , , "IDNumber = " & Me!YourComboBox

changing YourFormName and YourComboBox to the names of your main form and combo box. Once the code is executed, you will be looking at the selected
main form record and the related records in each subform will display.

I've tried this. Still won't work.

DoCmd.Openform "frm_CTAMainView",,,"IDNumber="& Me!CTAName_Combo
I've even tried it without the " "
I put this inthe "after event on the main form - Didn't work and tried it on
the subform -- No Go -- It still prompts me with "Enter IDNumber Number"

Here is the Main Table:
IDNumber
CTAName
Address
City
State
Zip
Email
Phone
Fax
Contact Name
Contact Phone
Contact Fax
Contact Email

AdditionalContacts Table:
IDNumber
Contact Name2
Contact Phone2
Contact Fax2
Contact Email2
Same for Contacts 3 & 4

As a CTAName (Company) could have up to 4 contacts.



:

Hi, melwester.

I'm not entirely clear, but it sounds like you're using an unbound combo box
on your main form to select a customer whose record you would like to view.
Is this correct?

I am assuming that the main form is based on a table or query that includes
the IDNumber. All that is necessary then to display related records in each
of the subforms is to set the LinkMasterFields and LinkChildFields properties
of each subform to the name of the field in the main form's recordsource and
the subforms recordsource, respectively.

To use the combo box to navigate to the selected record, place the following
code in its AfterUpdate event:

DoCmd.OpenForm "YourFormName", , , "IDNumber = " & Me!YourComboBox

changing YourFormName and YourComboBox to the names of your main form and
combo box. Once the code is executed, you will be looking at the selected
main form record and the related records in each subform will display.

If this does not resolve your problem, then my assumptions are incorrect.
In that case, please post the table or query structures of the main and each
subform.

Hope that helps.
Sprinks


:

I have 4 subforms on my Main form and I'm trying to get the IDNumber to come
up on all 4 subforms. When the user clicks on the combo box and finds the
company they want to see info from, the IDNumber shows up on the main form.
This same IDNUmber also should show up on the other forms as well so that the
user will know that additional info is available with that company.

The 4 subforms are from 4 different queries & tables. The Primary Key is
IDNumber.

I'm confused as to where this should be located. Right now I'm trying to
put it in the "Link Master Fields" within the subform.

Here are the different ways I've tried but none work:

Me!RecordSource
=Forms![CTAMainView]![CTAContactsView].Form![IDNumber]
Me!Parent.CTAContactsView!IDnumber
Forms!CTAMainView!CTAContactsView!IDNumber

When I try to run the main form I get a box which says"Enter Parameter
Value" "OK or Cancel". Have not a clue what to put in.

Also tried "=[CTAName_Combo].[Column](1)
Which came up with a syntax error of "
'([__=[CTAName_Combo]].[[Column](1))=IDNumber]

Please help and tell me the correct way of doing this and where it should go.

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Melwester,

I'm still willing to help; but your latest post doesn't have any new
information.

Sprinks

melwester said:
Sprinks said:
Melwester,

The Contacts table should have its own primary key, unrelated to the
IDNumber of your main table, that uniquely identifies each record. To relate
these records to a main form record, include a "foreign" key field whose data
matches the primary key of the main table.

Perhaps the confusion is because you've called both keys IDNumber. Let's
presume you changed them to the following:

Main Table
CTA_ID AutoNumber (PK)
CTAName Text
etc.

Contacts Table
ContactsID AutoNumber (PK)
CTA_ID Number (Foreign Key to Main Table)
ContactName Text
etc.

With a subform based on Contacts, and a main form based on the main table,
the subform properties would be:

LinkMasterFields: CTA_ID (of the main table)
LinkChildFields: CTA_ID (of the contacts table)

And the code I gave you should work.

The code you posted was attached to a command button that is designed to
open a new form. It has a flaw in it, in that stLinkCriteria is never
assigned, nor is it needed, as its placement is for a FilterName, whereas the
code is utilizing a Where condition assigned to stWhere. So you are passing
OpenForm a parameter that has no value. If you are using this code, it may
be causing the error.

Hope that makes my meaning clear. Good luck.
Sprinks

melwester said:
The IDNumber IS the Primary Key on all tables.

I'm still getting "Enter Parameter Value" Wants me to enter a CTANumber.
Should just come up to a blank Form. For the user to click on the combo and
the combo will fill in the form in.

I had this working last week. Can't remember what I did to get it working.
I do have this VBA code that someone had mentioned:

Private Sub Additional_Contacts_Click()
On Error GoTo Err_Additional_Contacts_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim stWhere As String

stDocName = "sfrm_CTAContactsView"
stWhere = "[IDNumber] = '" & Me!Number_Text & "'"
DoCmd.OpenForm stDocName, acNormal, stLinkCriteria, stWhere

Exit_Additional_Contacts_Click:
Exit Sub

Err_Additional_Contacts_Click:
MsgBox Err.Description
Resume Exit_Additional_Contacts_Click

End Sub

This was working.
When I tested it I would click on the Combo Box and the name of the company
and the info for the main form filled. If I wanted more info such as
"Additional Contacts, Notes, Products, etc" (Subforms) I would click on the
subform and the info for that IDNumber was displayed. Why won't this work
now - It worked LAst week!!!!!!


:

I'm not sure what you mean by "after event of the main form". The code
belongs in the AfterUpdate event of the unbound combo box.

However, you have a table normalization issue. Perhaps correcting this
first will help to an easier solution.

The AdditionalContacts table does not appear to have a primary key, and you
are attempting to implement a natural one-to-many relationship by having
multiple contacts in a single record. A Contacts table that has a single
contact per record is much easier to implement with a single subform. For a
given main table record, you could have 1, 2, or any other arbitrary number
of Contacts:

Contacts
ContactID AutoNumber (Primary Key)
IDNumber Number (Foreign Key to Main Table)
ContactName Text
Phone Text
Fax Text
Email Hyperlink

The syntax of your code looks correct. Check:

- That the AfterUpdate property shows (Event Procedure), and that when you
open this procedure it contains the code you've typed. I don't think you cut
and pasted it because Access would put a space between the " and the &
symbols.
- That the Field name in the main table matches the name on the left side of
the equal sign exactly.
- That the name of your unbound combo box control matches the name on the
right hand side of the equal sign exactly.
- That the main form's RecordSource property is what you think it is.
- That the main form's RecordSource contains the IDNumber field.
- Same previous two for the subform.
- That the subform's LinkMasterFields and LinkChildFields are set to the
IDNumber.

The cut-and-pasted code I posted was tested and works as described, so I
know the approach is correct.

Hope that helps.
Sprinks

:

I'm not entirely clear, but it sounds like you're using an unbound combo box
on your main form to select a customer whose record you would like to view.
Is this correct?

YES

I am assuming that the main form is based on a table or query that includes
the IDNumber. All that is necessary then to display related records in each
of the subforms is to set the LinkMasterFields and LinkChildFields properties
of each subform to the name of the field in the main form's recordsource and
the subforms recordsource, respectively.

Based on a query, Subform1 is based on it's table, sub2 on a query, sub3 & 4
I haven't gotten to yet. Trying to get sub1 working first. Then will do the
same for 2-4.

I do have the Master & Child field properties in place for each subform.
But its not working.

To use the combo box to navigate to the selected record, place the following
code in its AfterUpdate event:

DoCmd.OpenForm "YourFormName", , , "IDNumber = " & Me!YourComboBox

changing YourFormName and YourComboBox to the names of your main form and combo box. Once the code is executed, you will be looking at the selected
main form record and the related records in each subform will display.

I've tried this. Still won't work.

DoCmd.Openform "frm_CTAMainView",,,"IDNumber="& Me!CTAName_Combo
I've even tried it without the " "
I put this inthe "after event on the main form - Didn't work and tried it on
the subform -- No Go -- It still prompts me with "Enter IDNumber Number"

Here is the Main Table:
IDNumber
CTAName
Address
City
State
Zip
Email
Phone
Fax
Contact Name
Contact Phone
Contact Fax
Contact Email

AdditionalContacts Table:
IDNumber
Contact Name2
Contact Phone2
Contact Fax2
Contact Email2
Same for Contacts 3 & 4

As a CTAName (Company) could have up to 4 contacts.



:

Hi, melwester.

I'm not entirely clear, but it sounds like you're using an unbound combo box
on your main form to select a customer whose record you would like to view.
Is this correct?

I am assuming that the main form is based on a table or query that includes
the IDNumber. All that is necessary then to display related records in each
of the subforms is to set the LinkMasterFields and LinkChildFields properties
of each subform to the name of the field in the main form's recordsource and
the subforms recordsource, respectively.

To use the combo box to navigate to the selected record, place the following
code in its AfterUpdate event:

DoCmd.OpenForm "YourFormName", , , "IDNumber = " & Me!YourComboBox

changing YourFormName and YourComboBox to the names of your main form and
combo box. Once the code is executed, you will be looking at the selected
main form record and the related records in each subform will display.

If this does not resolve your problem, then my assumptions are incorrect.
In that case, please post the table or query structures of the main and each
subform.

Hope that helps.
Sprinks


:

I have 4 subforms on my Main form and I'm trying to get the IDNumber to come
up on all 4 subforms. When the user clicks on the combo box and finds the
company they want to see info from, the IDNumber shows up on the main form.
This same IDNUmber also should show up on the other forms as well so that the
user will know that additional info is available with that company.

The 4 subforms are from 4 different queries & tables. The Primary Key is
IDNumber.

I'm confused as to where this should be located. Right now I'm trying to
put it in the "Link Master Fields" within the subform.

Here are the different ways I've tried but none work:

Me!RecordSource
=Forms![CTAMainView]![CTAContactsView].Form![IDNumber]
Me!Parent.CTAContactsView!IDnumber
Forms!CTAMainView!CTAContactsView!IDNumber

When I try to run the main form I get a box which says"Enter Parameter
Value" "OK or Cancel". Have not a clue what to put in.

Also tried "=[CTAName_Combo].[Column](1)
Which came up with a syntax error of "
'([__=[CTAName_Combo]].[[Column](1))=IDNumber]

Please help and tell me the correct way of doing this and where it should go.

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top