Can't modify/add records because of record source with COUNT function!!!! (1 Viewer)

hortense76 · Jun 19, 2006

I modified a form and now it seems it's impossible to modify/add records. It seems that all comes from when I modified my record source query to add a a field in which it COUNTs all related records in another table. Now the form can't modify anything else but I need to change other fields or add new records!
In other words: how can I add a simple box which, for every record of my table/form, counts the number of related records in another table (relationship is 1-to-many, and of course is based on a field in table2) letting me modify and add records?
Thank you

SamDeMan · Jun 19, 2006

how can I add a simple box which, for every record of my table/form, counts the number of related records in another table

what you can do is on the Form_Current add this:
me.txtMyCount = Dcount([ValueToBeCounted],[RelatedTable],"condition optional)
and create a text box that is called: txtMyCount

now you can change back the controlsource of the form.

good luck,

sam

hortense76 · Jun 19, 2006

Thanks Sam for your help, it was really interesting BUT it didn't work.
Perhaps I forgot to tell you my form is a "all record view form" (sorry, I'm trying to translate italian Access words in English) and I want that txtbox for EVERY record to have a fast glance of this stats.
The way you tought me changed the value of txtbox in ALL records at a time to the same value (the "correct" value of the current record), but I want every one with ITS value.
...I think I'm back to the start.
I thought to create a Subform just for that d****d txtbox but as you know Access doesn't let me put a subform in a "all records view" form!!!! HELP!!!!

hortense76 · Jun 19, 2006

Just to explain better:
Let's say I'm God and I have the table AnimalFamilies which is linked to the table AnimalSpecies: 1-to-many of course.
I build my Form AnimalFamilies and I want it in the "all-records-consequent-view" to keep track of every family I add and modify.
But I also want every row to show the number of Species I created in that very Family... that's veryverysimple but believe me I couldn't (yes, I'm not God...)
Help me please! (to both God and you)

MarkK · Jun 19, 2006

Try putting the DCount() function described above directly into the ControlSource of the control.

hortense76 · Jun 20, 2006

Thank you lagbolt, but I couldn't make it work (error in syntax), perhaps because Access don't let me use Dcount in the ControlSource...
Back to the start...

hortense76 · Jun 21, 2006

Still STUCK!

Please someone help me! I'm completely sure it's a problem so simple to solve but I'm stuck! Tahnk you

SamDeMan · Jun 22, 2006

Here is my attempt to show the animals example.

please see atttached database.

sam

SamDeMan · Jun 22, 2006

Here is my attempt to show the animals example.

please see atttached database.

sam

hortense76 · Jun 26, 2006

Thank you Sam! I've been away from work but now I see your post. I see that your example works perfectly. I wonder why I couldn't. Now I'm trying but now I really can't tell which detail could have made the difference...
Anyway, Thanks a lot.

hortense76 · Jun 27, 2006

Ok, I made it.
Only 1 little problem: refresh is done only when I go to the record, but anyway, anything else works. The difference is you made a new field DIRECTLY in the table. That was the simplest way. Thank you very much

hortense76 · Jun 27, 2006

Thank you a lot Sam, but I found another problem: your system is great but in some manner triggers the update of table when I'm entering a new record, because it assigns some values to real table fields. The result is quite bad because of some conflict with other processes connected to new fields I used in my form. Using it in the afterupdate event change the prpblem in another one: now it doesn't make the count when I simply enter in a record to see it...
Perhaps solution is telling him that if this is a new record he don't have to make anthing. Bu How to check if current record is a new one?
Thanks

SamDeMan · Jun 27, 2006

Private Sub Form_BeforeInsert(Cancel As Integer)
bolNewEntry = True ' this variable is global to the entire form
End Sub

hortense76 · Jun 28, 2006

it was that simple! Now it all works! thank you Sam!!!

Can't modify/add records because of record source with COUNT function!!!! (1 Viewer)

hortense76

Registered User.

SamDeMan

Registered User.

hortense76

Registered User.

hortense76

Registered User.

MarkK

bit cruncher

hortense76

Registered User.

hortense76

Registered User.

SamDeMan

Registered User.

Attachments

SamDeMan

Registered User.

hortense76

Registered User.

hortense76

Registered User.

hortense76

Registered User.

SamDeMan

Registered User.

hortense76

Registered User.

Similar threads

Users who are viewing this thread