Count displayed in textbox

[cbende2]

Hello all,



I have two tables, table1 and table2 (for ease of explaining). Table2 is linked to Table1 with a one-to-many relationship (A record in table1 can have several linked records in table2).

I have a continuous form displaying info from table1, and I'd like to throw a textbox on there to display the count of records that are linked in table2.

So if record 1 in table1 has 5 linked records in table2, I'd like the text box to display "5"

Hope this makes sense, I can explain more if need be.

[John_G]

You can't do this on a continuous form if the textbox is unbound, and the textbox is a part of each record. What you could do is use a form header, put the textbox in the header, and then update its value in the On Current event of the form (using DCount perhaps).

[cbende2]

Darn, not even using a WHERE ID = ForeignID?

[John_G]

Sorry, no. When an unbound control is given a value, ALL occurrences of that control are given that value, so it is not much use in a continuous form.

What you could do is create a summation query using the two tables, just show a count for records in the second table, and base your form on the query. Be sure to set it so that all of table1 records are included, even if there are no matching table2 records (outer join).

However, that query is not updatable, so if you want to use the form for editing table1 data, it won't work.

[cbende2]

Blah, alright so it sounds like I can't do what I'd like it to do.

Thanks John_G, appreciate the quick responses.

[ssanfu]

alright so it sounds like I can't do what I'd like it to do.

Just curious... John_G provided an answer to your question. Is there more to the question?

If you want to be to edit the data in Table1, use John_G's suggestion, then open another form, that would allow editing of the record selected. I do this using the double click event of the first and second controls of the record. Double click either control and an edit form is displayed.

Or you could have an unbound form with a list box (on the left of the form) to display table1 data and the count of records in table 2 and a subform (on the right side) to allow editing the data in table1.