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!
29 2302
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: - =DCount("*", "tbl_Orders", "Customer_ID = " & Me.Customer_ID)
2) You can filter a form when it opens using the fourth parameter of the - 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.
1) You don't need any code. Just set the the control source of the textbox to: - =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!
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
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 - 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).
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?
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 - =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: - Private Sub btnViewSpecimens_Click()
-
On Error GoTo Err_btnViewSpecimens_Click
-
-
Dim stDocName As String
-
Dim stLinkCriteria As String
-
-
stDocName = "frm_Specimen_Entrainment"
-
-
stLinkCriteria = "[Entrainment_ID]=" & Me![Entrainment_ID]
-
DoCmd.OpenForm stDocName, , , stLinkCriteria
-
-
Exit_btnViewSpecimens_Click:
-
Exit Sub
-
-
Err_btnViewSpecimens_Click:
-
MsgBox Err.Description
-
Resume Exit_btnViewSpecimens_Click
-
-
End Sub
-
The ID field is an autonumber in the Customers table (primary key) and number field in the Orders table (foreign key).
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
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 - =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: - Private Sub btnViewSpecimens_Click()
-
On Error GoTo Err_btnViewSpecimens_Click
-
-
Dim stDocName As String
-
Dim stLinkCriteria As String
-
-
stDocName = "frm_Specimen_Entrainment"
-
-
stLinkCriteria = "[Entrainment_ID]=" & Me![Entrainment_ID]
-
DoCmd.OpenForm stDocName, , , stLinkCriteria
-
-
Exit_btnViewSpecimens_Click:
-
Exit Sub
-
-
Err_btnViewSpecimens_Click:
-
MsgBox Err.Description
-
Resume Exit_btnViewSpecimens_Click
-
-
End Sub
-
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.
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.
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.
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.
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?
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. :(
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?
1) If it's on another form then use [Forms]![frm_Orders]![SpecimenCount]
Urg, still getting #Name? This is my full code now: - =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: - Private Sub Form_Load() 'When form loads go to new record
-
If Not IsNull(Me.OpenArgs) Then
-
Me.DataEntry = True
-
Me!txt_EntrainmentID.DefaultValue = Me.OpenArgs
-
Me.Species_ID.SetFocus
-
End If
-
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.
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 :(
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.
Is the format for the Specimen Count a number?
P.S. Next time when you have seperate questions, make seperate threads.
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?
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.
Can you explain to me again what you want to display in that textbox? Get as detailed as you can.
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.
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
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 :)
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?
Was just about to - thanks! :)
NeoPa 32,556
Expert Mod 16PB
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.
NeoPa 32,556
Expert Mod 16PB
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 - =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. - DSum("[SpecimenCount]", _
-
"[tbl_Orders]", _
-
"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.
NeoPa 32,556
Expert Mod 16PB
Urg, still getting #Name? This is my full code now: - =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: - Private Sub Form_Load() 'When form loads go to new record
-
If Not IsNull(Me.OpenArgs) Then
-
Me.DataEntry = True
-
Me!txt_EntrainmentID.DefaultValue = Me.OpenArgs
-
Me.Species_ID.SetFocus
-
End If
-
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.
- 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 :)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Timothy Madden |
last post by:
Hy
I have destructors that do some functional work in the program flow.
The problem is destructors should only be used for clean-up, because
exceptions might rise at any time, and destructors...
|
by: barb28 |
last post by:
Hello, sometimes I have noticed that
in web addresses, the number:
20%
will show up in the address. I think this is
somehow related to a 'blank space', but am
unsure, does anyone know about...
|
by: Miguel Dias Moura |
last post by:
Hello,
i have a dataSet in an ASP.Net / VB page which connects to a database named
"DB" with fields AuthorName, BookTitle, BookEditor.
I want do display 4 records in my page as follows:...
|
by: Christo |
last post by:
I have this script for showing news on a page, but i want it to only
show the last 10 records, as in the 10 records that were added to the
database last. the script shows the entries in descending...
|
by: Nathan Sokalski |
last post by:
I have tried everything I can think of, I have even tried copying examples
from books and websites I have found, but every Formview control that I have
tried to create does not show the Paging...
|
by: Sanjaylml |
last post by:
I have made a form in Access 2000. In addition to just simply enter the data through form, I have appended sub-form in main form to show the all the entered records as Sub-Form (DataSheet), which is...
|
by: DAHMB |
last post by:
I am not sure if I worded my question correctly. But what I want to do is run a report from two related tables as follows:
The first table is called Category - it has one field, called...
|
by: simonyong |
last post by:
hello, everyone
Sorry for disturb..
im newbie to asp.net
im trying to do a task that as below:
In a SAME datagrid, it ll show all data when user key in key word in textbox...
datagrid will...
|
by: warrior2009 |
last post by:
I have 2 lists (unlinked and unrelated), where one can be called the parent and the other the child with one to many relationship between parent-child. The child table is really big, over a million...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |