Reading a value from a text box

[Intrepid_Yellow]

Greetings all,

So here’s relevant information about my database.

Form (frmCompany_Management) has a combo box to select a company name, this
then shows the details for the chosen company. The company name is displayed
in a text box (txtCompany_Name). There is a button (btnContactDetails) that
loads a form (frmContact_Details). This form has within it a Sub form
(subfrmContact_Details) to display a table with all contacts associated with
the relevant company. Company names are stored in a field called Company_Name
in table (tblCompany_Details) for the Company Management form and then the
sub form refers to table (tblContact_Details) which has the same Company_Name
field (which has a relationship to the other Company_Name field).

And here’s what I want to happen.

When the button is clicked and the Contact Details form is opened I need to
read the Company Name from the text box in the Company Management form and
only display the relevant contacts in the datasheet sub form.

And what exactly do I need?

The one or two lines of code to read the name and only show the relevant
contacts and where exactly to put this piece of code (eg ‘On Click’ of
button, ‘On Load’ for the subform etc). I have some idea of what I need but
can’t seem to get it working.

Anyones help would be appreciated. Thanks.

 

[Albert D. Kallal]

Assuming that the form you open works correctly now

(ie: when you navigate this form, the sub-form shows the related companies).

If the above works now, then is a very easy solution.

The code behind your existing button can simply go

me.refresh
docmd.openReport "frmContact_Details",,,"id = " & me!id

the above assumes that you have a primary key in the table called id (that
is a the default, but change id to whatever you used).

 

[Intrepid_Yellow]

at the moment i have open frmCompany_Management i select a company from my
combo box and it loads the company information into the relevant text boxes
so company name loads into txtCompanyName. i then click btnContactDetails and
it will load frmContactDetails which contains subfrmContactDetails. the
datasheet subform then shows every contact for all companies.

company_id is the primary key for the company_details table
company_name is the field linked to all tables and has no duplicates in this
table

contact_id is the primary key for the contact_details table
and is linked via a relationship with company_name

me.refresh
docmd.openReport "frmContact_Details",,,"id = " & me!id

so if i change your code to read

me.refresh
docmd.openForm "frmContact_Details",,,"Company_Name = " & me!Company_Name

i get an error message that says

Syntax error (missing operator) in query expression 'Company_Name = Microsoft'

 

[Intrepid_Yellow]

Can I use

DoCmd.ApplyFilter , (make Company_Name = text box)

and in OnOpen of subfrmContactDetails

if so what would i write?

CompanyName = frmCompanyManagement.txtCompanyName.value

something like that?

 

[pietlinden]

you're matching a text value then you have to delimit the company name
in single quotes.
Const cQUOTE = "'" ' that's " ' " (without the extra spaces)

docmd.openForm "frmContact_Details",,,"Company_Name=" & cQUOTE &
Me!Company_Name & cQUOTE

 

[Intrepid_Yellow]

Problem solved.

Thanks all to their help.