Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-19-2006, 06:39 AM   #1
hortense76
Registered User
 
Join Date: Mar 2006
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
hortense76 is an unknown quantity at this point
Can't modify/add records because of record source with COUNT function!!!!

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

hortense76 is offline   Reply With Quote
Old 06-19-2006, 07:49 AM   #2
SamDeMan
Registered User
 
Join Date: Aug 2005
Location: New York
Posts: 182
Thanks: 0
Thanked 0 Times in 0 Posts
SamDeMan is on a distinguished road
Quote:
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
SamDeMan is offline   Reply With Quote
Old 06-19-2006, 08:52 AM   #3
hortense76
Registered User
 
Join Date: Mar 2006
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
hortense76 is an unknown quantity at this point
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 is offline   Reply With Quote
Old 06-19-2006, 09:14 AM   #4
hortense76
Registered User
 
Join Date: Mar 2006
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
hortense76 is an unknown quantity at this point
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)
hortense76 is offline   Reply With Quote
Old 06-19-2006, 12:19 PM   #5
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,289 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Try putting the DCount() function described above directly into the ControlSource of the control.
MarkK is offline   Reply With Quote
Old 06-20-2006, 03:36 AM   #6
hortense76
Registered User
 
Join Date: Mar 2006
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
hortense76 is an unknown quantity at this point
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 is offline   Reply With Quote
Old 06-21-2006, 06:21 AM   #7
hortense76
Registered User
 
Join Date: Mar 2006
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
hortense76 is an unknown quantity at this point
Unhappy Still STUCK!

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

hortense76 is offline   Reply With Quote
Old 06-22-2006, 07:40 AM   #8
SamDeMan
Registered User
 
Join Date: Aug 2005
Location: New York
Posts: 182
Thanks: 0
Thanked 0 Times in 0 Posts
SamDeMan is on a distinguished road
Here is my attempt to show the animals example.

please see atttached database.


sam
Attached Files
File Type: zip animals.zip (33.3 KB, 115 views)
SamDeMan is offline   Reply With Quote
Old 06-22-2006, 07:45 AM   #9
SamDeMan
Registered User
 
Join Date: Aug 2005
Location: New York
Posts: 182
Thanks: 0
Thanked 0 Times in 0 Posts
SamDeMan is on a distinguished road
Here is my attempt to show the animals example.

please see atttached database.


sam
SamDeMan is offline   Reply With Quote
Old 06-26-2006, 03:12 AM   #10
hortense76
Registered User
 
Join Date: Mar 2006
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
hortense76 is an unknown quantity at this point
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 is offline   Reply With Quote
Old 06-27-2006, 06:32 AM   #11
hortense76
Registered User
 
Join Date: Mar 2006
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
hortense76 is an unknown quantity at this point
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 is offline   Reply With Quote
Old 06-27-2006, 09:46 AM   #12
hortense76
Registered User
 
Join Date: Mar 2006
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
hortense76 is an unknown quantity at this point
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
hortense76 is offline   Reply With Quote
Old 06-27-2006, 12:45 PM   #13
SamDeMan
Registered User
 
Join Date: Aug 2005
Location: New York
Posts: 182
Thanks: 0
Thanked 0 Times in 0 Posts
SamDeMan is on a distinguished road
Private Sub Form_BeforeInsert(Cancel As Integer)
bolNewEntry = True ' this variable is global to the entire form
End Sub
SamDeMan is offline   Reply With Quote
Old 06-27-2006, 03:58 PM   #14
hortense76
Registered User
 
Join Date: Mar 2006
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
hortense76 is an unknown quantity at this point
it was that simple! Now it all works! thank you Sam!!!

hortense76 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Which event fires AFTER next record is displayed? DKDiveDude Modules & VBA 15 05-09-2013 10:26 AM
TimeElapsed function gives #error on new record only MsLady Modules & VBA 11 06-27-2006 07:07 AM
[SOLVED] Function to count records yaronh Modules & VBA 5 05-27-2002 06:43 AM
Testing the count of related records in the many side table. JHMarshIII Queries 0 04-15-2002 05:52 PM
Sorting by record count in report detail. Terry_C Reports 0 12-07-2001 05:36 AM




All times are GMT -8. The time now is 09:43 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World