Can't modify/add records because of record source with COUNT function!!!!

hortense76

Registered User.
Local time
Today, 13:01
Joined
Mar 16, 2006
Messages
21
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
 
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
 
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!!!!
 
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)
 
Try putting the DCount() function described above directly into the ControlSource of the control.
 
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...
 
Still STUCK!

Please someone help me! I'm completely sure it's a problem so simple to solve but I'm stuck! Tahnk you
 
Here is my attempt to show the animals example.

please see atttached database.


sam
 

Attachments

Here is my attempt to show the animals example.

please see atttached database.


sam
 
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.
 
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
 
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
 
Private Sub Form_BeforeInsert(Cancel As Integer)
bolNewEntry = True ' this variable is global to the entire form
End Sub
 

Users who are viewing this thread

Back
Top Bottom