By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,067 Members | 717 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,067 IT Pros & Developers. It's quick & easy.

show and tell number of related records

AccessIdiot
100+
P: 493
I'm sure this must be possible but I'm still a newbie and not sure how to do it.

Part One:
Using the good 'ol customers and orders tables and forms example - how would I go about putting a little text box on the customers form that shows, for any given record (customer), the number of related orders that customer has?

In pseudocode its something like count the number of records in Orders where Customer_ID = X.

Would the code go in the VB section or in the Properties panel of a textbox that was bound by a query?

Part Two:
I would like to have a button on the Customers form that says "show me the orders related to this customer". This would open launch the Orders form but would only have those records related to that Customer_ID. In other words, there could be 75 records in the Orders table but clicking that button only shows records 17-23 (for example). The user would still have the ability to add new records but only for that same customer id.

How difficult is this?

Thanks for any help!
Apr 25 '07 #1
Share this Question
Share on Google+
29 Replies

Rabbit
Expert Mod 10K+
P: 12,432
I'm sure this must be possible but I'm still a newbie and not sure how to do it.

Part One:
Using the good 'ol customers and orders tables and forms example - how would I go about putting a little text box on the customers form that shows, for any given record (customer), the number of related orders that customer has?

In pseudocode its something like count the number of records in Orders where Customer_ID = X.

Would the code go in the VB section or in the Properties panel of a textbox that was bound by a query?

Part Two:
I would like to have a button on the Customers form that says "show me the orders related to this customer". This would open launch the Orders form but would only have those records related to that Customer_ID. In other words, there could be 75 records in the Orders table but clicking that button only shows records 17-23 (for example). The user would still have the ability to add new records but only for that same customer id.

How difficult is this?

Thanks for any help!
1) You don't need any code. Just set the the control source of the textbox to:
Expand|Select|Wrap|Line Numbers
  1. =DCount("*", "tbl_Orders", "Customer_ID = " & Me.Customer_ID)
2) You can filter a form when it opens using the fourth parameter of the
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm , , , "Customer_ID = " Me.Customer_ID
You might also want to pass the customer ID in the OpenArgs. Then on that form either disable or not have a field for customer ID and in the On Current event, set the Customer_ID to the OpenArgs, as long as OpenArgs is not null of course.
Apr 25 '07 #2

AccessIdiot
100+
P: 493
1) You don't need any code. Just set the the control source of the textbox to:
Expand|Select|Wrap|Line Numbers
  1. =DCount("*", "tbl_Orders", "Customer_ID = " & Me.Customer_ID)
The textbox is showing "#Name?" Is this because I don't have any records yet? I was hoping it would default to 0 if it were a new customer say, or a customer that didn't have any orders. Or does it mean I don't have the syntax right?

thanks!
Apr 25 '07 #3

Rabbit
Expert Mod 10K+
P: 12,432
The textbox is showing "#Name?" Is this because I don't have any records yet? I was hoping it would default to 0 if it were a new customer say, or a customer that didn't have any orders. Or does it mean I don't have the syntax right?

thanks!
Sorry, take the Me. off of Me.Customer_ID
Apr 25 '07 #4

AccessIdiot
100+
P: 493
Worked beautifully. But I would have guessed using Me also. Why didn't I have to?

2) You can filter a form when it opens using the fourth parameter of the
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm , , , "Customer_ID = " Me.Customer_ID
*edit* nevermind, no compile error, but it doesn't open the selected record set. Instead I get a blank record and the Orders.Customer_ID is 55 (though it should be 8).
Apr 25 '07 #5

Rabbit
Expert Mod 10K+
P: 12,432
Worked beautifully. But I would have guessed using Me also. Why didn't I have to?



*edit* nevermind, no compile error, but it doesn't open the selected record set. Instead I get a blank record and the Orders.Customer_ID is 55 (though it should be 8).
1) The Me is a reference to the form or report that triggered an event. So because this was done outside of code, the Me reference doesn't exist.

Also, even within code you don't really need the Me. The visual basic editor makes an inference of Me and only rarely is it wrong.

2) What's the code for the entire line you use to open the form?

And is Customer_ID text or number?
Apr 25 '07 #6

AccessIdiot
100+
P: 493
Also, back to #1 for a second. I have a "count" field on the Orders form - for example if the customer orders 5 of the exact same thing. So I need to multiply the number of records by the count so I can see, for example, that Customer X ordered 5 widgets. There is only one record for the widget, but the count column recorded 5 of them.

I tried
Expand|Select|Wrap|Line Numbers
  1. =DCount("*","tbl_Orders","Customer_ID = " & [Customer_ID])*[tbl_Orders.Count] 
but it didn't like it. :(

#2 I went through the button wizard and it came up with this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnViewSpecimens_Click()
  2. On Error GoTo Err_btnViewSpecimens_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "frm_Specimen_Entrainment"
  8.  
  9.     stLinkCriteria = "[Entrainment_ID]=" & Me![Entrainment_ID]
  10.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  11.  
  12. Exit_btnViewSpecimens_Click:
  13.     Exit Sub
  14.  
  15. Err_btnViewSpecimens_Click:
  16.     MsgBox Err.Description
  17.     Resume Exit_btnViewSpecimens_Click
  18.  
  19. End Sub
  20.  
The ID field is an autonumber in the Customers table (primary key) and number field in the Orders table (foreign key).
Apr 25 '07 #7

AccessIdiot
100+
P: 493
oops sorry, I copied and pasted and realized I've got oranges instead of apples there.

Customers = tbl_Entrainment (or frm_Entrainment)
Orders = tbl_Specimen_Entrainment (or frm_Specimen_Entrainment)

Customer_ID = Entrainment_ID for both tables
Apr 25 '07 #8

Rabbit
Expert Mod 10K+
P: 12,432
Also, back to #1 for a second. I have a "count" field on the Orders form - for example if the customer orders 5 of the exact same thing. So I need to multiply the number of records by the count so I can see, for example, that Customer X ordered 5 widgets. There is only one record for the widget, but the count column recorded 5 of them.

I tried
Expand|Select|Wrap|Line Numbers
  1. =DCount("*","tbl_Orders","Customer_ID = " & [Customer_ID])*[tbl_Orders.Count] 
but it didn't like it. :(

#2 I went through the button wizard and it came up with this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnViewSpecimens_Click()
  2. On Error GoTo Err_btnViewSpecimens_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "frm_Specimen_Entrainment"
  8.  
  9.     stLinkCriteria = "[Entrainment_ID]=" & Me![Entrainment_ID]
  10.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  11.  
  12. Exit_btnViewSpecimens_Click:
  13.     Exit Sub
  14.  
  15. Err_btnViewSpecimens_Click:
  16.     MsgBox Err.Description
  17.     Resume Exit_btnViewSpecimens_Click
  18.  
  19. End Sub
  20.  
The ID field is an autonumber in the Customers table (primary key) and number field in the Orders table (foreign key).
1) So is Count a control on the form or a field on the table?
You also shouldn't use Count as a name as that's often used as properties for many objects in Access.

2) The code should open the form and filter all records where the entrainment ID is equal to the entrainment ID entered on the first form in the field/control called entrainment ID.
Apr 25 '07 #9

AccessIdiot
100+
P: 493
1) So is Count a control on the form or a field on the table?
You also shouldn't use Count as a name as that's often used as properties for many objects in Access.
Absolutely right - I just changed it to "SpecimenCount". It is a field in the table and a textbox on the Specimen_Entrainment form. I figured I had to name the table in the pathway of the formula. Better to use the form name? Even if I use frm_Specimen_Entrainment.SpecimenCount I am still back to #Name? so there must be an error in my formula somewhere?

2) The code should open the form and filter all records where the entrainment ID is equal to the entrainment ID entered on the first form in the field/control called entrainment ID.
Yes that would be great! Unfortunately it doesn't. If my Entrainment_ID on frm_Entrainment (autonumber) = 8 then when I click the button I get frm_Specimen_Entrainment opening to a blank record with Entrainment_ID = 55.
Apr 25 '07 #10

AccessIdiot
100+
P: 493
I did just notice however that at the bottom of frm_Specimen_Entrainment where the navigation buttons are that it does say "Record 1 of 1 (Filtered)" and that in the Properties Panel on the Data tab the Filter says Entrainment_ID = 8.

So it seems like it is sort of working, its just not pulling the appropriate records or stating the correct Entrainment_ID.
Apr 25 '07 #11

AccessIdiot
100+
P: 493
If I pass the Entrainment_ID as OpenArgs I get the correct Entrainment_ID on the Specimen_Entrainment form but not the correct record set.
Apr 25 '07 #12

Rabbit
Expert Mod 10K+
P: 12,432
Absolutely right - I just changed it to "SpecimenCount". It is a field in the table and a textbox on the Specimen_Entrainment form. I figured I had to name the table in the pathway of the formula. Better to use the form name? Even if I use frm_Specimen_Entrainment.SpecimenCount I am still back to #Name? so there must be an error in my formula somewhere?



Yes that would be great! Unfortunately it doesn't. If my Entrainment_ID on frm_Entrainment (autonumber) = 8 then when I click the button I get frm_Specimen_Entrainment opening to a blank record with Entrainment_ID = 55.
You'd leave off the FormName., you don't need it. Just [SpecimenCount] is all you need.

Well, I was gonna say that it's using the character '8' but I just looked up the ASCII code and '8' is 56 so that shot down that theory. But it's pretty close so I'm still suspicious.

What happens with other Entrainment_IDs?
Apr 25 '07 #13

AccessIdiot
100+
P: 493
You'd leave off the FormName., you don't need it. Just [SpecimenCount] is all you need.
Hmmm, not working. I was thinking because the code is going on a field in one form but the SpecimenCount field is on another form. How does Access know where SpecimenCount is? To go back to the Customers/Orders analogy, it's like OrdersCount is on the Orders form but I have Number of Orders field on the Customers form. Does that make sense?

Well, I was gonna say that it's using the character '8' but I just looked up the ASCII code and '8' is 56 so that shot down that theory. But it's pretty close so I'm still suspicious.

What happens with other Entrainment_IDs?
Same thing unfortunately. :(
Apr 25 '07 #14

Rabbit
Expert Mod 10K+
P: 12,432
Hmmm, not working. I was thinking because the code is going on a field in one form but the SpecimenCount field is on another form. How does Access know where SpecimenCount is? To go back to the Customers/Orders analogy, it's like OrdersCount is on the Orders form but I have Number of Orders field on the Customers form. Does that make sense?



Same thing unfortunately. :(
1) If it's on another form then use [Forms]![frm_Orders]![SpecimenCount]

2) By same thing do you mean it's still filtering for records where Entrainment ID = 55? No matter what your Entrainment ID actually is?
Apr 25 '07 #15

AccessIdiot
100+
P: 493
1) If it's on another form then use [Forms]![frm_Orders]![SpecimenCount]
Urg, still getting #Name? This is my full code now:
Expand|Select|Wrap|Line Numbers
  1. =DCount("*","tbl_Specimen_Entrainment","Entrainment_ID = " & [Entrainment_ID])*Forms!frm_Specimen_Entrainment!SpecimenCount
Interesting - when I wrote it I used the brackets as you suggested but Access changed it to the above.

2) By same thing do you mean it's still filtering for records where Entrainment ID = 55? No matter what your Entrainment ID actually is?
Yes. If I pass Entrainment_ID as OpenArgs then I get the correct Entrainment_ID but it isn't filtering any records. It just opens to a blank clean record and says "1 of 1".

I do have an on load event on this form:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()                                                                 'When form loads go to new record
  2. If Not IsNull(Me.OpenArgs) Then
  3.         Me.DataEntry = True
  4.         Me!txt_EntrainmentID.DefaultValue = Me.OpenArgs
  5.         Me.Species_ID.SetFocus
  6.     End If
  7. End Sub
This is because I have another button to add a new Specimen but I am also trying to show Specimens that already exist. I tried commenting out the code thinking it was interfering but it's still doing the same thing.
Apr 25 '07 #16

AccessIdiot
100+
P: 493
I think I am seeing my own problem. On frm_Specimen_Entrainment the control source is Entrainment_ID but the name is txt_Entrainment_ID. Argh! Let me fix that and see if it helps.

*edit* no dice :(
Apr 25 '07 #17

Rabbit
Expert Mod 10K+
P: 12,432
Wait, you passed it both in the OpenArgs AND using the filter argument (fourth parameter) right?

The fourth parameter to filter and the the OpenArgs to set the default value?

Also, it doesn't matter if you use the control name or the field name, it it's bound they both return the same value.
Apr 25 '07 #18

Rabbit
Expert Mod 10K+
P: 12,432
Is the format for the Specimen Count a number?

P.S. Next time when you have seperate questions, make seperate threads.
Apr 25 '07 #19

AccessIdiot
100+
P: 493
Wait, you passed it both in the OpenArgs AND using the filter argument (fourth parameter) right?

The fourth parameter to filter and the the OpenArgs to set the default value?
Yes. When I do this, if I keep hitting the "next record" (not the "new record" button but "next") then it keeps adding records with that Entrainment_ID value to the Specimen_Entrainment table.

Is the format for the Specimen Count a number?

P.S. Next time when you have seperate questions, make seperate threads.
Sorry. :( I felt that since they were related questions it would be easier to combine them. One question about how to show the number of records in one table related to a record in another table and 2nd, how to show those records.

SpecimenCount is a number, yes, a long integer.

Would it be easier to move this one to a different/new thread?
Apr 25 '07 #20

Rabbit
Expert Mod 10K+
P: 12,432
Yes. When I do this, if I keep hitting the "next record" (not the "new record" button but "next") then it keeps adding records with that Entrainment_ID value to the Specimen_Entrainment table.



Sorry. :( I felt that since they were related questions it would be easier to combine them. One question about how to show the number of records in one table related to a record in another table and 2nd, how to show those records.

SpecimenCount is a number, yes, a long integer.

Would it be easier to move this one to a different/new thread?
It's not a problem. If you think about it they're seperate questions. Each problem does not rely on each other. You can solve one problem without ever having to think about the other problem.

We'll leave it as it is, the posts are intertwined with each other.
Apr 25 '07 #21

Rabbit
Expert Mod 10K+
P: 12,432
Can you explain to me again what you want to display in that textbox? Get as detailed as you can.
Apr 25 '07 #22

AccessIdiot
100+
P: 493
I think I'm going to skip the SpecimenCount multiplier for now. I think it would be much easier just to show how many records in Specimen_Entrainment are related to a single record in Entrainment. I know that works and I can always use the multiplier in a report setting instead.

So for now I just need to work on clicking a button that displays only those Specimen_Entrainment records that are related to a single Entrainment record.

Thanks Rabbit and sorry for the confusion.
Apr 25 '07 #23

Rabbit
Expert Mod 10K+
P: 12,432
You're setting the data entry property to Yes when the form loads, that's what's preventing the older data from being seen. Data Entry means you can only add new records and edit what's been added since that form's been open.

Get rid of that line in the code and go into design view of the form and set it back to no. If it needs to default to yes then in the code set it to no.

For your main menu you might want to set the record selector property of the form to no. That gets rid of the gray bar on the far left. Also you might want to prevent the main form from opening if they don't have the mousehook.dll
Apr 25 '07 #24

AccessIdiot
100+
P: 493
That did it! I never knew what DataEntry meant. I think someone helped me with another issue and gave me that code.

One problem however - let's say there are 2 Specimen_Entrainment records for Entrainment_ID = 8. If I keep hitting the "next" navigation button it continues to add new records in the Specimen_Entrainment table, even if I don't add any data. Is there any way to prevent this?

Thanks for the tip on the Record Selector property too - another feature I didn't know about.

Cheers!
Melissa :)
Apr 25 '07 #25

Rabbit
Expert Mod 10K+
P: 12,432
That did it! I never knew what DataEntry meant. I think someone helped me with another issue and gave me that code.

One problem however - let's say there are 2 Specimen_Entrainment records for Entrainment_ID = 8. If I keep hitting the "next" navigation button it continues to add new records in the Specimen_Entrainment table, even if I don't add any data. Is there any way to prevent this?

Thanks for the tip on the Record Selector property too - another feature I didn't know about.

Cheers!
Melissa :)
Glad to help.

This is a new question, could you make a new post for it?
Apr 26 '07 #26

AccessIdiot
100+
P: 493
Was just about to - thanks! :)
Apr 26 '07 #27

NeoPa
Expert Mod 15k+
P: 31,770
Can you post a link in here to the new thread too please Melissa.
I've just caught up with this so I'll add my twopennorth if I can.
May 2 '07 #28

NeoPa
Expert Mod 15k+
P: 31,770
Also, back to #1 for a second. I have a "count" field on the Orders form - for example if the customer orders 5 of the exact same thing. So I need to multiply the number of records by the count so I can see, for example, that Customer X ordered 5 widgets. There is only one record for the widget, but the count column recorded 5 of them.

I tried
Expand|Select|Wrap|Line Numbers
  1. =DCount("*","tbl_Orders","Customer_ID = " & [Customer_ID])*[tbl_Orders.Count] 
but it didn't like it. :(
DCount() will simply count the number of records found in your filtered recordset. This is fine if you have one item per record, but if you actually need to sum various figures from a whole set of records you need the DSum() Domain Aggregate function.
Expand|Select|Wrap|Line Numbers
  1. DSum("[SpecimenCount]", _
  2.      "[tbl_Orders]", _
  3.      "Customer_ID=" & Me.Customer_ID)
NB. Because the Me.Customer_ID is added to the string within the VBA code it is perfectly valid. I don't know the names of your controls, but if that's the name of your control it should work. If it were inside the literal string then it would be passed to the function to interpret, but in this case only the result should be passed to the function.
May 2 '07 #29

NeoPa
Expert Mod 15k+
P: 31,770
Urg, still getting #Name? This is my full code now:
Expand|Select|Wrap|Line Numbers
  1. =DCount("*","tbl_Specimen_Entrainment","Entrainment_ID = " & [Entrainment_ID])*Forms!frm_Specimen_Entrainment!SpecimenCount
Interesting - when I wrote it I used the brackets as you suggested but Access changed it to the above.



Yes. If I pass Entrainment_ID as OpenArgs then I get the correct Entrainment_ID but it isn't filtering any records. It just opens to a blank clean record and says "1 of 1".

I do have an on load event on this form:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()                                                                 'When form loads go to new record
  2. If Not IsNull(Me.OpenArgs) Then
  3.         Me.DataEntry = True
  4.         Me!txt_EntrainmentID.DefaultValue = Me.OpenArgs
  5.         Me.Species_ID.SetFocus
  6.     End If
  7. End Sub
This is because I have another button to add a new Specimen but I am also trying to show Specimens that already exist. I tried commenting out the code thinking it was interfering but it's still doing the same thing.
Expand|Select|Wrap|Line Numbers
  1. Me!txt_EntrainmentID.DefaultValue = Me.OpenArgs
sets a value inside the record that the form is showing. This makes the buffer dirty so a record will be added any time you move off that record.
This can be got around, but is one of the 'little' gotchas of doing things the way you're doing them.
Don't be put off though, with careful consideration you can code a way around this. You seem to be getting past some fairly heavyweight stuff recently. Keep it up :)
May 2 '07 #30

Post your reply

Sign in to post your reply or Sign up for a free account.