Linking to field in a subform used in multiple forms.

[GolfProRM]

Design:

Tables:
Customer
Researches (linked by Customer ID)
Research Contacts (linked by Research ID)
Frequently Used Contacts

Forms:
Add Customer
-> Subform: Add Research
-> Sub-Subform: Research Contacts

Edit Research
-> Subform: Research Contacts (using same subform as above)

I am building a form that will allow users to enter a research request.
The main form is linked to the Customer table. Subform Add Research
links back to the research and it has a subform Research Contacts.

My issue is with the Research Contacts Subform.

I have four text boxes (Company Name, Contact Person, Phone#,
Extension), and a ComboBox that links to the Frequently Used Contacts.
I'd like the user to have the option of entering the contact
information manually (no issues here), or select a contact from the
drop-down list and have it pre-fill the information from the Frequently
Used Contacts Table.

Currently, the only way I can figure out how to do this is to have a
query based on the Frequently Used Contacts table that pulls the
Contact name from the ComboBox as a lookup. I've linked the query to a
hidden form that lists out the information (Company Name, Contact
Person, Phone#, Extension)

I use this code to open the form (thus running the query) and paste the
data into my current fields.
DoCmd.OpenForm "frmIdentifyCompany"
Forms!frmIdentifyCompany.Visible = False
Me.Company = Forms!frmIdentifyCompany![CompanyName]
Me.Contact = Forms!frmIdentifyCompany![Contact]
Me.ContactPhone = Forms!frmIdentifyCompany![ContactPhone]
Me.Extension = Forms!frmIdentifyCompany![Extension]
DoCmd.Close acForm, "frmIdentifyCompany"
Me.cmboCompanyName = ""

First question: Is this the easiest way to accomplish what I want to
do? Or is there a better way?

Question 2: If this is the best way, how to I setup my query to lookup
from the combobox no matter which form has it open?
The syntax I can get to work is this:
Forms![frmAddCustomer]![subfrmAddResReq].Form![subfrmResearchContact].Form![cmboCompanyName]

Obviously this only works from the Add Customer form, and won't work
from the Edit Research form. I'd like it to work from both without
having to make 2 different subforms that do the same thing.

Thanks for any help!

 

[GolfProRM]

Worked like a charm! Thanks for the quick reply!

Question 1:
No, this is a very inefficient way to accomplish what you want. There is no
need to pay the overhead of loading a form. The better way is to use a query
within your form to load the information directly from the query or table to
your form:

Dim dbs As DAO.Database
Dim qdf As QueryDef

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qselCompanyInfo")
qdf.Parameters(0) = Me.Company
Set rst = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
If rst.RecordCount > 0 Then
Me.Company = rst![CompanyName]
Me.Contact = rst![Contact]
Me.ContactPhone = rst![ContactPhone]
Me.Extension = rst![Extension]
Else
Msgbox "Company Info Not Found"
End If
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set dbf = Nothing

Question2:
Notice in the above code this line:
qdf.Parameters(0) = Me.Company

You can set a non specific parameter in a query that you can then pass a
value to as I have done in the code above. Just put something like [Enter
Company] in the criteria row of the field you are wanting to use as a
parameter. That way, it doesn't matter where you call the query from. If
you try to hard code a form/control reference in the query, then only one
form will be able to use it.

Design:

Tables:
Customer
Researches (linked by Customer ID)
Research Contacts (linked by Research ID)
Frequently Used Contacts

Forms:
Add Customer
-> Subform: Add Research
-> Sub-Subform: Research Contacts

Edit Research
-> Subform: Research Contacts (using same subform as above)

I am building a form that will allow users to enter a research request.
The main form is linked to the Customer table. Subform Add Research
links back to the research and it has a subform Research Contacts.

My issue is with the Research Contacts Subform.

I have four text boxes (Company Name, Contact Person, Phone#,
Extension), and a ComboBox that links to the Frequently Used Contacts.
I'd like the user to have the option of entering the contact
information manually (no issues here), or select a contact from the
drop-down list and have it pre-fill the information from the Frequently
Used Contacts Table.

Currently, the only way I can figure out how to do this is to have a
query based on the Frequently Used Contacts table that pulls the
Contact name from the ComboBox as a lookup. I've linked the query to a
hidden form that lists out the information (Company Name, Contact
Person, Phone#, Extension)

I use this code to open the form (thus running the query) and paste the
data into my current fields.
DoCmd.OpenForm "frmIdentifyCompany"
Forms!frmIdentifyCompany.Visible = False
Me.Company = Forms!frmIdentifyCompany![CompanyName]
Me.Contact = Forms!frmIdentifyCompany![Contact]
Me.ContactPhone = Forms!frmIdentifyCompany![ContactPhone]
Me.Extension = Forms!frmIdentifyCompany![Extension]
DoCmd.Close acForm, "frmIdentifyCompany"
Me.cmboCompanyName = ""

First question: Is this the easiest way to accomplish what I want to
do? Or is there a better way?

Question 2: If this is the best way, how to I setup my query to lookup
from the combobox no matter which form has it open?
The syntax I can get to work is this:
Forms![frmAddCustomer]![subfrmAddResReq].Form![subfrmResearchContact].Form![cmboCompanyName]

Obviously this only works from the Add Customer form, and won't work
from the Edit Research form. I'd like it to work from both without
having to make 2 different subforms that do the same thing.

Thanks for any help!

 

[OfficeDev18 via AccessMonster.com]

An easier way to get the info via the combo box is to get it directly. It
looks to me that your combo box's row source is set to the Contact Person
field in the Frequently Used Contacts table. Change this as follows:
1-Change the Column Count property in the property sheet to 4.
2-Change the Column Widths property to 1";0";0";0" That's 1 inch, 0 inches, 0
inches, and 0 inches. This makes column 1 visible and the other columns
invisible. Set the Row Source to include all four required fields from the
FUC table (pardon my French). Let's cast them in concrete: Columnn 1 is still
the Contact Person, column 2 is the Company Name, column 3 is the Phone#, and
column 4 is the Extension.
3-In the combo box's After Update event, have code that populates the text
boxes from the combo box's columnar information, to wit:

Me.Contact = ComboBoxName.Column(1)
Me.Company = ComboBoxName.Column(2)
Me.ContactPhone = ComboBoxName.Column(3), and
Me.Extension = ComboBoxName.Column(4)

Doing it this way doesn't need a hidden form. Also, you don't need any
external queries to do the work. Everything's right in your combo box.

Hope this helps,

Sam

Design:

Tables:
Customer
Researches (linked by Customer ID)
Research Contacts (linked by Research ID)
Frequently Used Contacts

Forms:
Add Customer
-> Subform: Add Research
-> Sub-Subform: Research Contacts

Edit Research
-> Subform: Research Contacts (using same subform as above)

I am building a form that will allow users to enter a research request.
The main form is linked to the Customer table. Subform Add Research
links back to the research and it has a subform Research Contacts.

My issue is with the Research Contacts Subform.

I have four text boxes (Company Name, Contact Person, Phone#,
Extension), and a ComboBox that links to the Frequently Used Contacts.
I'd like the user to have the option of entering the contact
information manually (no issues here), or select a contact from the
drop-down list and have it pre-fill the information from the Frequently
Used Contacts Table.

Currently, the only way I can figure out how to do this is to have a
query based on the Frequently Used Contacts table that pulls the
Contact name from the ComboBox as a lookup. I've linked the query to a
hidden form that lists out the information (Company Name, Contact
Person, Phone#, Extension)

I use this code to open the form (thus running the query) and paste the
data into my current fields.
DoCmd.OpenForm "frmIdentifyCompany"
Forms!frmIdentifyCompany.Visible = False
Me.Company = Forms!frmIdentifyCompany![CompanyName]
Me.Contact = Forms!frmIdentifyCompany![Contact]
Me.ContactPhone = Forms!frmIdentifyCompany![ContactPhone]
Me.Extension = Forms!frmIdentifyCompany![Extension]
DoCmd.Close acForm, "frmIdentifyCompany"
Me.cmboCompanyName = ""

First question: Is this the easiest way to accomplish what I want to
do? Or is there a better way?

Question 2: If this is the best way, how to I setup my query to lookup
from the combobox no matter which form has it open?
The syntax I can get to work is this:
Forms![frmAddCustomer]![subfrmAddResReq].Form![subfrmResearchContact].Form![cmboCompanyName]

Obviously this only works from the Add Customer form, and won't work
from the Edit Research form. I'd like it to work from both without
having to make 2 different subforms that do the same thing.

Thanks for any help!