Display Count of Records in a Textbox

TheArgyll

New Member
Joined
Oct 26, 2015
Messages
14
Hello All,
I am continuing work on my database and I am now at the point where I am putting a user interface together.

I have Employees (t_People) who each may have multiple of each of the following:

Addresses (t_Address).
Email Addresses (t_Email).
Telephone Numbers (t_Telephone).

The Employees are stored in one table and the Addresses/Email Addresses/Telephone Numbers are in 3 separate tables. How can I count the number of Addresses/Email Addresses and Telephone Numbers the currently displayed Employee has and set three textboxes on my form to display these values?

Thanks!
Rob
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Hi Denis,
Ideally I would like to display the number of addresses, and when then box is clicked load a second form that allows the user to browse only those x addresses associated with the person on the first form.

I have cobbled this together:

SELECT Count(t_Address.PeopleID) AS CountOfPeopleID
FROM t_People INNER JOIN t_Address ON t_People.PeopleID = t_Address.PeopleID
WHERE [Forms]![frmPeople]![PeopleID] = t_Address.PeopleID;

It's saved as Query1 (I will change this later) and called from Control Source of the Textbox I wish to display my data in.
When I run it, the Textbox reads "#NAME!"

Thanks for replying!
Rob
 
Upvote 0
Textboxes can't hold query results, which is the problem you are having at the moment.
Actually, rephrase that. Textboxes *can* hold query results but you can't have a query as the Control Source of a Textbox.

You can have code that pushes the query result to a textbox but, if you want to pop up a form to view details, I would suggest a button for each Address / Phone / Email that pops up a form with the filtered data. This page on my site shows how the OpenForm command works in VBA.

Alternatively, you can have a subform for each of address / phone / email which is synchronised with the person's ID. When you go to the record, the data is displayed for you and you can edit it in that form. No pop-ups or code required.
To do that, go through these steps:
1. Create a form for Address with multiple records per page. I prefer the Datasheet format. This can have all the fields in the t_address table if you like, but at least it should have the PersonID and address fields.
2. in Design view on the main form, drag and drop the Address form.
3. IMMEDIATELY go to the Properties sheet and see (on the Data tab) whether the Master and Child fields both have PersonID. If not, edit them so they do.
4. Resize the subform to your liking, then save and view the data. As you flick through the Person records you will see all relevant address details for that person. And that subform is editable.
5. Repeat for the other 2 forms.

Denis
 
Upvote 0
I went for the subform, although I used a Single Form instead of Datasheet as it ties in with the appearance of the Parent form I have designed. I am new to Access and that option has definitely made my life over the next couple of weeks a lot easier!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top