G
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!
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!